Help

Re: Setting a checkbox for the last record inserted only, via Scripting

Solved
Jump to Solution
2741 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Dave_Sheppard
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi - I think I need a scripting app to solve my problem, but I’m new to Airtable scripting and so am reaching out for help.

I have a table that I’m populating every day, but I want a 2nd grid/view with only the latest record showing. So I wanted to use a ‘latestrecord’ checkbox column, and use a script that would fire on a create record trigger to basically reset the checkbox on every row, and then only set it for that latest record that was just inserted.

My 2nd view would be filtered on this checkbox - which would then be used in a combination of charts (on all content) and summary apps (just on the latest record) in a dashboard.

I’m struggling with the scripting syntax, and so just felt it would be easier to stand on the shoulders of giants, and ask the community!

Many thanks in advance,
Dave.

Note: I’m struggling with even reset all the checkboxes - the following code runs but just doesn’t seem to work (resetting/clearing all of the ‘LatestRecord?’ checkbox columns to be blank/unchecked…

let SSITable = base.getTable(“SSI Tracker”);
let SSITableRecords = await SSITable.selectRecordsAsync();
for (let SSIrecord of SSITableRecords.records)
{SSITable.updateRecordsAsync(SSIrecord, {“Latest Record?”: false})
};

I’d then need a script to locate the latest row and then set it’s Latest Record checkbox to true/checked.

1 Solution

Accepted Solutions
Dominik_Bosnjak
10 - Mercury
10 - Mercury

It’s not often that a new user posts here asking for help in such a pleasantly verbose manner.

And the few that do tend to be at least knee-deep in the need-work-for-hire category, knowingly or not.

But you, @Dave_Sheppard? You’re as unicorny as Airtable itself.

So here, welcome to the community:

Click for welcomeness
// statement 1
let table = base.getTable('Table 1'),

    q = await table.selectRecordsAsync(
        {
            sorts: [{ field: 'ID', direction: 'desc' }],
            fields: ['Done']
        }),

    latestRecord = q.recordIds[0],
    cache = []

// statement 2
q.records.forEach(
    rec => {
        let mark = rec.id === latestRecord ? true : null
        if (rec.getCellValue('Done') !== mark) {
            cache.push({ id: rec.id, fields: { Done: mark } })
        }
    })

// optional prepwork for bonus UX points, let's label it D like "Dominik"
let neededUpdating = [...cache = cache.filter(obj => obj !== null)]

// statement 3
while (cache.length > 0) {
    await table.updateRecordsAsync(cache.splice(0, 50))
}

// Here about that bonus? Promise.resolve(D)
output.markdown(`
|                      |                             |
|:---------------------|----------------------------:|  
|     Records checked  |  ${q.recordIds.length}      |
|     Records updated  |  ${neededUpdating.length}   |
`)

The eccentric code style is a combination of the following factors:

a) I needed a coding distraction from coding

b) It meshes well with the phrase “finicky filtering”, which is how I’ve been calling this type of use cases for a while now and won’t miss an opportunity to tell people about it.

c) While not necessarily optimal for most scenarios – including this one – I liked Dave’s original idea because of the line of thinking that preceded it.

Namely, he broke up the problem into smaller pieces, did some research to confirm those should be doable - and only then got stuck on the implementation.

If he managed to go any further, I’d have asked him for the lottery numbers because no one is so lucky to guess their way through even the most user-friendly syntax at the beginning of their coding journey. And JavaScript, while one of the most acccessible high-level languages ever made, also has a record number of “gotchas” for both newbies and veterans alike. You’re destined for great coding undertakings Dave, don’t give it up now because it will only get easier from here, exponentially so, especially so with JavaScript.

Overall, d) I wanted to see if I can condense the original idea into as few statements as possible because I felt the discussion was starting to get derailed, kind of missing the forest for the trees.

I do agree with pretty much everything Justin said so far, that interlinking pattern is as close to a universal recommendation as you can get when it comes to finnicky filtering with Airtable.

So yeah, anyone is free to use this script however they’d like, but please keep in mind this is far from how your code should look like if the task at hand is even an ounce more complex and you aren’t way inside your comfort zone.

But on that note, if you’re having trouble with figuring out how this works or can’t get it to work for you at all, just ping me and I can walk you through it. The base,table,and field names I’ve used are all Airtable’s default values when you create a new table and start from scratch, then add an Autonumber and Checkbox fields to it.

P.S. Say finnicky filtering out loud and tell me it doesn’t just roll off the tongue.

See Solution in Thread

9 Replies 9

I can probably help with a scripting solution, but there may be another way to do this that wouldn’t require any scripting, or even an automation. However, I need to know more about your base design before heading down that path.

