Help

Trouble with a script and multi-select fields

Topic Labels: Base design
Solved
Jump to Solution
359 3
cancel
Showing results for 
Search instead for 
Did you mean: 
mu71rd
6 - Interface Innovator
6 - Interface Innovator

I have a script that I'm having difficulties with. The script is part of an automation block which is triggered by a new record being added to a table called Projects. This record in the projects field has some info about it, (how many episodes, series number, etc) but crucially, has a multi-select field. 

The automation is supposed to look at the trigger record, which has a field for how many 'episode' records it should create, and then makes the episode records and links them back to the project. It is supposed to also copy the multi-select entries that are in the triggering record and make them the same in the newly created 'episode' records. But it seems to be failing at that, and I'm getting an error:

 

Error: Field "fldYr1BVOeRj2DLLz" cannot accept the provided value.
    at main on line 43

 

Can anyone see from the script what might be the issue. The field mentioned above is the 'Elements to create' field in the episodes table. 

Here's the script:

 

let inputConfig = input.config();
let recordId = inputConfig.recordId;

console.log(`Starting script for Project ID: ${recordId}`);

let projectsTable = base.getTable("Projects");
let episodesTable = base.getTable("Episodes");

console.log("Fetching the triggering Project record...");
let projectRecord = await projectsTable.selectRecordAsync(recordId);
if (!projectRecord) {
    throw new Error('Triggering project record not found');
} else {
    console.log(`Project record found: ${projectRecord.name}`);
}

let howManyEpsToMake = projectRecord.getCellValue("How many eps to make?");
if (!howManyEpsToMake) {
    throw new Error('How many eps to make? field is empty or invalid');
} else {
    console.log(`Number of episodes to make: ${howManyEpsToMake}`);
}

let elementsToCreate = projectRecord.getCellValue("Elements to create");
if (!elementsToCreate) {
    console.log('No elements to create or field is empty');
} else {
    console.log(`Elements to create will be copied: ${elementsToCreate.map(elem => elem.name).join(', ')}`);
}

console.log("Fetching existing Episode records linked to this Project...");
let existingEpisodesQuery = await episodesTable.selectRecordsAsync();
let existingEpisodesForProject = existingEpisodesQuery.records.filter(record =>
    record.getCellValue("Project") && record.getCellValue("Project").find(link => link.id === recordId)
);

let existingEpisodeNumbers = existingEpisodesForProject.map(record => record.getCellValue("Ep"));
console.log(`Found existing episode numbers: ${existingEpisodeNumbers.join(', ')}`);

for (let epNumber = 1; epNumber <= howManyEpsToMake; epNumber++) {
    if (!existingEpisodeNumbers.includes(epNumber)) {
        console.log(`Attempting to create Episode ${epNumber} with Elements to Create: ${JSON.stringify(elementsToCreate.map(elem => ({name: elem.name})))}`);
        await episodesTable.createRecordAsync({
            "Project": [{ "id": recordId }],
            "Ep": epNumber,
            "Elements to create": elementsToCreate // Direct copy of the multi-select field without changes
        });
        console.log(`Successfully created Episode ${epNumber}.`);
    } else {
        console.log(`Episode ${epNumber} already exists. Skipping.`);
    }
}

console.log(`Script execution completed.`);

 

thank you in advance for your help. 

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

The issue is that the two multi-select fields are in different tables. The "read" format of a multiple-selects field is an array of objects that include the choice ID. Different select fields have different choice IDs, even if they have the same names. By using the exact same value that you got from the original record in one table, you are using the choice IDs for the first table, which do not exist in the second table.

See the scripting documentation on multiple-selects for more info.

You can map the array you got from the initial record to a new array that only has the name, and not the ID.

const choicesForNewTable = choicesFromOldTable.map(choice => ({name: choice.name}))

If there is a chance that the choicesFromOldTable might be blank, you will also need to have code to adjust for that.

 

See Solution in Thread

3 Replies 3
kuovonne
18 - Pluto
18 - Pluto

The issue is that the two multi-select fields are in different tables. The "read" format of a multiple-selects field is an array of objects that include the choice ID. Different select fields have different choice IDs, even if they have the same names. By using the exact same value that you got from the original record in one table, you are using the choice IDs for the first table, which do not exist in the second table.

See the scripting documentation on multiple-selects for more info.

You can map the array you got from the initial record to a new array that only has the name, and not the ID.

const choicesForNewTable = choicesFromOldTable.map(choice => ({name: choice.name}))

If there is a chance that the choicesFromOldTable might be blank, you will also need to have code to adjust for that.

 

mu71rd
6 - Interface Innovator
6 - Interface Innovator

Incredible, I'm humbled. I've changed the code and it now works. Thank you so much. 

Incredible, I'm humbled. I've changed the code and it now works. Thank you so much.