Automation to create record if does not exist
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 ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendI 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
------------------------------
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
------------------------------
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
------------------------------
Thank you for the help!
------------------------------
Solace Sonder
------------------------------
- MarkShnier__YouOK!
Set the Key field of the Lab Type to be a text field called
[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]
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.
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. 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.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------- Thank you, yes, the last paragraph is where I'm struggling.
In my pipeline, I have the following:
Start
1. On New Event - triggers from any field change on the sync table
2. 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__YouI'm not seeing any screen shot but.
Start
1. On New Event - triggers from any field change on the sync table (but only where lab exists is NO
2. 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. 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".
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------