I’ve got a nice little script within an automation to auto-categorize transactions in a bookkeeping base. It relies on a table for transactions with various fields, as well as a table called Rules, with the rules I want to create for my categorizations. The Transactions table has the following fields:
*Name - single line text
Lowercase Name - formula
Matched Terms - single line text
*Vendor - single line text
Payment Type (tax purposes) - single line text
Payment Type - single select
Category - Linked field
Merchant - Linked field
Transaction Year - formula
The Rules table has the following fields:
Match Term - single line text
Lowercase Match Term - formula
Payment Type - single select
Desired Vendor Name - single line text
Categories - linked field
Update Rule? - checkbox
It all works great, but now I want to add another parameter to the Rules table, a linked field to my table called Operational Expenses. The Transactions table already has a linked field called “Operational Expenses.” I want the script to see whenever I add a record to the linked field in Rules, that I want that same linked record added to the field in Transactions (same as with the Category item). However, I didn’t write this code, so I don’t know how to go about adding this. I’d also like for it to just do nothing if the Operational Expenses field is blank in the Rules table. Can anyone help me adjust the code so it can include this? Here it is below:
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.getCellValueAsString("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.");
}
Thank you so much!!
