Automation to add record to another table if it does not exist
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.
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.
