10/26/2021 – BuiltOnAir Live Podcast Full Show – S09-E07

Duration: 60 minutes

Be Sure to Subscribe to the podcast!

FULL EPISODE VIDEO

Watch the full video of the show. See below for segment details.

FULL EPISODE AUDIO

Listen to the full Audio podcast for this episode here. Or add to your favorite podcast player

Listen On: Apple | Overcast | Spotify

The BuiltOnAir Podcast is Sponsored by On2Air – Integrations and App extensions to run your business operations in Airtable.

In This Episode

Welcome to the BuiltOnAir Podcast, the live show.  The BuiltOnAir Podcast is a live weekly show highlighting everything happening in the Airtable world.

Check us out at BuiltOnAir.com. Join our community, join our Slack Channel, and meet your fellow Airtable fans.

Todays Hosts

Alli Alosa – Hi there! I’m Alli 🙂 I’m a fine artist turned “techie” with a passion for organization and automation. I’m also proud to be a Community Leader in the Airtable forum, and a co-host of the BuiltOnAir podcast. My favorite part about being an Airtable consultant and developer is that I get to talk with people from all sorts of industries, and each project is an opportunity to learn how a business works.

Kamille Parks – I am an Airtable Community Forums Leader and the developer behind the custom Airtable app “Scheduler”, one of the winning projects in the Airtable Custom Blocks Contest now widely available on the Marketplace. I focus on building simple scripts, automations, and custom apps for Airtable that streamline data entry and everyday workflows.

Show Segments

Round The Bases – 00:02:31 –

Field Focus – 00:08:27 –

A deep dive into the Autonumbers Autonumber – Learn about autonumbers and how to fake them 

Automate Create – 00:29:34 –

Watch as we review and work through automations. Write an automation to generate autonumbers

Scripting Time – 00:42:13 –

Explore Scripting with “Scripting autonumbers”.

An advanced way to build auto numbers via a script.

Full Segment Details

Segment: Round The Bases

Start Time: 00:02:31

Roundup of what’s happening in the Airtable communities – Airtable, BuiltOnAir, Reddit, Facebook, YouTube, and Twitter.

Segment: Field Focus

Start Time: 00:08:27

Learn about the Autonumbers – Learn about autonumbers and how to fake them

A deep dive into the Autonumbers Autonumber – Learn about autonumbers and how to fake them 

Segment: Automate Create

Start Time: 00:29:34

Airtable Automations – Autonumbers with Automations

Watch as we review and work through automations. Write an automation to generate autonumbers

Segment: Scripting Time

Start Time: 00:42:13

Scripting Time: Scripting autonumbers

Explore Scripting with “Scripting autonumbers”.

An advanced way to build auto numbers via a script.

Full Transcription

The full transcription for the show can be found here:

[00:01:42] Well, good morning everyone.
[00:01:44] Welcome to an interesting
[00:01:46] episode of BuiltOnAir.
[00:01:47] My name is Kamille
[00:01:48] and I'm joined by Alli.
[00:01:50] Hello.
[00:01:51] Unfortunately Dan won't be
[00:01:53] able to join us today.
[00:01:55] So I'll be running the show
[00:01:57] and boy will I be winging it. But
[00:02:01] we'll see how that goes.
[00:02:05] this is a webcast where
[00:02:07] we talk about all things Airtable,
[00:02:09] I don't have too many updates
[00:02:11] that I've selected, but,
[00:02:13] I have the update that
[00:02:15] is most near and dear to my heart.
[00:02:17] The infamous sandwich bet I believe has
[00:02:20] finally been settled
[00:02:21] and we can give a little bit of
[00:02:23] Airtable history if you will.
[00:02:26] Sure
[00:02:27] we're gonna start there.
[00:02:29] All right.
[00:02:31] So the scripting app,
[00:02:33] is this the sandwich bet? Yes. So
[00:02:36] This post was from February 25, 2021,
[00:02:41] but this isn't the first time that
[00:02:46] this sort of topic had come up.
[00:02:49] So essentially back in 2020 or late 2019,
[00:02:53] Airtable released the scripting app
[00:02:55] as well as the custom apps development
[00:02:57] environment, both of which
[00:02:59] had a disclaimer saying
[00:03:01] they are free for the next
[00:03:02] six months.
[00:03:03] Well those six months came
[00:03:05] and went and Airtable
[00:03:07] extended it and I believe they
[00:03:09] extended it again
[00:03:10] after that. And that's
[00:03:13] yeah, that's where we got
[00:03:15] to this point where
[00:03:16] someone on the forums ask,
[00:03:18] hey, is this,
[00:03:19] you know, is that real?
[00:03:21] And
[00:03:22] there was some discussion back and forth.
[00:03:25] I said, I don't believe they'll
[00:03:28] extended again
[00:03:29] and Bill says that he thought
[00:03:32] that they would.
[00:03:33] So Bill French is another
[00:03:35] frequent commenter on the forums,
[00:03:38] very knowledgeable about all things
[00:03:41] even beyond Airtable, database related.
[00:03:43] And so we had this sort of back
[00:03:46] and forth about whether
[00:03:48] or not this extension
[00:03:50] would come again.
[00:03:51] This is a very important
[00:03:52] sort of concept that's going on
[00:03:54] because scripting unlocked
[00:03:56] so many doors for so many people.
[00:03:58] It was
[00:03:59] sort of a game changer
[00:04:01] for a lot of people's base setups
[00:04:03] and so it was very
[00:04:04] important whether or not that
[00:04:06] remained a free feature
[00:04:08] versus a pro feature,
[00:04:09] Kuovonne jumped in.
[00:04:11] We eventually started a bet
[00:04:14] where Bill proposed that if
[00:04:16] they,
[00:04:18] you know, stop
[00:04:19] Extending it and making it free
[00:04:21] forever that I would owe him
[00:04:23] a sandwich and then
[00:04:24] the reverse, if they made
[00:04:26] it a pro feature,
[00:04:27] he would owe me a sandwich.
[00:04:28] That was February 25, 2021
[00:04:32] I think they extended it again after that
[00:04:37] there's a lot,
[00:04:38] I'm scrolling through,
[00:04:40] there's a lot happening.
[00:04:42] And then in August,
[00:04:44] 0
Jordan Scott who is the

90
00:03:06.1000 --> 00:03:09.340
new community sort of liaison
[00:04:49] from Airtable side came in
[00:04:51] and she recognized the bet,
[00:04:53] which I thought was very
[00:04:54] funny because this
[00:04:57] post had existed prior to her
[00:04:59] joining Airtable I think.
[00:05:00] So she was going back
[00:05:02] and reading all of like
[00:05:04] the
[00:05:05] the post with a lot of activity
[00:05:07] on them found this bet,
[00:05:08] and acknowledged it and
[00:05:09] said, well sorry, we couldn't settle it.
[00:05:12] This was August 2021 more time passes.
[00:05:18] Again now it's September 21
[00:05:21] that it's been extended to
[00:05:23] the end of August is it
[00:05:24] going to be extended again?
[00:05:27] And then Jordan comes back
[00:05:29] and says actually they've
[00:05:31] made it officially free for
[00:05:33] everyone that's both scripting
[00:05:35] and I believe the custom block
[00:05:38] A. P. I. So, everyone gets
[00:05:41] to use it for free now. Great news,
[00:05:44] it does mean that I lost the bet, so
[00:05:48] you gotta ship Bill a sandwich.
[00:05:51] Yeah, you know, bittersweet.
[00:05:55] I just thought that was a funny
[00:05:58] addendum that happened last week
[00:06:03] and it sort of encapsulated
[00:06:05] a it's a major announcement.
[00:06:06] I don't know how,
[00:06:08] you know,
[00:06:09] announced this was because
[00:06:11] this is a reply to a very old thread,
[00:06:13] I don't recall if this,
[00:06:15] there was a separate thread
[00:06:16] that went over this
[00:06:17] but you know,
[00:06:20] if you hadn't already heard
[00:06:22] here's your announcement
[00:06:24] that scripting is now free.
[00:06:26] I do believe the run a script
[00:06:29] action in an automation
[00:06:31] is still a pro feature.
[00:06:33] So there's three different things
[00:06:36] we're kind of talking about
[00:06:38] the custom apps, sdk
[00:06:39] if you haven't already used it,
[00:06:41] you might not use it.
[00:06:43] It's very, it's much more hands on,
[00:06:45] a little bit more complicated
[00:06:47] and requires a
[00:06:47] little bit more familiarity
[00:06:49] with javascript,
[00:06:51] but scripting a lot of people
[00:06:52] had used with the script app
[00:06:54] which lives in the
[00:06:55] apps sort of side bar
[00:06:57] and then the run a script action
[00:06:59] is something that exists
[00:07:01] within an automation and there
[00:07:02] are some differences between that and the
[00:07:04] scripting app.
[00:07:07] I believe that one is
[00:07:09] still a pro plan or higher.
[00:07:12] Yeah, that's interesting.
[00:07:14] I've never actually validated that but
[00:07:18] I mean we can test,
[00:07:20] it's the Wild West this episode
[00:07:22] we're actually going to
[00:07:24] be doing a lot of automation
[00:07:27] slash scripting
[00:07:28] sort of
[00:07:30] work here.
[00:07:33] I just wanted to go through that
[00:07:35] and that's more or less the end of this
[00:07:36] thread.
[00:07:37] But
[00:07:39] other than that I couldn't find
[00:07:43] too many sort of announcements or
[00:07:47] sort of major discussion threads
[00:07:50] that hadn't already been solved.
[00:07:53] There is,
[00:07:54] I did see this morning on Reddit,
[00:07:57] there is a new
[00:07:59] third party that allows you to
[00:08:03] query Airtable via SQL
[00:08:05] I'll see if I can pull
[00:08:07] that up but I don't
[00:08:09] have that link available.
[00:08:13] But other than that,
[00:08:16] what I was going to do with
[00:08:18] 0
this episode was going to

