I am trying to insert new records into related tables. wo_id is parent table Primary key. When new record is created, I need to obtain the new 'wo_id' value and insert that into my related table wo_task (related column in this table is named the same as parent table, but set up as FK).
Have tried multiple ways with no luck, can someone point me in the right direction please?
I am able to obtain the last record id from my parent table (see pic), but not sure how to extrapolate just the value alone.
I had to do something similar recently. So, I set an success handler on the insert query to run the query to get the last record id. Then, I added an success handler on my query to get the last record id (so, in your case, the get_lastRecord_id_SQL query) to run a javascript query. Try this for the javascript:
let i = -1
let datalen = work_orders.displayedData.length
let lastUp = get_lastRecord_id_SQL.data.wo_id[0]
let ind = -1
while (i < datalen && ind == -1){
if (work_orders.data.wo_id[i] == lastUp){
ind = i}
i++
}
return ind
Then, I set an success handler on the javascript query to select row, and set the Index or indices field to the name of the query, followed by .data. Then, I can reference this selected row to insert or obtain rows in the child table.
I actually got it to work sunday morning. Not as thought through as yours, but working so far.
Basically set a "temp_state" variable to grab last _id from parent table and add 1 {{get_lastRecord_id_SQL.data.max[0]+1}}.
That's the _id of my new parent record. I then simply use that temp_state var as the id for child tables. Simplistic in nature, but is working so far.
Will think through if there's a need to sort table first...hhmmm
curious, what's your code to select row?
This shows my javascript query with the success handler to select the row. find_person_index is the name of my query.
To get the id in the first place, I used:
SELECT person_id FROM persons WHERE added = (SELECT MAX(added) FROM persons);
My reason for doing it this way, rather than with a sort is that I wanted to keep my tables sorted in a particular order (in this case, alphabetically by name, but still be able to find the specific row I just added/updated.
