Re: Can't programmatically create new records because of linked records

Ulrich_Mabou · ‎Jul 28, 2020

Hi all,

I have 2 tables in a base (Businesses & Users); I’m using the scripting block to upload a JSON file and add new records to the bases if they don’t already exist. Attached are the screenshots of the 2 tables and the script.

When the ‘Users’ & ‘Businesses’ fields in the 2 tables are not linked, I can update the records with the script, but when they are linked, I get the following error when I run the script.

What can I do to fix it, please?

O: Can't create records: invalid cell value for field 'Users'.
Cell value has invalid format: <root> must be an array.
Linked records field value must be an array of objects with property 'id' corresponding to linked record id.
    at spawnErrorWithOriginOmittedFromStackTrace on line 1
    at _assertMutationIsValid on line 1
    at applyMutationAsync$ on line 1
    at tryCatch on line 1
    at invoke on line 1
    at tryCatch on line 1
    at invoke on line 1
    on line 1
    at Promise
    at callInvokeWithMethodAndArg on line 1
    at enqueue on line 1
    on line 1
    at createRecordsAsync$ on line 1
    at tryCatch on line 1
    at invoke on line 1
    at tryCatch on line 1
    at invoke on line 1
    on line 1
    at Promise
    at callInvokeWithMethodAndArg on line 1
    at enqueue on line 1
    on line 1
    at tryCatch on line 1
    at invoke on line 1
    at asyncGeneratorStep on line 1
    at _next on line 1
    on line 1
    at Promise
    on line 1
    at tryCatch on line 1
    at invoke on line 1
    at asyncGeneratorStep on line 1
    at _next on line 1
    on line 1
    at Promise
    on line 1
    at handle on line 1
    on line 1
    on line 1
    at X on line 1
    at asyncFunctionResume
    at promiseReactionJobWithoutPromise
    at promiseReactionJob

output.markdown('# KYC JSON Upload');

// Prompting the operator to upload a JSON file
let jsonPayload = await input.fileAsync('Upload a JSON file',
    {
        allowedFileTypes: ['.json'],
    }
);

let newRows = jsonPayload.parsedContents;
let table = base.getTable("Businesses");
let query = await table.selectRecordsAsync();

let filteredRows = newRows.filter(newRecord => !query.records.find(record => {
    const result = record.name === newRecord.user_metadata.business_data.businessName;
    return result;
    }));

let shouldContinue;

if (filteredRows.length > 0) {
    shouldContinue = await input.buttonsAsync(
    `Import ${filteredRows.length} new record(s) from ${jsonPayload.file.name} into ${table.name}?`,
    [{label: 'Yes', variant: 'primary'}, 'No']
    )
} else {
    output.markdown(`No new record availabe for the ${table.name} table.`);
}

if (shouldContinue === 'Yes') {
    let newRecords = filteredRows.map(newRow => {
        return {
        fields: {
            'Name': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.businessName : null}`,
            'Address': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.address : null}`,
            'City': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.city : null}`,
            'Country': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.country : null}`,
            'Trade Tax': `${newRow.app_metadata.business_data.trade_tax ? newRow.app_metadata.business_data.trade_tax.status : null} : ${newRow.app_metadata.business_data.trade_tax ? newRow.app_metadata.business_data.trade_tax.filename : null} @ ${newRow.app_metadata.business_data.trade_tax ? newRow.app_metadata.business_data.trade_tax.date : null} | ${newRow.app_metadata.business_data.trade_tax ? newRow.app_metadata.business_data.trade_tax.comments : null}`,
            'Tax Card': `${newRow.app_metadata.business_data.tax_card ? newRow.app_metadata.business_data.tax_card.status : null} : ${newRow.app_metadata.business_data.tax_card ? newRow.app_metadata.business_data.tax_card.filename : null} @ ${newRow.app_metadata.business_data.tax_card ? newRow.app_metadata.business_data.tax_card.date : null} | ${newRow.app_metadata.business_data.tax_card ? newRow.app_metadata.business_data.tax_card.comments : null}`,
            'Tax Card Number': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.taxCard : null}`,
            'Commercial Registry': `${newRow.app_metadata.business_data.commercial_registry ? newRow.app_metadata.business_data.commercial_registry.status : null} : ${newRow.app_metadata.business_data.commercial_registry ? newRow.app_metadata.business_data.commercial_registry.filename : null} @ ${newRow.app_metadata.business_data.commercial_registry ? newRow.app_metadata.business_data.commercial_registry.date : null} | ${newRow.app_metadata.business_data.commercial_registry ? newRow.app_metadata.business_data.commercial_registry.comments : null}`,
            'Business Registration Number': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.businessRegistration : null}`,
            'Business HQ unique Diool ID': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.shopId : null}`,
            'Users': `${newRow.user_metadata.personal_data.firstname} ${newRow.user_metadata.personal_data.lastname}`
        }
    };
    });
    // A maximum of 50 record creations are allowed at one time, so do it in batches
    while (newRecords.length > 0) {
        await table.createRecordsAsync(newRecords.slice(0, 50));
        newRecords = newRecords.slice(50);
    }
}

let table2 = base.getTable('Users');
let query2 = await table2.selectRecordsAsync();

let filteredRows2 = newRows.filter(newRecord => !query2.records.find(record => {
    const result = record.name === `${newRecord.user_metadata.personal_data.firstname} ${newRecord.user_metadata.personal_data.lastname}`;
    return result;
    }));


let shouldContinue2;

if (filteredRows2.length > 0) {
    shouldContinue2 = await input.buttonsAsync(
    `Import ${filteredRows2.length} new record(s) from ${jsonPayload.file.name} into ${table2.name}?`,
    [{label: 'Yes', variant: 'primary'}, 'No']
    )
} else {
    output.markdown(`No new record availabe for the ${table2.name} table.`);
}

if (shouldContinue2 === 'Yes') {
    let newRecords = filteredRows2.map(newRow => {
        return {
        fields: {
            'Name': `${newRow.user_metadata.personal_data.firstname} ${newRow.user_metadata.personal_data.lastname}`,
            'ID Live Check Status': `${newRow.app_metadata.identity_verification ? newRow.app_metadata.identity_verification.status : null}`,
            'ID Document Type': `${newRow.app_metadata.identity_verification ? newRow.app_metadata.identity_verification.document.type : null}`,
            'ID Document Scan Reference': `${newRow.app_metadata.identity_verification ? newRow.app_metadata.identity_verification.scan_reference : null}`,
            'ID Document Number': `${newRow.app_metadata.identity_verification ? newRow.app_metadata.identity_verification.document.number : null}`,
            'ID Document Expiry Date': `${newRow.app_metadata.identity_verification ? newRow.app_metadata.identity_verification.document.expiry : null}`,
            'Diool Unique User ID': newRow.user_id,
            'User Picture Link': newRow.picture,
            'Email': newRow.email,
            'Email Verified?': newRow.email_verified,
            'Businesses': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.businessName : null}`,
            'Shop ID': `${newRow.user_metadata.business_data ? newRow.user_metadata.business_data.shopId : null}`,
            'Has Account?': newRow.app_metadata.has_account,
            'Profile': newRow.app_metadata.profil,
            'Sign Up Step': newRow.app_metadata.signup_step,
        }
    };
    });
    // A maximum of 50 record creations are allowed at one time, so do it in batches
    while (newRecords.length > 0) {
        await table2.createRecordsAsync(newRecords.slice(0, 50));
        newRecords = newRecords.slice(50);
    }
}