186
00:06:39.1000 --> 00:06:41.380
be sort of an overview
[00:08:21] of
[00:08:22] auto numbers and how to fake them.
[00:08:25] Let's do it.
[00:08:26] Okay. So I have,
[00:08:28] I've pulled aside this particular
[00:08:38] thread on the Airtable forums as
[00:08:39] it is a little bit older.
[00:08:42] It is essentially asking for
[00:08:48] Well let me back up.
[00:08:49] So Airtable has a field type
[00:08:52] called auto number and it
[00:08:54] does exactly what it says it does.
[00:08:56] If you have 10 records, it will number the
[00:08:58] first one one, the second one two,
[00:09:00] the third one three,
[00:09:02] all the way down to 10.
[00:09:03] If you were to delete any one
[00:09:06] of those 10 records and add another record
[00:09:09] after that the new record
[00:09:11] would be labeled 11.
[00:09:12] It wouldn't go back and find
[00:09:14] whichever one is missing
[00:09:15] and fill in that gap.
[00:09:16] there are some limitations
[00:09:18] to the auto number field.
[00:09:20] It does not allow you to
[00:09:21] have a prefix. So you can't say
[00:09:25] like the year, the current year-1-2-3 etc.
[00:09:29] And you can't really have auto numbers
[00:09:33] for different clients.
[00:09:35] So if you wanted to say
[00:09:37] job number one for client a,
[00:09:38] job number two for client A
[00:09:40] and then job number one
[00:09:42] for client B couldn't
[00:09:43] really do that with the auto number field.
[00:09:45] And so there's pros
[00:09:47] and cons to using it.
[00:09:49] But it's somewhat limited
[00:09:51] and so this is essentially
[00:09:53] what this user had run
[00:09:55] across. They wanted to have essentially
[00:09:59] an auto number that ran by
[00:10:02] whichever field whichever
[00:10:04] record a record in a
[00:10:06] second table was being into two.
[00:10:09] So I came up with a solution that uses
[00:10:17] links records which they had
[00:10:18] already had in place.
[00:10:20] They were linking clients to
[00:10:21] orders and then using roll ups
[00:10:24] and formulas and then
[00:10:26] a simple automation to
[00:10:29] sort of fake the appearance
[00:10:30] of an auto number.
[00:10:32] And so we're going to go over
[00:10:33] that solution in a second.
[00:10:35] So I have recreated essentially
[00:10:39] what that setup would be.
[00:10:41] I have a few clients
[00:10:44] and each of those clients
[00:10:46] is linked to an order. So you know
[00:10:50] what is the client name,
[00:10:52] and then I have a regular
[00:10:54] number field not an auto
[00:10:55] number. Just to sort of demonstrate.
[00:10:59] If I were to insert
[00:11:01] an auto number,
[00:11:04] you notice there's no settings
[00:11:06] that you can add and it will just 12345.
[00:11:09] Well I don't want jane doe
[00:11:10] to continue on from john doe
[00:11:12] two completely different
[00:11:14] numbering system that I want to do. So
[00:11:16] just to illustrate why that wasn't
[00:11:18] the best solution in this case.
[00:11:20] But you'll notice that my
[00:11:23] regular number field goes 1213.
[00:11:26] I deleted a record. That's why 134.
[00:11:31] That's this sort of behavior that I want.
[00:11:36] I have a look up field that's
[00:11:39] saying what is the next client order?
[00:11:41] If I go back to my client's field,
[00:11:44] you'll notice I have a roll up field
[00:11:47] that's asking what is the largest number
[00:11:50] that has been linked to
[00:11:52] this particular client.
[00:11:53] So in john doe's case that would
[00:11:55] be four
[00:11:57] This formula is literally just saying
[00:12:00] take that maximum order and add one to it.
[00:12:04] That's the next number in a linear list.
[00:12:08] Going back to my orders table.
[00:12:10] That's what you're seeing here.
[00:12:11] So that five is the next number.
[00:12:16] This was should remember was testing
[00:12:24] in an alternate solution to this,
[00:12:27] you don't really need it for this.
[00:12:29] But
[00:12:31] if I look at my simple automation,
[00:12:34] I mean close that down.
[00:12:36] So I have an automation
[00:12:38] for when record matches
[00:12:40] conditions. What I want to do is find
[00:12:43] records in my orders table where the
[00:12:46] client order number is blank
[00:12:49] and where client is not blank.
[00:12:52] So if I were to make
[00:12:54] a new record,
[00:12:56] hopefully nothing should happen. Yes.
[00:13:00] So I don't want this to start numbering
[00:13:02] until I've linked it to a client because
[00:13:04] it doesn't know what the
[00:13:06] next order number is yet. So
[00:13:08] Let's say let's do John because
[00:13:10] I know the next order number is five.
[00:13:14] If I give it a second.
[00:13:18] You'll notice a whole bunch
[00:13:19] of things are highlighted.
[00:13:21] But that's because all of
[00:13:22] those records happened to
[00:13:23] be linked as well.
[00:13:24] It found what the next client order
[00:13:26] was which was five, as we had said,
[00:13:28] put that number into this field
[00:13:32] done using the update record action step.
[00:13:36] We are updating the same record
[00:13:38] that we found with our trigger
[00:13:40] and we're inserting for the value
[00:13:42] in the client order number field.
[00:13:44] The value of the client next order field.
[00:13:48] So because all of these records
[00:13:51] are linked together
[00:13:52] using regular Airtable sort
[00:13:54] of functionality.
[00:13:56] The formula and the roll up
[00:13:58] and look up fields are all
[00:14:01] updating themselves.
[00:14:02] So they know that the next
[00:14:04] order number is now six.
[00:14:06] So if I were to keep going
[00:14:09] And do John Doe again this would be six.
[00:14:14] I love it. And then all the other
[00:14:16] ones turn to seven.
[00:14:18] So if I am just going to
[00:14:19] group by clients of things
[00:14:21] you can see this a little bit better.
[00:14:25] If I do
[00:14:26] one for jane doe you'll see
[00:14:29] that she gets it too
[00:14:31] and it's completely separate
[00:14:33] from john doe's numbering system.
[00:14:36] Recently this week
[00:14:38] someone had found that
[00:14:40] older post was from around July
[00:14:43] when I had presented this solution
[00:14:47] and they discovered I'm going to turn this
[00:14:50] off really quickly.
[00:14:55] They discovered if you were
[00:14:58] to go in and duplicate a record,
[00:15:01] it comes in with the value
[00:15:03] of client order number already filled in.
[00:15:06] Well that means that my trigger
[00:15:09] for this automation
[00:15:10] won't fire for that record.
[00:15:13] It's looking for when client
[00:15:15] order is empty, which it isn't.
[00:15:17] So the solution there would
[00:15:19] be either to once I've
[00:15:21] duplicated the record to
[00:15:23] delete that value
[00:15:24] and then give it a second.
[00:15:26] and now it will do it
[00:15:29] but that's annoying.
[00:15:30] And sometimes you might forget.
[00:15:32] So my solution there
[00:15:34] was you can only do this for pro
[00:15:37] and enterprise bases
[00:15:38] but if you go into edit
[00:15:40] field permissions
[00:15:42] and set this to nobody
[00:15:44] and then turn on,
[00:15:45] allow this field to be modified by
[00:15:48] automations.
[00:15:49] Now if I duplicate this jane doe record,
[00:15:55] wow, it doesn't come in
[00:15:57] with a client order number.
[00:16:00] So there's actually no change
[00:16:02] that you need to make
[00:16:03] to your formula field,
[00:16:04] your roll ups, your lookups
[00:16:06] or your automation,
[00:16:07] all you have to do is change the
[00:16:09] permission level for the
[00:16:11] client order number field
[00:16:13] and there's pros and cons to that.
[00:16:15] Remember that an auto number,
[00:16:17] an actual auto number field
[00:16:19] is calculated,
[00:16:19] meaning you couldn't edit that field
[00:16:21] anyway. So if you think of it like
[00:16:23] that you're not losing functionality
[00:16:25] but it does make this a little
[00:16:27] bit less flexible because
[00:16:29] if you wanted to go
[00:16:30] back and renumber a whole bunch
[00:16:32] of old records,
[00:16:33] you would have to remember to
[00:16:34] turn
[00:16:35] the field permissions back on
[00:16:37] so you can do all of that
[00:16:38] and then turn them back
[00:16:39] off so that you can continue
[00:16:41] with this functionality
[00:16:42] but it is possible.
[00:16:44] That's so interesting I guess I never
[00:16:47] like thought about how if you
[00:16:49] do it like... I can't talk, to the field
[00:16:51] permissions. Like I didn't realize
[00:16:53] that duplicating it
[00:16:55] would actually blank that
[00:16:56] value out. Like
[00:16:58] yep, I didn't think of it first either,
[00:17:01] which is why should read number is a
[00:17:03] field. So what I was trying to do
[00:17:05] was to answer that
[00:17:07] second person's question,
[00:17:08] what do you do if you
[00:17:10] duplicated record
[00:17:11] and it comes and filled.
[00:17:12] So I had started to build this
[00:17:14] formula saying okay well if
[00:17:18] if the field is blank,
[00:17:20] if the client order number
[00:17:22] is blank or if the order
[00:17:24] number equals the next client order equal
[00:17:28] output. Yes otherwise output no, but that
[00:17:31] that's not
[00:17:32] accurate. Like I couldn't get this portion
[00:17:36] of the formula to say
[00:17:37] what I wanted to say.
[00:17:39] What I was trying to say was if
[00:17:41] this is six and this is also six
[00:17:44] which is very difficult
[00:17:46] to do with a formula
[00:17:48] without several roll ups or look ups
[00:17:53] aiding you
[00:17:54] so we this field is not helpful.
[00:17:58] I am glad I eventually remembered
[00:18:01] that field permissions, they're useful.
[00:18:04] Yeah
[00:18:05] it's so interesting though, I've
[00:18:08] struggled with trying to use
[00:18:10] the nobody option in
[00:18:12] field permissions because even
[00:18:14] when you say allow this field
[00:18:15] to be modified by automation
[00:18:17] and maybe this has
[00:18:17] been fixed but
[00:18:19] I've run into scripting errors
[00:18:20] where I've had an automated
[00:18:22] script trying to edit
[00:18:23] that field value
[00:18:24] and it gives an error
[00:18:25] and says insufficient permissions but
[00:18:27] it's literally a toggle right there.
[00:18:31] Yeah, so I can see that.
[00:18:33] I don't know if that's been fixed.
[00:18:36] I think it certainly
[00:18:38] should be because it is
[00:18:40] it's not not an automation.
[00:18:42] I get that it's a script
[00:18:43] but it is within an
[00:18:44] automation. So one should assume
[00:18:49] I wonder if you output
[00:18:51] the value in the script,
[00:18:53] you know like an output.set
[00:18:55] then used that to update
[00:18:57] the field. Maybe that would work.
[00:18:59] It
[00:19:01] it should because this is this is a
[00:19:06] this is a regular update,
[00:19:08] record action steps.
[00:19:09] So if instead of using from the
[00:19:11] trigger like we're doing here,
[00:19:13] if we had a step before
[00:19:14] this which was a run a
[00:19:14] script action
[00:19:16] it should be able to pull
[00:19:18] that in as the value
[00:19:20] that it's being updated.
[00:19:22] And while I'm in here
[00:19:24] I might as well test, wait
[00:19:29] what workspace is this on
[00:19:30] because I'm testing whether
[00:19:32] or not run a script is
[00:19:35] is
[00:19:38] a pro feature? Yeah pro feature so
[00:19:42] no okay, it's on temporary pro
[00:19:45] so that tells us nothing
[00:19:48] I have for everyone's
[00:19:52] Yeah that you can see behind the curtain.
[00:19:54] I have a whole workspace devoted to
[00:19:56] answering questions
[00:19:57] that I find other forums
[00:19:58] I should do that.
[00:20:00] It's better to just keep them separate.
[00:20:05] Oh my goodness,
[00:20:06] I've learned that the hard way
[00:20:08] and my workspace is just like
[00:20:10] so long just going and
[00:20:12] going and going and going
[00:20:13] and I've tried to like put in
[00:20:15] parentheses like example base.
[00:20:17] So that way I know
[00:20:18] not to delete it but like
[00:20:19] I accidentally deleted one base
[00:20:21] that I had put a link to
[00:20:23] on the forum like years
[00:20:24] ago and then I started getting everybody
[00:20:26] commenting like this space doesn't work
[00:20:28] anymore. And so I had to rebuild it,
[00:20:30] put a new one and I was like,
[00:20:31] oh no I've started to
[00:20:34] my strategy has changed, you
[00:20:39] way back, I used to build the whole base
[00:20:42] and then just share that base.
[00:20:43] So people could copy the configuration
[00:20:46] but that means you do have to keep that
[00:20:48] base forever because the person
[00:20:51] who you're directly
[00:20:53] replying to will copy it
[00:20:55] like that day or the next day
[00:20:57] when they see the
[00:20:59] someone has commented.
[00:21:00] but anyone who finds that
[00:21:02] post afterward is going to
[00:21:03] you know,
[00:21:05] look to see if they can do the same,
[00:21:07] answer a very similar question and if it's
[00:21:09] not there, then you kind of
[00:21:11] might get roped back into
[00:21:14] that very old question by
[00:21:16] then you've probably forgotten
[00:21:18] what the setup was
[00:21:19] if you deleted the base.
[00:21:21] And so what I've done
[00:21:22] I've started doing now is set up
[00:21:24] bases to test things out
[00:21:26] and then just screenshot with the
[00:21:30] what the configuration
[00:21:31] is so that I can delete the base
[00:21:33] when I feel like I don't
[00:21:34] need it anymore.
[00:21:35] But the answer is still
[00:21:36] there for everyone to see.
[00:21:38] Perfect.
[00:21:40] Yeah,
[00:21:41] I am now within a base
[00:21:43] that is currently on a free plan
[00:21:46] or free workspace and
[00:21:47] run a script is not available. So
[00:21:50] to answer the
[00:21:52] earlier question I had
[00:21:54] run a script is still a pro feature
[00:21:57] and I believe that
[00:21:58] was always the case.
[00:22:00] It was introduced separately
[00:22:02] from the script
[00:22:03] app and the custom apps
[00:22:05] sdk because automations
[00:22:07] didn't exist yet and when
[00:22:09] run a script was introduced
[00:22:11] it was always a pro feature
[00:22:13] so that hasn't changed.
[00:22:15] Excellent.
[00:22:17] Yeah.
[00:22:19] What was I gonna say?
[00:22:20] There's one thing I really
[00:22:22] wish that they would release
[00:22:23] and that is the option to
[00:22:25] run an automation
[00:22:27] based on a button being pushed
[00:22:29] without having to run a script.
[00:22:31] Like
[00:22:33] my workaround is like you hit
[00:22:35] the button it runs a script
[00:22:36] and the app like a
[00:22:37] scripting app to check
[00:22:38] which then couldn't
[00:22:40] start the automation.
[00:22:42] Yeah if we take a look at what are
[00:22:46] triggers are, our Airtable based triggers
[00:22:50] none of them really get
[00:22:53] what you want.
[00:22:54] So when record matches the condition
[00:22:57] will a button by itself
[00:22:59] doesn't change the conditions
[00:23:02] of the record. So you would be
[00:23:05] clicking the button and the button
[00:23:07] would fire a script and that script would
[00:23:09] check a checkbox or something.
[00:23:13] Or when form is submitted
[00:23:15] that's not really a button
[00:23:16] pressed when record is
[00:23:17] created obviously isn't
[00:23:19] when record is updated.
[00:23:21] That's kind of the same problem
[00:23:22] with when record matches
[00:23:24] the conditions pressing the button
[00:23:26] alone does not update
[00:23:27] the record at scheduled
[00:23:28] time is not even connected
[00:23:30] to any particular record.
[00:23:32] So that's even worse in
[00:23:34] this in this case
[00:23:35] and when record enters
[00:23:36] the view again there's no change to the
[00:23:38] actual
[00:23:41] conditions of the
[00:23:44] record itself. So
[00:23:46] I mean you could do
[00:23:49] this instead of having,
[00:23:56] you make a button field
[00:23:58] and just do that for the
[00:24:00] formula you could have
[00:24:03] you know the button
[00:24:04] you click the button and it
[00:24:05] runs the script and it does
[00:24:06] something like check a
[00:24:08] checkbox or you could fire the
[00:24:10] you could fire something else,
[00:24:13] send something to a web hook or do
[00:24:18] so you have a you have a lot
[00:24:19] more control if you do
[00:24:21] actually use a button field,
[00:24:22] if you
[00:24:24] run, you know,
[00:24:25] have this automation be triggered when
[00:24:28] the done field equals
[00:24:31] check,
[00:24:32] you could do that, just you lose out on
[00:24:37] a couple of things.
[00:24:39] It's a lot easier to accidentally
[00:24:41] fire if you use an actual
[00:24:42] checkbox field. I know,
[00:24:44] I stopped doing that
[00:24:45] actually, I use a single select that only
[00:24:47] has one option.
[00:24:48] Yeah, that's actually,
[00:24:49] you know that I would say that's better
[00:24:51] because I was about to say you
[00:24:53] also lose, you could change
[00:24:55] this label to say
[00:24:55] like
[00:24:56] fire automation or some descriptive text.
[00:25:00] You would be able
[00:25:02] to get that same sort of vibe
[00:25:04] if you were to do
[00:25:05] a single select
[00:25:12] run automation. Exactly.
[00:25:15] And then you would just do that.
[00:25:17] It's much more purposeful. Right.
[00:25:19] Yeah. And way less likely
[00:25:21] to accidentally fire.
[00:25:23] Yes, I've had that happen a few
[00:25:25] times recently with just other clients and
[00:25:28] I mean like how did this get sent?
[00:25:30] And it's like, oh well somebody check the
[00:25:32] box, so we should not
[00:25:34] have a checkbox anymore.
[00:25:36] I mean ideally not. But
[00:25:39] so that's that's sort of the
[00:25:42] general overview of how you could use
[00:25:46] A one step automation
[00:25:49] that's only looking at the single record
[00:25:55] to sort of fake your own
[00:25:58] custom auto number.
[00:26:00] And one more time just to
[00:26:02] demonstrate if I delete two
[00:26:05] the next number is still four
[00:26:06] because it's looking at the maximum
[00:26:08] if I were to do this again,
[00:26:10] It should output four and
[00:26:12] then this should change to five.
[00:26:15] So you have all the same
[00:26:16] functionality that you would
[00:26:18] have had with auto number.
[00:26:19] But now custom for each
[00:26:21] client or whichever you're
[00:26:23] linked record field is
[00:26:25] yeah this is a really great work around.
[00:26:30] I used it in my like wedding base.
[00:26:32] I was trying to get really
[00:26:34] nerdy and number
[00:26:34] like ideas that I had.
[00:26:36] So every time I add a
[00:26:37] regular topic would be like idea
[00:26:39] 1234 Blah Blah Blah.
[00:26:42] But it was funny at first I
[00:26:43] did it in a really silly way.
[00:26:45] I did it with the
[00:26:45] count field.
[00:26:46] It was thinking that way
[00:26:48] because that would that would definitely
[00:26:50] screwed things up.
[00:26:52] So it's like if you just demonstrated
[00:26:54] if you were to delete a
[00:26:55] record
[00:26:57] then you would end up
[00:26:58] with two number threes. Like
[00:27:00] for example.
[00:27:01] Insert
[00:27:03] count. So
[00:27:06] we're talking about
[00:27:08] orders.
[00:27:11] Yeah so here's that difference.
[00:27:14] So we have
[00:27:16] 1,3 and 4.
[00:27:17] So the maximum order is four,
[00:27:19] meaning the next order should be five,
[00:27:22] but there's only three that are linked.
[00:27:25] So if we were to have this next order
[00:27:28] number be if I change this my automation
[00:27:31] I think might, no it won't actually
[00:27:33] because no. All right,
[00:27:36] I'll just change it back before,
[00:27:38] so if I change this to
[00:27:39] count plus one,
[00:27:41] the next order number is four for jane,
[00:27:44] even though four is already here.
[00:27:47] So that's you know, a sort of gotcha.
[00:27:49] Yeah, that's the way you don't want to do
[00:27:52] it
[00:27:53] go with Kamille's first version.
[00:27:56] I mean it took me a second
[00:27:58] to sort of figure
[00:27:59] this out because I think that's
[00:28:01] not the first time someone
[00:28:02] has asked that on the
[00:28:02] forum, it's not
[00:28:05] necessarily the easy thing
[00:28:07] to just come up with.
[00:28:09] But you know, also while we're here,
[00:28:11] before we move on to the advanced version,
[00:28:14] you'll notice I have a third client
[00:28:16] called Mark Smith. And you know,
[00:28:17] because he's linked to nothing,
[00:28:20] his his maximum order is zero
[00:28:22] because there's nothing attached to it.
[00:28:24] So zero plus one is one.
[00:28:26] So just so you know,
[00:28:28] you don't, when you start with
[00:28:29] this method it works from the get go.
[00:28:32] Right.
[00:28:32] Exactly.
[00:28:34] Excellent. Super useful.
[00:28:36] I think it was a Justin
[00:28:40] Justin Barrett friend of the show. Yes,
[00:28:43] he came up with like a way
[00:28:45] to do it before automations existed
[00:28:48] and it was like absolute insanity.
[00:28:51] Just like
[00:28:51] genius way.
[00:28:52] Like it was literally like just with
[00:28:55] lookups roll ups and I can't
[00:28:56] even it was like a substitute function
[00:28:58] to substitute all the numbers before.
[00:29:00] It's up now. And like oh my God,
[00:29:02] it was like wow,
[00:29:04] that was really cool. I
[00:29:08] I never figured that one out.
[00:29:11] It worked, but you know,
[00:29:13] today is a today is an
[00:29:15] automation themed today,
[00:29:19] so that's the simple way to do stuff.
[00:29:21] Yes.
[00:29:24] here I have a base that
[00:29:26] I've called auto test where I just,
[00:29:29] anytime someone asks
[00:29:30] an automation based question,
[00:29:33] I will put it in here
[00:29:36] and sort of test things out.
[00:29:38] But the purpose of this
[00:29:41] if you were to read the names
[00:29:44] of some of my automation tests,
[00:29:47] there's more complicated ways
[00:29:49] to auto number records.
[00:29:51] These however involve
[00:29:53] scripts. So
[00:29:58] the best way to do it,
[00:29:59] I think it's just to jump
[00:30:01] into the automation itself,
[00:30:02] so
[00:30:05] this one requires a different
[00:30:09] trigger doesn't require
[00:30:10] different trigger,
[00:30:11] just uses a different one.
[00:30:12] This one is simply
[00:30:13] when a new record is created
[00:30:16] and then there's a script,
[00:30:18] so instead of going straight to
[00:30:23] going straight to an update record,
[00:30:25] there's a script that runs before it.
[00:30:30] So I have a
[00:30:35] A field in table one
[00:30:38] that is the year week, so 2020,
[00:30:42] You can see right here, 2020-42.
[00:30:45] So the 42nd week of the year,
[00:30:47] 2020, what this script is attempting
[00:30:50] to do is to find all of the records that
[00:30:54] were produced that week
[00:30:56] and then number them like that.
[00:30:58] So the advantages here
[00:31:00] is
[00:31:00] I don't have to link them
[00:31:02] to a table full of week numbers.
[00:31:05] which is what a simple solution required,
[00:31:08] it required you to have records
[00:31:10] linked together so you could get
[00:31:12] a roll up of the maximum
[00:31:14] of the numbers that
[00:31:15] have been counted thus far
[00:31:16] and adding one to that maximum, yada,
[00:31:18] yada yada.
[00:31:19] This one, nothing is linked to anything.
[00:31:22] I'm just doing some sort of
[00:31:24] an auto number based on
[00:31:26] meta. You can do this by the year
[00:31:29] something was produced.
[00:31:30] You don't have to get
[00:31:31] down to the week if you didn't want to.
[00:31:34] In this example it's a year and week.
[00:31:38] So a fairly simple script, only 12 lines.
[00:31:43] You start off with getting
[00:31:45] the input config
[00:31:46] I have just one config variable
[00:31:48] which is the year week.
[00:31:52] The reason why this is able to come in
[00:31:54] if you recall the trigger was when
[00:31:56] record was created.
[00:31:58] This is a formula field.
[00:32:00] So that value would have
[00:32:02] been there at creation
[00:32:03] and it's doing it based on date created.
[00:32:06] So there's no real input
[00:32:08] anyone has to do for this.
[00:32:12] I'm getting the table that the
[00:32:14] record has come from.
[00:32:16] I'm querying all the
[00:32:17] records. We've talked about this
[00:32:19] before on the show.
[00:32:20] We have a strikethrough
[00:32:21] across select records a sync
[00:32:24] because they've depreciated
[00:32:26] calling this function
[00:32:28] without also specifying which
[00:32:31] fields you wanted to look at.
[00:32:34] This script was written before
[00:32:36] that change had been made.
[00:32:39] That is interesting. I actually,
[00:32:42] I don't think I was here when
[00:32:44] I think you were on honeymoon
[00:32:46] while you were on
[00:32:46] your honeymoon,
[00:32:47] they depreciated that function.
[00:32:49] It's still, I think it's still
[00:32:51] kind of works, but you will get like a,
[00:32:55] you know, the script editor
[00:32:57] doesn't like it, so
[00:33:00] that's so interesting.
[00:33:01] I just ran into this yesterday, I was
[00:33:03] I had somebody write to me
[00:33:05] and they were like,
[00:33:06] hey this script keeps timing out
[00:33:07] and my browser keeps refreshing
[00:33:08] and I was like weird, I'm seeing the same
[00:33:10] behavior
[00:33:12] and that's how I fixed it
[00:33:13] was I to find like a
[00:33:15] definitive list of all the fields I needed
[00:33:18] and then it ran. No problem,
[00:33:20] but I am behind the times
[00:33:23] and was not aware that
[00:33:24] they had depreciated it.
[00:33:26] So that is really good to know.
[00:33:29] Well folks,
[00:33:29] you heard it here,
[00:33:31] they've depreciated that
[00:33:33] function with that.
[00:33:35] So you, you saw me type in
[00:33:37] the select records
[00:33:39] a sync accepts an object.
[00:33:42] So for either fields or sort as
[00:33:44] key values. So
[00:33:47] I typed in an object with the key
[00:33:50] of fields and fields
[00:33:51] accepts an array of field
[00:33:53] names or IDs. So I know I'm only
[00:33:56] looking at the year weak field.
[00:34:00] So that's the only one I passed in.
[00:34:03] So now you no longer
[00:34:05] see the strikethrough. Going through,
[00:34:08] I am querying all of the records
[00:34:11] and I'm just trying to find
[00:34:15] how many records
[00:34:17] Have the same year, week as this one.
[00:34:21] So
[00:34:23] that means
[00:34:26] the at some point
[00:34:28] when it's clearing through
[00:34:30] all of the records,
[00:34:31] it's going to get to the same
[00:34:37] it's going to get to the same
[00:34:39] record that triggered this script,
[00:34:40] so no matter what,
[00:34:42] you're not going to end up with zero,
[00:34:44] at least one record has the same
[00:34:47] year week and that one record
[00:34:49] is the record that fired the script. So
[00:34:52] yeah,
[00:34:54] I have accounted for that error. Yes.
[00:34:57] it's going to return the number
[00:35:01] of records that matched,
[00:35:04] so at least one or
[00:35:05] some greater number of records.
[00:35:08] And then I'm out putting that
[00:35:10] number of records as the position.
[00:35:12] So we had talked about in our
[00:35:15] simple example,
[00:35:16] the difference between using count
[00:35:18] and maximum
[00:35:20] this in this, what the script is doing is
[00:35:23] using the count method and not maximum
[00:35:25] you could edit the script to be,
[00:35:28] to use the reduced function
[00:35:31] to find the maximum
[00:35:33] of every
[00:35:39] of every auto number faked auto
[00:35:41] number that is found.
[00:35:43] This would require some
[00:35:44] adjustments to what the script is doing.
[00:35:46] Right. But just to relate it back to the
[00:35:48] other example,
[00:35:50] it's basically following this method
[00:35:53] out putting the number of records
[00:35:55] that have the same year, week
[00:36:00] and then the next step is
[00:36:03] to simply update that original record,
[00:36:05] you could update the record
[00:36:08] from within the script itself.
[00:36:10] I separated them
[00:36:12] out because,
[00:36:14] and I wanted to keep the original
[00:36:16] script sort of clean and simple.
[00:36:17] And for explanation purposes
[00:36:20] I think it's easier to see
[00:36:24] each constituent part of
[00:36:26] a solution of a solution by itself.
[00:36:29] So the actual updating
[00:36:31] of the record,
[00:36:32] we're taking the record ID from whichever
[00:36:34] one file fired the automation
[00:36:37] and I'm building a custom auto number
[00:36:41] as opposed to doing this with
[00:36:43] a two separate fields
[00:36:44] and a formula to concatenate
[00:36:45] things together. I'm taking the
[00:36:49] original year week that it came in on.
[00:36:52] Let me see if I could scroll
[00:36:54] and find where that is.
[00:36:56] Yeah, we're looking at
[00:36:57] we're looking at these
[00:37:00] at these two fields the year week
[00:37:03] and then
[00:37:05] the auto number
[00:37:07] and then space dash space
[00:37:09] the position from the scripts.
[00:37:11] You can see it's coming
[00:37:12] from the second step
[00:37:13] which would have been
[00:37:14] the run the script action.
[00:37:15] So I think this is on,
[00:37:18] who knows if I run this,
[00:37:20] will it work? I hope.
[00:37:23] Love it.
[00:37:25] I might have to I remember
[00:37:27] seeing update thing.
[00:37:29] Yeah, I might have to do this.
[00:37:36] Okay. One of my automation
[00:37:38] but it's doing the other,
[00:37:41] I've done two separate automations that
[00:37:43] it's doing at the same time. So
[00:37:47] Okay there.
[00:37:48] Perfect. That's a fun experiment.
[00:37:51] There's two like I
[00:37:53] showed the start there's
[00:37:54] two different auto number,
[00:37:57] script based things
[00:37:59] that I have going in this table
[00:38:01] output into different fields
[00:38:04] and I forgot that they were both on
[00:38:07] so it auto numbered it
[00:38:08] using this method first
[00:38:10] and then the 2nd one
[00:38:11] which I was actually waiting for
[00:38:13] love it.
[00:38:14] So if I were to keep,
[00:38:16] let me change my group to be
[00:38:21] by year week.
[00:38:25] So
[00:38:27] because it's a
[00:38:29] because it's a calculated field,
[00:38:31] I can't just create a new one
[00:38:34] within that group. So
[00:38:36] going down here to create
[00:38:38] a new record there, you can see it's
[00:38:42] auto numbering by the week
[00:38:44] itself without having to link
[00:38:47] to some other record in
[00:38:48] order to come up with the maximum.
[00:38:53] Yeah, that's awesome.
[00:38:54] And it looks like the
[00:38:56] other one you're doing is
[00:38:58] like making sure you're setting it to a
[00:39:02] like consistent length,
[00:39:03] so you've got the least zeros
[00:39:06] which I do that a lot.
[00:39:07] I really like to have everything like
[00:39:10] perfectly like I think it's so
[00:39:12] much easier to read.
[00:39:13] Yeah, I think for a lot of
[00:39:14] people, they are going to
[00:39:16] want to follow a similar sort of
[00:39:22] process for leading zeros
[00:39:25] for this type of auto numbering.
[00:39:28] So I've seen this particular format
[00:39:31] a lot just like the year dash and then
[00:39:34] you might have
[00:39:35] 1000 jobs in the year,
[00:39:37] so you might want four
[00:39:38] digits instead of three.
[00:39:39] Like I have at the end.
[00:39:41] But it's sort of keep it
[00:39:43] makes things just easier to read
[00:39:45] in this case, you know,
[00:39:48] a year is always four
[00:39:50] characters. And then the year
[00:39:52] week is also always two characters.
[00:39:55] So this is going to be consistent.
[00:39:59] Oh no it's not because
[00:40:00] the first few weeks I think,
[00:40:02] but you can change that
[00:40:03] within
[00:40:04] how you've done this.
[00:40:06] I've done week number
[00:40:08] if you want to force leading zeros
[00:40:10] this is outputted as a string anyway
[00:40:12] because there's spaces and dashes.
[00:40:14] You could do something like
[00:40:20] this
[00:40:24] two and then do
[00:40:30] and
[00:40:31] I think
[00:40:33] that's not right.
[00:40:35] 00. Yeah
[00:40:39] 00 and the actual week number
[00:40:42] and then get the right
[00:40:44] most two characters from
[00:40:46] that to get you.
[00:40:49] Yeah the exact same position
[00:40:50] because all of these had two characters.
[00:40:52] And I can't
[00:40:54] none of these were done in January.
[00:40:58] So I don't know,
[00:40:59] I can't
[00:41:00] demonstrate that that worked.
[00:41:03] But sure. You can't time travel.
[00:41:05] Yeah,
[00:41:05] I like that method too
[00:41:07] using the right function.
[00:41:09] Somebody had posted that
[00:41:11] recently because I posted
[00:41:13] something using
[00:41:14] I like doing it with the repeat.
[00:41:16] But I feel like that's
[00:41:18] what you just did is so much cleaner like
[00:41:22] way less characters to type.
[00:41:24] Well it really depends
[00:41:26] on the format you want this
[00:41:28] auto number to take,
[00:41:29] to take the form of.
[00:41:31] So if you wanted a great many number
[00:41:33] of leading zeros,
[00:41:34] I would have done repeat.
[00:41:35] Yeah, if you have like 10,
[00:41:37] well it's Airtable so you're
[00:41:39] going to run out of
[00:41:40] Space in your in your base very quickly.
[00:41:42] 10,000 records a year.
[00:41:45] But like if you had
[00:41:46] 10,000 records a year,
[00:41:48] you would want that many
[00:41:50] number of
[00:41:53] leading zeros at the start
[00:41:56] to make sure that it comes
[00:41:58] out as a consistent length.
[00:42:01] Exactly. So repeat would come in handy.
[00:42:05] So that's that's advanced
[00:42:07] automation number one.
[00:42:11] I have two more I think
[00:42:12] I just want to do auto number
[00:42:14] by groups because this one
[00:42:15] is harder to explain.
[00:42:18] Perfect.
[00:42:23] and I've changed things so
[00:42:28] let's see.
[00:42:29] Yeah,
[00:42:31] okay, I'm trying to remember the precise
[00:42:36] set up for
[00:42:41] for this automation
[00:42:42] and I've gone around
[00:42:44] and changed how all of my groupings
[00:42:46] have been made. But
[00:42:49] in this we're currently looking at,
[00:42:53] we have a table that is grouped
[00:42:56] by two different fields
[00:42:59] linked to table two
[00:43:01] and then grouped again by check.
[00:43:05] So if I go into my
[00:43:07] auto number by groups table
[00:43:12] it consists of a single
[00:43:14] action step after the trigger
[00:43:19] and you'll see that it's
[00:43:20] much longer but not
[00:43:22] too long.
[00:43:24] Right.
[00:43:25] So it actually has no input variables.
[00:43:28] It's going all based on
[00:43:33] You know, everything is happening
[00:43:35] within this script, so it's not using
[00:43:38] a trailing update record action step.
[00:43:40] Everything is happening within the script
[00:43:42] because I need to actually update
[00:43:45] multiple records at once.
[00:43:47] What this script is
[00:43:48] doing
[00:43:49] is
[00:43:51] asking first what is the
[00:43:53] table that we're looking at
[00:43:55] and a particular view,
[00:43:57] you don't have to include
[00:43:59] a view necessarily If you
[00:44:01] wanted to renumber everything
[00:44:03] within the table at once,
[00:44:04] you could or if you want
[00:44:05] to do a select number within
[00:44:07] a particular grit,
[00:44:09] particular view you could.
[00:44:11] It's querying all those
[00:44:13] records from the chosen view.
[00:44:15] I have a variable I've called group path.
[00:44:19] So it is an array of in my case two
[00:44:22] fields link and check,
[00:44:26] going back this is going to
[00:44:28] become obnoxious very quickly
[00:44:30] exiting in and out
[00:44:31] of this automation,
[00:44:32] but if we go back to this grid view
[00:44:35] I've changed the name
[00:44:36] of the field,
[00:44:37] it used to just be called link.
[00:44:40] linked to table two and
[00:44:42] check.
[00:44:43] So that's what it's referencing. So
[00:44:51] I'll just do that. So it's clear
[00:44:54] Group path. So
[00:44:56] in scripting and custom apps
[00:44:59] we can't we don't really
[00:45:01] have access to what
[00:45:02] field a view is being grouped by.
[00:45:05] That's not something
[00:45:08] that's exposed via
[00:45:09] the api it's just something
[00:45:11] that's visually there
[00:45:12] when you're looking at
[00:45:14] something within Airtable,
[00:45:16] which is why I've had to construct this.
[00:45:18] So you have to know what if you wanted
[00:45:21] to include a similar functionality
[00:45:23] in your base,
[00:45:24] you would have to know what the
[00:45:28] table is being grouped by.
[00:45:30] I know it's grouped first
[00:45:32] by linked to table two,
[00:45:33] which is why that's the first
[00:45:35] item in the array
[00:45:36] and the second field
[00:45:37] is being grouped by this check.
[00:45:41] then I have, this is more complicated
[00:45:44] than I remember I have ranked by main
[00:45:47] points. And then rank field name rank.
[00:45:50] So what it's doing essentially
[00:45:53] is looking at the
[00:45:56] all of the groups
[00:45:57] that I have for this particular view
[00:46:01] and then some numerical field that
[00:46:06] that indicates
[00:46:10] in this case it's points.
[00:46:12] But some numerical differentiation
[00:46:15] between the
[00:46:15] different records within that group.
[00:46:17] I want to know who
[00:46:19] has the best score
[00:46:21] within that particular group.
[00:46:24] You could simplify this particular
[00:46:27] script down if you just wanted to do,
[00:46:30] what order do they appear in
[00:46:32] within that group. So
[00:46:34] this one isn't necessary
[00:46:36] if you don't have that number field.
[00:46:39] If you wanted to do just
[00:46:40] what order do they appear
[00:46:41] within that group
[00:46:42] you could.
[00:46:43] But that's not particularly
[00:46:45] useful because the order
[00:46:47] something might appear in a
[00:46:48] group,
[00:46:49] you know, might not make sense.
[00:46:51] If I could re sort these things around it,
[00:46:54] it wouldn't
[00:46:57] It's not letting me because
[00:46:59] I've sorted by whatever, you know,
[00:47:01] you know. So
[00:47:06] now that I'm back in this table,
[00:47:10] you'll see
[00:47:12] these people have various
[00:47:14] different points
[00:47:15] that they've been awarded for
[00:47:16] whatever reason. So 2569, 15.
[00:47:19] I want to know comparatively
[00:47:23] of the eight people within this group,
[00:47:25] or eight records within this group,
[00:47:27] how does this record
[00:47:29] fair against those eight?
[00:47:31] Right. So
[00:47:33] perfect.
[00:47:35] Back into the script.
[00:47:36] So many button clicks. Love it.
[00:47:40] so essentially you're ranking
[00:47:42] a group of records
[00:47:44] by how many points they have.
[00:47:45] Yes. Which is a far more complicated
[00:47:48] version of a auto number if you will
[00:47:51] because you've ordered things
[00:47:54] a very particular way,
[00:47:57] and now you're trying to
[00:48:00] Say one through in this case eight
[00:48:02] because there was eight
[00:48:03] in that group that we
[00:48:04] had looked at
[00:48:05] one through 8. What is their
[00:48:08] auto number based on all of this
[00:48:12] more complicated criteria
[00:48:13] which you couldn't really
[00:48:15] get at with just a formula
[00:48:16] alone.
[00:48:18] Well you could have if it
[00:48:19] was extraordinarily long formula maybe.
[00:48:24] And then from there we have
[00:48:26] this is fairly common
[00:48:27] I think with how other
[00:48:28] people set up their
[00:48:31] with their scripts having a two
[00:48:33] blanca raised one for batches and one for
[00:48:36] updates, updates is pretty common.
[00:48:38] Batches will become important.
[00:48:40] You'll see in a second
[00:48:42] and then this is where things
[00:48:44] start to get more
[00:48:46] complicated and hyper specific
[00:48:49] for this particular script. So
[00:48:52] I'm going to be explaining
[00:48:54] the logic of this script
[00:48:55] and then we will, when this
[00:48:57] video gets finalized,
[00:48:59] I will provide the link to get to
[00:49:02] the code of this script.
[00:49:04] So you don't have to copy everything down
[00:49:07] over the course of this episode
[00:49:10] is just sort of to go
[00:49:11] over the logic of what's
[00:49:12] happening and how it's numbering by group.
[00:49:18] So it is
[00:49:24] essentially what it's doing,
[00:49:26] it's trying to find for
[00:49:27] each of the records that
[00:49:28] are within table one
[00:49:31] It is putting together
[00:49:34] its own set of groups. So because
[00:49:38] scripting doesn't have access
[00:49:41] to what there's no group ID.
[00:49:43] So I have to make my
[00:49:44] own essentially.
[00:49:46] So what I'm doing is I have to
[00:49:50] two fields that I'm grouping
[00:49:52] by this part of the script
[00:49:55] is taking the value for
[00:49:57] each of those fields and can
[00:50:00] concatenating them together.
[00:50:02] So linked to table two.
[00:50:04] I think an example would have been
[00:50:09] applicant one
[00:50:10] and then check would
[00:50:12] have been true or false. So
[00:50:15] applicant one true is one group
[00:50:18] applicant one false
[00:50:20] is another group applicant
[00:50:22] to true applicant to false, et cetera.
[00:50:26] And then blank null might be a,
[00:50:30] you know, it might not
[00:50:32] be linked to anything.
[00:50:34] So null true, null false.
[00:50:35] Those are all of the groups
[00:50:37] that are probably within this base.
[00:50:40] And I'm essentially building together
[00:50:44] a series of batches.
[00:50:45] So batches started off as an empty array.
[00:50:49] I have put in,
[00:50:51] I'm filling it with a
[00:50:55] series of objects so
[00:50:58] that object will fill
[00:51:00] it in with the group.
[00:51:02] The group again is the composed
[00:51:05] a string of applicant
[00:51:07] one true applicant
[00:51:09] one false etcetera.
[00:51:10] And then the record IDs is going to be
[00:51:17] all of the record IDs
[00:51:19] that are within that group.
[00:51:21] And also I'm supplying it
[00:51:23] with the whole record object as well.
[00:51:25] You probably don't need both.
[00:51:27] I just chose to do both because I could
[00:51:30] The reason I'm doing this
[00:51:35] because it wants to find
[00:51:37] if that batch has already been found yet.
[00:51:39] If that group has already been found yet
[00:51:44] then just add the record I. D.
[00:51:47] And the record object
[00:51:49] to this already existing
[00:51:51] object within that array. If
[00:51:54] this is the first time the script
[00:51:56] has come across a record within that
[00:51:58] particular group,
[00:51:59] it has to create that object first.
[00:52:02] Which is why this if else
[00:52:03] is there
[00:52:04] and then from there
[00:52:07] it's looking at rank. So
[00:52:10] because this is happening
[00:52:12] in like a sequence.
[00:52:13] And because you're pulling off
[00:52:15] of a view instead of a table,
[00:52:17] there is a predictable
[00:52:19] order that records would
[00:52:21] come in on.
[00:52:22] So thinking back on it
[00:52:25] now because this view is already
[00:52:31] is already sorted by points,
[00:52:34] you might not necessarily need
[00:52:37] this portion.
[00:52:39] So you know in retrospect
[00:52:41] this script could be what could be more
[00:52:45] optimized and you'll notice up at the top
[00:52:47] it has that same problem with the
[00:52:49] strike the select records because I didn't
[00:52:52] go back and
[00:52:54] adjust for the for the
[00:52:55] fields that you would need
[00:52:57] right
[00:52:58] But now what it's doing
[00:53:01] is it's now finding the
[00:53:03] relative position that each
[00:53:05] of these
[00:53:07] records that were found
[00:53:09] within each group is
[00:53:10] the relative position against
[00:53:12] however many were found
[00:53:14] for that particular batch,
[00:53:16] that's what all that's happening.
[00:53:19] If there's no other ones,
[00:53:21] then it's rink is just one, right
[00:53:25] and then from there it's pushing
[00:53:28] to the update empty array which is at this
[00:53:31] point in the script hadn't
[00:53:33] been touched yet. It's pushing
[00:53:35] the format that the update records,
[00:53:39] a sync function requires.
[00:53:41] So update records,
[00:53:43] plural, a sync, needs an array
[00:53:47] of objects.
[00:53:49] The object must have the ID.
[00:53:51] So that's what I'm I'm filling it in
[00:53:55] and then the fields
[00:53:57] that need to be updated. So
[00:53:59] rank field name is
[00:54:01] the is where we're out putting,
[00:54:03] this is the first, this is the second,
[00:54:05] this is the third are
[00:54:07] fake auto number. If you will
[00:54:08] rank would be its relative position
[00:54:11] against all of the records that were
[00:54:13] found in that particular group.
[00:54:17] And then the record id
[00:54:19] is coming from whichever
[00:54:20] record we are currently
[00:54:21] iterating through.
[00:54:23] So it's doing this for every
[00:54:25] single record within that view.
[00:54:28] And the reason for that is
[00:54:30] unlike with the previous
[00:54:33] examples that we had
[00:54:34] shown where
[00:54:38] you know, time is a fairly linear thing.
[00:54:41] So auto numbering by week.
[00:54:43] You know, you're not,
[00:54:45] it's not going to suddenly
[00:54:47] be
[00:54:49] a previous week that it was before.
[00:54:52] it's and it's not going to,
[00:54:54] you know, all of the records
[00:54:56] that came before it chronologically.
[00:54:58] You know,
[00:54:59] still came first chronologically.
[00:55:01] So there's no need to go back and re
[00:55:03] number things that had
[00:55:04] already been numbered
[00:55:05] in this case, if you're trying
[00:55:07] to see who scored the most points
[00:55:10] in a
[00:55:11] competition or in a something
[00:55:15] where points are being
[00:55:17] updated continuously.
[00:55:19] Jim might get 10 points
[00:55:21] and he might be ranked number one. But if
[00:55:25] carl
[00:55:26] Carl comes in and he scores 12. Carl
[00:55:31] carl, our good friend Carl,
[00:55:33] if he comes and scores 12,
[00:55:34] well now he's number one,
[00:55:36] meaning you have to change Jim
[00:55:39] Jim I've already forgotten.
[00:55:42] Jim who used to be
[00:55:43] Number one is now number two.
[00:55:45] So you have to go back and
[00:55:47] re number jim
[00:55:48] You have to remember everything.
[00:55:50] So
[00:55:52] that's why this is doing it
[00:55:54] looping through several,
[00:55:56] several records every
[00:55:57] record within that
[00:55:59] chosen view as opposed to
[00:56:04] just doing the one
[00:56:06] that had recently been updated
[00:56:08] right.
[00:56:09] And it's another reason
[00:56:10] why everything is happening
[00:56:12] the script because
[00:56:12] the update record step
[00:56:14] only updates a single record.
[00:56:17] It can't do multiple
[00:56:18] records together.
[00:56:20] Exactly
[00:56:22] yeah. I love this.
[00:56:23] And this would I think
[00:56:24] I saw somebody recently,
[00:56:25] I think it was the facebook group.
[00:56:27] But they were asking about creating a
[00:56:29] leaderboard
[00:56:30] And this would be a really
[00:56:31] good solution for that.
[00:56:32] Because then you could limit
[00:56:33] your like you could just
[00:56:35] share a view
[00:56:35] and say you only want to share your top 10
[00:56:37] or whatever
[00:56:38] when it would be updated automatically.
[00:56:41] Because you can't you can't
[00:56:42] limit how many records are in a view,
[00:56:44] you could sort it but you can't say only
[00:56:45] show me the top 10, but
[00:56:48] But if you had a rank field,
[00:56:49] you would know it stops at 10.
[00:56:51] Yeah. You know. Yeah.
[00:56:54] That's funny.
[00:56:55] I use something similar
[00:56:56] to this too for a kind of a different
[00:56:58] use case. Like
[00:57:00] say you've got like a crm base
[00:57:03] and you've got like a bunch of
[00:57:04] interactions and notes
[00:57:06] all rolling up to a person for example.
[00:57:09] And you want that like
[00:57:11] I often will use like a big roll up
[00:57:13] field to show like
[00:57:14] time stamped
[00:57:16] interactions and all the notes,
[00:57:18] like all on the person's record,
[00:57:20] but that always ends up
[00:57:22] with the most recent being at the bottom.
[00:57:24] And a lot of people are like,
[00:57:26] well I want the most recent at the top
[00:57:28] and I'll use a script to say
[00:57:30] every time you add a new interaction to a
[00:57:32] particular person
[00:57:33] and I'll actually go
[00:57:35] and I'll have a view
[00:57:37] that is like sorted by what by date
[00:57:39] time. And I'll just kind of
[00:57:40] like force that
[00:57:41] to do most of the work for me.
[00:57:43] Like I'll select records,
[00:57:45] they sync and actually
[00:57:46] pass into that sorts that I want.
[00:57:48] So I'll use that and
[00:57:49] sort it when I select it
[00:57:51] and then just
[00:57:52] update the field.
[00:57:54] So grab all the link records
[00:57:55] as assorted value,
[00:57:57] filter them down
[00:57:58] to just that person and then
[00:57:59] update it with like
[00:58:01] literally just grab them all
[00:58:03] and sorting them and putting them back in.
[00:58:04] So it's always
[00:58:05] most recent at the top.
[00:58:10] It's hard out here
[00:58:12] is the moral of this story,
[00:58:15] right. And I don't understand
[00:58:18] like, and I know I've mentioned
[00:58:20] this Airtable before, but like,
[00:58:22] that would be such a
[00:58:23] great feature to have
[00:58:25] to like, just in a set of linked
[00:58:27] records, just be able to choose
[00:58:29] how they're sorted, like Yeah,
[00:58:31] I think it should be there,
[00:58:34] you know, under the hood
[00:58:36] lookups and roll up Well,
[00:58:38] roll ups sometimes
[00:58:41] lookups are arrays, so,
[00:58:43] you know, sorting arrays
[00:58:45] alphabetically
[00:58:47] it's not that hard
[00:58:48] to implement from a coding perspective.
[00:58:50] It would be nice if there
[00:58:52] was just a toggle
[00:58:54] that we could use to sort
[00:58:56] what a look up field
[00:58:58] displays and for linked record
[00:59:01] fields and you could
[00:59:02] use the batch update app but
[00:59:04] that's annoying
[00:59:05] to do every single time
[00:59:07] you wanna resort how linked records the
[00:59:09] actual field itself,
[00:59:11] how those values are sorted.
[00:59:14] It would be nice if that was just a thing
[00:59:17] to toggle that you could turn on,
[00:59:19] it would be awesome.
[00:59:21] Like, it really would. It would,
[00:59:23] I mean just like,
[00:59:24] you could like if they like how
[00:59:26] they did the conditions
[00:59:27] to like toggle for all up
[00:59:29] or count or look up something,
[00:59:31] you know, sort these records
[00:59:33] by X. Like yeah, that'd be great.
[00:59:36] I would want that added.
[00:59:40] but that, you know,
[00:59:42] that's the long and short
[00:59:44] of this somewhat complicated
[00:59:46] script to give
[00:59:47] you even more advanced
[00:59:49] auto numbering system
[00:59:50] that more or less takes into
[00:59:52] account
[00:59:54] how you've grouped records together.
[00:59:56] Again, the real magic is
[00:59:58] coming from this array.
[01:00:01] So it's not
[01:00:04] technically going by how
[01:00:05] you have grouped the records,
[01:00:07] it's just matching how
[01:00:08] you group the records,
[01:00:10] assuming you put in the value,
[01:00:12] the names of the fields
[01:00:14] the same way they appear in your
[01:00:16] group records settings.
[01:00:18] Right.
[01:00:20] Yeah, I love that. That's awesome.
[01:00:22] And that's like that allows
[01:00:24] you to do it
[01:00:24] with not just a linked record,
[01:00:25] like Yes, one of them is
[01:00:27] but you've got the
[01:00:28] check box too which is... Yes.
[01:00:29] Yeah. So yeah I think
[01:00:31] it's fairly agnostic towards field type.
[01:00:35] So attachments would be,
[01:00:37] it couldn't I don't think because every
[01:00:42] I think it comes in the attachment field,
[01:00:44] I think it comes in with a link to the
[01:00:46] file and every file has its own link.
[01:00:48] So that wouldn't help you at all.
[01:00:50] You would have one,
[01:00:51] you would have unique groups for every
[01:00:53] attachment field value.
[01:00:55] So aside from attachments,
[01:00:57] I think technically you could do
[01:01:00] any type of
[01:01:02] field in order to get the
[01:01:05] relative ranking
[01:01:07] for yeah,
[01:01:11] awesome.
[01:01:14] We're at we're at 9.01. Well
[01:01:17] an hour and one minute,
[01:01:18] I don't know what everyone's time zone is.
[01:01:22] Right. No worries.
[01:01:23] Alright, that's awesome.
[01:01:25] Well we, I think we survived
[01:01:27] the Wild West
[01:01:28] without our Fearless leader Dan.
[01:01:29] Yeah. hold on, hold on,
[01:01:32] shoot as soon as you said that,
[01:01:35] how do I stop,
[01:01:41] we did it, screens,
[01:01:44] yeah, curtains closing.
[01:01:47] Oh my God.
[01:01:50] Yeah, so like I said,
[01:01:51] I will find a place to put that script.
[01:01:54] I think it's on the forums already.
[01:01:57] if not I'll,
[01:01:59] I'll put it somewhere so so everyone can
[01:02:01] can see it's madness
[01:02:03] and all its glory and I'll fix the select
[01:02:06] records things so it's compatible with
[01:02:10] modern scripting if you will.
[01:02:13] Excellent,
[01:02:15] Awesome. Thank you Kamille.
[01:02:16] That was great.
[01:02:18] Yeah. Auto numbers.
[01:02:21] I love it. There's so many things
[01:02:22] you can do,
[01:02:23] there's so many things you can do
[01:02:24] if you absolutely force it
[01:02:26] using an automation.
[01:02:28] Yeah.
[01:02:30] Oh my goodness. Perfect. Alright.
[01:02:34] Well I believe that does it
[01:02:37] for us this episode
[01:02:39] tune in next week for a
[01:02:41] much more structured episode
[01:02:45] that talks less about auto
[01:02:48] numbers. Probably
[01:02:50] probably a little bit less,
[01:02:52] but still useful nonetheless.
[01:02:54] Right.
[01:02:56] Oh, thank you everybody