Convert string into an objectid

[Daniel_Melnech1]

Hi all, my first new topic. My background includes coding in many languages, working with mysql, and other database tools (filemaker). New to scripting in AT.

Have a field that is a Link field to another record called “art# FK to Inventory”. And as the name implies, the link points to the Primary Key (aka the first field in a record here in AirTable world). Some of the records have this field blank. Luckily i have a backup copy of this field that has the data that needs to go into this field.

In my first foray into scripting in AirTable (AT), i searched around in scripting basics and a bunch in this fine topic and found the code i needed to try what i thought might work to copy the backup field, which is a Text field, and put it into the Lookup field. Before i hit Run, i put a Break into the code to stop after finding just one blank field to fix just in case. So then I hit Run button to test (i have a backup copy of my base), and got this display:

When i hit the Save button, i then got this message:
Error
L: Can’t set cell values: invalid cell value for field ‘art# FK to Inventory’.
Cell value has invalid format: .0.0.id must be an objectId.
Linked records field value must be an array of objects with property ‘id’ corresponding to linked record id.
at main on line 53

In the code, i am essentially typecasting a Text string to an id in an array. But this obviously is not working.

Is there a way to do what i am trying to do via scripting, which is to automate taking the contents of a Text field, place it into a Link field forcing it to be recognized as an objectID of the linked record’s PK to link the records?

If you got this far, thank you for reading. Any thoughts or insights or code examples will be much appreciated. If i can’t automate, i can copy the text, click on the Link field, then search for the ID to link to with the text i have copied. It will just take some time. Ugh.

Peace,
Dan

Here is the script:

let table = base.getTable(“INVOICE LINE ITEMS”);
let dest = table.getField(“art# FK to Inventory”);
let source = table.getField(“art# FK to Inventory Backup copy”);

// Load all of the records in the table
let result = await table.selectRecordsAsync();

// Find every record we need to update
let replacements = ;
for (let record of result.records) {
let originalValue = record.getCellValue(dest);

// Skip records which have a value set
if (originalValue) {
    continue;
}

// i tried just as Text string and that didn't work, so tried to "typecast" with id:
let newValue = [{id: record.getCellValue(source)}];

if (originalValue !== newValue) {
    replacements.push({
        record,
        before: originalValue,
        after: newValue,
    });
}

// Break out here for testing after first one
break

}

if (!replacements.length) {
output.text(‘No replacements found’);
} else {
output.markdown(’## Replacements’);
output.table(replacements);

let shouldReplace = await input.buttonsAsync('Are you sure you want to save these changes?', [
    {label: 'Save', variant: 'danger'},
    {label: 'Cancel'},
]);

if (shouldReplace === 'Save') {
    // Update the records
    let updates = replacements.map(replacement => ({
        id: replacement.record.id,
        fields: {
            [dest.id]: replacement.after,
        }
    }));

    // Only up to 50 updates are allowed at one time, so do it in batches
    while (updates.length > 0) {
        await table.updateRecordsAsync(updates.slice(0, 50));
        updates = updates.slice(50);
    }
}

}

[Daniel_Melnech1]

Sorry that all the code didn’t come out as code style. Not sure what happened.

[Kamille_Parks]

You have to get the actual record ID for the record which has the primary field value that matches your “source” field. For that you’d need to filter a query of the intended table for fieldValue = record.getCellValue(source) and then do let newValue = [{id: filtered[0].id}].

// Add this to outside your loop
let inventoryTable = base.getTable("I assume this table is called Inventory")
let inventoryQuery = await inventoryTable.selectRecordsAsync()
let inventoryRecords = inventoryQuery.records

// Add this to the loop
let filtered = inventoryRecords.filter(x => x.getCellValue("Primary field name") == record.getCellValue(source))

let newValue = [{id: filtered[0].id}]

[Daniel_Melnech1]

Hi Kamille!

First, thanks so much!

The code makes sense: do a search of “Inventory” table’s Primary Field (PK) for ones that match my text string. And then take the id of the 0th element of the matching array and use that value for my link field that wants the id. Essentially what you do if you do a manual search to link in AT. Code will be so much better!

So i should add a check to see if there the array returned has no items, and if it has more than 1 and decide what to do.

Thanks again!

Peace,
Dan

[Daniel_Melnech1]

Hi Kamille,

Worked great. Using it in all sorts of scripts to fix this database. .filter() is clearly an important tool in scripting. Thanks for bootstrapping my toolbox!

You rock!

Peace,
Dan