Automation Scripting Error - Field cannot accept provided value

[Megan_Ecclesto1]

Not sure why I keep getting an error message when I try to automate this script:

I just need the first 5 digits of the Project code pasted into the 5 digit (linked) column. If it doesn’t already exist, it needs to create a new record.
Help?

[Megan_Ecclesto1]

Full code:

//home table – long//

let longTable = base.getTable(“AIR Project List(SubCodes)”);

let queryResult = await longTable.selectRecordsAsync();

let record = queryResult.records[0];

let codeFull = record.getCellValueAsString(“Project Code”);

let fiveD = codeFull.substring(0,4)

//destination table - short//

let shortTable = base.getTable(“AIR Project List (5 digit)”);

let shortQuery = await shortTable.selectRecordsAsync();

let config = input.config();

// Find the matching record//

let matched = shortQuery.records.filter(code => {return code.name === config.fiveD});

let codeRecord;

// If a matching record exists, use it; otherwise make a new record//

if (matched.length)

codeRecord = matched[0];

else

codeRecord = await shortTable.createRecordAsync({"Project Code_First5": config.fiveD});

await longTable.updateRecordAsync(config.recordId, {“AIR Project Code 5digit”: [{id: codeRecord.id}]});

[kuovonne]

Does the script work when there is a matching record?

If it only fails when the script needs to create a matching record, the problem probably is because the createRecordAsync function returns the id of the new record, not the full record.

[Justin_Barrett]

There are a few errors that I see:

1. Your script has an input variable name recordId, but you’re only using it to update the found/created record in the latter part of the script. If I understand your goal correctly, that record also contains the full project code that you’re trying to find in your second table.
2. You’re trying to use an input variable named fiveD, but no input variable exists with that name.
3. At the top of your script, you’re collecting the first record from the table, instead of the ID of the record that triggered the automation. That triggering record ID is the recordId input variable that’s not currently being used.
4. When attempting to grab the first five characters of the project code into the fiveD variable, you’re actually only grabbing the first four. The substring method grabs a series of characters starting with the position indicated by the first parameter, up to but not including the position indicated by the last parameter. That second number should be 5, not 4.

Here’s how I think the code should look with those changes:

//home table – long//
let longTable = base.getTable("AIR Project List(SubCodes)");
let queryResult = await longTable.selectRecordsAsync();
let config = input.config();
let record = queryResult.getRecord(config.recordId);
let codeFull = record.getCellValueAsString("Project Code");
let fiveD = codeFull.substring(0,5);

//destination table - short//
let shortTable = base.getTable("AIR Project List (5 digit)");
let shortQuery = await shortTable.selectRecordsAsync();

// Find the matching record//
let matched = shortQuery.records.filter(code => {return code.name === fiveD});

let codeRecord;
// If a matching record exists, use it; otherwise make a new record//
if (matched.length)
    codeRecord = matched[0];
else
    codeRecord = await shortTable.createRecordAsync({"Project Code_First5": fiveD});
    
await longTable.updateRecordAsync(config.recordId, {"AIR Project Code 5digit": [{id: codeRecord.id}]});

[kuovonne]

Great job finding and investigating the different issues in the script.
Your code is clearly written and easy to read.

I suggest the changing the variable codeRecord to codeRecordId because the createRecordAsync function returns a record id, not a complete record. You can see the changes in the snippet below.

let codeRecordId; // change name of variable to reflect new value
if (matched.length)
    codeRecordId= matched[0].id; // get the id of the record, not just the record
else
    codeRecordId= await shortTable.createRecordAsync({"Project Code_First5": fiveD}); // returns an id, not a record
    
await longTable.updateRecordAsync(config.recordId, {"AIR Project Code 5digit": [{id: codeRecordId}]}); 

[Justin_Barrett]

Great catch, @kuovonne! I haven’t written many scripts that create records, and still need to be reminded that createRecordAsync returns just the ID.

[Megan_Ecclesto1]

Unfortunately even with those updates I am still receiving the same error code. The Linked column fields cannot accept that value.

The ultimate goal here is to automate what I would do manually, which is just paste the first 5 digits of the project code into the 5 digit column, which is linked, creating (or using an existing) record in the 5 digit linked tab.

So, still wont let me do that.

[Megan_Ecclesto1]

If i run the code
let longTable = base.getTable(“AIR Project List(SubCodes)”);

let queryResult = await longTable.selectRecordsAsync();

let config = input.config();

let record = queryResult.getRecord(config.recordId);

let codeFull = record.getCellValueAsString(“Project Code”);

let fiveD = codeFull.substring(0,4)

it will output the 5 digits into the log. So I KNOW it pulls the right data from the right place. It just wont paste it into the linked field on that record.

Thank you so much for helping me with this @Justin_Barrett and @kuovonne!!

[kuovonne]

I recommend that you write the script using scripting app so that you can better debug the script. For example, in an automation, you can’t tell where the process is breaking down. It could be that you are receiving the wrong digits. It could be that you are filtering on the wrong value. It could be that the read and write formats are different, etc. There are way too many possibilities given that we don’t know the field types or data values.

[Megan_Ecclesto1]

I have tried the script in the scripting app. It pulls the correct data, and before the If/Then statement console.log(fiveD) returns the correct value. The same error message occurs in the scripting app. The issue is that the fiveD value cannot be added to the linked column field.