Hello there!
I have a Retool table which is populated by a LEFT JOIN statement like this:
SELECT c.*, e.end_of_contract
FROM checklist c
LEFT JOIN
employee e
ON
c.employee_id = e.id
Now, if I try to perform a bulk update via primary key with the GUI Mode and the "save changes" button Retool returns an error.
GUI mode only lets me select one table, and of course my table1.recordUpdates won't work because e.end_of_contract does not exist in the checklist table.
Is there any way I can perform an inline edit of a JOIN table without using an inline action button?
Hey @axelz! I'm curious if you might want to use a mapper on the recordUpdates property of your table to select those columns you want to update. There's a post here that goes into more detail on that. Can you let me know if it helps?
Hi @Kabirdas. I already tried a similar solution I found in a different thread, using _.omit and _.pick.
I find this map option very useful in general but for my specific case it doesn't work:
The thing is that I cannot update values from both tables at the same time, since if i run the query {{table1.recordUpdates.map(row => _.omit(row, ["end_of_contract"]))} and on success run {{table1.recordUpdates.map(row => _.pick(row, ["end_of_contract"]))} the last one will update no records. That's because 'Save Changes' once completed reload the table content and resets the update I made on e.end_of_contract
I ran into this and solved it fairly easily. I am not sure is optimal. As Kabirdas suggested, I set up the Event Handlers to run two separate queries to save data on each of the joined tables.
If having both queries run as simultaneous event handlers isn't working for you then you can trigger them both from the same JS query and pass the updates in using additionalScope., it may look something like:
Then in both query1 and query2 you'd reference {{ tableUpdates }}. Note - it might show up as undefined when you first enter it into the editor but that's because it's only defined at the moment the query is triggered!
Can you let me know if that works?
I would expect you to run the two simultaneously as @j9o mentioned though. Curious to know what issues you run into there since that's pretty much what the above script does.
Hi Kabirdas,
So far I haven't yet ventured into custom js scripts in the app. I've just been using the queries and basic javascript on the components themselves, which has gotten me surprisingly quite far.
So I'll need to get my head around your solution..... i don't fully understand it yet.
The problem with simultaneous handlers (I think) is because I am using the bulk update option, on tableUpdates. Although, I think my first problem is resolved - I was mapping a column (in the table) using the incorrect id (for a dropdown) which was throwing it off.
Technically, I shouldn't need to update more than one table (as it's just an id which is the fk to another table)... however, to get round that problem - I have had to 'rename' the id field (I.e. AS = sector_new_name_id) in question - which now means, when I try to run an update on the table - it doesn't recognise the (new) field name....... so I need to figure that part out.
Ahh I see, you might be able to use a transformer to rename the field as you pass it to your update query!
Can you try something like {{ table.recordUpdates.map(({sector_new_name_id, ...rest}) => ({id: sector_new_name_id, ...rest})) }} and let me know if that works?
Hi Kabirdas, would you be able to elaborate on this solution? i think i have a similar problem to OP where the table being displayed is from a number of joins (i.e i have a list of items in sql table masterTaskList where a user id is assigned to each row, but in the table there is a join to get the name of the user id), so when in the table i make an edit to the name and choose another name, i need the update query to essentially look up the user id for this new user name, then update the masterTaskList table with this id. is this achievable with the GUI bulk update option or would i need to write a raw sql update statement? cheers
You can have your table trigger two separate GUI update queries on save that correspond with each of your tables. Instead of passing the full changesetArray to each of the queries you can use lodash's _.pick function to select the columns that exist in each of the respective tables, e.g.
Since you're triggering two separate queries it can be helpful to combine the two in a JavaScript query so that you can trigger the data source for your table when both are complete. Something like:
With that you'd just need to add the JavaScript query as the save handler on your table.
