Help

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

2037 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Ulrich_Mabou
4 - Data Explorer
4 - Data Explorer

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

image
image

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.`);
13 Replies 13

This can be done. It’s just about finding the correct method, and unfortunately I’m short on time to experiment and find it.

This may sound odd, but if you can, I suggest un-marking my comment above as the solution for this thread. Even though the info that I shared may be helpful, we haven’t actually hit upon the solution yet, and the forum software is designed to lock any “solved” thread three days after the last post. I don’t consider this issue solved, and wouldn’t want to lose the chance of keeping this discussion going if I (or someone else) can’t find the solution in the next few days.

I also believe it can be done. I’m just not able to figure it myself at the moment. I might come back to it later.

Noted, I un-marked it. Thanks for your assistance and suggestions. Greatly appreciated. If someone attempt to look into it, we will take it from there.

Isaac_Googgle
5 - Automation Enthusiast
5 - Automation Enthusiast

I GOT IT!!!

OMFG. I really suck at programming but here’s your solution

            fields: {
                "Voter": [{ id: qryMaidFiltered[i].id }],
            },

Instructions

  1. Voter is my linked field. Not sure what your linked field is - if it’s Name then change Voter to Name for your case
  2. “qryMaidFiltered[i].id” you’ve got to change this to whatever you use to get your record id.

Once you’ve change it, it’ll work magically already.

@BillFrench i just wanna say a big thanks to Bill French for investing the time to help me out with all my other pesky questions!!

Kind words and on a Friday no less. Have a great weekend; pay it forward.