Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 13, 2022 04:34 PM
I have a fetch that calls an API and adds all of the records to my table (Title, Date, Unique ID). That is good.
Now when I make another call to the API I want to run thought the existing field, match the Unique ID and update the name and date (does not matter if they are different) and if there is no record with that Unique ID add the Title, Date and Unique ID to the end of the records.
let response = await remoteFetchAsync("URL", requestOptions);
let json = await response.json();
let table = base.getTable("Table");
let titleColumn = table.getField("Title")
for (i=0; i < json.length; i++) {
if(json[0].Title !== titleColumn){
let recordId = await table.createRecordAsync({
"Title": json[i].Title,
"Date": json[i].Date,
"Unique ID": json[i].uniqueID
})
}
};
Thanks!
Solved! Go to Solution.
May 14, 2022 07:29 PM
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 = [
{
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')
existingUniqueIDs[uniqueID] = record.id
}
let recordsToCreate = new Array
let recordsToUpdate = new Array
for (let data of newData){
if(existingUniqueIDs[data.uniqueID]){
recordsToUpdate.push({
id: existingUniqueIDs[data.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);
}
May 13, 2022 04:38 PM
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.
May 13, 2022 11:58 PM
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:['Unique ID']
})
let existingUniqueIDs = new Object
for (let record of query.records){
let uniqueID = record.getCellValue('Unique ID')
existingUniqueIDs[uniqueID] = record.id
}
for (let data of newData){
if(existingUniqueIDs[data.uniqueID]){
table.updateRecordAsync(existingUniqueIDs[data.uniqueID],{
"Title": data.title,
"Date": data.date
})
}
else{
await table.createRecordAsync({
"Title": data.title,
"Date": data.date,
"Unique ID": data.uniqueID
});
}
}```
May 14, 2022 03:58 AM
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).
May 14, 2022 07:29 PM
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 = [
{
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')
existingUniqueIDs[uniqueID] = record.id
}
let recordsToCreate = new Array
let recordsToUpdate = new Array
for (let data of newData){
if(existingUniqueIDs[data.uniqueID]){
recordsToUpdate.push({
id: existingUniqueIDs[data.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);
}
May 14, 2022 08:19 PM
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.
May 14, 2022 10:29 PM
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
May 16, 2022 08:36 AM
@Adam_TheTimeSavingCo
This is perfect, it works exactly as I was hoping and lots for me to learn too. Thanks!