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. 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 and the [Parent Exists?] lookup is unchecked and creates a new parent record with Key = Child Formula Value. 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
------------------------------

Sign up

Login to the community