How to mantain the choice of a drop-down list for more inserction rows in UI/SQL table?

[QGIO]

Hi,

I have a flow containing a drop-down node with a list of instruments.
I want to select an instrument of the drop-down list, and for this instrument insert more than one row in a dedicated table (three data are sent by a form node) .
The problem I have is the following: I need always to select the instrument from the drop list, even if I want to insert more than one row for the same instrument.

Is there a way to avoid to select each time the instrument from the drop list and so add more than one row for that instrument?

Many thanks

[jbudd]

@QGIO

for this instrument insert more than one row in a dedicated table

Are you talking about inserting rows in a database table?

Can you show us a screenshot of your dashboard to illustrate the problem, and an export of your flow?

[QGIO]

Yes, database table (which is shown also in the UI).

This is a part of a complex flow in progress. I tried to extract the part on which is focused the post.

[{"id":"e23226821db52820","type":"ui_dropdown","z":"d05f249a6f99c6bf","name":"","label":"Cerca Strumento","tooltip":"","place":"","group":"010506b862f616aa","order":3,"width":4,"height":1,"passthru":true,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"options","topicType":"msg","className":"","x":370,"y":380,"wires":[["b4b550601c3abde5"]]},{"id":"ba20981416b74e3e","type":"ui_table","z":"d05f249a6f99c6bf","group":"2699aa19a99f500b","name":"Date Controllo","order":1,"width":20,"height":"9","columns":[{"field":"DATAULTIMOCONTROLLO","title":"Data Ultimo Controllo","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"DATASCADENZA","title":"Data Scadenza","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"RESPONSABILE","title":"Responsabile","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"STATON","title":"Stato","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"ID_Scad","title":"ID","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":1,"cts":true,"x":1160,"y":1060,"wires":[[]]},{"id":"c13df134ef23db78","type":"MSSQL","z":"d05f249a6f99c6bf","mssqlCN":"6fb70e68d89e36c4","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"SELECT CODIFICA FROM [DB_Strumentazione].[dbo].[StrumentazioneAttrezzature]","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[{"output":true,"name":"CODIFICA","type":"VarChar","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":240,"y":180,"wires":[["6e0a1c706a7d2fe4","380938770ffb58d4"]]},{"id":"6e0a1c706a7d2fe4","type":"debug","z":"d05f249a6f99c6bf","name":"Out DB","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":460,"y":200,"wires":[]},{"id":"4e062855148f77d2","type":"ui_form","z":"d05f249a6f99c6bf","name":"","label":"","group":"04fee1bcfe3710a2","order":2,"width":6,"height":1,"options":[{"label":"DATA ULTIMO CONTROLLO","value":"DATAULTIMOCONTROLLO","type":"date","required":true,"rows":null},{"label":"RESPONSABILE","value":"RESPONSABILE","type":"text","required":true,"rows":null},{"label":"NOTE","value":"NOTE","type":"text","required":false,"rows":null}],"formValue":{"DATAULTIMOCONTROLLO":"","RESPONSABILE":"","NOTE":""},"payload":"","submit":"INVIA","cancel":"CANCEL","topic":"topic2","topicType":"str","splitLayout":"","className":"","x":470,"y":860,"wires":[["3d853a67cbf1fdef","0113663611b555b1"]]},{"id":"ece9c901994b0e20","type":"debug","z":"d05f249a6f99c6bf","name":"Form Out","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":660,"y":980,"wires":[]},{"id":"b4b550601c3abde5","type":"function","z":"d05f249a6f99c6bf","name":"","func":"pld =       \"SELECT ID \"  \npld = pld + \"FROM [DB_Strumentazione].[dbo].[StrumentazioneAttrezzature]\"\npld = pld + \"WHERE CODIFICA = '\"+msg.payload+\"'\"\n\nmsg.payload = pld\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":560,"wires":[["ccea13996967a098","57ec03dc7bac852f"]]},{"id":"ccea13996967a098","type":"MSSQL","z":"d05f249a6f99c6bf","mssqlCN":"6fb70e68d89e36c4","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"SELECT CODIFICA, MODELLO, FREQUENZATARATURA FROM [DB_Strumentazione].[dbo].[StrumentazioneAttrezzature]\nWHERE CODIFICA = ","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"msg","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[],"x":520,"y":560,"wires":[["cab67d7702b99345","8deadb13452e4b3d"]]},{"id":"57ec03dc7bac852f","type":"debug","z":"d05f249a6f99c6bf","name":"Out func 1","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":520,"wires":[]},{"id":"4bf58cc4e60f5207","type":"join","z":"d05f249a6f99c6bf","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":810,"y":840,"wires":[["6372b652ebd01577","ed115efd4e13c62c"]]},{"id":"6372b652ebd01577","type":"debug","z":"d05f249a6f99c6bf","name":"join out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":940,"y":780,"wires":[]},{"id":"cab67d7702b99345","type":"change","z":"d05f249a6f99c6bf","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"topic1","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":700,"wires":[["4bf58cc4e60f5207","ebbda513df9ccb02"]]},{"id":"b6f46ed1049f6a4e","type":"ui_text","z":"d05f249a6f99c6bf","group":"04fee1bcfe3710a2","order":1,"width":9,"height":1,"name":"","label":"Inserisci la data dell'ultimo controllo per lo strumento selezionato","format":"{{msg.payload}}","layout":"row-left","className":"","x":490,"y":800,"wires":[]},{"id":"8deadb13452e4b3d","type":"debug","z":"d05f249a6f99c6bf","name":"Out DB Cerca","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":820,"y":580,"wires":[]},{"id":"60efcca2c61f0b9b","type":"inject","z":"d05f249a6f99c6bf","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":90,"y":240,"wires":[["c13df134ef23db78"]]},{"id":"38f6b27d6a758da3","type":"ui_button","z":"d05f249a6f99c6bf","name":"","group":"010506b862f616aa","order":1,"width":6,"height":1,"passthru":false,"label":"Refresh Data","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":130,"y":80,"wires":[["c13df134ef23db78","40a7f0e6592841e8"]]},{"id":"ebbda513df9ccb02","type":"debug","z":"d05f249a6f99c6bf","name":"topic","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":830,"y":680,"wires":[]},{"id":"3d853a67cbf1fdef","type":"debug","z":"d05f249a6f99c6bf","name":"date out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":880,"y":720,"wires":[]},{"id":"0113663611b555b1","type":"moment","z":"d05f249a6f99c6bf","name":"Day adjust","topic":"topic2","input":"payload.DATAULTIMOCONTROLLO","inputType":"msg","inTz":"Europe/Rome","adjAmount":"0","adjType":"days","adjDir":"add","format":"\"YYYY-MM-DD\"","locale":"en-US","output":"payload.DATAULTIMOCONTROLLO","outputType":"msg","outTz":"Europe/Rome","x":650,"y":880,"wires":[["4bf58cc4e60f5207","ece9c901994b0e20"]]},{"id":"40a7f0e6592841e8","type":"debug","z":"d05f249a6f99c6bf","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":250,"y":20,"wires":[]},{"id":"ed115efd4e13c62c","type":"MSSQL","z":"d05f249a6f99c6bf","mssqlCN":"6fb70e68d89e36c4","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"UPDATE [DB_Strumentazione].[dbo].[SCADENZIARIO]\nSET STATO =(@STATO)\nWHERE ID_STRUM=(@ID_STRUM) and ID=(SELECT MAX(ID) FROM [DB_Strumentazione].[dbo].[SCADENZIARIO])\n\nINSERT INTO [DB_Strumentazione].[dbo].[SCADENZIARIO]\n(ID_STRUM,DATAULTIMOCONTROLLO,RESPONSABILE,NOTE) \nVALUES (@ID_STRUM, @DATAULTIMOCONTROLLO,@RESPONSABILE,@NOTE)\n\n\nSELECT [dbo].[SCADENZIARIO].ID as ID_Scad,\n[dbo].[SCADENZIARIO].ID_STRUM,\nFORMAT( DATAULTIMOCONTROLLO, 'yyyy-MM-dd') as DATAULTIMOCONTROLLO,\n[dbo].[SCADENZIARIO].RESPONSABILE,\n[dbo].[SCADENZIARIO].STATO, \n[dbo].[StrumentazioneAttrezzature].ID,\n[dbo].[StrumentazioneAttrezzature].FREQUENZATARATURA, \n[dbo].[StrumentazioneAttrezzature].CODIFICA,\nFORMAT(dateadd(day,cast(FREQUENZATARATURA as int)-1, DATAULTIMOCONTROLLO),'yyyy-MM-dd') as DATASCADENZA,\n(case when [dbo].[SCADENZIARIO].STATO=1 then 'Done'\n    when [dbo].[SCADENZIARIO].STATO is null then 'None'\n    end) as STATON\n   \nFROM [dbo].[SCADENZIARIO]\nINNER JOIN  [dbo].[StrumentazioneAttrezzature]\nON [dbo].[SCADENZIARIO].ID_STRUM = [dbo].[StrumentazioneAttrezzature].ID\nWHERE [dbo].[StrumentazioneAttrezzature].ID = (@ID)","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"","rowsType":"msg","params":[{"output":false,"name":"STATO","type":"int","valueType":"num","value":"1","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"ID_STRUM","type":"int","valueType":"msg","value":"payload.topic1[0].ID","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"DATAULTIMOCONTROLLO","type":"Date","valueType":"msg","value":"payload.topic2.DATAULTIMOCONTROLLO","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"RESPONSABILE","type":"VarChar","valueType":"msg","value":"payload.topic2.RESPONSABILE","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"NOTE","type":"VarChar","valueType":"msg","value":"payload.topic2.NOTE","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"ID","type":"int","valueType":"msg","value":"payload.topic1[0].ID","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":900,"y":1040,"wires":[["ba20981416b74e3e"]]},{"id":"380938770ffb58d4","type":"change","z":"d05f249a6f99c6bf","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"$.payload.CODIFICA","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":260,"wires":[["e23226821db52820"]]},{"id":"010506b862f616aa","type":"ui_group","name":"Ricerca Strumento","tab":"084d9d4d7c0a6ac5","order":3,"disp":false,"width":"20","collapse":false,"className":""},{"id":"2699aa19a99f500b","type":"ui_group","name":"Storico Date Controllo","tab":"084d9d4d7c0a6ac5","order":6,"disp":true,"width":"20","collapse":false,"className":""},{"id":"6fb70e68d89e36c4","type":"MSSQL-CN","name":"DB_Strumentazione","server":"","port":"","encyption":true,"trustServerCertificate":true,"database":"DB_Strumentazione","useUTC":false,"connectTimeout":"30000","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true},{"id":"04fee1bcfe3710a2","type":"ui_group","name":"Inserimento data controllo","tab":"084d9d4d7c0a6ac5","order":7,"disp":true,"width":"10","collapse":false,"className":""},{"id":"084d9d4d7c0a6ac5","type":"ui_tab","name":"Inserimento Date Controllo","icon":"assignment","order":5,"disabled":false,"hidden":false}]

[E1cid]

Sure, feed the payload properties you want to retain back to the form node.
e.g.

[{"id":"132adec2.3fbc91","type":"ui_dropdown","z":"bf9e1e33.030598","name":"","label":"","tooltip":"","place":"Select option","group":"8b5cde76.edd58","order":3,"width":0,"height":0,"passthru":true,"multiple":false,"options":[{"label":"test1","value":"test11","type":"str"},{"label":"test2","value":"test2","type":"str"},{"label":"test3","value":"test3","type":"str"}],"payload":"","topic":"topic","topicType":"msg","className":"","x":90,"y":160,"wires":[["576f9af5.8a1a84"]]},{"id":"576f9af5.8a1a84","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"payload.inst","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":160,"wires":[["53edd975.6d96f"]]},{"id":"53edd975.6d96f","type":"ui_form","z":"bf9e1e33.030598","name":"","label":"","group":"8b5cde76.edd58","order":8,"width":0,"height":0,"options":[{"label":"instrument","value":"inst","type":"text","required":false,"rows":null},{"label":"another","value":"ant1","type":"text","required":false,"rows":null},{"label":"another","value":"ant2","type":"text","required":false,"rows":null}],"formValue":{"inst":"","ant1":"","ant2":""},"payload":"","submit":"submit","cancel":"cancel","topic":"topic","topicType":"msg","splitLayout":"","className":"","x":360,"y":100,"wires":[["2a85e08d.874c7","dca62af1.e61208"]]},{"id":"2a85e08d.874c7","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"delete","p":"payload.ant1","pt":"msg"},{"t":"delete","p":"payload.ant2","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":350,"y":40,"wires":[["53edd975.6d96f"]]},{"id":"dca62af1.e61208","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":570,"y":80,"wires":[]},{"id":"8b5cde76.edd58","type":"ui_group","name":"default","tab":"8f03e639.85956","order":1,"disp":false,"width":"12","collapse":false},{"id":"8f03e639.85956","type":"ui_tab","name":"Home","icon":"dashboard","order":2,"disabled":false,"hidden":false}]

[QGIO]

@E1cid

Sure, feed the payload properties you want to retain back to the form node.

Thanks a lot!
So, Is it mandatory to send throught the form node the 'instrument' selected in the drop node?
I see that you delate all the fields of the form, leaving the first one (which is the one selected in the drop list).

[E1cid]

Mandatory? no
I deleted the other values to show you can preserve the values required and delete one's you do not need to be preserved .

[QGIO]

why did you report the selected instrument from the drop node in the form?

[E1cid]

So when you select the instrument from the dropdown it auto completes the instrument section of the form. If i understood your question correctly.

[QGIO]

What I need is to mantain the selection in the drop node and have the possibility to submit more time the form node for this selected instrument.
In my flow seems I need to press always the refresh button and select the instrument again to submit new form data.

[E1cid]

Why if the form node already holds the previous selection from the dropdown?
The issue you have trying to do it your way is the dropdown will not submit the same value twice. If you wanted that functionality you would have to request it from the node maintainer , as a feature request.

[QGIO]

I mean submit the form node more time but with different values for the same instrument.
So the step are:

1. select the instrument by the drop list node
2. fill the form (date, responsible and note)
3. Submit data to SQL table and show the submitted data in a UI table
4. fill again the form with different data (new date, new responsible, new note) for the same instrument
5. Submit new data to SQL table and show the new submitted data in a UI table (together with the previous)

Everything works up to step 3. If I continue with step 4 (whitout re-select the same instrument in the drop list node), data are not sent to SQL table and so not shown in the UI table.

I hope now is more clear.

Thanks a lot

[E1cid]

As stated before the dropdown will not send the same value twice, So I am suggesting adding the instrument to the form, and feeding back to reinstate it after submission.
1 select instrument
2 add to form
3 fill out form
4 submit (instrument is feed back to form)
5 submit data to sql and show in table (store data to add to new data for next update)

[QGIO]

this is my flow, modified according to your suggestions (if I have well understood).

[{"id":"c13df134ef23db78","type":"MSSQL","z":"d05f249a6f99c6bf","mssqlCN":"6fb70e68d89e36c4","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"SELECT CODIFICA FROM [DB_Strumentazione].[dbo].[StrumentazioneAttrezzature]","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[{"output":true,"name":"CODIFICA","type":"VarChar","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":240,"y":180,"wires":[["6e0a1c706a7d2fe4","380938770ffb58d4"]]},{"id":"6fb70e68d89e36c4","type":"MSSQL-CN","name":"DB_Strumentazione","server":"XXX\\SQLEXPRESS","port":"","encyption":true,"trustServerCertificate":true,"database":"DB_Strumentazione","useUTC":true,"connectTimeout":"30000","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true,"credentials":{"username":"","password":""}}]

So, I need always to reload the instrument in the drop list (even if it is on the form).
Is there any solution to avoid this?

Thanks a lot!