XLSX uploader script - update linked record or create record if it doesn't exist

[Devon_Moore]

Hi all,

I am using the Spreadsheet Importer in the Scripting Extension to upload an XLSX file to create new records in my table (“Report Data”). One of the fields in this XLSX report is a Customer ID. That should be a linked field to a table called “Customer IDs”.

After going through a few different threads, I declared the following variables to create a “Dictionary” array of the Customer IDs (primary field) & their RecordIDs:

let IDTable = base.getTable("Customer IDs");
let IDQuery = await IDTable.selectRecordsAsync();
let IDDict = {};
for (let record of IDQuery.records) {
    IDDict[record.name] = record.id;
}

Then when I am mapping the fields I am declaring that field like so:

    let newRecords = xlsxRows.map(xlsxRow => ({
        fields: {
           'Customer ID': [{ id: IDDict[String(xlsxRow['Customer ID'])] }]
        }
    }));

This works… only IF the ID already exists. If an ID is new, I get the following error:

j: Can't create records: invalid cell value for field 'Customer ID'.
Cell value has invalid format: <root>.0.0.id is missing.
Linked records field value must be an array of objects with property 'id' corresponding to linked record id.
    at main on line 143

Is there any way I can modify this code so that it links to the existing record if it exists, but creates a new record if it does not exist? I am able to achieve this via automation but can’t figure it out with scripting.

Thanks in advance!

[Justin_Barrett]

There’s no way to immediately create a link to a brand new record while in the middle of creating another record. You need to create the new target record first, then you can link to it.

This will have to be done in two different groups: one group for customer records that already exist, and another group for those that don’t. To do this, use the filter() method on your xlsxRows array to target records from the [Customer IDs] table that either do or do not match against the incoming IDs.

[Devon_Moore]

Thank you Justin! I’ve been messing with this for a while but I’m definitely not fluent in JS…

When I try the code below I am getting a blank array as a result:

let filteredRecords = IDQuery.records.filter(ID => {
    return ID.getCellValue('Customer ID').includes(String(xlsxRow['Customer ID']))
    })
console.log(filteredRecords);

I think I’m doing something wrong with the includes() portion, because when I hard-code an ID I do get the result.

let filteredRecords = IDQuery.records.filter(ID => {
    return ID.getCellValue('Customer ID').includes('123')
    })
console.log(filteredRecords);

How do I properly update the includes() function to check for all IDs in the file? Any suggestions?

Thank you!

[Justin_Barrett]

Devon_Moore:

When I try the code below I am getting a blank array as a result:

let filteredRecords = IDQuery.records.filter(ID => {
    return ID.getCellValue('Customer ID').includes(String(xlsxRow['Customer ID']))
    })
console.log(filteredRecords);

What field type is {Customer ID}?

Your structure looks good, though I’d recommend a couple of tweaks:

1. When choosing the variable name for array methods like filter, map, etc., my suggestion is to name it based on the type of the array item. In this case it’s a record, so I’d recommend record over ID.
2. While forcing the customer ID from your XLSX data into a string by wrapping it in the String constructor will work, the preferred method is to use the .toString() method

With those two things in mind, here’s an alternate version of your snippet:

let filteredRecords = IDQuery.records.filter(record => {
    return record.getCellValue('Customer ID').includes(xlsxRow['Customer ID'].toString())
})
console.log(filteredRecords);

I don’t think that will change the output any, but you might try it and see. If not, try adding some console.log() statements to show what’s being compared. Something like this:

let filteredRecords = IDQuery.records.filter(record => {
    console.log(`Comparing "${record.getCellValue('Customer ID')}" against "${xlsxRow['Customer ID'].toString()}"`)
    return record.getCellValue('Customer ID').includes(xlsxRow['Customer ID'].toString())
})
console.log(filteredRecords);