Hi there,

I’ve got a bookkeeping base I created and added an Auto-categorization feature that’s giving me some trouble. Basically, there is a Transactions Table, a Categories table, a Merchants table and a Rules table. In the rules table, you add the match terms you want the automation to look for, and a linked field with the category you want it to be. The way the automations works is this: 

1. record is created in Transactions, triggers automation
2. automation looks to see if any match terms in the Rules table match up with the text in the transaction name
3. if there’s a match, automation runs a script to fill in the category and some other fields (like “merchant”, which is a combo of the Vendor name that comes filled in from the bank plus the transaction year, and “payment type” etc) for the transaction in question

I confess I’m not adept at scripting and enlisted the help of ChatGPT to work this out. The script seems to be working most of the time, but recently has started throwing an error I don’t understand. Could anyone who knows more about this than I do take a look and let me know what’s going wrong? 

Script:

 

let inputConfig = input.config();
let transactionsTable = base.getTable("Transactions");
let matchTermsTable = base.getTable("Rules");
let merchantsTable = base.getTable("Merchants & Tax Docs"); // Updated table name

// Get the record ID of the newly created transaction
let transactionRecordId = inputConfig.transactionRecordId;

// Fetch the transaction record
let transactionRecord = await transactionsTable.selectRecordAsync(transactionRecordId);

// Get the lowercase transaction name
let transactionName = transactionRecord.getCellValue("Lowercase Name");

// Fetch the transaction year
let transactionYear = transactionRecord.getCellValue("Transaction Year");

// Fetch all match terms
let matchTerms = await matchTermsTable.selectRecordsAsync();

// Check if there are any match terms
if (matchTerms.records.length === 0) {
    console.warn("No match terms found in the Rules table. Exiting script.");
    return; // Exit the script if no match terms are found
}

let matchedTerms = []; // Initialize matchedTerms here
let vendorToUpdate = ""; // Variable to store the vendor from the matched term
let paymentTypeToUpdate = ""; // Variable to store the payment type from the matched term
let categoryToUpdate = []; // Variable to store the categories from the matched term

// Loop through the match terms to check if the transaction name contains any of them
for (let matchTerm of matchTerms.records) {
    let matchTermValue = matchTerm.getCellValue("Lowercase Match Term");
    if (transactionName.includes(matchTermValue)) {
        matchedTerms.push(matchTerm.name); // Store the matched term name
        vendorToUpdate = matchTerm.getCellValue("Desired Vendor Name") || "Unknown Vendor"; // Default value
        paymentTypeToUpdate = matchTerm.getCellValue("Payment Type") || "Unknown Payment Type"; // Default value
        categoryToUpdate = matchTerm.getCellValue("Categories") || []; // Default to empty array
        break; // Exit the loop after the first match (if you only want the first match)
    }
}

// Only update the transaction record if there are matched terms
if (matchedTerms.length > 0) {
    // Construct the Merchant value
    let merchantName = vendorToUpdate + " " + transactionYear;
    console.log("Constructed Merchant Name:", merchantName); // Log the constructed merchant name

    let existingMerchants = await merchantsTable.selectRecordsAsync(); // Updated table reference
    let existingMerchant = existingMerchants.records.find(record => record.getCellValue("Name") === merchantName);

    let merchantId; // Declare merchantId variable

    if (existingMerchant) {
        // If the merchant already exists, use its ID
        merchantId = existingMerchant.id;
        console.log("Found existing merchant:", existingMerchant.name); // Log the found merchant
    } else {
        // Create a new record in the Merchants & Tax Docs table if it doesn't exist
        let newMerchantRecord = await merchantsTable.createRecordAsync({
            "Name": merchantName // Assuming "Name" is the field in the Merchants & Tax Docs table where you want to store this value
        });
        merchantId = newMerchantRecord.id; // Get the ID of the newly created merchant record
        console.log("Created new merchant:", merchantName); // Log the created merchant
    }

    // Update logic here...
    let updateData = {
        "Matched Terms": matchedTerms.join(", "), // Assuming this is a text field
        "*Vendor": vendorToUpdate !== "Unknown Vendor" ? vendorToUpdate : null, // Only set if valid
        "Payment Type (tax purposes)": paymentTypeToUpdate !== "Unknown Payment Type" ? paymentTypeToUpdate : null, // Only set if valid
        "Category": Array.isArray(categoryToUpdate) && categoryToUpdate.length > 0 ? categoryToUpdate : null, // Only set if valid
        "Merchant": merchantId ? [{id: merchantId}] : null // Only set if valid
    };

    // Log the update data to see what is being sent
    console.log("Updating record with data:", updateData);

    // Filter out any null values from updateData
    updateData = Object.fromEntries(Object.entries(updateData).filter(([_, v]) => v != null));

    if (Object.keys(updateData).length > 0) {
        await transactionsTable.updateRecordAsync(transactionRecordId, updateData);
    } else {
        console.warn("No valid fields to update.");
    }
} else {
    console.log("No matched terms found. No updates will be made to the transaction record.");
}

Screenshots:

If anyone has any thoughts, I’d really appreciate them!