Skip to main content
Solved

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

  • February 22, 2021
  • 4 replies
  • 32 views
U

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!

Best answer by Raminder_Singh

@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

4 replies

R
Forum|alt.badge.img+3
  • Raminder_Singh
  • Participating Frequently
  • February 22, 2021

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

U
  • Ulu_Mills
  • Author
  • New Participant
  • February 23, 2021

@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}`);
    }
R
Forum|alt.badge.img+3
  • Raminder_Singh
  • Participating Frequently
  • Answer
  • February 23, 2021

@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

U
  • Ulu_Mills
  • Author
  • New Participant
  • February 23, 2021

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

Terms & ConditionsAccessibility statement