(From Office Hours) form.data does not contain keys with undefined values

  • Goal: trying to submit a form to change a value in the database from 1 -> null

  • Steps: Checked state, tried transforming, chatted with Retool folks at Office Hours here

  • Details:
    I have a form that reads/writes to a postgres table. One of the form fields is integer input

Submitting the form writes to the postgres table using retool's postgres GUI -> object changeset {{ form.data }}

The form works fine when we submit the form with a value, but sometimes the team wants to change the value from an integer back to null. form.data does not contain undefined keys, which means the blank update goes missing

  • Screenshots:
    When the field is populated with an integer, num_owners appears in form.data

When the field is not populated (blank), num_owners disappears from form.data

@freetool have you tried instead of pushing the whole object to do it via key value pairs? That way you can set it like num_owners.value || null to pair with the column in your postgres table.


Also make sure you allow null value on the validation rules, and/or set it to null by default

hope this helps!

1 Like

Thanks Milan - we've definitely considered it but the form itself has ~50 fields we'd have to map, and we have several apps that need to support the same functionality and are trying to avoid overengineering

During office hours, one of the retool engineers indicated there might be a way to do it via javascript. Waiting for them to chime in here...

Hi @freetool, I found a workaround for this issue. It takes an extra step but it should work like a charm.

We'll need a JS query to format the data from the form. In other words, change the "" to null when needed.

To keep this example simple, I added an age column to my sample_users table:


Note: I'm also allowing null at the db level to simulate your use case.

Here, I've selected the row with the id of 72011 (pardon the mess, long story). As we can see in the form below, the form gets populated with the data of the selectedRow.

The query that is open on the left panel, formatFormData, is what we will run on submit of the form. On success of formatFormData, we'll run the actual update query, passing the output of the former query as the "Changeset object".

Once I clear the value of age on the form and submit it:

The JS query runs, and returns an object where the value of the key age is either the new number on the form, or null if it was cleared (no more "" :person_gesturing_no:).

Here is the new output:


YAY!

On success, we now run the update query, passing the output of formatFormData as mentioned above:

Here is the new data on the table:

I also tested it a few times by adding and removing the age:

You may have to set up your version of the formatFormData to include all columns that are numerical and you allow null:

const obj = form1.data
let ageVal = age.value ? age.value : null
let columnNameVal = formInput.value ? formInput.value : null // repeat this line for each one of those columns
return {...obj, age: ageVal, colName: columnNameVal} //add each k-v pair here
1 Like

This worked like a charm! Thanks so much for taking the time to run the tests and the comprehensive writeup @Paulo, it was very easy to follow.

Hope this is an issue you guys resolve eventually, but in the meantime this formatter works great for our needs.

1 Like

You are welcome. Happy to help! :slightly_smiling_face: