Hello everyone ! Let’s see if someone can help me
As you will see in the image attached, I have a bit of a complicated setup for one of my clients.
It’s a distribution company for television programs.
Programs are produced using content from different partners.
Each partner has a specific share of the pie for every program sold (that’s handled !)
When the company sells to a client, a contract is drawn and it has n programs sold (handled !)
Each contract can have n installments and therefore n payments (handled)
Partners having some shares in the programs are only paid when my client gets paid.
I have the repartition between the different partners on a percentage basis (i.e. partner A, on this program, gets X% of what we sold it for, partner B will get Y%, etc…).
What I now need to do is, for EACH payment made, create a sort of junction table between the partners (one record per partner for that particular program, for that particular client contract) and the payments (one payment per installment, n installments per deal).
If I use the script “junction table” by Airtable, it doesn’t work since it just creates ALL the possible records, regardless of the contract.
What I need is kind of a “conditional” junction which has the following behavior :
1 - When a new payment appears in the table A (it’s a synced view) = trigger
2 - Find the records in the table B that have the same contract name as the payment in table A
3 - Create in table C one record per record found in table B linking each of those records for table B to the payment in table A. This table C basically needs those two fields to be populated : the ID of the payment & the ID of the record in table B. The rest I can populate with lookups, easy peezy
1 & 2 are, of course, super easy.
For me, 3 is a script, ideally within an automation with 1 & 2.
Does anyone know what script I can use ? Who should I ask ?