Automation to create record if does not exist

Question

I have a table that imports Patient Labs via a Google sync. We may get these labs from multiple vendors, and each vendor uses their own test and result codes. So, when we see a new lab imported where source-testcode-resultcode is unique, we want it to be entered in a new table (that then will serve as a way to link the two results together as parent child). A Lab table can have many Patient Lab records. The fields that overlap between them would be source, testcode and resultcode.

I have a table that has Labs in it. What makes a row non-unique is a unique combination of Source (where the lab is from), test order code (string) and result code (string). QB does not support assigning formula fields as a key field, but I would like the key field to be source&testcode&resultcode. Ideally, I would like the Google Sync to import, and would create a formula field that is the reference field to Labs without any automations. I cannot set the key field to a formula field, and I cannot create a dynamic form rule because these records are only ever edited by a Google/QB sync, not a form.

Any suggestions on how to get around this? I tried searching for similar items, but couldn't find the answer specific to QB Sync.

------------------------------
Solace Sonder
------------------------------

markshnier__you · Answer

I read your post through a couple of times but never could quite figure out your question. Can you add an update to your post and then fewer words say

when XYZ happens as a result of a Sync record added, I want to do this. What is this?

Also keep in mind that if you want a child record to automatically connect to a parent record then that can be done with a reference field which is a Formula. It is sounding like to me that you want to create a parent record automatically if needed. Please explain what the Keyfield would be to the parent record.

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

solacesonder1 · Answer

When I Sync record added in Table A, I want to check whether this lab type is already in Table B. If the lab type (which is uniquely determined by 3 fields) is not in Table B, I want to add it to Table B.

Thank you for the help!

------------------------------
Solace Sonder
------------------------------

markshnier__you · Answer

OK!Set the Key field of the Lab Type to be a text field called&nbsp;[Source-Test Order Code-Result Code]Make a formula checkbox field on that Labs Table called Lab Exists with a formula of True.Make a Relationship down to the Sync table and for the reference field make a formula text field called [Source-Test Order Code-Result Code]&nbsp;List"-", [Source], [Test Order Code], [Result code])Lookup the value for [Lab Exists?]Initialize the Labs table by making a summary report on the Sync table of [Source-Test Order Code-Result Code] and use the option on the report to copy these to another table.&nbsp;Hey assuming you got this far then the question is how do you automatically create parent records where new orphans are created in the Sync table.The straightforward way to do this is to simply make a pipeline to create a parent record when one does not exist.&nbsp; ie when [Lab Exists?] is not checked.That would be a very simple pipeline but the problem you may run into is that when the Sync table updates it may add records very quickly and pipelines may trigger at the same time for two different Sync records who happened to need the same parent created. When that happens you will get a pipeline error because the second instance of the pipeline running may get there a split second too slow and find out the Parent Lab already been created and of course you cannot duplicate that because it's the key field to the Labs Table.The way I choose to deal with that now as I go through the extra steps in the pipeline to have the Pipeline Create a Bulk Upsert, add a row, and then and then commit the upsert. What that will do it will merge in the Parent Labs record with any existing Labs record if it already exists or it will create a new one if needed.&nbsp;&nbsp;&nbsp;------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

solacesonder1 · Answer

Thank you, yes, the last paragraph is where I'm struggling. In my pipeline, I have the following:&nbsp;Start1. On New Event - triggers from any field change on the sync table2. Prepare Bulk Record Upsert - on the lab table, with the merge field the source-test order code-result code field.3. Add a bulk upsert row - ​See the screenshot below - it seems like this doesn't return anything. I think this needs to be a loop creating all the new rows we are going to upsert, but I can't figure out how to configure that.4. Commit Upsert to the labs table. ​------------------------------Solace Sonder------------------------------

markshnier__you · Answer

I'm not seeing any screen shot but.Start1. On New Event - triggers from any field change on the sync table (but only where lab exists is NO2. Prepare Bulk Record Upsert - on the lab table, with the merge field the source-test order code-result code field. Yes, perfect.3. Add a bulk upsert row - ​See the screenshot below - it seems like this doesn't return anything. I think this needs to be a loop creating all the new rows we are going to.&nbsp; We are only adding exactly 1 row, from the Trigger step A.4. Commit Upsert to the labs table. ​ Yes!I know that it seems like a crazy thing to have to go through all these jumps just to "Upsert" a single row. What we really need from Quickbase is a new kind of Step where we can upsert a record without having to first build a bulk upsert "container".&nbsp;------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

Automation to create record if does not exist