Re: Can't programmatically create new records because of linked records
1
2710
1
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.`);
