How to Auto create a Parent when Multiple Children Exists

Question

I often have a need to have an overnight safety net process to create a parent when children exists. I often have situations where I set up Automations to Auto create a parent record when a the first child record gets created. The Key to the Parent will be determined by formula field on the child.

But sometimes for one reason or another, the Automation will fail. For example during some Copy Master detail process many children get created all at once and the Automaton fails to keep up due to rate limits or if my method involved the automation running a saved table to table import, there are children with a duplicate formula field both looking to create the same parent and the import will fail on duplicate Key field in the same import.

I'm looking for ideas on how to have an automated process which can create parents from orphan child records when here can be duplicate Parent Key. My only method now is to have a safety net subscription Summary Report on that calculated [Related Parent Key field] and then I do a manual copy from that report to the Parent table.

I see that one of the new connections in the Pipelines is something called "Text". I see that is seems to be some kind of text manipulation tool, and I was thinking maybe it could de-dup a column of records.

Any ideas out there to solve this generic problem?

I'm a low code / no code kind of guy, so I'm looking for a solution using only native Quick Base to a challenge I often face. Not looking for a javascript or coding solution or an add on product.

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

oanawhalen · Answer

If I understand your process correctly, these are your current steps:

A relationship already exists between the parent and child tables, with the key field in the parent table being some formula-calculated value in the child table.
A record gets added to a child table and the formula field automatically calculates.
An automation runs whenever a child record is added and creates a new parent record with Key = Child Formula Value. This works fine if it is the first time that Child Formula Value exists, but fails if a parent record with that key value already exists.

It sounds like you are just missing one step in this process to check whether the parent record already exists before telling the automation to create a new record. In the parent table, create a Formula - Checkbox field called [Parent Exists?] and have the formula simply be "true". Add a Lookup field down to the Child table where the [Parent Exists?] checkbox value gets passed down. If a parent record with a key value equal to your formula result already exists, that checkbox lookup will be checked. If no parent field with that key value exists, and therefore the child is an orphan, the checkbox lookup will not be checked. Your new process looks like this, with edits to the original process in bold:

A relationship already exists between the parent and child tables, with the key field in the parent table being some formula-calculated value in the child table.
A record gets added to a child table and the formula field automatically calculates.&nbsp;The [Parent Exists?] lookup field checks if there is already a parent record for the new child record to associate to.
An automation runs whenever a child record is added&nbsp;and the [Parent Exists?] lookup is unchecked and creates a new parent record with Key = Child Formula Value.&nbsp;If the [Parent Exists?] lookup is already checked, no new parent record is created and the child is automatically associated with the parent record based on the formula value in the related field.

Does this process make sense for your application?------------------------------Oana Toma------------------------------

markshnier__you · Answer

Yes, the process that you are describing is exactly the process I do now. It will mostly work. But if the Automation fails or if child record get created too quickly for the Automation to keep up (say a bunch of children get created by a Copy Parent Child button), then I'm looking for an overnight process to be able to create the parents. My issue is that these children may not all have unique parents, so I can't just import them into the Parent table as there may be duplicated in the child data.

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

oanawhalen · Answer

Are you able to identify where your automations start tripping over each other? I had an app do that recently and I was able to build a 5 minute delay into a code page that checked a box to trigger the automation. This works for a manually triggered import process and slowed my very length automation process down enough to let the system catch up, though I'm not exactly sure how a code page could get called on a scheduled import.

(function(){

setTimeout(()=>alert("Please wait a few moments while your data is processed."),500);
document.body.style.cursor = "wait";
setTimeout(()=>alert("1 min remaining"),240000);
setTimeout(()=>$.get(DataImported),270000); //Delay Data Imported checkbox by 4 min 30 sec.
setTimeout(()=>alert("Data import is complete!\n\nIf you do not see all your data on the next page, please refresh the page in a
few minutes."),295000);
setTimeout(()=> document.location.href = GoToPropConditions,300000) ; //Delay screen refresh by 30 additional seconds

})();

*Code abbreviated for clarity.

------------------------------
Oana Toma
------------------------------

If you do not see all your data on the next page, please refresh the page in a &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; few minutes."),295000); &nbsp; &nbsp; &nbsp; &nbsp; setTimeout(()=&gt; document.location.href = GoToPropConditions,300000) ; //Delay screen refresh by 30 additional seconds})();*Code abbreviated for clarity.------------------------------Oana Toma------------------------------

markshnier__you · Answer

I suppose that a partial solution would be to import the children into the Parent table, like i do now on a scheduled basis, but to introduce a filer to only import where the Record ID# ends in a zero.&nbsp; That would cut down on the chance of a duplicate parent being created in to the import set (causing it to fail) by a factor of 10.&nbsp; Then the Automation would update a counter value to 1 and rerun the import, etc for 10 times.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

How to Auto create a Parent when Multiple Children Exists

Related Content

Deleting Orphaned Children (records)

Using Grid Edit to Edit Multiple Records at Once

Edit Multiple Records at Once with Grid Edit

AI Smart Builder for Creating Apps and Streamlining Pipelines

How can I create a report in Quickbase that aggregates multiple rows into a single line per record?