Need Script Help - Updating Information Between Two Tables

[andrew8]

Hey Community, 

Thanks for reading my post.  I am trying to update records in Table A (sales estimates) with a number from Table B (sales invoices) when a new record comes into Table B.  I could also do this on a regular interval instead of having a new record entry in Table B kick the automation.  Whatever is easiest.

Essentially, I'm trying to get around the autolinking problem.  Sales estimate numbers are unique and when they convert to an invoice the invoice has the same number.  However, I'd still have to go in every day and link all the new invoices generated to their estimates to make the reporting work correctly.  I REALLY don't want to do that.  Any suggestions?  I imagine there are several ways to get around this, but I'm really hitting a wall.

[TheTimeSavingCo]

What if we have an automation that would trigger when a record gets created in Table B with a Find Record action to look for the unique sales estimate number in Table A, and then link the two records together?

[andrew8 · Accepted solution]

I actually found a solution a couple hours after posting this.  I used a new feature to Airtable the conditional login in the automation engine.  I created a view to pull in invoices as they met certain criteria, then I found a list of the records in the Estimates table that matched the invoice number.  That generated a list of targets to update.  Then I used the list repeater inside of the "update record" action to update each record in the list individually.  That seems to have worked well and solved the need for a script to solve the problem.  I love Airtable!