Hello,
Retool noob here, but I'm learning fast and overall love the product. I am having problems with a specific app I built to allow updating of a data record in my Postgres DB. I have a form that is initially populated by a Data source table {{ getData.data }} query. I then have form text input fields that I pull into an updateRow query where I specify the key of the data table record in the Filter by section, and then I specify each table column/form input value mapping in the Key value pairs section.
I am seeing strange behavior, which I can recreate, where a text field update either contain a bracket and quote, or it is truncated. For example, if I specify textInput1.value, I will get a value posted in my database update that contains brackets and quotes. If I select textInput1.value.[0] to try and reference the textInput value without the brackets and quotes, I get a truncated update where only the first character appears. This behavior seems to come and go and changes as I add additional Key value pair mappings. Please help me understand what I am doing wrong or if this is some kind of bug. Thank you!
Are you able to provide some screenshots of the query and text input component state?
So when the form initially runs the getData query, it appears to treat all the return values as arrays:
Then, if I don’t edit the value, it treats it as an array and my update includes brackets and quotes:

However, if I do update the value in the form, it treats it as a string and the update saves as expected:
1 Like
When Retool returns SQL query values it does so with an object with an array per column with an element per row in the returned data. Even if your query only returns a single row it'll still generate arrays.
To format this into a usable object there's a helper method, formatDataAsArray that'll generate an array of objects that each represent a row. Try using formatDataAsArray(getData.data) in your data source field there. Also, I'm not sure what component that is but if it's expecting a singular object then you'll need to add [0] to your source reference to select it from the array.
Greatly appreciate the reply and the help! Unfortunately, I'm still not sure of the best way to proceed. You have explained why the initial getData results come back as arrays (makes perfect sense as the query could return multiple records), however, I am still experiencing the problem with how to refer to the KeyValue pairs when I go to do the update statement. If the field is edited, then I can refer to it as a string and everything is fine, but if the field is not edited the existing value is stored as an array. Is there some sort of "if field is edited" function wrapper I should be using maybe?
1 Like
Apologies if I'm missing something but I think the part that I touched on last is the key here, if this is the form component. The form expects a single object as the data source, so passing it the entire formatDataAsArray(getData.data) object won't map to your components correctly.
If you use formatDataAsArray(getData.data)[0] and set your component form keys correctly everything should work like magic. If the source data for the component isn't an array to start with you won't have to mess with flattening anything during the query.
Ah, OK. I get it now. Tried it and it works! Thank you so much for your help and expertise (and making it extra clear to this Noob)!!!
1 Like
No worries! The component tooltip doesn't make it super clear what it expects so it's easy to do. Glad you got it working.
