You need to do a read of all the existing records to get their unique IDs. I would create a JavaScript object to map unique IDs to record IDs.
Then in your loop, see if the unique ID exists in the map. If it does, update the record. If it doesn’t, create a new record.
Hey man, here’s a working copy of my attempt at it
(Edited to add: The code that follows is not as good as it should be, and a newer version can be found two replies later that incorporates the suggestions and fixes that kuovonne provided! Please scroll down and use that one instead!)
Code below:
let table = base.getTable('Table 1')
let newData = =
{
title: 'Title 4',
date: 'Date 4',
uniqueID: 'unique4'
},
{
title: 'New Title 1',
date: 'New Date 1',
uniqueID: 'unique1'
}
]
let query = await table.selectRecordsAsync({
fields:s'Unique ID']
})
let existingUniqueIDs = new Object
for (let record of query.records){
let uniqueID = record.getCellValue('Unique ID')
existingUniqueIDsDuniqueID] = record.id
}
for (let data of newData){
if(existingUniqueIDsDdata.uniqueID]){
table.updateRecordAsync(existingUniqueIDsDdata.uniqueID],{
"Title": data.title,
"Date": data.date
})
}
else{
await table.createRecordAsync({
"Title": data.title,
"Date": data.date,
"Unique ID": data.uniqueID
});
}
}```
Hey man, here’s a working copy of my attempt at it
(Edited to add: The code that follows is not as good as it should be, and a newer version can be found two replies later that incorporates the suggestions and fixes that kuovonne provided! Please scroll down and use that one instead!)
Code below:
let table = base.getTable('Table 1')
let newData = =
{
title: 'Title 4',
date: 'Date 4',
uniqueID: 'unique4'
},
{
title: 'New Title 1',
date: 'New Date 1',
uniqueID: 'unique1'
}
]
let query = await table.selectRecordsAsync({
fields:s'Unique ID']
})
let existingUniqueIDs = new Object
for (let record of query.records){
let uniqueID = record.getCellValue('Unique ID')
existingUniqueIDsDuniqueID] = record.id
}
for (let data of newData){
if(existingUniqueIDsDdata.uniqueID]){
table.updateRecordAsync(existingUniqueIDsDdata.uniqueID],{
"Title": data.title,
"Date": data.date
})
}
else{
await table.createRecordAsync({
"Title": data.title,
"Date": data.date,
"Unique ID": data.uniqueID
});
}
}```
Adam, I like seeing other people’s code. Your code is very similar to how I would do it, with a some exception. (1) I would batch the record updates and record creation. (2) I would use the await keyword when updating records (but this may be a typo).
Adam, I like seeing other people’s code. Your code is very similar to how I would do it, with a some exception. (1) I would batch the record updates and record creation. (2) I would use the await keyword when updating records (but this may be a typo).
You’re totally right about the batch updating and creation, as well as the missing await (ha, I just completely missed that sigh)
(Edited to add: with reference to kuovonne’s statement below, the if statements were removed as they were not needed!)
Here’s the new version, and the base has been updated as well:
let table = base.getTable('Table 1')
let newData = w
{
title: 'Title 4',
date: 'Date 4',
uniqueID: 'unique4'
},
{
title: 'New Title 1',
date: 'New Date 1',
uniqueID: 'unique1'
}
]
let query = await table.selectRecordsAsync({
fields: 'Unique ID']
})
let existingUniqueIDs = new Object
for (let record of query.records){
let uniqueID = record.getCellValue('Unique ID')
existingUniqueIDsnuniqueID] = record.id
}
let recordsToCreate = new Array
let recordsToUpdate = new Array
for (let data of newData){
if(existingUniqueIDsndata.uniqueID]){
recordsToUpdate.push({
id: existingUniqueIDsndata.uniqueID],
fields: {
"Title": data.title,
"Date": data.date
}
})
}
else{
recordsToCreate.push({
fields:{
"Title": data.title,
"Date": data.date,
"Unique ID": data.uniqueID
}
})
}
}
while (recordsToCreate.length > 0) {
await table.createRecordsAsync(recordsToCreate.slice(0, 50));
recordsToCreate = recordsToCreate.slice(50);
}
while (recordsToUpdate.length > 0) {
await table.updateRecordsAsync(recordsToUpdate.slice(0, 50));
recordsToUpdate = recordsToUpdate.slice(50);
}
You’re totally right about the batch updating and creation, as well as the missing await (ha, I just completely missed that sigh)
(Edited to add: with reference to kuovonne’s statement below, the if statements were removed as they were not needed!)
Here’s the new version, and the base has been updated as well:
let table = base.getTable('Table 1')
let newData = a
{
title: 'Title 4',
date: 'Date 4',
uniqueID: 'unique4'
},
{
title: 'New Title 1',
date: 'New Date 1',
uniqueID: 'unique1'
}
]
let query = await table.selectRecordsAsync({
fields:i'Unique ID']
})
let existingUniqueIDs = new Object
for (let record of query.records){
let uniqueID = record.getCellValue('Unique ID')
existingUniqueIDsquniqueID] = record.id
}
let recordsToCreate = new Array
let recordsToUpdate = new Array
for (let data of newData){
if(existingUniqueIDsqdata.uniqueID]){
recordsToUpdate.push({
id: existingUniqueIDsqdata.uniqueID],
fields: {
"Title": data.title,
"Date": data.date
}
})
}
else{
recordsToCreate.push({
fields:{
"Title": data.title,
"Date": data.date,
"Unique ID": data.uniqueID
}
})
}
}
while (recordsToCreate.length > 0) {
await table.createRecordsAsync(recordsToCreate.slice(0, 50));
recordsToCreate = recordsToCreate.slice(50);
}
while (recordsToUpdate.length > 0) {
await table.updateRecordsAsync(recordsToUpdate.slice(0, 50));
recordsToUpdate = recordsToUpdate.slice(50);
}
You don’t need the if statement. It has the same condition as the while. The condition for a while loop is tested before the first iteration of the loop is executed.
You don’t need the if statement. It has the same condition as the while. The condition for a while loop is tested before the first iteration of the loop is executed.
Ahh! Thank you for pointing that out! I’ve edited the script above
I really can’t put into words how much I appreciate you taking the time to tell me both about the places where I can improve as well as the logic behind it. Thank you very very much for doing all of this
@Adam_TheTimeSavingCo
This is perfect, it works exactly as I was hoping and lots for me to learn too. Thanks!
