I want to know how to use the GUI mode bulk update to modify records in a bridge table or if there're other workarounds. The bridge table has a composite primary key based on two columns. In GUI mode, the bulk update can only be performed "via primary key", and the primary key column option doesn't have the composite key shown.
Here's an example of array of records to update: [{id_a: 1, id_b: 10, value: some value }, {id_a: 1, id_b: 20, value: some value }, {id_a: 2, id_b: 20, value: some value }]. The update query can't be run and throws an error message: "The primary key supplied "id_a" is not unique!"
Hey, you are receiving that error because the primary key cannot be duplicated. Based on your example, could you use id_b as your primary key given those values are unique?
Sorry for confusing you. There will be duplicate values for both id_a and id_b, but their combination is unique. I updated the example in the post to make it more clear.
I tried a workaround in the GUI mode:
action type set to be "updating an existing record" action type
filter by set to be one of the foreign keys (id_a or id_b) in the bridge table
check "Allow this query to modify multiple rows in the database"
It works. If the filter by value is id_a = 1, any records with id_a = 1 are updated ( [{id_a: 1, id_b: 10, value: some value }, {id_a: 1, id_b: 20, value: some value }] )
But not sure if this is the best solution. Any other suggestions? Thank you
A workaround a past user did was write a query to update each row, then use a map to return an array of triggers on the recordUpdates object utilizing JS promises. Not sure if something like that could work for your use case?