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:
| TRANSACTION STATUS | CATEGORY (single select) | AMOUNT ($) | CONTRACTOR (linked field) |
| Processed | Income | $800 | |
| Precessed | Misc Expense | $120 | |
| Expected | Contractor Payment | $120 | Joe |
| Expected | Contractor Payment | $120 | Sally |
| Expected | Contractor Payment | $70 | Bob |
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.
