Update existing records without deleting fields

App Building
1

I'm using a data table connected to a MySQL database and a form. The user selects a row from the table, fills in the form fields and then submits which triggers my query. Not all form fields are required and there's about 30 fields.

My query uses GUI mode to update an existing record, where ID = projectTableSelectedRow.ID and the Changeset Object {{ contractSetupForm.data }}

The query updates the database columns with the field values as expected but removes column data if the field is left blank.

Besides for manually entering an SQL statement that checks the field for a value before updates, is there another way?

image
image1049×634 35.5 KB

2

Hi @yourbudweiser , no there is no other way than writing the query with the checks yourself. The GUI update, just overwrites the fields mentioned.
And if your field was previously populated and it is empty upon update, than I think the query is correct, as there is a change in data.

3

Hi @yourbudweiser, welcome to the fam :hugs:

I use a global function (in the Retool settings, not App settings):

const cleanForm = (obj) => {
  const cleaned = _.omitBy(obj, (value) => _.isEmpty(value) && !_.isNumber(value));
  return cleaned;
};
window.cleanForm = cleanForm;

And then: cleanForm(contractSetupForm.data)

Let me know if that works for you :v:

1 Like
4

Thanks for your reply @minijohn.

I actually resolved this by loading the database values in my form fields so the existing values will not be lost on submit.

2 Likes