Automation: update multiple fields when conditions match

Topic Labels: Automations
343 2
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

I've having a little bit of trouble wrapping my head around this automation. I believe I know how to do this using multiple automations but I'm wondering if there is a way to get it all done in a single automation.

Table 1 essentially calculates payments to submit to contractors for each "job". Example of relevant fields in a single job record:

  • Joe payment = $100
  • Sally payment = $150
  • Bob payment = $80
  • Jane payment = $0
  • Bill payment = $0

Table 2 includes ALL transactions for the business (income and payments). Record entries linked to a single "job" may look something like:

PrecessedMisc Expense$120 
ExpectedContractor Payment$120Joe
ExpectedContractor Payment$120Sally
ExpectedContractor Payment $70Bob

What I'd like to do is set up an automation that triggers when a new record is added to Table 1. The automation then:

  • Check Table 2 for records linked to the same job referenced in the new Table 1 record
  • Only focus on Table 2 records with "Contractor Payment" category
  • Match "CONTRACTOR" in Table 1 record with the contractor in the new Table 2 record. 
  • If a match is made, update the AMOUNT in Table 1 with the amount associated with Joe in the new Table 2 record.
  • Repeat this for every Table 1 Contractor Payment found (in this case, Sally and Bob).
  • Bonus if I can also update TRANSACTION STATUS to "Pending" for each Contractor Payment in Table 2 that was updated

Expected results in Table 1:

  • Joe's AMOUNT = $120 $100
  • Sally's AMOUNT = $120 $150
  • Bob's AMOUNT = $70 $80
  • TRANSACTION STATUS for all over the above = Expected Pending

Since I can't use both conditional logic AND a repeat group block in an automation, the only way I can think to do this is to create multiple automations, one for each contractor (i.e., when record created, find matching job records that are contractor payments, find Joe, updated amount field and transaction status). I have many contractors though, and they don't all work every job. 

2 Replies 2
4 - Data Explorer
4 - Data Explorer

Hey. Did you ever find a work around for this?

No, not really. I just ended up making a ton of separate but almost identical automations.