Automation to add record to another table if it does not exist

  • 0
  • 1
  • Question
  • Updated 4 weeks ago
  • In Progress
  • (Edited)
Hello, colleagues,


I have two tables:

Loads
Fields:
Load ID (Key field)
Destination ID
... others


Destination IDs MasterData
Destination ID (Key field)
Destination name
... others


The data is added to "Loads" table in batches. As soon as Load ID with Destination ID not yet present in Destination IDs MasterData appears (in Loads table) I need to add it (to Destination IDs MasterData table).

In order to do so I made a relation, which looks up Destination ID in "MD" table by Destination ID in "Loads" and then compares them in a formula, let's call it "DestinationExists?". If they are equal, it returns "True". Then I made an automation, which is triggered upon addition or modification of any field in "Loads" table, filtered by stated "comparison" formula field.

The problem is automation results in errors 80% of times: "Trying to add a non-unique value to a field marked unique".
I guess this happens because at the point the scope for automation's actions is defined, the field "DestinationExists?" is still "False".

I experimented further and tried adding a field "Destination added by automation" to "Loads" and added second action of the automation typing "Yes" into it, and respectively filtering the scope by it. I may have to observe it for a little more, but it seems that the trick did not work.

Also tried creating summary fields both ways (Loads -> Destination IDs MD & Destination IDs MD -> Loads) but also with no luck, maybe i'm doing it wrong.

I'm pretty sure this is a common problem and would like to know what is a generally accepted, "good" approach towards handling it.



Photo of Alexander Berezovskiy

Posted 2 months ago

  • 0
  • 1
The problem is automation results in errors 80% of times: "Trying to add a non-unique value to a field marked unique". 
guess this happens because at the point the scope for automation's actions is defined, the field "DestinationExists?" is still "False".

I think you might be overthinking this? Sounds like in the table where you're adding records, you have a field that's set to accept only unique values, but your automation is attempting to add records that have non-unique (blank?) values in that field. Is that a direction you looked when troubleshooting your issue here?
Checked the table now and there are just two fields marked as "unique": Destination ID (Key field) and Record ID# which I cannot unmark as "unique".
I wish there was a native answer to this. The issue is, as you state that the records are being added in batches, and you want to trigger the Add record in the other table for just the first new unique value.