Creating an Automation so only 1 deposit record is created for multiple checks

Question

I have a table called Checks (parent) and another table called Deposits (child). When a check is entered, the automation creates a Deposit record for that bank account and that date. However, if I make the ?Bank Account-Date? formula field to be unique on the child table, then I keep getting error messages on the second and subsequent checks entered that the automation could not be run ? which is great, because I only want one deposit record created per day/per bank account. But I don?t want to keep getting the error emails. If I uncheck to have that field unique, then multiple records are created. At one point, the automation was even disabled.

In a previous post regarding a similar situation, one solution presented was to create a formula checkbox on the summary table (in my case ? the deposits table), then look that field up down to the detail record. I tried creating that but am unsure what the formula should be for the checkbox. I do have fields on both tables called ?Bank Account-Date? and look that field up from the child table ?Deposits? to the parent table ?Checks?. What should the formula for the checkbox be?

quickbasecoachd · Answer

You can create a table where the Key field is
Bank Account-Date and also make a formula check box field on that table called [Bank Account-Date exists?]. The formula for that field should be

true

so it will always be checked.

Then make a relationships back to your Checks table based on the formula field [Bank Account-Date] and look up the
[Bank Account-Date exists?] field.

Then set the Automation to only fire when the [Bank Account-Date exists?] is unchecked].

amygosz2 · Answer

I think I got this working, but I always get an error message email when multiple entries are made for the same bank account/date.

Message: "Trying to add a non-unique value to a field marked unique, The field named "Bank Account-Date 2" with field id 154 requires a unique value."

Is there something I may have forgotten to mark, or maybe didn't do this quite correctly?

quickbasecoachd · Answer

Did you include this step here?Then set the Automation to only fire when the [Bank Account-Date exists?] is unchecked].

amygosz2 · Answer

I did not have that set. I did set that now in the TRIGGER Criteria section so will see what happens next time entries are made. It's always the little things that one misses..... Thank you for the quick response!

quickbasecoachd · Answer

Right,
So the issue is that you only want to create that record (One Bank Account on one Date) where it does not yet exists.

When you try to create it twice for the same value, that field is the Key field to the table and hence it is not allowed to be there twice. So that is why the Automation was unhappy.

Forum Discussion

Creating an Automation so only 1 deposit record is created for multiple checks

Related Content

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?

How to consolidate multiple rows into one