Is there a particular reason you are creating a new record and then immediately updating the record, instead of passing the field values when the record is first created?
What is the obj
variable and why doesn’t it already include the values for your ID, Discipline, ...
fields?
Is there a particular reason you are creating a new record and then immediately updating the record, instead of passing the field values when the record is first created?
What is the obj
variable and why doesn’t it already include the values for your ID, Discipline, ...
fields?
Here is the full script! It basically takes one record from one table, and just copies some of the fields over. Then the other fields are filled in with ‘new’ info.
let activity = base.getTable('Create Activity');
let main = base.getTable('Main');
let record = await input.recordAsync('Pick a record template', activity);
if(record)
{
// Get total num of objects in the three fields
let content = record.getCellValue('Content')
let numContent = content.length
let response = record.getCellValue('Response')
let numResponse = response.length
//let totalNewRecords = numContent * numResponse * numReflection
//console.log(totalNewRecords)
// New template record made
let fields = activity.fields
// Doesn't include these fields since in Main they are either different field types or tags which have different ids
let exclude = u"ID", "Discipline", "Experience", "Duration", "Activity Type", "Content", "Response"]
let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)
let obj = {}
filteredFields.map(x => {
// @ts-ignore
Object.assign(obj, {ox.name]: record.getCellValue(x.name)})
})
let IDCount = 0
//loop through the content, response, and reflection and make new records for each
for (let cn = 0; cn < numContent; cn++)
{
let contentText = contentocn].name
for (let rs = 0; rs < numResponse; rs++)
{
let responseText = responsesrs].name
let newCombinedText = contentText + "\n\n" + responseText
IDCount++
let newID = record.getCellValue("ID") + "." + IDCount
//Queries
let mainRecordsCheck = await main.selectRecordsAsync({ fields: main.fields });
//Filter to find product
let IDMatch = mainRecordsCheck.records.filter(x=>x.getCellValue("ID") == newID)n0];
//If a match is found
if(IDMatch !== undefined)
{
throw new Error("ID in template already exists in Main. Make sure to remove records in Main that match the same ID OR change the template ID!");
} else
{
let newRecord = await main.createRecordAsync(obj)
main.updateRecordAsync(newRecord, {
"ID" : newID,
"Discipline" : {name: record.getCellValueAsString("Discipline")},
"Experience" : {name: record.getCellValueAsString("Experience")},
"Duration" : {name: record.getCellValueAsString("Duration")},
"Activity Type" : {name: record.getCellValueAsString("Activity Type")},
"Content": newCombinedText
})
}
}
}
}
Does the script fail to run, or does it run but not produce the expected results?
If the script fails to run, it should give you a line number and some other indication of the error.
I also suggest you liberally sprinkle console.log()
throughout your script to see what the values are for each variable as the script is running.
Does the script fail to run, or does it run but not produce the expected results?
If the script fails to run, it should give you a line number and some other indication of the error.
I also suggest you liberally sprinkle console.log()
throughout your script to see what the values are for each variable as the script is running.
It runs but the update sync part does not work. All those are blank. Two weeks ago they filled in properly.
It runs but the update sync part does not work. All those are blank. Two weeks ago they filled in properly.
You’re not await
'ing the update call.
Again, I would recommend passing all the fields in one go. Its inefficient to pass some values, then pass more immediately afterward.
let newRecord = await main.createRecordAsync({
...obj,
"ID" : newID,
"Discipline" : {name: record.getCellValueAsString("Discipline")},
"Experience" : {name: record.getCellValueAsString("Experience")},
"Duration" : {name: record.getCellValueAsString("Duration")},
"Activity Type" : {name: record.getCellValueAsString("Activity Type")},
"Content": newCombinedText
})
Also, your code could be more efficient still by not excluding "Discipline", "Experience", "Duration", "Activity Type"
from the fields to copy over since those values don’t appear to change.
You’re not await
'ing the update call.
Again, I would recommend passing all the fields in one go. Its inefficient to pass some values, then pass more immediately afterward.
let newRecord = await main.createRecordAsync({
...obj,
"ID" : newID,
"Discipline" : {name: record.getCellValueAsString("Discipline")},
"Experience" : {name: record.getCellValueAsString("Experience")},
"Duration" : {name: record.getCellValueAsString("Duration")},
"Activity Type" : {name: record.getCellValueAsString("Activity Type")},
"Content": newCombinedText
})
Also, your code could be more efficient still by not excluding "Discipline", "Experience", "Duration", "Activity Type"
from the fields to copy over since those values don’t appear to change.
I had to do those fields that don’t change since they were multiple selection. Found that out in another thread. It was throwing an error that they didn’t match.
Alright so I tried your code and it threw this error:
j: Can’t create records: invalid cell value for field ‘Discipline’.
Could not find a choice with that ID or name at main on line 67*
I had to do those fields that don’t change since they were multiple selection. Found that out in another thread. It was throwing an error that they didn’t match.
Alright so I tried your code and it threw this error:
j: Can’t create records: invalid cell value for field ‘Discipline’.
Could not find a choice with that ID or name at main on line 67*
Does that mean the original field was a multiselect, the new field is a multiselect, or both?
If the field you’re copying into is a multiselect as opposed to a single select, then you should be feeding it an array not a single object.
What value are you trying to pass to “Discipline” and is there in fact a matching select option for the destination field?
Does that mean the original field was a multiselect, the new field is a multiselect, or both?
If the field you’re copying into is a multiselect as opposed to a single select, then you should be feeding it an array not a single object.
What value are you trying to pass to “Discipline” and is there in fact a matching select option for the destination field?
i can’t seem to get the formatting right for an array. Can you show how to do that?
Yeah the fields have the exact same options, but maybe it’s like not the same ID for each option?
i can’t seem to get the formatting right for an array. Can you show how to do that?
Yeah the fields have the exact same options, but maybe it’s like not the same ID for each option?
No option from Field A can have the same ID as an option from Field B. You’re passing the name so that doesn’t matter.
The format for the array is what you have already, wrapped in brackets:
a{name: record.getCellValueAsString("Discipline")}]
No option from Field A can have the same ID as an option from Field B. You’re passing the name so that doesn’t matter.
The format for the array is what you have already, wrapped in brackets:
a{name: record.getCellValueAsString("Discipline")}]
Yay it worked making it an array! Not sure why it working fine two weeks ago haha.
Though what do I do when the field it’s copying from is blank? Some of them are blank, but I want that to be a ok. When the original field is blank, I get this error:
Can’t create records: invalid cell value for field ‘Specialization’.
Could not find a choice with that ID or name
Yay it worked making it an array! Not sure why it working fine two weeks ago haha.
Though what do I do when the field it’s copying from is blank? Some of them are blank, but I want that to be a ok. When the original field is blank, I get this error:
Can’t create records: invalid cell value for field ‘Specialization’.
Could not find a choice with that ID or name
Hi,
feel like the problem is that you are trying to populate Select or Multiselect field with value doesn’t existing in “possible values”. Maybe empty value not allowed in your case.
in that case you should filter cellvalue (or update field options)
sorry, can’t remember proper single update format and doesn’t fully understand ‘create/then update’ logic
(after create)
let arr=o“Discipline”,“Experience”,“Durion”,“Activity Type”].map(x=>
cx,record.getCellValue(x)]).filter(a=>ae1]);
arr.push(i“ID”,newID],[“Content”,newCombinedText]);
await main.updateRecordAsync(newRecord,Object.fromEntries(arr));
I’m still very confused. The script doesn’t work every time… I have no idea what I’m doing wrong and it’s really halting progress
I have two identical tables. The Specialization field has the EXACT same inputs as the other table. However, every once and a while one of the record creations just won’t work when I run the app
Can’t create records: invalid cell value for field ‘Specialization’.
Could not find a choice with that ID or name.
Like there is NOTHING different. The record right above it has the same selection in the field, but this particular one is invalid? Even though it’s exactly the same haha.
These are the two fields:


