How to check if a linked record should be created vs updated?

[Margro_Advisors]

We have a script to add a record (client login info) to a table (ComputerLogins). The script asks the user to select which client the new record is for, from a clients table, which is linked to the ComputerLogins table. They are then asked what single-select category (“Portal”) the entry should be assigned. It then creates the record.

Problem is that if a record already exists with the same category, for the same client, it creates a duplicate. We’d like to check the ComputerLogins table to see if (1) the client already has a record and (2) if they do, does it have the same category that the user wants to add?

If it exists, then the record should be updated, if not it should be created.

I’m new to this scripting but I can do the first part and the last part, but I’m having trouble with the comparison code. I’ve tried to figure out if it’s a records query with .includes or some && condition statement or some type of getAsString to do an if A===B then update statement. I think the issue I’m having is that the clients field in the ComputerLogin table is a link, so I’m not able to use a getCellValue(“Clients”) to due a comparison. I’ve tried using the client.id and comparing with a query of the Login table but no luck.

Here’s the current code to collect the input and create the record:

//Pick tables from the base
let logins = base.getTable('ComputerLogins');
let clientlist = base.getTable('Clients');

//Provide instructions
output.markdown('## Be sure Client exists before adding ');

// Select Client from the Clients Table
let selectedClient = await input.recordAsync('Choose Client',clientlist);

// Label an ID to match the client in the different tables
let clientId = selectedClient.id;

//Pull client name from the selected record and assign label "cname"
let cname = selectedClient.name;

// Prompt the user for the USERID info
output.markdown('## Enter Client UserID');
let portalID = await input.textAsync('UID');

// Prompt the user for the Password info
output.markdown('## Enter Client password');
let portalPass = await input.textAsync('Password is case sensitive');

//Create selections for the portal type (from the single-select option in the table)
let logintype = [ "PC1","PC2","Macbook1"];

//Request the user input based on the selections button
output.markdown('## Select which computer you are providing a login for:');

//Assign label to the selection
let portal = await input.buttonsAsync("Click to select", logintype);

//Add new record to the table
   let loginRecord = await logins.createRecordAsync({
    'Clients' : [{id: clientId}],
    'UserID' : portalID,
    'Password' : portalPass,
    'Portal' : {name: portal},
 });

output.markdown(`## ${portal} login added for **${cname}**` );

Once I have the comparison, let’s call it “match”, I would do something like this:

if (match === null) { 
   let loginRecord = await logins.createRecordAsync({
   //Add record to the table
    'Clients' : [{id: clientId}],
    'UserID' : portalID,
    'Password' : portalPass,
    'Portal' : {name: portal},
 })} else {
   let loginRecord = await logins.updateRecordAsync(clientId,
    {
    //update the client record in the table
    'UserID' : portalID,
    'Password' : portalPass,
    'Portal' : {name: portal},
 });
 }

Suggestions on how to code finding a match?

[Justin_Barrett]

Welcome to the community, @Margro_Advisors! :grinning_face_with_big_eyes: See if this works for you:

let recordToUpdate = ""
let loginsQuery = await logins.selectRecordsAsync()
let clientRecords = loginsQuery.records.filter(record => record.getCellValue("Clients")[0].id === clientId)
let portalRecords = clientRecords.filter(record => record.getCellValue("Portal").name === portal)
if (portalRecords.length) {
    recordToUpdate = portalRecords[0].id
}

On a side note, you can drop the “let loginRecord =” parts of your latter lines. If you’re not going to do anything with the returned record ID, no need to capture it (and updating a record doesn’t return anything anyway, so definitely nix it from the update portion).

Instead of using a match variable, I’m going to use the recordToUpdate string variable. That will serve two purposes. Its size will work as a condition trigger (empty = falsey, non-empty = truthy), and its actual contents if not-empty will be the ID of the record to update.

With those changes applied, your post-match code looks like this:

if (!recordToUpdate) { 
    await logins.createRecordAsync({
        //Add record to the table
        'Clients' : [{id: clientId}],
        'UserID' : portalID,
        'Password' : portalPass,
        'Portal' : {name: portal},
    })
} else {
    await logins.updateRecordAsync(recordToUpdate,
    {
        //update the client record in the table
        'UserID' : portalID,
        'Password' : portalPass,
        'Portal' : {name: portal},
    });
}

[Margro_Advisors]

Thank you so much for responding! I’ve put in your suggestion and got the following error:

TypeError: Cannot read property ‘0’ of null

This is for the “let clientRecords…” line.

After checking the table, I found that some records had the client field blank (user manual entry error). When I removed the blank records from the table, the script worked fine.

Is it possible to add an error check for this or have the query ignore the blank records instead?

[Justin_Barrett]

Heh…I was wondering if there would be a case where no client was linked. :slightly_smiling_face: Try this replacement for that line:

let clientRecords = loginsQuery.records.filter(record => record.getCellValue("Clients") !== null && record.getCellValue("Clients")[0].id === clientId)

The way the && operator works (effectively JavaScript’s version of Airtable’s AND() function), it tests conditions in order from left to right, and will automatically return false after the first failed test without even bothering to test the other conditions. In this case, if a record has no links and the field returns null, it won’t even test the ID match for the first linked item.