• Sign In
  1. Messages

creating both sides of a FK using LABKEY.Query.saveRows()

LabKey Support Forum (Inactive)

View Message

view list print
creating both sides of a FK using LABKEY.Query.saveRows() Will Holtz  2017-08-01 17:43
Status: Closed
 
Table A has a foreign key into table B. I'd like to insert into both tables A and B in a single transaction and create both sides of the foreign key relationship. The resulting record inserted into A would be pointing to the record inserted into B, both inserted in the same transactions. I'm trying to do this with client side javascript and LABKEY.Query.saveRows(). I thought perhaps I could explicitly set the rowid field for inserts into table B, but I get an error about the rowid not existing. I'm guessing I need to use Java to do this, but please let me know if you can think of way around this.

thanks,

-Will
 
 
Ben Bimber responded:  2017-08-01 17:52
Hi Will,

In situations where we've done this, you could consider:

1) can you use a string PK, instead of the rowID? There's a LABKEY.Utils method to generate a GUID, for example. this way you could establish the key upfront, and insert both rows in a transaction as you're doing w/ saveRows().

2) You could add something in the server-side JS trigger scripts to do one of these two inserts. I'm not sure this is always the best pattern for what you describe (making a dedicated java action is probably cleaner), but it can be done. In other words, if you submit a wide row to the child table, if no matching value exists in the FK's source table, you do that insert into that table from code in the trigger script.

-Ben
 
Will Holtz responded:  2017-08-02 17:42
Hi Ben,

These are some good ideas. I just tested out your first method and I think it is going to work for me.

thanks!
-Will