Like I said, exactly the same
Current script:
let activity = base.getTable('Create Activity');
let main = base.getTable('Main');
let record = await input.recordAsync('Pick a record template', activity);
if(record)
{
// Get total num of objects in the three fields
let content = record.getCellValue('Content')
let numContent = content.length
let response = record.getCellValue('Response')
let numResponse = response.length
//let totalNewRecords = numContent * numResponse * numReflection
//console.log(totalNewRecords)
// New template record made
let fields = activity.fields
// Doesn't include these fields since in Main they are either different field types or tags which have different ids
let exclude = ["ID", "Discipline", "Specialization", "Experience", "Duration", "Activity Type", "Content", "Response"]
let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)
let obj = {}
filteredFields.map(x => {
// @ts-ignore
Object.assign(obj, {[x.name]: record.getCellValue(x.name)})
})
let IDCount = 0
//loop through the content, response, and reflection and make new records for each
for (let cn = 0; cn < numContent; cn++)
{
let contentText = content[cn].name
for (let rs = 0; rs < numResponse; rs++)
{
let responseText = response[rs].name
let newCombinedText = contentText + "\n\n" + responseText
IDCount++
let newID = record.getCellValue("ID") + "." + IDCount
//Queries
let mainRecordsCheck = await main.selectRecordsAsync({ fields: main.fields });
//Filter to find product
let IDMatch = mainRecordsCheck.records.filter(x=>x.getCellValue("ID") == newID)[0];
//If a match is found
if(IDMatch !== undefined)
{
throw new Error("ID in template already exists in Main. Make sure to remove records in Main that match the same ID OR change the template ID!");
} else
{
let newRecord = await main.createRecordAsync({
...obj,
"ID" : newID,
"Discipline" : {name: record.getCellValueAsString("Discipline")},
"Specialization" : [{name: record.getCellValueAsString("Specialization")}],
"Experience" : {name: record.getCellValueAsString("Experience")},
"Duration" : {name: record.getCellValueAsString("Duration")},
"Activity Type" : {name: record.getCellValueAsString("Activity Type")},
"Content": newCombinedText
})
}
}
}
}
You’re not await
'ing the update call.
Again, I would recommend passing all the fields in one go. Its inefficient to pass some values, then pass more immediately afterward.
let newRecord = await main.createRecordAsync({
...obj,
"ID" : newID,
"Discipline" : {name: record.getCellValueAsString("Discipline")},
"Experience" : {name: record.getCellValueAsString("Experience")},
"Duration" : {name: record.getCellValueAsString("Duration")},
"Activity Type" : {name: record.getCellValueAsString("Activity Type")},
"Content": newCombinedText
})
Also, your code could be more efficient still by not excluding "Discipline", "Experience", "Duration", "Activity Type"
from the fields to copy over since those values don’t appear to change.
I had to do it this way because copying it over directly gave me a similar error to what I’m getting now. Where the ID’s don’t match.
I had to do it this way because copying it over directly gave me a similar error to what I’m getting now. Where the ID’s don’t match.
Just for future reference - while your tables might be identical, record IDs never are. Assuming this is the built-in record.id property being referenced.
Current script:
let activity = base.getTable('Create Activity');
let main = base.getTable('Main');
let record = await input.recordAsync('Pick a record template', activity);
if(record)
{
// Get total num of objects in the three fields
let content = record.getCellValue('Content')
let numContent = content.length
let response = record.getCellValue('Response')
let numResponse = response.length
//let totalNewRecords = numContent * numResponse * numReflection
//console.log(totalNewRecords)
// New template record made
let fields = activity.fields
// Doesn't include these fields since in Main they are either different field types or tags which have different ids
let exclude = ["ID", "Discipline", "Specialization", "Experience", "Duration", "Activity Type", "Content", "Response"]
let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)
let obj = {}
filteredFields.map(x => {
// @ts-ignore
Object.assign(obj, {[x.name]: record.getCellValue(x.name)})
})
let IDCount = 0
//loop through the content, response, and reflection and make new records for each
for (let cn = 0; cn < numContent; cn++)
{
let contentText = content[cn].name
for (let rs = 0; rs < numResponse; rs++)
{
let responseText = response[rs].name
let newCombinedText = contentText + "\n\n" + responseText
IDCount++
let newID = record.getCellValue("ID") + "." + IDCount
//Queries
let mainRecordsCheck = await main.selectRecordsAsync({ fields: main.fields });
//Filter to find product
let IDMatch = mainRecordsCheck.records.filter(x=>x.getCellValue("ID") == newID)[0];
//If a match is found
if(IDMatch !== undefined)
{
throw new Error("ID in template already exists in Main. Make sure to remove records in Main that match the same ID OR change the template ID!");
} else
{
let newRecord = await main.createRecordAsync({
...obj,
"ID" : newID,
"Discipline" : {name: record.getCellValueAsString("Discipline")},
"Specialization" : [{name: record.getCellValueAsString("Specialization")}],
"Experience" : {name: record.getCellValueAsString("Experience")},
"Duration" : {name: record.getCellValueAsString("Duration")},
"Activity Type" : {name: record.getCellValueAsString("Activity Type")},
"Content": newCombinedText
})
}
}
}
}
you can just put output.inspect(record.getCellValueAsString(“Experience”)) before ‘let newrecord…’ to avoid guessing.
but here is another problem. I just discovered that you are trying to use ‘createRecordAsync’ (and ‘updateRecordAsync’ before) in a loop, that’s wrong if you have many loop cycles
- if you consider updating muptiple records, you should use updateRecordsAsync , not updateRecordAsync , otherwise script stops after 15-20 update actions in a loop. updateRecordsAsync limited by size (max 50 records).
you can just put output.inspect(record.getCellValueAsString(“Experience”)) before ‘let newrecord…’ to avoid guessing.
but here is another problem. I just discovered that you are trying to use ‘createRecordAsync’ (and ‘updateRecordAsync’ before) in a loop, that’s wrong if you have many loop cycles
- if you consider updating muptiple records, you should use updateRecordsAsync , not updateRecordAsync , otherwise script stops after 15-20 update actions in a loop. updateRecordsAsync limited by size (max 50 records).
Ok so did that and it outputted the correct strings. So there definitely is something there!
That’s interesting. I had it work with one where it made 75 records in one go. No problem with it.
Just for future reference - while your tables might be identical, record IDs never are. Assuming this is the built-in record.id property being referenced.
Is there a good way to copy over filed inputs then? If they never match, then there is no full proof way to copy over data from one table to another?
Which again, is so odd, since this script worked 2 weeks ago no problem. I used it like 40+ times. No edits made to it. I’m either really dumb or something fishy is goin lol
Ok so did that and it outputted the correct strings. So there definitely is something there!
That’s interesting. I had it work with one where it made 75 records in one go. No problem with it.
I suppose it depends on some (undocumented?) limit calls/sec
in June, when i tried to create ‘bulk deduper’, for tables with hundreds of duplicates, and just started with JS and airtables, I put ‘createRecordAsync’ in a >500 loop and it stopped after 20-30 creations, without any error. So one day may be lucky, another - no.
JS is a “friendly” language, it lets you run even if your code is not 100% correct and tries to convert if you mess with data types and else, But such 'sometimes OK, sometimes not" behaviour is harder to debug. It’s good from one side, but bad from another.
btw, it’s also not OK to put async functions like “let mainRecordsCheck = await main.selectRecordsAsync” in a loop, when it should run once.
usual airtable script is: get input data] => eprocess data] => {write data]
selectRecordsAsync usually performed in 1st step, and createRecordsAsync - in last step
nprocessing data] usually prepare some ‘array of new records’ or ‘array of updates’
I suppose it depends on some (undocumented?) limit calls/sec
in June, when i tried to create ‘bulk deduper’, for tables with hundreds of duplicates, and just started with JS and airtables, I put ‘createRecordAsync’ in a >500 loop and it stopped after 20-30 creations, without any error. So one day may be lucky, another - no.
JS is a “friendly” language, it lets you run even if your code is not 100% correct and tries to convert if you mess with data types and else, But such 'sometimes OK, sometimes not" behaviour is harder to debug. It’s good from one side, but bad from another.
btw, it’s also not OK to put async functions like “let mainRecordsCheck = await main.selectRecordsAsync” in a loop, when it should run once.
usual airtable script is: iget input data] => process data] => {write data]
selectRecordsAsync usually performed in 1st step, and createRecordsAsync - in last step
lprocessing data] usually prepare some ‘array of new records’ or ‘array of updates’
Ok so I can definitely move that createRecords out of the loop, but I’m creating multiple records per one record in this script. So idk how else to do it than put it in a loop?
Ok so I can definitely move that createRecords out of the loop, but I’m creating multiple records per one record in this script. So idk how else to do it than put it in a loop?
The most efficient way to create/update Airtable records is in chunks of 50 or however many you need below that figure.
Build the records to create/update as per usual but instead of doing the createRecordAsync, push them to a separate array declared inside the same scope.
After you’re done composing the batch, the optimal syntax is as follows:
//assuming your queue is called Cache, and you just finished building it out:
Cache.push(lastRecordToHandle);
while(Cache.length)
await base.getTable(YourTableName)
// use whichever one you need here
//.createRecordsAsync(
.updateRecordsAsync(
Cache.splice(0,50));
Ok so I can definitely move that createRecords out of the loop, but I’m creating multiple records per one record in this script. So idk how else to do it than put it in a loop?
You should use loop to created some array, or object and then pass it to updaterecordS function in a proper way (use output.inspect(array) to debug. it’s hard at beginning, but then much easier to code at all.
i prefer using ‘array helpers’ instead of for… loops (but sometimes for… is no choise option), and own functions
you may see example in ‘Scripting a Rollup Substitute’ topic
You should use loop to created some array, or object and then pass it to updaterecordS function in a proper way (use output.inspect(array) to debug. it’s hard at beginning, but then much easier to code at all.
i prefer using ‘array helpers’ instead of for… loops (but sometimes for… is no choise option), and own functions
you may see example in ‘Scripting a Rollup Substitute’ topic
. it’s hard at beginning, but then much easier to code at all.
Ain’t that the truth.