Skip to main content

Hi, I hope it's ok to ask for some human help here. Like many, i'm sure, I've been getting OpenAI to help me out when my coding doesn't work, and for the most part that's been fine. But i'm stuck on something now.

I have a script which takes a 'booking' (which is a record in a Bookings table), looks at the linked record for {Episode} and {Element} and increments the number found within each by 1. It then searches up those records in the relevant tables "Episodes" and "Elements" and tries to create a new record with those found RecordIDs in the relevant fields. I am getting stuck as the script reports that the fields in question (which I've confirmed are correct IDs) won't accept the RecordIDs in the format they are being given. 

The fields are linked records, able to accept multiple records, so I think the script is presenting the RecordIDs as an array. the script is below, and also the error I receive.

 
// Correctly retrieve the record ID from the automation's input configuration
let recordId = input.config().recordId;

if (!recordId) {
throw new Error('Record ID is undefined. Make sure the input variable is correctly configured.');
}

let bookingsTable = base.getTable("Bookings");
let episodesTable = base.getTable("Episodes");
let mixElementsTable = base.getTable("Mix Elements");

// Retrieve the trigger record
let record = await bookingsTable.selectRecordAsync(recordId);

let numberOfDuplicates = record.getCellValue("Duplicates") ? parseInt(record.getCellValue("Duplicates"), 10) : 1;
numberOfDuplicates = Math.max(1, numberOfDuplicates); // Ensure at least one duplicate

console.log(`Creating ${numberOfDuplicates} duplicates for record ID ${recordId}`);

async function findNextRecordId(tableName, name) {
let table = base.getTable(tableName);
let records = await table.selectRecordsAsync();
let match = name.match(/(\d+)/);
if (match) {
let num = parseInt(match[1], 10);
let numIndex = name.indexOf(match[1]);
let newNamePrefix = name.substring(0, numIndex);
let newNameSuffix = name.substring(numIndex + match[1].length);
let incrementedNum = num + 1;
let searchPattern = newNamePrefix + incrementedNum + newNameSuffix;
console.log(`Searching for "${searchPattern}" in ${tableName}`);

let found = records.records.find(r => r.name.includes(incrementedNum.toString()) && r.name.startsWith(newNamePrefix) && r.name.endsWith(newNameSuffix));
if (found) {
console.log(`Found matching record "${found.name}" with ID ${found.id} in ${tableName}`);
return [found.id]; // Ensure this is returned as an array
} else {
console.log(`No matching record found for "${searchPattern}" in ${tableName}`);
return null;
}
} else {
console.log(`No numeric part found in "${name}" to increment`);
return null;
}
}

for (let i = 0; i < numberOfDuplicates; i++) {
let episodeName = record.getCellValueAsString("Episode");
let elementName = record.getCellValueAsString("Element");

let newEpisodeId = await findNextRecordId("Episodes", episodeName);
let newElementId = await findNextRecordId("Mix Elements", elementName);

console.log(`Attempting to create record with new Episode ID: ${JSON.stringify(newEpisodeId)} and Element ID: ${JSON.stringify(newElementId)}`);
if (newEpisodeId && newElementId) {
try {
await bookingsTable.createRecordAsync({
"Episode": newEpisodeId, // "Episode" field expects an array of record IDs
"Element": newElementId, // "Element" field expects an array of record IDs
// Include other fields as needed from the original record, ensuring to adjust for correct data types
});
console.log('Record created successfully');
} catch (error) {
console.error(`Error creating record: ${error}`);
}
} else {
console.error('Could not find matching Episode or Element for increment');
}
}

The error I get is:
  1. "Searching for "Mr *** 102" in Episodes"

CONSOLE.LOG

  1. "Found matching record "Mr *** 102" with ID recQ3WRDltScngM4W in Episodes"

CONSOLE.LOG

  1. "Searching for "MBS 102 Foley record" in Mix Elements"

CONSOLE.LOG

  1. "Found matching record "MBS 102 Foley record" with ID recW8ts94WGLesW83 in Mix Elements"

CONSOLE.LOG

  1. "Attempting to create record with new Episode ID: ["recQ3WRDltScngM4W"] and Element ID: ["recW8ts94WGLesW83"]"

CONSOLE.ERROR

  1. "Error creating record: a: Field "fldUVZDMOGZpHpgnS" cannot accept the provided value."

 

[I redacted some data in the episode name for privacy issues]

As I've been dealing with AI Bots all day, I forgot to say "thank you for your help!" 

Hi there! I think you’re close - in your function “findNextRecordId”, you’re returning “ found.id]” which is an array of one string.

A linked record field expects an array of objects, not strings.

Try changing that function to return “ {id: found.id}]” instead of “found.id]” - I think that should do the trick 😊

Incredible! Thank you so much, you were absolutely right. I've adjusted that and it now works. Thank you so much

 

As I've been dealing with AI Bots all day, I forgot to say "thank you for your help!" 


Hi,

Would like to suggest, if you have questions in future, use

it helps quickly distinguish code block

// Retrieve the trigger record

let record = await bookingsTable.selectRecordAsync(recordId);



let numberOfDuplicates = record.getCellValue("Duplicates") ? parseInt(record.getCellValue("Duplicates"), 10) : 1;

numberOfDuplicates = Math.max(1, numberOfDuplicates); // Ensure at least one duplicate



console.log(`Creating ${numberOfDuplicates} duplicates for record ID ${recordId}`);

 from a piece of usual text.

ah yes fantastic, thank you i will. Did think it was likely there was a better way of presenting my info

Reply


Terms & Conditions