Discussions

Expand all | Collapse all

Automation to create missing parent record when a child added

  • 1.  Automation to create missing parent record when a child added

    Posted 20 days ago
    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
    ------------------------------


  • 2.  RE: Automation to create missing parent record when a child added

    Posted 20 days ago
    Edited by Mark Shnier (YQC) 20 days ago
    There is safe way to do this without generating Automation failures.

    Make the creation of the parent a 2 step process.

    Automation #1 called Request Creation of Parent sets a date time field called perhaps [Date Time to Request Creation of Parent]  to the value in the formula field [Current date/time]  and it is triggered when a child is created  and the the lookup of [Parent Exists?] is false.

    Automation # 2 called Actually Create Parent is triggered when ​​[Date Time to Request Creation of Parent] changes and and the Parent does not exist.   It will actually create the Parent.

    What will happen is that if say 10 records are all imported at once and among them they need 5 parents created. Automation #1 will fire.   But then all the requests to Automation #2 will nicely queue up and be processed single file.

    What you will see will be that Automation #2 fires 1 time but if you drill down you will see that it has 5 actions - even though the Automation itself only has 1 Action.

    You should experiment with an import or grid edit mass update or mass add records  in grid edit to see how this works beautifully. ​​​

    I have done this in situations where there are hundreds of Actions generated in one "run" even though the Automation might have just 1 or two action steps.

    The whole trick is in the 2 step process.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Automation to create missing parent record when a child added

    Posted 20 days ago
    It would be necessary to check for existing of the Parent in Automation #2, would it not?

    Essentially, Automation #1 is creating a virtual queue, and Automation #2 is processing that queue. Clever.

    Minor reservation: the method requires a sequence of Automations where the sequencing is implicit in the logic of the two Automations. That makes it slightly more vulnerable than a single-Automation approach such as my 4a or 4c, where the sequence is explicitly specified.​​​

    Thanks for taking the time to spell out a good alternative.

    ------------------------------
    Alex Wilkinson
    ------------------------------



  • 4.  RE: Automation to create missing parent record when a child added

    Posted 20 days ago
    Yes, for sure Automaton #2 needs to make sure that the Parent still does not exists. I will update my post above..

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Automation to create missing parent record when a child added

    Posted 20 days ago
    And yes you can have a Safety Net Automation run each night to trigger a saved table to table each night to mass update the field [Date Time to Request Creation of Parent] for orphans to be sure that here was no hiccup in the live process.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------