Skip to main content

Getting this "Error updating record: Field "fldkFF6U0cEliKKyy" cannot accept the provided value."

  • September 30, 2024
  • 3 replies
  • 0 views
C

I am creating an automation script in transferring data from a source table to a target table. The source contains AI generated responses so everything is in text. I have 4 fields in the target table as single-select fields and I found out that the error I am getting is for those fields. Here's the code below. Hope someone can help.

 
let sourceTable = base.getTable('Update Repository'); 
let targetTable = base.getTable('UPdates'); 
 
let inputConfig = input.config();
let recordId = inputConfig.recordId;
let newRecord = inputConfig.newRecord;

// Fetch the record from the source table
let record = await sourceTable.selectRecordAsync(recordId);

// Fields where AI responses are stored in the source table
let flag1 = record.getCellValue('Name change - AI'); 
let flag3 = record.getCellValue('Future Fundraising - AI');
let flag4 = record.getCellValue('Future Tokens - AI');
let flag5 = record.getCellValue('Future Exit - AI');
let flag6 = record.getCellValue('Future Shutdown - AI');
let flag7 = record.getCellValue('One-liner AI');
let flag8 = record.getCellValue('Website Update - AI');
let flag9 = record.getCellValue('New Investors - AI');
let daterecord = record.getCellValue('Date'); 

function isFlagPresent(flag) {
    return flag !== 'None' && flag !== 'None.';
}

// Define valid options for single select fields
const validOptions = {
    Future_Raise: ['Mentioned', 'Not mentioned'],
    Future_Tokens: ['Mentioned', 'Not mentioned'],
    Future_Exit: ['Mentioned', 'Not mentioned'],
    Future_Shutdown: ['Mentioned', 'Not mentioned'],
};

if (newRecord) {
    let updateFields = {
        'Brand_Up': isFlagPresent(flag1) ? flag1 : null, 
        'Future_Raise': isFlagPresent(flag3) && validOptions.Future_Raise.includes('Mentioned') ? 'Mentioned' : 'Not mentioned',
        'Future_Tokens': isFlagPresent(flag4) && validOptions.Future_Tokens.includes('Mentioned') ? 'Mentioned' : 'Not mentioned',
        'Future_Exit': isFlagPresent(flag5) && validOptions.Future_Exit.includes('Mentioned') ? 'Mentioned' : 'Not mentioned',
        'Future_Shutdown': isFlagPresent(flag6) && validOptions.Future_Shutdown.includes('Mentioned') ? 'Mentioned' : 'Not mentioned',
        '1Liner_Up': isFlagPresent(flag7) ? flag7 : null,
        'Co-URL_Up': isFlagPresent(flag8) && flag8.startsWith("http") ? flag8 : null, // Ensure valid URL
        'New Investors_Up': isFlagPresent(flag9) ? flag9 : null,
        'Up_Date': daterecord ? daterecord : undefined,
    };

    // Log the update fields to check their values
    console.log("Update Fields:", JSON.stringify(updateFields, null, 2));

    // Check if any field is undefined
    for (const [key, value] of Object.entries(updateFields)) {
        if (value === undefined) {
            console.warn(`Field "${key}" is undefined. Skipping...`);
            delete updateFields[key]; // Optionally, remove undefined fields
        }
    }

    // Update the existing record in the target table
    try {
        await targetTable.updateRecordAsync(newRecord, updateFields);
        console.log(`Record ${newRecord} updated successfully.`);
    } catch (error) {
        console.error(`Error updating record: ${error.message}`);
    }
} else {
    throw new Error('No target record ID provided for updating.');
}

3 replies

TheTimeSavingCo
Forum|alt.badge.img+18

Check out the API docs for the expected formatting when updating single select fields: https://airtable.com/developers/scripting/api/cell_values#single-select

It expects:

{ id: string } | { name: string }
Adam, Consultant @ The Time Saving Co
C
  • cyber_nales
  • Author
  • New Participant
  • 1 reply
  • October 1, 2024
TheTimeSavingCo wrote:

Check out the API docs for the expected formatting when updating single select fields: https://airtable.com/developers/scripting/api/cell_values#single-select

It expects:

{ id: string } | { name: string }

If I am to correct my script, how would that apply?

 

Alexey_Gusev
Forum|alt.badge.img+12
  • Alexey_Gusev
  • Inspiring
  • 1111 replies
  • October 2, 2024

Hi,

I don't like to edit chatGPT generated code, so I rewrited it using my templates for basic operations
unfortunately, can't test, too much tables/fields to create

complete writeto={ } with other field pairs 

 

const source=base.getTable('Update Repository')
const dest=base.getTable('UPdates')
const {recordId,newRecord}=input.config()
const rec=await source.selectRecordAsync(recordId)
if(!rec) throw new Error('record not exists')

const writeto={
'Future Exit - AI' : 'Future_Exit',
'Future Shutdown - AI' : 'Future_Shutdown',
'One-liner AI' : '1Liner_Up',
'Website Update - AI' : 'Co-URL_Up'
}

const flds=writeto.keys().map(k=>source.getField(k))
const flag=x=>['None','None.'].includes(x)? 'Not mentioned':'Mentioned'
const convert=(fld,val=rec.getCellValue(fld))=>fld.type.includes('elect')
? {name:flag(val)}: writeto[fld.name].includes('URL')
? (val.startsWith('http')? val:null ) : val

const update=Object.fromEntries(flds.map(f=>[writeto[f],convert(f)]).filter(el=>el[1]))
console.log(update); //filter el[0,1]=el[field,value]=>filter null values
await dest.updateRecordsAsync([{id:newRecord,fields:update}])

 

 
be aware to use with number fields in source, 0 will be filtered same as null.

Additionally, you can use this small script to get field(s) by part of ID, for example FF6 in your case

 

const fid=await input.textAsync('field ID or part').then(x=>a=>a.filter(b=>b.id.includes(x)));
output.table(base.tables.filter(n=>fid(n.fields).length).map(t=>[t.name,fid(t.fields).map(f=>f.name).join()]))

 

 

Reply


Most helpful members this week

Terms & Conditions