In this table where you want to spotlight the last added record, are you by chance linking these records to records in any other tables in the base?

Hi @Justin_Barrett - thanks for the reply. No linking. It’s currently just one table. Not sure if you use LinkedIn at all, but it’s basically an SSI (your LinkedIn score) web scraper which I run every day and dump the results into a table with a date/timestamp. I have Charts producing trends over time, but I want to use a Summary app to get the values from the very latest record.

So - whenever I add a record to the table, I just need it to have a flag set to say that it’s the most current record - so that I can hang a view off it for my summary app / dashboard.

I’ve read other results about build other rollups in other tables to get a max date and then match that back against the original table, but that seems like an overhead just in order to identify the latest record (I come from a SQL background).

If scripting is too complex, then I can use another table etc. Your advice would be really appreciated.

That other-table-rollup process was where I was going to go, but only if you already had a link to another table. While I agree that it’s a bit complex, if the base design is already set up to make it work it’s not that bad. Just-starting-out me might have suggested going that direction regardless of your setup, but I’ve come a long way since then. :slightly_smiling_face:

I have another idea that just came to me, but it also spawns another question for you: how do you add this new record each day? You mentioned web scraping, but I couldn’t quite tell if you’re scraping manually or if you have some other automated process do that for you.

While waiting for your response to that, I’ll address the issue that you raised in your initial post re: the script you were trying to use to clear the checkbox on all records. The main thing that jumps out to me is that you used the updateRecordsAsync option. Notice the plural “records” in there. That method is designed for updating multiple records, and accepts an array of objects containing the relevant changes. Because you didn’t pass an array, my gut says that’s why it didn’t work, though it’s a mystery why it didn’t throw an error for passing the wrong type of data. (You also omitted the “await” keyword before calling that method, but that likely wasn’t a contributor to the lack of success.)

If we do end up going with a scripting option, we can greatly simplify the unchecking process. The only thing we’d need to uncheck is the one previously-checked record from that custom view. Adding a new checked record to that view means that the previous record is easy to find and uncheck without needing to uncheck all of the other records as well.

Hey @Justin_Barrett. I’m using the Airtable WebClipper app. Please find attached some screenshots that hopefully help explain what I’m doing. Look forward to your thoughts…

Oh and apologies for the scripting syntax errors. I’m literally copy/pasting from examples & YT videos - I have no idea what I’m doing :slightly_smiling_face: , hence the reason for asking here.
Dave.

Slide1
Slide2
Slide3
Slide4
Slide5
some

Dominik_Bosnjak
10 - Mercury
10 - Mercury

It’s not often that a new user posts here asking for help in such a pleasantly verbose manner.

And the few that do tend to be at least knee-deep in the need-work-for-hire category, knowingly or not.

But you, @Dave_Sheppard? You’re as unicorny as Airtable itself.

So here, welcome to the community:

Click for welcomeness
// statement 1
let table = base.getTable('Table 1'),

    q = await table.selectRecordsAsync(
        {
            sorts: [{ field: 'ID', direction: 'desc' }],
            fields: ['Done']
        }),

    latestRecord = q.recordIds[0],
    cache = []

// statement 2
q.records.forEach(
    rec => {
        let mark = rec.id === latestRecord ? true : null
        if (rec.getCellValue('Done') !== mark) {
            cache.push({ id: rec.id, fields: { Done: mark } })
        }
    })

// optional prepwork for bonus UX points, let's label it D like "Dominik"
let neededUpdating = [...cache = cache.filter(obj => obj !== null)]

// statement 3
while (cache.length > 0) {
    await table.updateRecordsAsync(cache.splice(0, 50))
}

// Here about that bonus? Promise.resolve(D)
output.markdown(`
|                      |                             |
|:---------------------|----------------------------:|  
|     Records checked  |  ${q.recordIds.length}      |
|     Records updated  |  ${neededUpdating.length}   |
`)

The eccentric code style is a combination of the following factors:

a) I needed a coding distraction from coding

b) It meshes well with the phrase “finicky filtering”, which is how I’ve been calling this type of use cases for a while now and won’t miss an opportunity to tell people about it.

c) While not necessarily optimal for most scenarios – including this one – I liked Dave’s original idea because of the line of thinking that preceded it.

Namely, he broke up the problem into smaller pieces, did some research to confirm those should be doable - and only then got stuck on the implementation.

