Solved

Javascripting Help

Forum|Forum|3 years ago
May 13, 2022
7 replies
36 views

+16

Vivid-Squid
Inspiring

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!

Best answer by TheTimeSavingCo

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 = [
    {
        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);
}

+29

kuovonne
Brainy
Forum|Forum|3 years ago
May 13, 2022

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.

+31

TheTimeSavingCo
Brainy
Forum|Forum|3 years ago
May 14, 2022

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
        });
    }
}```

Adam, Airtable Consultant - thetimesaving.company

+29

kuovonne
Brainy
Forum|Forum|3 years ago
May 14, 2022

Hey man, here’s a working copy of my attempt at it

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
        });
    }
}```

+31

TheTimeSavingCo
Brainy
Answer
Forum|Forum|3 years ago
May 15, 2022

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);
}

Adam, Airtable Consultant - thetimesaving.company

+29

kuovonne
Brainy
Forum|Forum|3 years ago
May 15, 2022

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);
}

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.

+31

TheTimeSavingCo
Brainy
Forum|Forum|3 years ago
May 15, 2022

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, Airtable Consultant - thetimesaving.company

+16

Vivid-Squid
Author
Inspiring
Forum|Forum|3 years ago
May 16, 2022

@Adam_TheTimeSavingCo
This is perfect, it works exactly as I was hoping and lots for me to learn too. Thanks!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded