How to preserve null values in tables

[kt727]

• Goal: I want to ensure that when an edit form is submitted to edit a row in my db, if the user does not edit a given field, it does not change the NULL value to blank.

• Steps: have an app that displays a table from my db, and then allows a user to edit values in rows. Currently many of those values are NULL in the db. However, when they are displayed in a table in Retool, they become blank. Then, when the user submits the form, probably updating one value out of 12, all the non-updated values then update from NULL to blank, or if they are integer fields, they update from NULL to 0, which breaks our product. I cannot figure out how to ensure that NULL values are preserved when the displaying the table, or how to only update fields that have been modified.

[WidleStudioLLP]

Hey @kt727 Welcome to the Retool Community,

You can design the app so that when editing an existing record, the component automatically loads the existing data. If the data is part of a table row being edited, the update query will update all values accordingly. This ensures that any unchanged data remains the same while any modified fields are updated seamlessly.

For example,

When you click the "Edit" button in the table, a modal opens, displaying a form with the data from the selected row.

To populate the form with the selected row's data, you can use {{ table1.selectedRow }}. For specific fields, reference their corresponding field names from the database.

When updating the data using an UPDATE query, ensure that all existing data remains unchanged except for the fields that have been modified. This way, only the updated values will be reflected in the database.

[MiguelOrtiz]

Hi there @kt727, and welcome to the forum!

So, for null values there are 2 things you can do:

1- Number inputs have a "Validation" rule where you allow nulls
2- For strings and dates, you can transform the object to update in your query applying something like what is explained here

Unable to create a function that turns form values from empty strings into nulls

Hey there @Alen_Ramic and welcome to the forum. So, the way I usually approach this is to manipulate the changesetObject within my insert query, i do something like: {{ ({ ...form8.data, delivery: date1.value === "" ? null : date1.value }) }} In the same you can define all of your date values.

Hope this helps keeping null values persistent!

[Darren]

I hope the above was helpful, @kt727! Welcome to the community.

Just to add my two cents, it might make sense to check the value of the form inputs on submit against the table's selectedSourceRow and only include those that have changed.

Don't hesitate to follow up here if you have any additional questions!

[Darren]

Have you had a chance to try out any of the above recommendations, @kt727?