"Conditional" Junction Table?

[Nadege_BOINNARD]

Hello everyone ! Let’s see if someone can help me :slightly_smiling_face:

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 :slightly_smiling_face:

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 ?

[Alexey_Gusev]

Hi,

one automation run action can update one record at a time.
but script step can do more.

//–skip that part (I didn’t remove it just for case when best method fails)
create checkbox field in B with any name, e.g. ‘payment’
create automation with trigger according to “when new payment appears”
step 2 - find records in B
step 3 - scripting
input variable: ‘same_contract’ (any name you choose), from step 2
choose “Make a new list of: Airtable record ID”

script text(i wrote similar 2 days ago):

*> const table=base.getTable('TABLE_B_NAME') 
*> function mark(rec){return {'id':rec,'fields':{'payment':true}}}
*> let updates=input.config().same_contract.map(mark)
*> while (updates.length) await table.updateRecordsAsync(updates.splice(0, 50));

Then, create second automation - when record matches condition (‘payment’ checked in B), do the needful in C (create record), for each marked record
///---------end skip–-//

Or (i just realized that), you can do it by script step, without checkbox and second automation.

so, step 3 - scripting
input variables:
‘same_contract’ , from step 2 choose “Make a new list of: Airtable record ID”
‘paymentID’ from step 1
‘recA_id’ - ID of record in A, step 1

script

const table=base.getTable('TABLE_C_NAME')
function newRecord(rec){return {'fields':{'PaymentID':input.config().paymentID, 
'recB_id':rec, 'link_toA':[{id:input.config().recA_id}] }}}
let newrecs=input.config().same_contract.map(newRecord)
while (newrecs.length) await table.createRecordsAsync(newrecs.splice(0, 50));

(note that field ‘link_toA’ must be linked field to table A.
you can also make field ‘recB_id’ linked to B, but instead of ‘recB_id’:rec (write format for text field), use ‘recB_id’: [{id:rec}] (it’s array of objects, whose id is where linked to, write format for linked field)

[Nadege_BOINNARD]

Hey there ! First thanks for this amazing answer :slightly_smiling_face:
Second, I’m gonna have to read this very very slowly and pause at almost every word to understand what you’re saying but I’m gonna spend the necessary time to try and understand it :slightly_smiling_face:
Will post here again if there’s something I definitely don’t get !