Modifying a checkbox based on a field in another table

[Tim_Wilson]

I’ve got a base I use to track my clients and the coaching sessions I have with them. Two tables: Clients and Sessions. The Sessions table has a link to the Clients table to display the client name.

The Clients table has various data about the clients themselves including a checkbox field called Paid that tracks whether this client is a Paid or Pro Bono client. (The status of the checkbox could change over time. For example, a pro bono could become a paid client at some future point.)

The Sessions table has data about the date, length of the session, etc. including whether it was a paid or pro bono session.

Here’s what I’d like to do:

When I create a new record into the Sessions table, I want Airtable to check the Paid field in the Sessions table if that client has a check in the Paid field of their record in the Clients table.

I have two input variables: recordId which is the ID of the newly created record, and client which is the ID of the client in the newly created session record.

My code:

let sessionsT = base.getTable('Sessions');
let clientsT = base.getTable('Clients');

let inputConfig = input.config();
let record = inputConfig.recordId;
let client = inputConfig.client;
let clientsResult = await clientsT.selectRecordsAsync();
let paidClient = clientsResult.getRecord(client[0]);

if (paidClient[0] == true) {
let update = await sessionsT.updateRecordAsync(record,
    {
        'Paid': true
    }
)
};

The script fails, and I can see that I’ve not getting the status of the Paid field in the Clients table populated.

Any suggestions? I’m a lot more accustomed to regular SQL querying and such, so I’m trying to wrap my brain about the AirScript concept.

[JonathanBowen]

Hi @Tim_Wilson - the main problem, I think, is that client[0] is giving a client object like this:

So you need client[0]['id']

That said, I’m not sure where this line comes from:

let client = inputConfig.client;

My approach is a bit different:

let sessionsT = base.getTable('Sessions');
let clientsT = base.getTable('Clients');
//pick a record
let record = await input.recordAsync('Pick a record', sessionsT);
//get the client from this session record
let client = record.getCellValue('Client');
//query the clients table
let clientsResult = await clientsT.selectRecordsAsync();
//get the specific client record using client[0]['id]
let paidClient = clientsResult.getRecord(client[0]['id']);
//log the checkbox value on the client table
console.log(paidClient.getCellValue('Checkbox'))

Mine is based on picking a record from the run script block, whereas yours is from an automation I think, but you should be able to adapt this for the automation. This part:

let record = await input.recordAsync('Pick a record', sessionsT);

becomes:

let inputConfig = input.config();
let record = inputConfig.recordId;

[Tim_Wilson]

@JonathanBowen, thanks for the reply. That was helpful. I’m going to post a follow-up here in case someone else is working through a similar issue.

Yes, I’m trying to run this script as part of an automation, and I think that’s part of the problem. The automation is set to trigger when a new record is created in the Sessions table and then fire off the script. The problem is that I haven’t yet linked to a client when the record is first created. So I think I need to trigger off a record update instead. However, I’d really like to chain those triggers together, because I don’t want this script to fire when I go back and update a record later because whether the session was paid or pro bono shouldn’t be updated for a historical record if I later change the Paid checkbox in the Clients table.

Currently, then, my script is throwing an error because the client record doesn’t exist when the script is trying to access it.

Here’s my current script code:

let sessionsT = base.getTable('Sessions');
let clientsT = base.getTable('Clients');
// Pick a record
let inputConfig = input.config();
let record = inputConfig.recordId;
// Get the client from this session record
let client = inputConfig.clientId;
// Query the clients table
console.log(typeof client[0]);
let clientsResult = await clientsT.selectRecordsAsync();
// Get the specific client record using client[0]['id']
let paidClient = clientsResult.getRecord(client[0]);
// Log the checkbox value on the client table
console.log(paidClient.getCellValue('Paid'))
// Set the "Paid" checkbox in the Sessions table if it's a paid client
if (paidClient.getCellValue('Paid')) {
    await sessionsT.updateRecordAsync(record,
        {
            "Paid": true,
        })
};

So given that I want this script to run only once—the first time I add a client to that newly created record in the Sessions table—should I continue down the Automation path or investigate another approach?

[Tim_Wilson]

Quick follow-up here.

I changed the automation trigger to look for a record update instead of record creation and have it watching only the Client field in the Sessions table. This will allow me to make changes to any other fields without the script firing as long as I don’t change the linked client. While it’s not a perfect solution, I don’t think it’s likely that I would go back and edit a past record in the Sessions table and change the Client field.

I’d say this is a 99% solution and good enough for now. I hope this is helpful to someone else.

[JonathanBowen]

Yes, that makes sense. Was going to suggest using when a “record meets condition” with trigger when “Client is not empty”, so as long as the client field is not emptied at some point in the future, then I think this will only run once when a client is added. Similar to watching the client field, so may not be worth changing from what you already have.