Update Record in One Table when New Record Added to Another Table

[Jonathan_Gunne1]

I have two tables that share a linked field. Example data:

Table 1:

FRUIT	STATUS (single dropdown)	REVIEWS (linked)
Apples	Incomplete	[1] [2] [3] [4]
Oranges	Incomplete
Bananas	Incomplete
Avocados	Incomplete

Table 2: 

REVIEW	DATE	FRUIT (linked)	OUTCOME
1	1-Jul	Apples	Fail
2	2-Jul	Oranges	Fail
3	3-Jul	Apples	Fail
4	4-Jul	Apples	Pass

The data in Table 2 is created using form.

I'm trying to update the STATUS field in Table 1 to "Complete" when a form is submitted with a passing outcome for a FRUIT. So in this case, the STATUS for the "Apples" record should have been updated to "Complete" on 4-Jul.

I've been trying this using an automation with the following settings:

Trigger: When a record is created. Table 2 is selected.

Conditional Logic group == If OUTCOME is "Pass". Within that logic group, I have an Update Record action item. Table = Table 1, Record ID = REVIEWS (the linked record), Fields = STATUS > Complete.

The trigger is able to find a record and the conditional logic is able to pass the test, but it gets caught up on the action item. I get the "Received invalid inputs" error every time.

[Sho]

Hmmm...I'm guessing.
Isn't this RecordID the record ID for Table2?
You need to get the RecordID from the FRUIT field of Table2.

[pressGO_design · Accepted solution]

If you’re using a form to get the data into Table 2, I would recommend using the trigger “when a form is submitted” instead of “when a record is created.” But that’s just me.

In any case, here’s one solution for you:

1. In Table 1, add a Record ID field for the fruit. 
2. In Table 2, add a lookup field that collects the Record ID from the linked fruit record.

3. When a form is submitted triggers the automation

4. The automation only runs if the Outcome is “Pass.”

5. The actions are

FIND the record in Table 1 where the Record ID matches the Record ID in Table 2

UPDATE the record you just found in Table 1. In the automations, you will need to pick the Record ID from the FIND part, not from the “when a record is submitted” part.

Apologies for formatting - I’m on mobile. Let me know if you need a screenshot.