Click Checkbox then find or create linked record

[Jake_Lara]

Hello!

I’m trying to automate a process for my client and I think Scripts may be the answer.

Use Case: We’ve built a purchase order system. Purchase orders populate on the purchase order table and the accounting dept decides when to pay them. When purchases for a vendor or vendors are to be paid, a batch is created for each vendor on a second table. So imagine if you will, a February Mastercard bill with 100+ purchase orders.

In an ideal world, it would be great if we could select a checkbox (next to each Purchase order) and it FINDS or CREATES a new batch on the second table. If the batch has already been created, the act of checking the checkbox links the purchase order to the batch. If the batch does not exist, it creates the batch and links the first purchase order.

Side note: Batches have two statuses (Pending and Complete), So if it doesn’t see a pending batch for the vendor it would create it.

Is this possible? We are also using Zapier.
Thanks
Jake

[Raminder_Singh]

Hi @Jake_Lara,

Yes this should be possible using a script. You can use the selectRecordsAsync method to read records: Airtable Scripting and then loop through the records and find only those with the Checked field set to true. You can also refer to the scripting examples here: Airtable Scripting.

-Raminder

[Tuur]

IMHO this is much easier to do with Automations. Use ‘when a record matches conditions’ (e.g. checkbox checked, empty linked field) then follow up with the create record option (insert record ID in linked field in second table).

[Jake_Lara]

@Raminder_Singh I love Airtable but have never done scripting. Which one in those examples? Thanks!

[Jake_Lara]

@Tuur Thank you would this find a “batch” if it was an existing record?

[Tuur]

Sure. You just have to define a key / condition that matches a field or formula.

You would need one automation for creating a batch (and at the same time linking the record that initiated the insert) in case there is none and another automation that links a record to a batch if there is one already.

All from the top of my head, so I might be missing details, but it can be done…