Help

Field won't accept RecordId from script (formatting issue?)

Topic Labels: Automations
278 5
cancel
Showing results for 
Search instead for 
Did you mean: 
mu71rd
6 - Interface Innovator
6 - Interface Innovator

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]

5 Replies 5
mu71rd
6 - Interface Innovator
6 - Interface Innovator

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

AlliAlosa
10 - Mercury
10 - Mercury

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 😊

mu71rd
6 - Interface Innovator
6 - Interface Innovator

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

 

Hi,

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

Alexey_Gusev_0-1711031723595.png

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.

mu71rd
6 - Interface Innovator
6 - Interface Innovator

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