primary field matching cross tables and adding them to a linked field?!?

Noobwithideas · ‎Mar 03, 2023

Hi Y'all!

First post. real problem. Have been trying for 2 days
(script below text)

I have a workplace problem that i am determined to solve using Airtable.
In table: Product data i have 1500+ product records with a primary barcode field called 'BRCDP'
In table: Scanlist i created there's a field called 'BRCDA' also a primary barcode field.

I needed a script that is be able to compare a dataset an match the records primary field, if a new records added to table: Scanlist matches a existing barcode in table: Product data, this new record should be automatically be added to the designated linked record field. It keeps crashing.

Does anyone have a simply script for this?

const productDataTable = base.getTable('Product data');
const scanListTable = base.getTable('Scanlist');

async function linkRecords() {
const queryResult = await scanListTable.selectRecordsAsync({
maxRecords: 1
});
const scanRecord = queryResult.records[0];
const scanBarcode = scanRecord.getCellValue('BRCDA');
const filterFormula = `{BRCDP} = "${scanBarcode}"`;
const productDataResult = await productDataTable.selectRecordsAsync({filterByFormula: filterFormula});
if (productDataResult.records.length > 0) {
const productDataRecord = productDataResult.records[0];
const linkedRecords = productDataRecord.getCellValue('Active Scans') || [];
const existingLinkedRecordIds = linkedRecords.map(linkedRecord => linkedRecord.id);
if (!existingLinkedRecordIds.includes(scanRecord.id)) {
linkedRecords.push({id: scanRecord.id});
await productDataTable.updateRecordAsync(productDataRecord, {'Active Scans': linkedRecords});
}
}
}

linkRecords();

Ben_Young1 · ‎Mar 03, 2023

Hey @Noobwithideas!

Wanted to flag you down and let you know that I'm working on something for you.
Sit tight and I'll post the finished product for you.

Noobwithideas · ‎Mar 03, 2023

Wow thanks! @Ben_Young1 I'll hold my horses!

Ben_Young1 · ‎Mar 03, 2023

Okay, here's what I have for you.

Here's a quick walkthrough of the test data I put together.
I created two tables: Products and Transactions.
Super simple.

Now, each transaction record needs to be linked to its respective product record.
For the script, you'll need to fill in a few details:

You won't have to set the details each time you run the script, so don't worry about it taking too much time.
Once you've confirmed all the settings and hit Run, you'll see a quick confirmation page that will let you know how many records you're about to update and ask for a confirmation.

It also outlines that this is a destructive update action.
Once you continue, the update will commence.

Really straight forward.
Here's the script itself:

const config = input.config({
    title: "🔗 Product Matcher",
    description: "This script was created in the context of a forum post for 'Noobwithideas' originally posted to the Airtable forums on March 3rd, 2023.",
    items: [
        input.config.table("_productTable", {
            label: "⭐ Please Select Your Product Table"
        }),
        input.config.field("_productPrimaryField", {
            label: "- Product Primary Field",
            parentTable: "_productTable"
        }),
        input.config.field("_relationshipField", {
            label: "- Product Table Linked Record Field",
            parentTable: "_productTable"
        }),
        input.config.table("_transactionTable", {
            label: "🗃 Please Select Your Transaction Table"
        }),
        input.config.field("_transactionPrimaryField", {
            label: "- Transaction Primary Field",
            parentTable: "_transactionTable"
        })
    ]
});

const { _productTable, _productPrimaryField, _relationshipField, _transactionTable, _transactionPrimaryField } = config;

const productRecords = await _productTable.selectRecordsAsync({fields: [_productPrimaryField.id, _relationshipField.id]})
    .then(query => query.records);

const transactionRecords = await _transactionTable.selectRecordsAsync({fields: [_transactionPrimaryField]})
    .then(query => query.records)
    .then(records => records
        .map(transactionRecord => ({
            id: transactionRecord.id,
            value: transactionRecord.getCellValueAsString(_transactionPrimaryField).trim()
        }))
    );

let productUpdates = productRecords
    .map(productRecord => {
        let updateObject = {
            id: productRecord.id,
            fields: {}
        };
       let transactions = transactionRecords
            .filter(transactionRecord => transactionRecord.value === productRecord.getCellValueAsString(_productPrimaryField.id))
            .map(result => ({id: result.id}));
    
    updateObject.fields[`${_relationshipField.id}`] = transactions;    
        return updateObject;
    });



const getConfirmation = async (updates) => {
    let totalUpdates = updates.length;
    output.clear();
    output.markdown(`## Before We Begin...

- **This Script Will Update ${totalUpdates === 1 ? `One Product Record`: `${totalUpdates} Product Records`}.**
- ⚠ **Caution**: Any product records that could *not* be matched to a transaction with preexisting linked transaction records will be unlinked in this update.
    `);
    return await input.buttonsAsync("", [
        {label: "👍 Continue", value: true, variant: 'primary'},
        {label: "❌ Cancel", value: false, variant: 'danger'}
    ]);
};


await getConfirmation(productUpdates)
    .then(async choice => {
        if (choice) {
            output.markdown(`### Updating...`)
            while (productUpdates.length > 50) {
                await _productTable.updateRecordsAsync(productUpdates.splice(0, 50));
                productUpdates = productUpdates.splice(50);
            };
            await _productTable.updateRecordsAsync(productUpdates)
                .then(() => output.markdown(`#### ✔ Update Successful!`))
        } else {
            output.clear();
            output.markdown(`### ❌ Script Cancelled`)
        }
    });

I'll take some time later today and toss it into a github gist with a bit of documentation, but this should work without needing much (if any) customization.

The biggest chance for an error will be when you're configuring the script settings.
If you run into an error, you'll definitely want to double-check that all the settings are correct.

On a separate note, I would recommend that you refactor your transactions (scans) table so that you don't have a ton of indistinguishable records.
Specifically, you should never have records with identical primary field values.
I would recommend that create formula-based primary field that creates a value like this:

{transactionId}
{productSku}
{transactionDate}

tr302io2
0001156
03/02/2023

Naturally, that's just an example, nonetheless, you should be able to identify unique records based on their primary field values.