Hello,
I’ve got a Bookkeeping Airtable Base and have created a record review interface for the user to review new records, created with transaction info pulled from their banks. They’ve got to make sure they categorize the transaction and that it has something written into the Vendor field, then they hit the “save” button, which I created to run an automation that updates some things behind the scenes, crucially, pasting the value of a formula field (that adds up the Vendor field plus the transaction year) into a linked field called Merchant, and then marking that record as “reviewed.”
So far it’s been going wonderfully, but I ran into an issue when a user told me they hit “save” but nothing happened, and going in I realized that the automation only works when there is already a record in the linked Merchants tab with the correct Vendor+year combo. If this transaction is from a brand new vendor, the automation doesn’t work, because – as I learned by checking out the Airtable Community posts – Airtable doesn’t paste text into linked fields via automations apparently, even though when I manually paste text into a linked field it DOES create new records in the linked table automatically. The error message I’m getting is: “Field "Merchant" cannot accept the provided value: Could not find matching rows for string.”
Per my reading on the topic, the only way to get it to create a new record in the linked table is by running a scrip (please correct me if I’m wrong here). I’m not really knowledgeable in that area, so I tried it with the help of ChatGPT (I’ve successfully created a script that works great that way once). It came up with this:
// Define the input variables
let inputConfig = input.config();
let transactionId = inputConfig.transactionId; // The ID of the record in Transactions
let merchantFormulaValue = inputConfig.merchantFormulaValue; // The value from Merchant Formula
// Log the input variables for debugging
console.log("Transaction ID:", transactionId);
console.log("Merchant Foramula Value:", merchantFormulaValue);
// Define the tables
let transactionsTable = base.getTable("Transactions");
let merchantsTable = base.getTable("Merchants & Tax Docs");
// Check if the merchant already exists
let existingMerchants = await merchantsTable.selectRecordsAsync();
let existingMerchantRecord = existingMerchants.records.find(record => record.name === merchantFormulaValue.trim());
let merchantRecordId;
if (existingMerchantRecord) {
// If the merchant exists, use its ID
merchantRecordId = existingMerchantRecord.id;
console.log("Found existing merchant record:", existingMerchantRecord);
} else {
// If the merchant does not exist, create a new record
let newMerchantRecord = await merchantsTable.createRecordAsync({
"Name": merchantFormulaValue.trim() // Adjust the field name if necessary
});
// Retrieve the ID of the new record
merchantRecordId = newMerchantRecord.id;
console.log("Created new merchant record:", newMerchantRecord);
}
// Log the merchant record ID before updating
console.log("Merchant Record ID:", merchantRecordId);
// Update the Transactions record with the linked Merchant
await transactionsTable.updateRecordAsync(transactionId, {
"Merchant": s{ id: merchantRecordId }] // Adjust the field name if necessary
});
these are the input variables:
And this is the result I’m getting:
Is this code nonsense? I sure cannot tell! 
If anyone has any insight into this I’d be ever so grateful. I could swear I’ve successfully had an Automation create new records in a linked table by simply pasting a value into the linked field, but for some reason it doesn’t want to do it this time and I don’t know why. Thank you all so much!
