I have 3 tables:
Table 1 = Customer Service
Table 2 = BackOrders & Dropshipments
Table 3 = On Hold Orders
Tables 2 and 3 are pulling data from an external system - any order flagged for one of these statuses in the external system is populated in this Table. The Primary field is the Order Number for these tables.
Table 1 is attempting to pull data from Tables 2 and 3 based on Order Number. The idea is that when a record is created in Table 1 with an Order Number, it will search Tables 2 and 3 to see if there is an existing record. Another automation pulls the Order Number “Name” to Lookup Fields for Tables 2 and 3 so the Order Number only needs to be entered once.
If a record exists, its name should be entered, if a record does not exist, it should remain blank. Part 1 works but we are facing the issue of a new record being created in Table 2 and 3 when the Order Number is not found.
I can’t seem to find a way to prevent the creation - we only want this field populated IF there is an existing record already existing in Table 2 or 3. Changing permissions on the tables to prevent creation through Automation results in an Automation error.
Is this possible within Automations or am I looking at a more custom solution? Hoping I’m missing something obvious here or overthinking the configuration needed for this.