If he managed to go any further, I’d have asked him for the lottery numbers because no one is so lucky to guess their way through even the most user-friendly syntax at the beginning of their coding journey. And JavaScript, while one of the most acccessible high-level languages ever made, also has a record number of “gotchas” for both newbies and veterans alike. You’re destined for great coding undertakings Dave, don’t give it up now because it will only get easier from here, exponentially so, especially so with JavaScript.

Overall, d) I wanted to see if I can condense the original idea into as few statements as possible because I felt the discussion was starting to get derailed, kind of missing the forest for the trees.

I do agree with pretty much everything Justin said so far, that interlinking pattern is as close to a universal recommendation as you can get when it comes to finnicky filtering with Airtable.

So yeah, anyone is free to use this script however they’d like, but please keep in mind this is far from how your code should look like if the task at hand is even an ounce more complex and you aren’t way inside your comfort zone.

But on that note, if you’re having trouble with figuring out how this works or can’t get it to work for you at all, just ping me and I can walk you through it. The base,table,and field names I’ve used are all Airtable’s default values when you create a new table and start from scratch, then add an Autonumber and Checkbox fields to it.

P.S. Say finnicky filtering out loud and tell me it doesn’t just roll off the tongue.

Many thanks for the kind words and clear instructions @Dominik_Bosnjak. The code worked a treat! Also thanks to @Justin_Barrett for the persistence and feedback. Much appreciated :thumbs_up:

Great to hear, and I almost forgot: this is the kind of thing that you really want to have running on autopilot.

Button-pushing “solutions” have a tendency to streamline your problems, but at the cost of adding at least one new responsibility to your agenda. So, avoid them whenever reasonably possible, which is very much the case here.

As an example, here’s a slightly modified version of the original that I’ve confirmed works as an Automation (the first one would probably run up until the custom logging part, then report a failure after updating the records):

The code
let table = base.getTable('Table 1'),

    q = await table.selectRecordsAsync(
        {
            sorts: [{ field: 'ID', direction: 'desc' }],
            fields: ['Done']
        }),

    latestRecord = q.recordIds[0],
    cache = []

q.records.forEach(
    rec => {
        let mark = rec.id === latestRecord ? true : null
        if (rec.getCellValue('Done') !== mark) {
            cache.push({ id: rec.id, fields: { Done: mark } })
        }
    })

let neededUpdating = [...cache = cache.filter(obj => obj !== null)]
// statement 3
while (cache.length > 0) {
    await table.updateRecordsAsync(cache.splice(0, 50))
}

neededUpdating.forEach(object => {
    object["Action taken"] = object.done
        ? 'I determined this to be the newest record but it wasn\'t marked as such. No need to get up, I handled it.'
        : 'This wasn\'t the latest record but it had a checkmark. Well, Checkbox field privileges #canceled, mister... object, whatever.'
})

console.log(` ${neededUpdating.length}/${q.recordIds.length} records updated`)
console.table(neededUpdating)

Example Automation trigger setup

image

How the new logging mechanic works

image

  • this output is always accessible via the “Run History” tab visible in the upper-right corner of the previous screenshot
    • that’s also where you’d find Airtable’s own logs
  • meaning this is merely a redundancy which we can afford because the script is so lightweight that there’s no feasible scenario in which it hits Airtable’s runtime or memory limits

Not saying you should be paying for an Airtable Pro subscription to automate this single action, obviously.

Just leaving it as general food for thought in case anyone from the future reading this is currently thinking something along the lines of “pressing this button solves my problem”.

No, it simplifies it.

But even Airtable buttons, as strikingly inviting as they are, aren’t worth your time in the long term. :slightly_smiling_face:

Many thanks again @Dominik_Bosnjak. I’ve set up an automation trigger to run on a ‘create record’ event (i.e. when I run the Web Clipper against the website). So now I can simply run the Web Clipper, and then go to my dashboard and it’s all up to date! Will probably see if I can share this now with any Airtable folk who are also keen to improve their visibility on LinkedIn!

If you are using a create record trigger, then you already know the record id of the latest record, and you can use a simpler script. Note that the automation script in this post requires you to create an input variable for the recordId.

const table = base.getTable("SSI Tracker")
const triggerRecordId = input.config()["recordId"]

const queryResult = await table.selectRecordsAsync({fields: ['Latest Record?']})

let recordsToUpdate = queryResult.records
  .filter(record => record.getCellValue('Latest Record?'))
  .map(record => ({id: record.id, fields: {'Latest Record?': false}}))
recordsToUpdate.push({id: triggerRecordId, fields: {'Latest Record?': true}})

await table.updateRecordsAsync(recordsToUpdate)

Note that if you create two or more records at the same time, or in very quick succession, you might end up with a race condition and the true “last record” might not get the checkbox, regardless of which automation script you use.