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

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered

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?


Photo of Amy

Amy

  • 180 Points 100 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
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].
Photo of Amy

Amy

  • 180 Points 100 badge 2x thumb
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?
Did you include this step here?

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

Amy

  • 180 Points 100 badge 2x thumb

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!
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.
Photo of Amy

Amy

  • 180 Points 100 badge 2x thumb

The automation is doing what it is supposed to - create one deposit record based on bank account and date, despite the number of checks entered. However, the automation disables itself because of consecutive failures. Obviously I'm still missing/overlooking something here?


My Trigger: Start the automaton when 'Check Bank Account-Date' field changes and when 'Deposit Exists' field is equal to Unchecked. Do I not need the first part of this trigger?
Have you checked that field [Deposit Exists?]  Did you make it a formula checkbox?  Did you put in the formula as

true

Did you observe that the field is indeed checked?


Photo of Minda M

Minda M

  • 568 Points 500 badge 2x thumb
Thank you - I used this for my time tracking Quick Base so I can add a time entry and the related workday gets added via automations, but only if that workday does not yet exist. Brilliant!