Automation to create missing parent record when a child added
Scenario: A child record is created. It has a field (often a text-formula) that uniquely identifies the parent record. If the parent record already exists, wonderful! If it does not, use an automation or a Quick Base action (webhook) to create the missing parent.
Problem: Occasionally, there is a race condition, whereby two child records are created almost simultaneously and have the same parent. The automation (or webhook) is queued up twice, and the faster one gets to create the parent. The slower one tries to create the parent, but finds that it already exists and generates an error. Not a huge problem, except that when you get the error you have to verify that it was harmless (since the desired parent did get created). FWIW, this race-condition seems more likely to occur with automations than with Quick Base actions.
I've seen various posts on this topic, spanning a few years, but none with a fully automated solution. I recently tried a new method, which seems to be working ... Here are the specs:
1. On the parent-child relationship, create a lookup field for the Record ID# of the parent. If this field is non-null, then the parent record already exists. My personal preference is to test this with Nz( [Related Parent ID#], 0 ) > 0.
2. On the child record, create a numeric field called "Sibling Of".
3. Create an automation that says: when a child record is created, modify *all* records in the table that have the same parent as the new child. On all these records, fill the "Sibling Of" field with the Record ID# of the new child. Conceptually, the "Sibling Of" field says all the other children have the same parent as the newest child. The trick here is that we will only create a parent record when Sibling Of and Record ID# have the same value *and* the condition in #1 is false.
4. To create missing parents, any of three methods could work:
(a) Add a second action to the automation that creates the parent record for the single (newest) child that has Sibling Of = Record ID#.
(b) Or, if there's no urgency, create a Table-to-Table Import that merges oprhan children into the parent table. And create a daily or weekly automation to run the import. In the import, set conditions that a child will be merged into a parent record only if the Related Parent field on the child matches the key field on the parent, *and* on the child record Sibling Of = Record ID#. (If you do a merge, rather than a copy, there's no need to test #1.)
(c) Or if (like me) you want a bit of an insurance policy, create the same Table-to-Table import, but run it as the second action in automation #3. Thus, every time there's a new parentless child, merge-update all the parent records, just in case something misfired previously.
To sum up, only one child among siblings should be allowed to create a missing parent. The trick is to mark children as siblings in a manner that identifies exactly one of them as being allowed.
------------------------------
Alex Wilkinson
------------------------------
Problem: Occasionally, there is a race condition, whereby two child records are created almost simultaneously and have the same parent. The automation (or webhook) is queued up twice, and the faster one gets to create the parent. The slower one tries to create the parent, but finds that it already exists and generates an error. Not a huge problem, except that when you get the error you have to verify that it was harmless (since the desired parent did get created). FWIW, this race-condition seems more likely to occur with automations than with Quick Base actions.
I've seen various posts on this topic, spanning a few years, but none with a fully automated solution. I recently tried a new method, which seems to be working ... Here are the specs:
1. On the parent-child relationship, create a lookup field for the Record ID# of the parent. If this field is non-null, then the parent record already exists. My personal preference is to test this with Nz( [Related Parent ID#], 0 ) > 0.
2. On the child record, create a numeric field called "Sibling Of".
3. Create an automation that says: when a child record is created, modify *all* records in the table that have the same parent as the new child. On all these records, fill the "Sibling Of" field with the Record ID# of the new child. Conceptually, the "Sibling Of" field says all the other children have the same parent as the newest child. The trick here is that we will only create a parent record when Sibling Of and Record ID# have the same value *and* the condition in #1 is false.
4. To create missing parents, any of three methods could work:
(a) Add a second action to the automation that creates the parent record for the single (newest) child that has Sibling Of = Record ID#.
(b) Or, if there's no urgency, create a Table-to-Table Import that merges oprhan children into the parent table. And create a daily or weekly automation to run the import. In the import, set conditions that a child will be merged into a parent record only if the Related Parent field on the child matches the key field on the parent, *and* on the child record Sibling Of = Record ID#. (If you do a merge, rather than a copy, there's no need to test #1.)
(c) Or if (like me) you want a bit of an insurance policy, create the same Table-to-Table import, but run it as the second action in automation #3. Thus, every time there's a new parentless child, merge-update all the parent records, just in case something misfired previously.
To sum up, only one child among siblings should be allowed to create a missing parent. The trick is to mark children as siblings in a manner that identifies exactly one of them as being allowed.
------------------------------
Alex Wilkinson
------------------------------