output.markdown(`All records up to date.`);

Justin_Barrett · ‎Jul 28, 2020

Welcome to the community, @Frank_Reagan! :grinning_face_with_big_eyes: Link fields require an array of record IDs to create a one or more links. Your script is passing the first and last name as a string to the {Users} field. You’ll need to search the [Users] table to find the ID of the record matching that name, and pass that ID (in an array) when creating the records.

Ulrich_Mabou · ‎Jul 29, 2020

Thank you, @Justin_Barrett. That’s what I thought from the error message, but I wasn’t sure how to resolve it. Could you please provide a hint on how to perform that search and passing? I’m not too comfortable with scripting; just managed to search around to come up with that script. Thanks

Justin_Barrett · ‎Jul 31, 2020

Here’s how I’d approach it. I’d first create an object that acts as a dictionary, associating each record in the [Users] table—based on the user’s name—with its record ID. Do this early in the script before you begin processing the JSON payload. Something like this:

let userTable = base.getTable("Users");
let userQuery = await userTable.selectRecordsAsync();
let userDict = {};
for (let record of userQuery.records) {
    userDict[record.name] = record.id;
}

Then when you’re creating a new record, you’ll replace:

        'Users': `${newRow.user_metadata.personal_data.firstname} ${newRow.user_metadata.personal_data.lastname}`

with:

        'Users': [userDict[`${newRow.user_metadata.personal_data.firstname} ${newRow.user_metadata.personal_data.lastname}`]]

Passing the first and last name to the userDict object will return the associated record ID for that named user. Wrapping that result in square braces turns it into an array, which is what the linked record field requires.

Ulrich_Mabou · ‎Jul 31, 2020

Thank you very much @Justin_Barrett! I will try to implement that asap.

Ulrich_Mabou · ‎Aug 01, 2020

I tried implementing the logic and I’m getting another error. It seems there is also a unique linked record id besides the unique ids for the records in each of the tables.

At this point, I’m a bit confused, and I think what I’m trying to do might just be out of my competence for now. Thanks for the assistance, greatly appreciated.

### ERROR

N: Can't create records: invalid cell value for field 'Users'. Cell value has invalid format: <root>.0.0 is missing. Linked records field value must be an array of objects with property 'id' corresponding to linked record id.

at main on line 63

Justin_Barrett · ‎Aug 01, 2020

Not exactly. The two are the same thing. The unique linked record ID is the ID of the referenced table record. The problem with my earlier solution is the format for passing the ID. As the error message says, the record ID is supposed to be passed in an array of objects. I made an error in my recollection of the setup, and my earlier version passed an array of strings. Try this code for building the reference object:

let userTable = base.getTable("Users");
let userQuery = await userTable.selectRecordsAsync();
let userDict = {};
for (let record of userQuery.records) {
    userDict[record.name] = {id: record.id};
}

Ulrich_Mabou · ‎Aug 01, 2020

It is still throwing the same error; the ‘id doesn’t correspond’ when it tries to create the new record. It’s a new record (existing ones filtered out before trying to add new ones); it doesn’t already have a linked record id.

Justin_Barrett · ‎Aug 01, 2020

Ah. In that case, you’ll need to make that new contact record first. You can’t make one on the fly while linking to it. Make it and save the returned record ID to a variable. Pass that ID in an object array when creating the other record:

[{id: NEW_RECORD_ID}]

I’m away from home, or else I’d write more detailed code. If someone else wants to jump in and assist, feel free.

Ulrich_Mabou · ‎Aug 02, 2020

Thanks, @Justin_Barrett; you’ve assisted a lot. If I can’t get this to work, we’ll have to try something else.

The problem is that I’m not able to programmatically add a record to any of the 2 tables when they have a linked field. If I remove the ‘link’ manually, I can add to any of the tables. If I create a new record manually in one of the tables, then update the other one programmatically, it defeats the purpose of the use case, since the aim was to automate that.

I was just exploring what was possible, it is not a big deal. Thank you