I have other apps working fine saving back to a table in postgres on save changes event handler. But when trying to do save changes functionality when user alters table records of a table that is a JSON join, it is not working.
Trying to see what I am missing here.
So my record updates references table2 which is my JSON table. The update query is updating the proper postgres table that my only editable column is using primary key of id which is present in the JSON table.
You need to click on Save changes button at the bottom of the table
Yes that is being done
Are you rerunning the query that populates the table after the Save Changes event query completes so you see the updates in the UI?
Hey @seandawes, as ScottR has mentioned, you may want to call the read query that populates the table data on successfully updating the table data. Can you share more on what indication you have that the query does not work? Have you confirmed that the trigger does run when Save changes button is clicked and if so is there any error shown?
This is the error I get
My thought was initially it cant read the JSON join as as its looking for all columns found in the joined table to be found in the table I am trying to update (1 of the tables in the join)?
Are you using the Bulk Update GUI? if so you can change the array being passed in if that one column doesn't exist, or you can write out the query and remove that one column from being updated that doesn't exist
@seandawes I second ScottR's suggestion. Here's a sample update query where I removed columns from table1.newRow that don't exist in my firstname table. I created a query firstNameCols that returns existing column names in the table.
{{ Object.keys(table1.newRow).reduce((accumulator, key) => {
return !!firstNameCols.data.includes(key) ? {...accumulator, [key]: table1.newRow[key]} : accumulator;
}, {}) }}
Can you try this out?
@Amanda Ok I think I am close. Had to make changes to your instructions as I am doing bulk update not new row.
Update is different in the GUI mode. filter by id would be equal to what value? I know its my primary key in the table which in my case would be the following in my JSON join table {{table2.id}} or do I reference another value as I only want the id's which have changes in the row?
@seandawes Your screenshot is not a bulk update, but rather shows Action type "Update an existing record". Therefore, the Filter by value field expects only one unique id which corresponds to the existing record to update. For bulk update, instead select Action type "Bulk update via a primary key" and provide the primary key column name (e.g. id), which is included in Array of records to update data. Because you are using recordUpdates, only the rows with changes will be included by default.
@Amanda Thank you. Sometimes my brain I think stalls haha.
Also note for anyone else. Hurdle I forgot about when following @Amanda advice was I was using column alias in my queries I was JSON joining which caused an issue. As of course data must match headings. Needed to swap them out and worked fine.
