Linking records in Parent/(multiple)Child tables and overwrite existing fields

Hi Everyone,

I have 3 tables:

  • Table A - Empty Master table intended to capture all records in Table B and C. Full name, email, and linked fields for Table B and C.
  • Table B - first event. Full names and emails
  • Table C - second event. Full names and emails

Objective: When new records and emails are added to Table B and Table C, link and update the emails to Table A (and eventually more tables that require the exact same automation). Because Table C has some people that were in Table B, and some now people, the email updating is required, to either overwrite an email in Table A that came from Table B, or add a new one altogether if the record doesn’t yet exist in Table A.

Method:

  • Trigger: When records are added to Table B
  • Action 1: Create and link that record in Table A. I achieve this by simply adding the name of record B into the Table A linked field in Table B. Because both tables live natively in the same base, I can create a new record in A by only operating in B.
  • Action 2: Update Table A with the email in Table B.

This automation works as intended for Table B to Table A. But not for Table C to Table A.

Problem: When I try to do the same thing with Table C, the automation fails. The only difference with this 2nd automation is that I want the email in Table A, which was carried over from Table B, to now reflect the latest email in Table C.

Troubleshooting: In Action 2, I have tried to change the record ID 4 ways and have gotten the following errors:

  • Step 1 ID - successful linking. no email. error: record doesn’t exist
  • Step 1 Name - successful linking. no email. error: record doesn’t exist
  • Step 2 ID - successful linking. no email. error: invalid input
  • Step 2 Name - successful linking. no email. error: invalid input

I’ve been messing with this for hours and still can’t seem to get it to work. In reality, I have a few more fields that I am trying to keep up-to-date, not just emails. Any help would be greatly appreciated, thank you!

Update 8/12 11:55am: I tried to split the 2nd automation in 2, where the first automation links, and the 2nd one overwrites the email. This is still returning an error for the 2nd automation.

Automation 1:

  • Trigger: When records are added to table C
  • Action: link that record to table A. Again, this will link if the record exists or create a new one if the record does not yet exist in Table A.

Automation 2:

  • Trigger: When record is updated. This looks at the field in Table C that was linked as a result of Automation 1
  • Action: Update the email field in Table A to reflect the email field in Table C.

I am still getting the same errors in the 2nd automation.