Update DB Row with form.data

Queries and Resources
1

I'm struggling with how to update a row in a DB table with the .data object from a form WITHOUT using the GUI query builder. I want to write and understand the SQL. The .data looks like this:

Screenshot by Dropbox Capture
Screenshot by Dropbox Capture666×520 46.3 KB

Thanks.

2

update yourschema.yourtablename set balanceOwed_column_name_in_database = {{yourdata.balanceOwed}}
Though since I don't have a complete picture of that object name, data, or structure; this is the best I can do...

1 Like
3

Thank you. I finally figured out a strategy that seems to work.

The values in form.data are of different types. Also, some of the values may be empty strings or nulls. The Retool DB complained about some of these types. After some experimentation, I discovered that all columns can be updated with strings, and the DB will coerce the values, if possible, to the proper type. So, this code ended up working for me.

  const formData = invoiceDetailForm.data;
  const formDataKeys = Object.keys( formData );
  const formDataValues = Object.values ( formData );
  
   const formDataValuesCleaned = formDataValues.map((e, i) => {
    return (!e ? 'NULL' : e);
  })
  
  formDataKeys.forEach((e, i) => {
    queryString += `"${ e }" = ${(e === null) ? '' : "'"}${ formDataValues[ i ] }${(e === null) ? '' : "'"}`;
    queryString += `${ ( i < formDataKeys.length - 1) ? ', ' : ' '}`;
    
  });

  queryString += `WHERE "uniqueID" = '${ invoiceDetailForm.data.uniqueID }';`;   
  });

Yielding this query string snippet that I will tack on the end of "UPDATE tablename SET :

"invoiceNumber" = '9999-0071', "invoiceDate" = '2023-01-03', "unit" = 'U089', "milestoneDate" = '2023-01-01', "dueDate" = '2023-09-08', "balanceOwed" = '3600.00', "invoiceTotal" = '3600.00', "milestone" = 'V5', "check" = '', "checkDate" = '2099-01-01', "depositDate" = '0001-01-01', "project_rel" = 'ea404ef9-70a4-4e65-b90a-8961b4b416c8', "building_rel" = 'a764db0b-d9dc-4857-a9c2-324a91832e25', "memo" = 'CO', "uniqueID" = '05a2b9de-e850-4d4c-b34c-89d702f9df98' WHERE "uniqueID" = '05a2b9de-e850-4d4c-b34c-89d702f9df98';

Getting this query string to run from a trigger has been another challenge, but that's for another post.

1 Like