Automations turn off after an error
I'm trying to add a value from Table A as a unique primary key into Table B. However, sometimes there are duplicate values in Table A, so I WANT them to error out and not be added to my table. However...
Forum Discussion
Hi all,
I'm wondering if there was a solution to this? I think of it as an issue of not being able to 'uniquify' data natively in Quickbase. I see a suggestion to 'use a t2t import' to do this but that will fail if Table A has multiple records that are desired to be uniquified as a primary key in Table B.
There has to be a better way than pulling that data out of QB and pushing it back in after uniquifying in a separate tool.
------------------------------
Lucas Buxman
------------------------------
I'm wondering if there was a solution to this? I think of it as an issue of not being able to 'uniquify' data natively in Quickbase. I see a suggestion to 'use a t2t import' to do this but that will fail if Table A has multiple records that are desired to be uniquified as a primary key in Table B.
There has to be a better way than pulling that data out of QB and pushing it back in after uniquifying in a separate tool.
------------------------------
Lucas Buxman
------------------------------
- MarkShnier__You
Qrew Legend
I do have a native solution for this but I'm tied up on client projects right now. Contact me directly if you would like one on one assistance and are in a hurry or else when I have a chance I will post back to this thread.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------- MarkShnier__YouWell I'm so far behind what I need to get done today, I guess another 5 minutes won't hurt. So to the Community this is your isolation gift of the day as a reward for sitting indoors "Staying at Home".
Automation (overnight) or a URL formula button runs a saved table to table import to import all records into the same table merging on [Record ID#] to update each record missing a parent with the current date and time into a field called [Date/Time Parent Create Requested].
Automation called Request Creation of Parent Numbers triggers on [Date/Time Parent Create Requested] and subject to the filter that the child is missing a Parent then Action = updates a field called [Actually Create Parent date / time] with the current date/time.
Automation called Actually Create Parent triggers on [Actually Create Parent date / time] and subject to the filter that the child is missing a Parent, creates the Parent.
I have not fully wrapped my head around why this does not exceed the rate threshold limits for Automations given that the process is initiated b y a saved table to table import which in my test was a mass update of 32,000 children records needing 1,000 parents created and it worked with only 1 error on duplicate Parent Key field.
It does not lot run especially fast, like at a rate in my example of creating a few parents a second like maybe 2 or 4 per second. But for small data sets needing parents or an overnight process, that slow rate will not be noticeable.
There is something about that two step Automation process with the filter on the Record ID# = Record ID of the Trigger Record which causes the Automations to queue up nicely and prevent the duplicates.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
I'm not sure if this is exactly what you're going for, but I've used a pretty simple solution for something similar in the past. If you create a relationship between Table A (child) and Table B (parent) using your match field as the reference, you can create a Formula - Checkbox field in Table B called Parent Exists? or something along those lines. The formula in that checkbox is simply "true."
Now create a lookup field in your relationship, where Table A wants to pull down that Parent Exists? field. If there is a match in Table B already, that lookup with be populated with a checkbox. If there is not yet a match in Table B, and therefore the record in Table A is an orphan, the lookup will be blank.
You can filter your automation to only run when the lookup checkbox is Table A is NOT checked, and it will not try to create a duplicate record in Table B.
If your intent is to get an error message instead in Table A, you can probably use form rules or some more complicated code to get that to happen right when you enter the data and it realizes that box is checked. Alternately, if you want to force the Table A value to be unique, you can create a Formula - Text field that checks if the Parent Exists? box is checked, and if so, adds some unique identifier.
------------------------------
Oana Toma
------------------------------- MarkShnier__YouThe problem is that the child table may contain duplicate missing parents. So a saved table to table import will fail as the import contains duplicates for the Key Parent field..
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------- I actually was envisioning skipping the table to table import altogether. With my checkbox method, you can trigger an automation every time a record is added to Table A, with the action being create one new record in Table B IF the parent record does not exist. Since this is a one-by-one automation, even if it trips over itself and tries to add the same record to Table B twice, all you get is an error message with no impact on the automation running for other records being added. Since the record is added to Table B already, the error doesn't matter.
------------------------------
Oana Toma
------------------------------
