[00:00:02]
We're back. Well welcome everybody. To the final episode of season, 24 of built on are Dan. Can't make it with us here today. We've done a little Switcheroo. He had to do an episode by himself but now it's me and Ollie.
[00:00:18]
Excellent. As we’re glad to be here. And Dan is
[00:00:23]
missing for good reasons and solid things. So that’s exciting. And congratulations to Dan’s family as well.
[00:00:33]
Very excited.
[00:00:34]
We were just discussing before we went live that there’s not a whole lot in the way of news. Nothing new on the what’s new page?
[00:00:45]
And there is an event in Boston this week on Thursday the 28th as part of Boston’s Tech week. I will be there hope to see as many of you there as possible.
[00:00:58]
And super excited for that.
[00:01:03]
And that’s
[00:01:05]
Really, all I got in the way, a new stuff.
[00:01:08]
Yeah, I think they typically post new announcements and features towards the end of the month, which is when we’re close, but not quite, I’m sure there’s probably some
[00:01:21]
stealth enhancement that they’ve snuck in there that we just haven’t
[00:01:25]
discovered yet. But when we returned sometime in July, we’ll probably talk about all of the new things that have been added in both May and June.
[00:01:36]
Exactly.
[00:01:38]
Love it.
[00:01:41]
All right. Well today I’ve got a bunch of hacks tips and tricks lined up around the
[00:01:51]
Sort function in the roll-up field and look up Fields. I have talked about this before in, I think a couple seasons ago and it was first released but I’ve said, it’s kind of added on to my repertoire as it were with or so to speak with that feature. And I use it all the time. For like things that you wouldn’t even think of like, when you hear sort. Like that’s usually where your mind goes, is just sorting things. But what it does is actually opens up a whole other world of like capabilities. So, I’ve got a bunch of little examples to show here on ways. You can use that feature to your advantage for tons of different use cases and Kamille please, feel free to turn in along the way as well. Sure.
[00:02:40]
I’ve been able to make use of the sort feature as well and it really is one of those things of it. Sounded like, oh, what a nice little enhancement but it really there’s there’s some hidden things that you can do with it that with some careful planning can really kind of unlock some more efficient processes that you can do.
[00:03:05]
Straight and airtable instead of having to rely on a kind of third-party automation Helper and things like that.
[00:03:12]
Absolutely. Yeah much of this was somewhat possible to do before the future but as Kamille said, would require like an automation or a script or both.
[00:03:24]
And it can easily get messy things. If the script fail to any point, then your numbers are all out of wax. So there’s just a lot to it, but this feature really,
[00:03:36]
Solidified, a lot of the capabilities without having to use automation. So I’m super excited about that.
[00:03:45]
And I’m happy to share my screen.
[00:03:53]
it’s Jenna, or
[00:03:55]
lovely post behind the scenes is able to
[00:04:00]
Put that up. That would be amazing.
[00:04:04]
And I can go to tune.
[00:04:09]
Let’s see.
[00:04:13]
Here. Thank you. Jenna amazing. All right. So
[00:04:19]
I’ve got in here.
[00:04:22]
To different.
[00:04:25]
Examples that we’re going to start with or two different sets of data. This is silly just blank table in between where I’m like separating them out. I didn’t two bases but figured we’d keep it all and in-house and
[00:04:42]
so,
[00:04:43]
The very first thing I’m going to start with is a simple example of ranking your records.
[00:04:49]
And this is something I’ve talked about before.
[00:04:52]
But it’s worth repeating because I find it very, very useful and I use it a lot. So,
[00:05:01]
In this table, I’ve got a bunch of fake people and they are linked with a self linking field to the contact that referred them. So you’ll see was referred by Tressa Tyson.
[00:05:18]
What a name jobina. Jobina goodbye. Anna laurents.
[00:05:25]
Funny. Okay.
[00:05:28]
So, each one of those has may or may not have been referred by a contact and then we have the reciprocal here.
[00:05:35]
that I’m able to count how many referrals each of these contacts has brought in
[00:05:42]
So, I have these sorted.
[00:05:44]
you know, by greatest to least what and
[00:05:51]
Sometimes that might be enough but what if I want to do a couple more things from there? Like if I want to potentially share a leaderboard, showing always only the top 10 records.
[00:06:05]
In order to do that, I would need to have the numbers one through 10 in another field somewhere. And that’s what I was referring to with needing an automation or a script previously, to be able to do that you would need to
[00:06:18]
At a regular Cadence select all of these records.
[00:06:24]
Sort them by this number. And then determine, you know, number them, one through, however, many records you have and update a field using a script.
[00:06:35]
And that can get really out of whack. Especially if you’re going off of, like, one record updated as your trigger, if you are going to use this as an automation, I would recommend doing it as a Cron job. Just
[00:06:47]
so that it’s regularly updated, not immediately because that could have weird side effects. If people are updating things at the same time, like you might say,
[00:06:58]
Link this person here that creates a makes this a five and then you do this and do it. The same thing down here to make that a four. Well now this automation is already running to update that number and then this one’s going to get the wrong number. It’s all over the place. So yeah, yeah, that could get so so messy.
[00:07:19]
But now and enter our new feature, so we won’t have to use any automation. It’ll just happen right in front of our eyes, which is amazing, but I’m gonna go over just some ways of
[00:07:34]
getting this, the steps that you need to put in place first and they can get a little confusing but I’m going to go slow.
[00:07:41]
so,
[00:07:42]
First, I’m going to do it with no ties.
[00:07:48]
so, therefore
[00:07:49]
Because I have two tens here. One of these will be number one and one will be number two, that’s what I mean. When I’m saying No Ties, then I’ll show another example where these would both be number one and then this would become number three,
[00:08:04]
and,
[00:08:06]
So we’ll go over how to do both of those first.
[00:08:10]
So the first thing you would need is a record ID or some other unique identifier, for each record, I would recommend using the record ID just because you can guarantee it’s going to be unique. And it’s
[00:08:22]
That would be there.
[00:08:25]
So once I have that that’s of course just this formula here.
[00:08:29]
Very simple.
[00:08:31]
Then what I need is, I need all of these records to be linked to
[00:08:38]
another record.
[00:08:40]
Because I need to be able to roll up this list of record IDs.
[00:08:46]
Somewhere.
[00:08:49]
the caveat I would add with this is if you have a lot of data,
[00:08:56]
This can get a little bit messy or rather expensive interns of performance. I only have 100 records on this table. I’ve done this with thousands of Records but once you get into like you know the tens of thousands of Records Zone
[00:09:12]
he might want to consider like,
[00:09:16]
Maybe having.
[00:09:18]
them broken up into
[00:09:21]
time ranges something to make it so that you’re not linking, all 50,000 records to one record and
[00:09:29]
There’s I could say a lot more. We could do a whole episode on just that technique, but let’s assume that we we know we’re never going to go over.
[00:09:39]
10,000 records in this space and I’m just going to link them all to one.
[00:09:43]
Record which I usually just call my summary record or utility record. And I’m going to just copy the same value all the way down.
[00:09:55]
That column.
[00:09:58]
And then, I’m going to switch this to link to a new table.
[00:10:03]
Also called summary.
[00:10:05]
And I don’t need them to be linked to multiple records.
[00:10:10]
Just one record on my summary table.
[00:10:16]
Now, I have this table here.
[00:10:19]
I’ve got all my contacts linked to it.
[00:10:22]
And what I would then recommend doing actually, just so we cover all of our bases here on intended. Is I would set up an automation to make sure that all of your contacts always stay linked to that record.
[00:10:37]
And so that is just going to be linked, contacts to summary.
[00:10:45]
And I always use one record matches conditions for this 99% of the time. I’m using this trigger and but that’s going to be on the context table. When
[00:10:55]
Summary.
[00:10:57]
Is empty.
[00:11:03]
Then I want to update that record.
[00:11:08]
On the contacts table, the same record that triggered the automation.
[00:11:14]
And I’m just gonna fill in the field summary.
[00:11:17]
With the word summary.
[00:11:21]
Super simple kind of silly but very powerful. And if I go now and wipe out any of these, it’ll get put right back if I add a new one,
[00:11:35]
It’ll immediately get linked to that summary table as well. Never have to worry about something, not being included.
[00:11:43]
Extra record now though.
[00:11:46]
All right. One thing you could also do is, you know, wait to include them until they actually have a referral. If you’re starting to hit that, you know, if you’ve got many many records and you want to limit the number of links you have, you could do that as well.
[00:12:01]
so,
[00:12:03]
From there on my summary table.
[00:12:06]
Essentially, what I need to do is I want to roll up
[00:12:09]
The record IDs of all of my contacts.
[00:12:13]
so, I’m going to add
[00:12:15]
a roll up field.
[00:12:18]
Pointed out my contacts table through this link here.
[00:12:22]
And I’m going to grab the record ID.
[00:12:26]
And I’m just going to leave that as my function here array unique values.
[00:12:32]
And this will be all contact record IDs.
[00:12:36]
but,
[00:12:37]
The most important part.
[00:12:39]
Is to scroll down. Which you wouldn’t really notice, like, roll up Fields. They’re my favorite field, but the
[00:12:46]
Configuration screen is just too squished and so you have to scroll down. It’s the last option before you actually write the formula. And you want to say, I want to sort these records before summarizing
[00:13:01]
And you want to pick the field you want to sort by so in my scenario that’s going to be my number of referrals number referrals field.
[00:13:11]
And I want to sort that.
[00:13:12]
Greatest to least.
[00:13:15]
Most least and nine to one.
[00:13:19]
And I’m gonna hit create.
[00:13:22]
So now theoretically, this first record ID should be one of the ones that is has number 10. So 3 and 3 and ieo.
[00:13:33]
We have.
[00:13:35]
Their ieo. Three and three. That’s these two so on and so forth. If you wanted to have a tiebreaker field, you could do that as well. Or you could be like, just go alphabetically.
[00:13:47]
So, in that scenario you could do.
[00:13:52]
Just add another sort and that would become your tiebreaker.
[00:13:56]
so, if you sorted by name then,
[00:14:00]
So now that switched it. So now three and three
[00:14:03]
his first and that’s because Jay comes before you.
[00:14:08]
and,
[00:14:11]
that’s,
[00:14:12]
step one, essentially now
[00:14:15]
The magic happens.
[00:14:18]
so what I’m going to do,
[00:14:22]
Is add a second roll up field.
[00:14:25]
I’m going to do this in a few different steps and then merge it all into one just so you can understand.
[00:14:31]
The.
[00:14:33]
The bits and pieces, of how this function works.
[00:14:37]
So, what I need is I’m going to do array join.
[00:14:45]
Values.
[00:14:49]
I think I have, yeah.
[00:14:52]
This will be all.
[00:14:55]
Right contact record IDs.
[00:15:02]
So essentially now on every single record I have the same value, this is another, like I love this trick using the summary table to get Global variables across the table. So that’s there’s just so many things you can do with that utility record trick. But
[00:15:20]
now that I have all of that list here,
[00:15:23]
what I want to do is find
[00:15:27]
where,
[00:15:28]
The record ID is.
[00:15:31]
In the list, but pertains to each one of these.
[00:15:36]
Um, and then figure out how many commas come before that. So, come from the
[00:15:44]
Very first record here or rather.
[00:15:47]
Either one of those rather.
[00:15:49]
And this would be.
[00:15:52]
The first record, there’s zero commas before it.
[00:15:56]
And in my function I’m going to add one to it. So that would become number one. If I find this record ID, there’s one comma before it that would be great too so on and so forth. So how do we go about doing that?
[00:16:12]
so, first thing I’m going to do is
[00:16:17]
Say.
[00:16:19]
I want to find.
[00:16:24]
Record ID.
[00:16:26]
Of the contact within.
[00:16:29]
All right. Contact record IDs.
[00:16:35]
so, let’s
[00:16:36]
Index of record ID.
[00:16:43]
So already, I’ve got
[00:16:46]
Kind of a value that I could go off of to sort by. And if I really wanted to
[00:16:53]
I could kind of cheat and say, I know that all
[00:16:58]
Record IDs are 17 characters long. So I could take that into account to do the math here and like, you know, be like, okay,
[00:17:07]
Get them modulus, divided by 17, figure out what all these numbers are that might be enough to go off of. However.
[00:17:16]
We don’t know, the record IDs are always going to be 17 characters. So what I would recommend is doing it based on the separators in between, rather than the actual count of characters and that way, you know, it’s always going to be
[00:17:30]
correct.
[00:17:32]
So, we know the index of the record ID.
[00:17:36]
That’s for here. That’s starts with this. This is character 19 that are right there.
[00:17:41]
So what I want to do is basically grab everything before it, so I’m going to use the left function and say, give me all of this.
[00:17:51]
And then count how many commas are within it.
[00:17:55]
So if I do all right.
[00:18:01]
USA left.
[00:18:04]
All right, contact record ideas.
[00:18:08]
Index.
[00:18:15]
Yeah, that’s what I want.
[00:18:19]
Then or I would actually say minus 1, I guess.
[00:18:24]
Because it doesn’t necessarily matter.
[00:18:29]
I think I have that in my other function.
[00:18:32]
All right.
[00:18:35]
Out of that.
[00:18:37]
so, this is the
[00:18:40]
I don’t know what to name this. I’m ultimately I would put all of this in one formula but I’m trying to break it out into steps just to make it, we’ll just be like trimmed, right to act.
[00:18:54]
All right. So now in order to get my number, I just need to count the number of commas. That is in that are in each one of these fields here.
[00:19:04]
And in order to do that, I still
[00:19:08]
Have absolutely no idea how this works. Math wise it blows my mind. Somebody dropped this in the Forum years and years ago I saw W van Hall, share it. What it was not he who wrote it but he like shared the formula and was like some guy dropped this on the forms years ago and I use it all the time and
[00:19:32]
so do I. So which means it’s been on the forms for at least like seven years.
[00:19:39]
At least I would say even longer. I would say probably 12 like a long time and
[00:19:46]
because I think I grabbed it and like,
[00:19:49]
2019.
[00:19:51]
Probably even longer before that, just crazy. And either way, the
[00:19:57]
What this does. And I always have to copy and paste it in here. I saved this function and like a separate repository, so that I could reuse it because I never remember how to write it myself. Um, but essentially it’s saying if we were to just leave this alone, but this is saying is how many times is the word sweatshirts appear in the field items and we’re gonna change these variables out for our real ones. So in this scenario, I have that as my items field.
[00:20:28]
It just copy that. Replace
[00:20:32]
The word items.
[00:20:35]
With that and then replace sweatshirts with a comma.
[00:20:44]
and then, I want to
[00:20:49]
Add.
[00:20:52]
One.
[00:21:02]
Oh well.
[00:21:04]
yeah, I think here your other Clause because this is if there is items
[00:21:11]
then your sum and then you’re you’re other your if false could just be one
[00:21:19]
Right.
[00:21:20]
Let’s see. Oh, that’s true. Yeah.
[00:21:26]
but then not necessarily though because if this, that would make all of my
[00:21:34]
If something errored in this was blank for another reason I wouldn’t want them to get fair enough one. I think this would do it instead, if I just say where the index is no comma.
[00:21:50]
There we go. Yep there. Okay. So this is my rank.
[00:21:58]
and if I sort by this,
[00:22:03]
the only thing that changed there was that these two flipped and
[00:22:08]
but,
[00:22:10]
just go over here to
[00:22:17]
there.
[00:22:18]
So now, let’s give somebody else. Let’s say that Cheryl was referred by Ulysses.
[00:22:28]
so, if we do this,
[00:22:31]
Now, you’ll see these becomes number one.
[00:22:34]
and,
[00:22:35]
Jovina to become number two.
[00:22:39]
So this will always change, dynamically.
[00:22:43]
based on your count, that you’re sorting off of
[00:22:46]
so,
[00:22:47]
Super, super powerful.
[00:22:49]
Use this all the time for, not just for ranking but like
[00:22:54]
numbering things within groups and
[00:22:59]
which can be useful as well. So if you’ve got like,
[00:23:03]
I don’t know.
[00:23:06]
If you just like instead of using the Auto number for any number of reasons that the Auto number would just keep going no matter what. Whereas if you use this technique, if I split these into different groups, it would start over.
[00:23:19]
With each group which yeah, super powerful.
[00:23:23]
So I’ve done some things. Similar for when I have invoices linked to a client, you want to number each invoice for every client. Starting with one. If I just starting working with a client, I don’t want their invoice to say and voice five. It’s the first one for that person. So, having a workflow like this, which involves linking the records together allows you to have your own bespoke Auto number, so to speak. Exactly. Yep. That’s and I’ve done exactly that same use case as well.
[00:23:55]
and now, if we were to
[00:24:00]
Let me just grab my finished function here and I’m just going to copy and paste.
[00:24:08]
Into a big.
[00:24:11]
Roll up field.
[00:24:14]
and essentially what that will do is give us our same value here, but all in one function,
[00:24:25]
So here.
[00:24:27]
We have Frank.
[00:24:29]
No ties.
[00:24:31]
And what I’m going to do is it’s still pointed at the same field, not changing that.
[00:24:37]
and,
[00:24:39]
Essentially, this I can push. I could make this tall.
[00:24:44]
once per episode, I asked are table to make Minor Adjustments to their UI and this is that for this episode
[00:24:54]
It’s wild.
[00:24:58]
So this function and actually I’m just gonna hit save and then put it.
[00:25:04]
Over here.
[00:25:09]
I wish I could.
[00:25:12]
Either way. So essentially, all this does is combine all those steps, we just went through into one big long formula in, on this a little bit. So we’ve basically just got all right. If I have values to roll up, meaning if there’s anything in that summary big list, that I’m gonna sum one plus
[00:25:37]
All this fun stuff, which this is that crazy formula that we were just talking about counting the number of commas, just split out onto multiple lines. So it’s a little easier to read.
[00:25:47]
And then this is where.
[00:25:51]
We are finding that record ID in the list and subtracting one. So that’s one big function that you can just copy and paste.
[00:25:59]
Instead of having to go through all of these separate, Little Steps here, I could do it all in my one field and that should have exactly the same values.
[00:26:08]
As this.
[00:26:10]
Super fun.
[00:26:13]
But let’s say we want we want to have ties. Because depending on your UK you wouldn’t want you might want two people with 10 to both be number one.
[00:26:24]
And let’s see what that’ll look like, I’m going to get rid of that. So that
[00:26:28]
Those stay.
[00:26:30]
Both of the same value.
[00:26:33]
All right, so Kamille actually helped me with this first time I went over, you did you had the idea at the end of one of our episodes where I went over this at the first time it was released
[00:26:47]
You were like oh well you could use the value instead of the record, right? Yeah and
[00:26:54]
I was like,
[00:26:56]
Light bulb, it was awesome. So thank you for that. And
[00:27:00]
so what we could do is instead of trying to find the record ID in that big Roll-Up
[00:27:08]
We can roll up the number. The actual numbers that we’re
[00:27:13]
ranking by
[00:27:14]
And then find the value in the list. So that means that both of these values or both of these contacts. Rather would be looking for the number 10 in the list. And that number 10 is going to be number one. So, they would both become number one.
[00:27:30]
Right.
[00:27:32]
Super cool. One, big piece to mention let’s just say I do it.
[00:27:39]
Let’s just do it and I’ll show you what the problem is. I think I’ve, I think I’ve detected with the problem when using numbers specifically,
[00:27:48]
I think you’re probably well ahead. Yes, absolutely. Let’s see. So all of this, can stay exactly the same, but what I need to do is point this instead to my referrals field.
[00:28:02]
Asterisks here. This is not how I’m going to leave it. I’m going to show you what will go wrong if I did this. So this would be all referral.
[00:28:13]
Why can’t I spell referral now?
[00:28:19]
So, we now have
[00:28:21]
The number of counts.
[00:28:24]
And it is important here to use array unique. If you use array join and had, you know, 10 10 6, 5, 4 4, 4, 4, 4 4, like, all that, that would throw off your numbers a lot. So
[00:28:38]
definitely use a ray unique to make sure you only have the unique values to compare it.
[00:28:44]
Let’s say, I just go through the motions and I take that field.
[00:28:50]
I’m just gonna replace.
[00:28:53]
Good luck. Yeah. Right fighting the UI to do it.
[00:28:58]
But Zoom back out, it’ll be better. Like there we go. So I’m switching the field. I’m rolling up to be my all referral counts and of the record IDs.
[00:29:08]
And I’m not finding the record ID anymore. Here, I’m finding the number referrals.
[00:29:19]
Here while you’re doing, this is a good time to remind the you everyone watching that. When you’re using the formula editor in a rollup field, you can use all regular formula functions and reference other fields and whatever table you’re currently on. But the autocomplete for field names isn’t there. So just type confidently and you’ll get it, it will still turn purple when you get the full name of it correctly. Thank you. Yes, exactly. I find that so frustrating when I’m like halfway done writing the formula and then I’m like oh my gosh is it number referrals or number of referrals? Like yeah, what? Actually this is kind of knew where now you can open this and look at it while you’re riding your formula. It won’t like
[00:30:06]
Freak out. Yeah.
[00:30:08]
So that’s really helpful.
[00:30:11]
And so basically, all I’ve done here is I’ve just replaced
[00:30:15]
the record ID with number referrals and I’m pointing this at that. Other field here, this is going to not be right.
[00:30:22]
And then this will be called with ties.
[00:30:29]
So looks all right at first like, oh okay, yeah, one one. Great
[00:30:34]
Two.
[00:30:35]
Yeah, that’s actually still not what we would do. I don’t think or would it be?
[00:30:41]
either way, this is all going to be incorrect because
[00:30:46]
Or well, actually with these values. I think it might be fine well until you get to one is the problem. So
[00:30:54]
yeah, we’ve we’ve detected the issue. And that what we’re doing is, we’re finding where in the unique list of values. Does this value first appear? And when Airtable is doing this, it’s treating it all, as that list is a one, big text object. So one really does appear first as part of the whole number of 10, but it’s not quite smart enough to tell. Oh, this is a different value than one. The single digit integers post to the full body of 10. This is fair, which I’m sure is about to go over.
[00:31:34]
Fixable.
[00:31:37]
Shore, probably a million ways of doing.
[00:31:39]
Doing this probably some more elegant than what I’m about to show. But what I have been doing is something like this where I’d be like number referrals, added
[00:31:52]
and I literally just say,
[00:31:55]
Asterisks.
[00:31:58]
Number referrals.
[00:32:01]
And asterisks.
[00:32:06]
So now I have asterisks to asterisks asterisks one asterisks, but the 10 is Asterix 10 asterisks. So that’s important. Because if I’m going to be searching for this value, instead of just one, I’m not gonna match on
[00:32:27]
Asterix 10 asterisks and I will only match on the one that I should, so super useful there. But what I would do is flip this to be.
[00:32:40]
That new field.
[00:32:42]
Padded.
[00:32:44]
Leave everything else, the same.
[00:32:47]
so now I have
[00:32:49]
this list.
[00:32:52]
And on my contacts, I’m just gonna come here and say rank with ties.
[00:32:58]
and flip this, with
[00:33:02]
I don’t need to do that. I can flip this with number referrals.
[00:33:09]
kind of,
[00:33:19]
and then,
[00:33:21]
All of my ones.
[00:33:26]
Should be correctly ranked at the end? Yeah.
[00:33:31]
so, that is
[00:33:34]
Pretty simple.
[00:33:37]
Thing you can do to just switch that out. Um,
[00:33:42]
Switch it with a different field and now you have ties allowed, which is very useful. Yeah. And the particular way that you’re doing this all, so makes it so that this works just fine with having a variable length of each value. Like it’s, you know, it’s handy that record, IDs are always 17 characters, you know? Today just possible that in the future, maybe they become longer when there’s too many records for them. They run out of unique combinations of record IDs. But with numbers like,
[00:34:13]
Someone might score one point and someone might score 100. So, having to deal with the difference between one and three characters, you don’t have to do that with this implementation again because you’re searching in that very long, roll up for how many commas are there, which tells you how many values there are before in the list?
[00:34:33]
Exactly.
[00:34:34]
Yeah. And theoretically could be any any separator.
[00:34:38]
As any separate any unique value. That’s all you need to or not necessarily unique value. If you’re if ties are okay,
[00:34:46]
right? And you would just make sure that whatever separator you choose, can’t be included in whatever value so record, IDs are all, you know,
[00:34:56]
Letter characters and numbers. It will never have a comma in the middle of a record ID. So, commas are safe, but you could have all. So chosen a pipe character or a semicolon, you know, exactly.
[00:35:10]
Yep. Very very good thing to note there.
[00:35:14]
Love it.
[00:35:16]
All right.
[00:35:18]
so, that’s
[00:35:19]
Ranking. And I’m going to skip to the next example.
[00:35:24]
Which I’ve kind of just started.
[00:35:29]
Experimenting with but I’ve been using it a lot and it’s very useful. So the skipping manual sort because we’re going to come back to that after the next record concept.
[00:35:43]
Which if you’re a long time user or a very techie person, you’re hackles might go up when you hear the words. Next record, when in talking about airtable or any database in general, they’re really isn’t a concept of the next record because it could be anything, you can sort, you can filter. Like, you don’t know how somebody has their things sorted Etc.
[00:36:09]
but if you have your data structured in such a way so that yes things are numbered, you know that like,
[00:36:18]
For example, I have here days and then like, let’s say we’re a landscaping company going out to different stops on each day, you have those stops numbered, you know, this is stop one. Stop to stop three. So theoretically
[00:36:35]
You can trust that stop to on the same day. Should be the next record in quotes air quotes.
[00:36:44]
and,
[00:36:45]
So we’re going to go about getting that record ID, the record ID of the next record.
[00:36:51]
On the same line as the one before it.
[00:36:54]
And why is that powerful?
[00:36:56]
Lots of reasons, I’m sure many of which I haven’t even thought of yet, and yeah, yeah, absolutely. I’m like, my gears just start turning like, oh, what else could I do with that? I use what you’re about to go through for approval, workflows where, if five people need to approve one thing. But it’s sequential, you got to go to person one first and then it’s important. The steps go in the right order. So the next approval makes it
[00:37:28]
Really easy for an update record step to say okay well take this record idea of the next record and paste it into the linked record field or whatever, so that the next person can be go through the approval process. So I too use this feature for a sequential items and getting the next thing in that sequence, right? It’s super, super useful and like and I love that. Yeah, approval workflows. That’s a great use case. And another one for this actually came up with this while working with a landscaping client and we have a softer portal for them, and so each of these stops has their own software page. But when they’re on stop one, they want to be able to quickly, just click to go to the next stop. So right thing they can just say, okay, next stop. So I don’t even need any automations with that. I just have, I could have a roll up field. That’s like, here’s the record idea of the next stop. And that becomes
[00:38:28]
My link for user, which is yeah, it’s great.
[00:38:33]
So how do we do it? Let’s find out what I want to do here is on the dais table and I got a little over zealous. I don’t need any of these. Keep it simple.
[00:38:47]
I am going to roll up similarly to before I want to roll up.
[00:38:55]
Oh wait. No, yes I do. Yes, I want to roll up. All the record IDs. That’s all I’m doing here.
[00:39:00]
And I’m going to say oh, stop record IDs.
[00:39:05]
On this, you might.
[00:39:07]
Use like conditional logic like depending on your use case.
[00:39:13]
For example, this use case they have stops that might be canceled sometimes. So I might also put this into here and be like, don’t include the canceled ones, just, you know, keep all of that in mind as you’re doing this and
[00:39:25]
You know, follow through your logic appropriately.
[00:39:29]
But we all know I’m missing a step need to sort this. We want to sort. Bye, the stop number and this time I’m going to leave it one to nine. So
[00:39:40]
Least to greatest.
[00:39:43]
and,
[00:39:44]
Then I’m going to go back to my stops table.
[00:39:51]
and,
[00:39:53]
I want to say roll up.
[00:39:57]
And this one’s a much, much more simple.
[00:40:00]
I’m doing all, stop record, IDs and here, I just want to say
[00:40:07]
array.
[00:40:09]
Join a race slice.
[00:40:12]
Array unique values.
[00:40:16]
And I want to slice.
[00:40:18]
This or I guess I could just do values, right? Don’t even need a rainy.
[00:40:24]
Now I get so confused about a race slice. I have confession time. I don’t get it either. So for lookups much simpler because it’s just sort of a toggle similar to the sort. Like you I erase life. I feel like I should get it and I’ve tried to use it before and it’s simply would not work in the way that I thought it would. Do you slicing all the time in JavaScript.
[00:40:53]
For some reason, the formula eludes me.
[00:40:56]
It is wild like it’s it’s different. I think it’s first of all it’s starts with index one which yeah, throws me off and then the slice basically what it’s trying to do is says the first value is index one.
[00:41:13]
But it doesn’t say.
[00:41:15]
Explicitly like you know how the mid so the mid function, you start with an index and then you say go, yeah and characters from there.
[00:41:26]
but this I think is different, it’s actually like
[00:41:30]
it’s not 10 pieces from where you start. It’s
[00:41:35]
actually, the numbers like the index value that you need to say,
[00:41:40]
Either way, let’s let’s just see what this gives me. That is what I want. Okay, so Wow. First try, which never happens on. I think I’m getting, I’m starting to get it, but not quite, but basically what this is is saying,
[00:41:55]
if I put this in a field,
[00:41:57]
Just so we can simplify it and make it look.
[00:42:03]
Hot number.
[00:42:05]
Wild. I can create that all while having this open still.
[00:42:10]
Either way. So next up number.
[00:42:14]
basically, what I want to do is
[00:42:17]
take this.
[00:42:20]
Just do erase so we can see it.
[00:42:23]
The whole thing.
[00:42:28]
So I want to find, I already know, I’m on stop one on this record, which is this record? I
[00:42:35]
Want to find stop to his record ID. So I want to find the second thing in the list.
[00:42:41]
so, that should
[00:42:44]
for a race slice, what it what it needs to be filled in with is
[00:42:49]
the index of the item you’re looking for. So in this case, it’s two and then where you end is also too
[00:42:59]
Which. Oh, okay.
[00:43:01]
The part that’s really confusing. It’s like start and end at 2. Okay, that, that explains my previous.
[00:43:09]
Issues with the the formula. So that’s helpful and just calling out why. This is so different from your first example, is because in your first example, we were doing a ray join which took the array and treated as a string. So the comma, which was your chosen separator was part of the whole list. But because we’re only doing array unique right now. It is still an array, meaning the commas. Not actually there. That is a visual representation for the people looking at the list. Exactly. Like, this is still an array under the hood, which is why we use a race Slice on it and if I had used a ray join on my initial roll up to the days table,
[00:43:52]
This would not no longer work and then you’d have to use left or mid or something like that. Correct.
[00:43:59]
and so, basically, all I need to do here is
[00:44:03]
I could, you know, replace this now with just next
[00:44:07]
Up number.
[00:44:09]
Of having to add one every time.
[00:44:15]
So if I were writing this like in JavaScript, this is the next stop record.
[00:44:21]
ID.
[00:44:23]
What really throws me off is usually I would say, you know, slice next. Stop number and then one would be right. I only want one, yes, but that doesn’t work, that actually gives me nothing because I’m starting further in the list than one, and I can’t so confusing. Yeah. Okay. So, all right, well, I’ve learned something. I’ve I now know what I’ve done wrong with The elusive erase life.
[00:44:54]
Love it. Glad
[00:44:56]
We could learn it together too. I’m feeling a little bit better with it.
[00:45:01]
but that I’ve found to be extremely useful, it also has come in handy and I don’t necessarily have a good example for this, but
[00:45:10]
So, um, same client. They have time entries and they might have more than once staff clocked in the same time.
[00:45:21]
Which is.
[00:45:23]
Theoretically, you could represent this with a junction table and force it to be.
[00:45:29]
One staff member per time entry? Yeah, but basically, We’ve Ended up with a many-to-many relationship.
[00:45:36]
so that means that we weren’t able to get
[00:45:40]
the most recent time, entry time on the staff table record, because
[00:45:47]
because reasons many to many relationships, very confusing, but
[00:45:54]
with this new feature, I think it actually kind of does work because
[00:45:58]
From my staff table I can do the same thing and just be like give me the most recent give me the first item like sort by time and then give me only the first one in the list for that staff member what they’re linked, right?
[00:46:12]
So that actually it works to get the most recent value, which is an example, I should have put in here.
[00:46:20]
As well, but it’s literally the same example. We just did just using using a different, a different field rather than the record ID. So super, super useful there.
[00:46:33]
Now.
[00:46:35]
The with this, as Kamille said, another step to make this even more powerful would be if you linked these together. So if I had stops,
[00:46:46]
Next stop.
[00:46:48]
And then I could even filter it by a condition to be like only where the record I’d imagine this. But I don’t need to do that in this scenario. Essentially now an automation could take this and, you know, make sure that this field always matches this. Yeah.
[00:47:04]
And then I can now look up any of the values or roll them up from the other stops to here. So you can imagine that just gets exponentially more powerful.
[00:47:16]
Keeping with the same like time, tracking example, if I want to see the time between these stops like the travel time, from stop one to stop, too, I can then figure out when the time on this one ends the time on this one starts and I can get the start time from this one on this record, figure out the difference. All sorts of things I could rant forever about all the ideas I have there. Yeah. For my approval process example, even the third record, which has no next ID because it’s the last in the list that’s all. So important. We had an automation that says if the last approver approved their item and there is no, Next Step record ID then, you know, you can mark the whole application or process or whatever it is complete because it’s gone through all of its approvals. Exactly. And so that you would want to know
[00:48:15]
you know, there’s a few ways to do this, you can count you could use the max
[00:48:19]
I mean, I the reason I’m going with Max here is
[00:48:25]
just in case.
[00:48:28]
Well, I guess, I don’t know if you deleted, stop to then one and three would be your stock numbers unless you corrected it but count would tell you too. So the number two does not appear in either the two remaining records, so max would be yeah, yeah, exactly. That’s why I’m going with Max here. So max number of stops. And then here, you could be like
[00:48:55]
What I would do is I’d add from the day.
[00:49:00]
We’ll stop number should have done. That should be number of stops, but either way, and I would be like if and
[00:49:16]
like only care if I have both things because
[00:49:20]
If I didn’t do this, if and part.
[00:49:23]
It would end up saying true, if both were empty, and I wouldn’t want that. And then I just say, like if stop number equals,
[00:49:34]
True.
[00:49:39]
Um, love this option. Now use this all the time. I as a general rule do not ever use actual checkbox Fields, but I’ve been using the formulated checkbox checkbox checkbox Fields, can’t skip today, all the time.
[00:49:54]
Last up.
[00:49:56]
So now I always know that that’s the last stop. If I add another
[00:50:01]
and number it for,
[00:50:03]
That becomes my last stop.
[00:50:07]
and then you could use that in your function to be like, okay, that’s where my
[00:50:11]
Approval process ends. So, if this is jacked,
[00:50:15]
Email the last step or whatever, whatever your problem is, yeah.
[00:50:21]
Super useful.
[00:50:23]
and,
[00:50:25]
Absolute.
[00:50:26]
now, I’m going to go jump into
[00:50:30]
One last.
[00:50:32]
Little Fun hack, which I’m going to use the same table to do.
[00:50:40]
And we’ll do.
[00:50:46]
Here, I’m going to go into an interface.
[00:50:51]
So manual sort ranking. This is just
[00:50:55]
an idea that popped into my head actually at their table last year in Chicago, and I built it and
[00:51:04]
I haven’t found a use case for it yet but I’m sure I will.
[00:51:09]
um,
[00:51:10]
so we’ll just do whatever I’m going to interface.
[00:51:15]
oh, I think we’re only seeing
[00:51:18]
One table. Yeah, your interfaces. Thank you. That’s weird. I thought I shared window. Okay, here, so gives you this now. Yes.
[00:51:28]
Excellent, perfect. Okay, so I want to not do contacts. I wanted to use stops
[00:51:43]
And let’s group these.
[00:51:50]
and,
[00:51:52]
Sort. Bye. I’m gonna add a manual sort field.
[00:51:57]
And for reasons unknown, I believe this is the only way you can add this field type. You cannot add it in. I think literally any other way even by
[00:52:09]
The API. I feel like you can’t do it.
[00:52:12]
Correct.
[00:52:13]
It is correct. So it’s quite a confusing field but basically What it lets us do it is it actually does add a field here.
[00:52:23]
Called manual sort. And when I allow for editing in line,
[00:52:29]
I can.
[00:52:32]
I can’t I move things around here, okay.
[00:52:36]
I’m going to ignore the stop number.
[00:52:39]
For now, because what? I want to do
[00:52:44]
Yeah.
[00:52:45]
Is create my own. Stop. Number filled with a new one.
[00:52:49]
So I have a manual sort.
[00:52:52]
if I move this one above the first one,
[00:52:58]
that A2 will change to something else.
[00:53:02]
It’s easy.
[00:53:03]
Yeah, I understand why is it comes before a?
[00:53:06]
If I the day I understand the manual sort field as I think the day they make me the CEO of Airtel. I think that’s the last remaining mystery.
[00:53:18]
How this feels even works. Yeah. It’s so confusing. Like I don’t understand how the
[00:53:25]
Comes before a but it’s been like that. Every time I do this, it ends up with a z and like, that’s so interesting. Either way, I’m going to now. Go back into the data layer.
[00:53:37]
and now, I have
[00:53:40]
a manual sort field on my actual table here and
[00:53:44]
I’m going to create a new roll up.
[00:53:49]
same thing, we did earlier, I’m going to just go copy that same formula but I’m gonna switch it so that we’re not
[00:53:57]
just back to like the ranking example. We’re not going off of stop number or record ID. We’re going off of manual sort.
[00:54:06]
And actually I think we should probably also do the padding for that one. So I’m going to do that quickly. Yeah, the mysteries of this field mean it’s it’s
[00:54:18]
Really impossible to know what it’s going to come up with and I think it’s alpha numeric but don’t quote me.
[00:54:28]
Because again, simply no way of knowing what it’s going to.
[00:54:33]
Really output.
[00:54:35]
Exactly.
[00:54:41]
Um, or no way, I’m being silly. I can just do the record IDs because I don’t want ties.
[00:54:48]
But still yeah that’s yeah I don’t need I don’t need to do the padded because I’m going off of record ID manual sort.
[00:54:57]
But yeah, I see ya.
[00:55:00]
That I think would make sense and then no ties. I’m just gonna literally copy this function.
[00:55:07]
Copy and paste it to my stops.
[00:55:11]
and then,
[00:55:12]
This will be days.
[00:55:20]
And record ideas. What I’m looking for.
[00:55:24]
And then this would be.
[00:55:27]
Annual sort. We’ll just stop number manual. Sort.
[00:55:37]
Of want to make sure I actually did. Yeah, I sorted by manual sword. Perfect. So now,
[00:55:43]
I need to go in this window interface.
[00:55:48]
My stop number manual, sort.
[00:55:51]
One, two, three, four.
[00:55:53]
if I preview,
[00:55:56]
if I’m like, actually this one needs to be stopped one.
[00:56:00]
Now it renumber. So wherever I put it in the list, it will become those numbers. And it’s not so easy to see with record IDs because I didn’t put the actual names in, but that did change.
[00:56:15]
But again, super excited about this one, haven’t really found the best use case for it yet but I’m sure I’m sure it’s out there for it’s sort of less.
[00:56:27]
I mean it’s not using the manual sort field anymore but something similar of like you tell me what number this is in the list, back to the auto ranking for again, sequential items. If you have the start time of a bunch of events that all happen on the same day. You could say, well, this is the first event and this is the second event. And this is the third because you’re able to that same sort of comparison, but, you know, using time, instead of a either a number or a mysterious alpha numeric combination.
[00:57:01]
Exactly. Yeah, no. I like I like that. It could also even be like your tiebreaker too if you want to you could manually set them that way.
[00:57:12]
But yeah.
[00:57:13]
There’s a whole bunch of tips and tricks for using the sort function. I didn’t even touch on, you know, the obvious ones, which would be, you know, sorting the actual roll-up field and
[00:57:26]
Which is, for example, just let’s do the name.
[00:57:32]
and I’m going to do a
[00:57:39]
so, for example, I might
[00:57:42]
I often use HTML to mark up a list like this into like a table or, you know, a nice looking
[00:57:50]
just set of little code for a to display on a page, but
[00:57:56]
It is sorted just because of the way I added the records, but I could. Now make sure it always stays sorted. Just always sort it by what I want and
[00:58:09]
Super, super useful. Just like the whole reason why I wanted this feature was to be able to do that. But yeah, I use it for all sorts of other things.
[00:58:20]
Excellent.
[00:58:22]
Yeah, thank you all very useful and pretty extensible. It comes up often enough that there’s some kind of something that you can build out of the general idea if you sort the records first, you’ll get
[00:58:39]
Most of the way there, they’re still, you know, we’ve always had to added extra helper formula at the end, but the Sorting of the array in the first place is what really unlocks all that potential. Yeah, exactly. And the array slice kind of adds into that whole other ballgame. Now that I know how to use it.
[00:59:00]
If it’s simply Works differently than all of the other are table functions that are similar in that regard. And it’s also different than how JavaScript does it, which is your tables coding language of choice.
[00:59:15]
But that’s okay because now we know
[00:59:19]
now, we know we’ll use it to our advantage.
[00:59:24]
Wonderful.
[00:59:25]
well, thank you everybody for joining in, and thank you Kamille for a great season and
[00:59:31]
Dan, we missed you today.
[00:59:35]
But we will be back in July.
[00:59:39]
with our new season, be sure will
[00:59:45]
Right. So bye everyone. Bye, thank you.