I have a csv of volunteers for upload via script into a table called Volunteers.
I have a table another table called campaigns.
The volunteers table is linked to the campaigns table via a field in Volunteers called Campaign.
The Problem:
While uploading, I wanted to link Volunteers data to the Campaigns table. I found the record ID of the appropriate campaign and made an array [{id: “recordID”}]. I checked that this would work by hardcoding the record ID. The linking worked. Here is the relevant code:
let newRecords = csvRows.map(csvRow => ({
fields: {
‘First Name’: csvRow.FirstName,
‘Last Name’: csvRow.LastName,
‘Vetting Status’: {name: csvRow.VettingStatus},
‘Skills’: csvRow.Skills,
‘Email’: csvRow.Email,
‘Phone Number’: csvRow.PhoneNumber,
‘Notes’: csvRow.Notes,
‘Campaign’:[{id:“recxxxxxxxx”}]
}
}));
while (newRecords.length > 0) {
await table.createRecordsAsync(newRecords.slice(0, 50));
newRecords = newRecords.slice(50);
}
After doing this, I wrote some code to loop through newRecords, the variable that has all the csv data to be uploaded to Volunteers, and compare csv.Campaign to any of the campaigns in the existing Campaigns table. If the the campaign name matched (csv.Campaign == vol[“fields”][“Campaign”] where vol is a record of newRecords) then the record ID of that campaign is found by looping over the campaigns table record. Once found we change the value in newRecords for csv.Campaign to [{id:“recordID”}]. Here is the pertinent code for this:
let newRecords = csvRows.map(csvRow => ({
fields: {
‘First Name’: csvRow.FirstName,
‘Last Name’: csvRow.LastName,
‘Vetting Status’: {name: csvRow.VettingStatus},
‘Skills’: csvRow.Skills,
‘Email’: csvRow.Email,
‘Phone Number’: csvRow.PhoneNumber,
‘Notes’: csvRow.Notes,
‘Campaign’:csv.Campaign
}
}));
for(let vol of newRecords){
for(let rec of allCampaignRecs.records){
let recArrName=rec.getCellValue(“Name”);
if (recArrName==vol[‘fields’][‘Campaign’]){
let recId=rec.id;
vol[‘fields’][‘Campaign’]=[{id:recID}]// I have tried to hardcode the ID as well just as in the example above. So instead of the variable recID, I put in a real record ID in string form, “recxxxxx”.
}
}
}
while (newRecords.length > 0) {
await table.createRecordsAsync(newRecords.slice(0, 50));
newRecords = newRecords.slice(50);
}
Using output.table() I was able to confirm that newRecords after running the loop was the same in structure as when I hardcoded the value for the Campaign’s column. Despite this I am still getting this error:
P: Can’t create records: invalid cell value for field ‘Campaign’.
Cell value has invalid format: must be an array.
Linked records field value must be an array of objects with property ‘id’ corresponding to linked record id.
Except that it is an array. It is coded exactly the same way as when I hardcoded newRecords. There is something about using vol[‘field’][‘campaign’]=[{id:recID/“recxxx”}] that is throwing this error, I think. When I output the newRecords table before the while loop to create the records this time around, I get this error. Before it worked great.
Thanks for reading. Hope I can get some help on this.
