Updating Existing Records

[Magenta_Jackie]

I can write back to postgres with the "Bulk update via primary key" option successfully using:

Primary key column = member_id
Array of records to update = {{table1.recordUpdates}}

But, I was also hoping to write a date timestamp to my database table to show when a row was last edited. Is there an easy way to do this with the "Bulk update via primary key"?

Instead, I've been trying to use the "Update an existing record" option

Filter by

member_id = {{table1.recordUpdates.member_id}}
hcc = {{table1.recordUpdates.hcc}}

Change set, Key Value pairs

review_complete = {{table1.recordUpdates.review_complete}}
notes = {{table1.recordUpdates.notes}}
last_edit = {{moment().format()}}

But, I keep getting a message of "No rows were updated" and an error code of "Unprocessable Entity".

I'm new to Javascript and making apps, so I'm guessing I'm referencing something incorrectly

[Jay]

Hey, there - Retools support here happy to help!

So technically this query updates a single record according to your action type: "update an existing record". In order to get it to update for several changes, you will need to create a separate javascript function and loop through the recordUpdates and trigger this write_alterwood_table query with the additional scope of each element that has been updated passed in.

Here's an example of what that Javascript loop might look like: http://community.retool.com/t/how-to-run-a-rest-api-query-for-each-item-in-an-array-and-return-all-results-together/2352

[alansee]

Hey @Magenta_Jackie !

Did you ever find a more robust solution here? Currently looking to solve the same issue.

I thought about bringing the last_edit column into the editable table and then somehow automatically setting that to the current timestamp.

[Magenta_Jackie]

Hi alansee,

I'm currently using a transformer to add the extra fields that I want to write back to my postgres table. Below is the transformer and "member_table" is my table in Retool where edits have been made. I'm using _.pick only because I wanted to write back only a few of the columns that exist in member_table.

let today = {{moment().format()}}
let username = {{current_user.fullName}} 
let updates = {{ member_table.recordUpdates.map(row => _.pick(row, ["member_hcc_id", "show_to_client", "notes"]) ) }}

updates.forEach(record => {
    output = Object.assign(record, {"reviewer": username, "review_date": today})
})

return updates

Then, to write back to the database, I'm referencing this transformer

Hope this helps!

[alansee]

Hey @Magenta_Jackie! Somehow I missed the notification for this. I was randomly looking up a different issue and came across this thread/my question.

Your solution works perfectly! This is the first time I've used a bulk upsert + an array of records and it's definitely pretty powerful. Thanks so much!