Help

Re: [Help debug, please!] Scripting Block + third party API: update records if they exist, create a new one if not

Solved
Jump to Solution
688 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Ulu_Mills
4 - Data Explorer
4 - Data Explorer

Hello!

I’m populating my base with data from an external API and would like to update it regularly as new results come in without creating new duplicate records. I’ve read a couple of other topics that discuss that (like this one and this one), but am still struggling to figure it out.

I’m using the [uri] field in the base records and trying to match them to the array of [uri]s from the api. If there’s a match, the record would update, and if not, a new one would be created.

FWIW my knowledge of js is survivalist at best! Here’s what I have so far:

// The loop for each result in the API
for(let i = 0, l = data.results.length; i < l; i++) {
    // ids for current records
    let table = base.getTable("Observation");
    let queryResult = await table.selectRecordsAsync();
    const records = queryResult.records;

    for(let i = 0, r = queryResult.records.length; i < r; i++) {
        // grab cell value uri for reference against the api
        let record = queryResult.records[i];
        let cellUri = record.getCellValue("uri");

        if (cellUri && data.results[i].uri) { // compare field and api
            // update record
        } else {
            // create record
        }
}

I’m getting way too many results for the update record function because of the double-loop, and nothing for the create record function, which means I’m probably setting this up wrong. Can anyone help me correct my code?

Thank you!

1 Solution

Accepted Solutions

Hi @Ulu_Mills,

I’ve tweaked my script a bit like this:

//We first put existing records' URIs into a map named existingRecords
let table = base.getTable("Observation");
let queryResult = await table.selectRecordsAsync();

let existingRecords = {};

for (let record of queryResult.records) {
    let uri = record.getCellValue("uri");
    existingRecords[uri] = record;
}

//Then we fetch the data from the api (I have hardcoded these values here just to test, you will of course need to fetch them from your API)
let data = {
    results: [{uri: "https://news.ycombinator.com/"}, {uri: "https://news.ycombinator.com/4"}]
}

//And finally we test which URIs exist in the map we created earlier.
//If the URI exists in the map we need to update the record, other we need to create a new record.
for (let result of data.results) {
    if (result.uri in existingRecords) {
        let existingRecord = existingRecords[result.uri];
        await table.updateRecordAsync(existingRecord, {"Status": "Done"});
        console.log(`Updated existing record with uri ${result.uri}`);
    } else {
        let newRecordId = await table.createRecordAsync({
            "Status": "Todo",
            "uri": result.uri,
        });
        console.log(`Created new record with id ${newRecordId}`);
    }
}

First, I have changed existingRecords[uri] = uri; to existingRecords[uri] = record;. This will ensure that when we update the record later we can access the existing record. The code to update a record is like this:

        let existingRecord = existingRecords[result.uri];
        await table.updateRecordAsync(existingRecord, {"Status": "Done"});

Here I’m updating my Status field to a value Done. In my case the Status fields was of type Single line text. If your field is of a different type you might have to tweak this part a bit. See this page for reference.

The code to create a new record is similar:

        let newRecordId = await table.createRecordAsync({
            "Status": "Todo",
            "uri": result.uri,
        });

Regarding your Do I need this? questions, you don’t need those as you can reference the table variable as I did in my script.

Good luck :grinning_face_with_smiling_eyes:
-Raminder

See Solution in Thread

4 Replies 4
Raminder_Singh
7 - App Architect
7 - App Architect

Hi @Ulu_Mills,

Something like this might work for you:

//We first put existing records' URIs into a map named existingRecords
let table = base.getTable("Observation");
let queryResult = await table.selectRecordsAsync();

let existingRecords = {};

for (let record of queryResult.records) {
    let uri = record.getCellValue("uri");
    existingRecords[uri] = uri;
}

//Then we fetch the data from the api (I have hardcoded these values here just to test, you will of course need to fetch them from your API)
let data = {
    results: [{uri: "https://news.ycombinator.com/"}, {uri: "https://news.ycombinator.com/4"}]
}

//And finally we test which URIs exist in the map we created earlier.
//If the URI exists in the map we need to update the record, other we need to create a new record.
for (let result of data.results) {
    if (result.uri in existingRecords) {
        console.log(`Update existing record with uri ${result.uri}`);
    } else {
        console.log(`Create new record with uri ${result.uri}`);
    }
}

Hope this helps,
-Raminder

Ulu_Mills
4 - Data Explorer
4 - Data Explorer

@Raminder_Singh

Raminder!! Thank you so so much for your help. I’m almost there! One more question:

For the update function, how do I get the record ID for the record with the uri being referenced? Building off of what you wrote:

for (let result of data.results) {
    if (result.uri in existingRecords) {
                let table = base.getTable("Observation"); //Do I need this?
                let query = await table.selectRecordsAsync(); //Do I need this?
                let records = query.records; //Do I need this?
                await table.updateRecordsAsync([
                {
                    id: // What should go here? ,
                    fields: {
                        "name" : result.user.name,
                        // etc. etc.
                },
            },
        ]);
    } else {
        console.log(`Create new record with uri ${result.uri}`);
    }

Hi @Ulu_Mills,

I’ve tweaked my script a bit like this:

//We first put existing records' URIs into a map named existingRecords
let table = base.getTable("Observation");
let queryResult = await table.selectRecordsAsync();

let existingRecords = {};

for (let record of queryResult.records) {
    let uri = record.getCellValue("uri");
    existingRecords[uri] = record;
}

//Then we fetch the data from the api (I have hardcoded these values here just to test, you will of course need to fetch them from your API)
let data = {
    results: [{uri: "https://news.ycombinator.com/"}, {uri: "https://news.ycombinator.com/4"}]
}

//And finally we test which URIs exist in the map we created earlier.
//If the URI exists in the map we need to update the record, other we need to create a new record.
for (let result of data.results) {
    if (result.uri in existingRecords) {
        let existingRecord = existingRecords[result.uri];
        await table.updateRecordAsync(existingRecord, {"Status": "Done"});
        console.log(`Updated existing record with uri ${result.uri}`);
    } else {
        let newRecordId = await table.createRecordAsync({
            "Status": "Todo",
            "uri": result.uri,
        });
        console.log(`Created new record with id ${newRecordId}`);
    }
}

First, I have changed existingRecords[uri] = uri; to existingRecords[uri] = record;. This will ensure that when we update the record later we can access the existing record. The code to update a record is like this:

        let existingRecord = existingRecords[result.uri];
        await table.updateRecordAsync(existingRecord, {"Status": "Done"});

Here I’m updating my Status field to a value Done. In my case the Status fields was of type Single line text. If your field is of a different type you might have to tweak this part a bit. See this page for reference.

The code to create a new record is similar:

        let newRecordId = await table.createRecordAsync({
            "Status": "Todo",
            "uri": result.uri,
        });

Regarding your Do I need this? questions, you don’t need those as you can reference the table variable as I did in my script.

Good luck :grinning_face_with_smiling_eyes:
-Raminder

Ulu_Mills
4 - Data Explorer
4 - Data Explorer

@Raminder_Singh , you have saved me DAYS of work. Thank you so much—it works perfectly :blush: