1/28/2025 – BuiltOnAir Live Podcast Full Show – S21-E04

Duration: 0 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.

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.

Dan Fellars – I am the Founder of Openside, On2Air, and BuiltOnAir. I love automation and software. When not coding the next feature of On2Air, I love spending time with my wife and kids and golfing.

Show Segments

Round The Bases – 00:01:40 –

Following Articles Used in this Segment:

[] End-user multi-field sorting in interfaces

Scripting Time – 00:01:40 –

Explore Scripting with “Custom GPT with Airtable”.

Airtable Expert, Julian Post, will show us how to teach a custom GPT to query your Airtable base.

Base Showcase – 00:01:41 –

We dive into a full working base that will Kamille will walk through a base to showcase how to deal with timesheets.

Field Focus – 00:01:42 –

A deep dive into the Time Frame Comparisons Formula – Alli will explain how to perform comparisons between two time frames – i.e. this year vs last year, or this quarter vs last quarter. 

Full Segment Details

Segment: Round The Bases

Start Time: 00:01:40

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

Following Articles Used in this Segment:

[] End-user multi-field sorting in interfaces

Segment: Scripting Time

Start Time: 00:01:40

Scripting Time: Custom GPT with Airtable

Explore Scripting with “Custom GPT with Airtable”.

Airtable Expert, Julian Post, will show us how to teach a custom GPT to query your Airtable base.

Segment: Base Showcase

Start Time: 00:01:41

Timesheet Solution

We dive into a full working base that will Kamille will walk through a base to showcase how to deal with timesheets.

Segment: Field Focus

Start Time: 00:01:42

Learn about the Time Frame Comparisons – Alli will explain how to perform comparisons between two time frames – i.e. this year vs last year, or this quarter vs last quarter.

A deep dive into the Time Frame Comparisons Formula – Alli will explain how to perform comparisons between two time frames – i.e. this year vs last year, or this quarter vs last quarter. 

Full Transcription

The full transcription for the show can be found here:

[00:00:00] Intro: Welcome to the Built On Air podcast, the variety show for all things Airtable. In each episode, we cover four different segments. It's always fresh and different and lots of fun while you get the insider info on all things Airtable. Our hosts and guests are some of the most senior experts in the Airtable community.

[00:00:26] Join us live each week on our YouTube channel every Tuesday at 11am Eastern and join our active community at BuiltOnAir. com. Before we begin, a word from our sponsor, On2Air. com. backups. Onto where backups provides automated air table backups to your cloud storage for secure and reliable data protection, prevent data loss and set up a secure air table backup system with onto air backups at onto air dot com.

[00:00:50] As one customer, Sarah said, having automated air table backups has freed up hours of my time every other week. And the fear of losing anything. Long time customer [00:01:00] David states, OntoAir Backups might be the most critical piece of the puzzle to guard against unforeseeable disaster. It's easy to set up, and it just works.

[00:01:08] Join Sarah, David, and hundreds more Airtable users like you to protect your Airtable data with OntoAir Backups. Sign up today with promo code BUILTONAIR for a 10 percent discount. Check them out at ontoair. com. And now let's check out today's episode, and see what we built on air.

[00:01:37] Dan Fellars: Welcome back to the built on air podcast. We are in episode four of season 21. Good to be with you. I've got our regulars Allie and Camille. We'll be back shortly stepped away for a second and we have special guests with us, Julian Post. Welcome Julian. Good to have you on with us, we'll get into [00:02:00] Julian's been on the show before, so we'll get a, we'll get a brief update on what he's got going on and he'll show us some cool stuff.

[00:02:06] I'll walk us through what we're going to be talking about on the show today. First, we'll start with our round the bases on everything going on, what people are talking about and any news or updates in the world of Airtable. Then a quick shout out to our sponsor, On2wear and On2wear Backups. And then Julian is going to walk us through how to build a custom GPT that can talk to Airtable.

[00:02:28] And then we've got Camille is going to show us how to build a simple timesheet solution in a base and in Airtable. And then how to join our community. And then we'll end with Ali and how to do timeframe comparisons, comparing times this year versus last year and things of that nature. So cool to bits and tricks for you around the bases.

[00:02:54] First, let's start with new. They posted a new new feature [00:03:00] in the what's new database. And this is this has been around for a while. I think we actually already talked about it, but they finally updated their base. So basically you can you. Format AI fields as a number percent or currency. So that's kind of nice.

[00:03:17] I actually do like the example here of like being able to extract from your your document a number and like your quarterly earning statement. You could extract a number and insert it into a table that that's a pretty cool use case if that can work correctly. So yeah, that's the only new feature announced.

[00:03:39] I didn't see any new features spied by anybody. So it might have been a relatively slow week on that front. Okay, here's one. We've talked about a couple other ways that people are helping the people out in Camille's neck of the woods in L. A. With the fires. Here's another one. Somebody built a portal [00:04:00] to help people who have lost their homes if they're in need of furniture or other items.

[00:04:05] Here's a portal that that will allow you to say, you know, you've got a furniture that you can share or if you're in need. And so that's kind of cool use of Airtable. We've seen others as well for, for people helping out with places to stay and things like that. So that's cool. Let's see, what do we got next?

[00:04:27] Oh, that's an old one. Okay, going into AI. We're going to talk a little bit about AI, but. Just kind of keeping you up to date on how AI is evolving. Perplexity is a, is an AI that engine and they've just launched assistance. So this is kind of like the next phase of AI is basically AI agents or assistants that can perform tasks in the background.

[00:04:51] So this can actually like browse the internet and complete a task for you in the background and things like that. So that's [00:05:00] kind of cool. And I'm starting to see more and more of this type of functionality, the next evolution of, of AI moving beyond just chat to, to AI agents. We got here, speaking of AI and how like some tools just will not go away.

[00:05:19] That is Excel. So this is somebody built an AI add on to Excel that allows you to, to, to run essentially a, Large language model inside of Excel. So let me see. I think there's a screenshot of it. So in the cell formula you can perform stuff like that. So cell M. So that's kind of cool. You don't need a fancy new AI tools.

[00:05:45] You can use your trusted old Excel, do some stuff in there. 

[00:05:52] Kamille Parks: All right, sure.

[00:05:57] There's a, it's just crazy that [00:06:00] people are still spending development time and resources to, to work inside of Excel. 

[00:06:06] Julian Post: Right. An article called Excel never dies. That basically talks about all the reasons that people love Excel. Would I think it's a good read? They make a lot of good points. Yeah, 

[00:06:19] yeah, yeah.

[00:06:20] It has not gone away, although my usage of it has definitely decreased quite a bit. Okay, what else we got here? This is another big AI announcement. So Stargate Project. So this is a combination of OpenAI. Let's see, where's the list? SoftBank, OpenAI, Oracle, MGX. They've put together a hun 500 billion dollars With a hundred billion to be spent immediately to go towards the infrastructure of AI in the U.

[00:06:53] S. So that's a lot of money going towards that. So, Stargate. So, if you've got a good [00:07:00] idea that can help with the AI infrastructure, there is a lot of cash out there ready to be earned. Let's see how that is spent. That entails for the future of AI. Okay, moving on. Here's kind of a cool trick. Russell showcases in the built on air community, how to build dynamic charts and then send them in your emails.

[00:07:26] So he's using a third party tool. I've, I've, I've used it in the past for some kind of pet projects. Quick chart. So it's basically, you can send your data to an API end point, and it'll give you back a URL, that's an image. And then you could save that image as an attachment and then use that image.

[00:07:45] In an email, or I guess you could even just use the URL for a quick chart. That might be what he's doing. So that's kind of a cool trick. So if you're needing charts or anything like that that says a nice little hack to do that with a tool. That's, that's relatively [00:08:00] inexpensive. 

[00:08:01] Kamille Parks: Yeah. It's, I think it's a really nice sort of tip.

[00:08:05] We know emails and air table are very limited and what you could put on it. And things like this are helpful. And I, I get requests all the time for like, I want a summary email and sometimes text just isn't enough. And I think you can convince people faster with a bar chart.

[00:08:26] Yeah. Charts make everything prettier. Okay. Next one. All right. I know, Allie, you were in this thread. This is kind of an interesting one. We're going to see if we can help diagnose what the issue is. So saying so Lauren has a formula or a filter, so here's the filter that she has on a view and then she created a formula trying to recreate the same parameters that are in this filter and then that formula prints out, I don't [00:09:00] know, like yes or no, maybe or prints out like different values and then she filters based off of those values.

[00:09:06] And she's trying to get the same number of records, like the same records, to show in the filter as are showing in the formula. And so there's some troubleshooting back and forth. Thoughts on this? 

[00:09:20] Alli Alosa: Yeah, I'd be interested to see where she ended up after Russell chimed in a few things. He noticed there's a, quite a lot missing from Her formula that's shown in these, in the actual filters that are not in the formula, which could absolutely have an effect.

[00:09:40] She did mention that it was weird that the formula is returning, like the number that's filled in for the formula is higher than what's being returned from the filter, which is really interesting. I would need to look further at it. But I think the original issue. Or a lot of it might have to do with [00:10:00] just field types and data types.

[00:10:02] Originally she had, you know, does not equal blank a function blank and produce some wonky results depending on your, on your field types or data types. So it's, it's really, I think, just looking at what each data type is and figuring out. If you're properly saying it's empty or not. 

[00:10:28] Kamille Parks: And if any of those fields are lookups sometimes even though a lookup looks blank, it's not actually blank unless you've added conditionals to like make sure that the value it's looking up is blank.

[00:10:41] So it's not returned. So there's a lot of hidden gotchas that are like visually invisible until you. Sort of do a deep dive. So on top of what Allie said, if any of those are lookups, then you have a little bit more Investigating to do as well. [00:11:00] 

[00:11:00] Yeah, I think that the main thing. Yeah, so it deals with arrays I think I think I think it has to do with some of these are likely array values and the comparisons are not exactly the same.

[00:11:15] So, yeah, I think your suggestion of converting it to a string and then comparing it's likely gonna, gonna get you closer. 

[00:11:23] Alli Alosa: Exactly. And then Russell had also pointed out that in the formula. She's saying where contact type equals local level elections, but the filter says contact type is none of and she's filtering out everything else that's not local level elections, but that alone could cause issues because this is including empty contact types as well, whereas the formula is only looking at.

[00:11:52] local level elections. That's probably, I think, a big piece. 

[00:11:58] Yeah. [00:12:00] Yeah. Interesting. So, I think it is good to kind of understand there are, there are differences in a formula versus the filter.

[00:12:14] Okay, next one. We've got a reminder. If you are interested, Airtable, the community is having an open feedback exchange. So this is tomorrow at 8 a. m. 830 Pacific. So there's a link here. You can sign up and give the community any feedback you want to see any improvements. Sounds like they want to hear from, from us as a community and on how they can do better.

[00:12:39] So we've got some input there. Feel free to join. And last one just kind of an update on the Airtable community. They've made some changes to their, their introduction course. And then, so this kind of just walks through all the changes that they made. And then the last thing I thought was interesting was [00:13:00] there's a new part of the builder essentials that talks about value realization.

[00:13:06] Thought that was kind of interesting on how that, I guess the that you can realize the most value from your, from your base, understanding the impact there. So good course material if you're looking to ramp up your education and yeah, that's kind of it. It was relatively a quiet week in the Airtable world.

[00:13:31] So that concludes everything on that front. 

[00:13:35] Quick shout out. If you're running your business on Airtable. Best practice is to back up your data outside of Airtable, and I have a case study today. So we were, so on Twitter, somebody said just lost all their data in their torso. So torso is kind of like a I'm drawing a blank.

[00:13:55] What's the database that you're using, Camille? 

[00:13:57] Kamille Parks: SupaBase? 

[00:13:58] SupaBase. So Terso is kind of [00:14:00] like a SupaBase alternative where you can kind of run your, your database here. And apparently they had an incident and all the data on one of their, one of their AWS servers was lost. And so they had to send an email out to that was affected by this.

[00:14:17] So it wasn't all of their customers, but anybody that was running on the one specific AWS region, their data was all lost. And so here's their apology letter. They're like, well, it was a beta, so it's kind of your fault, but sorry. 

[00:14:35] Kamille Parks: That I would hate. I would hate to be that person who has to send that email because my goodness, 

[00:14:41] yeah, but they did at least they they refunded your, your monthly payment for anybody that's on a paid plan.

[00:14:49] Kamille Parks: Well, at the very least, my goodness, 

[00:14:54] including overage charges. So. So that's unfortunate, but that is why you need to have your [00:15:00] data backed up outside of your primary source. So that's where Ontuere comes in. It'll make sure your data from Airtable is backed up to Box, Dropbox, Google Drive, or OneNote, and you can have peace of mind that if you ever get an email like this, I don't expect it from Airtable.

[00:15:18] I'm not suggesting that I I know that they have backups of their data, but it's still best practice to keep it outside of your primary source as well to avoid letters like this. Okay. 

[00:15:32] Julian, going to teach us a little bit about AI and custom GPTs and how they fit into the world of Airtable. So Julian, tell us a little bit about yourself and then let's jump in.

[00:15:44] Julian Post: Sure. Yeah. So my name is Julian Post and I'm an Airtable consultant and I also create video tutorials on. YouTube. I have a YouTube channel that, that, with lots of different content on Airtable [00:16:00] and other tools. And, yeah, and I've been I've been, I think, was maybe more cautious around AI for the first year or so of its existence, but have been getting actually more and more excited about it as the tools get better.

[00:16:16] Definitely using it a lot for Coding you know, JavaScript use cases in Airtable and then also I had a couple clients ask about whether they could hook up an agent to, to it. And so that's, that's how I started experimenting with this use case. 

[00:16:38] If you want to share your screen, jump in. All right.

[00:16:55] Julian Post: Can you see my screen? [00:17:00] Okay. And you can see the Airtable base. 

[00:17:01] Yes, yes. 

[00:17:02] Julian Post: Great. So this is an Airtable base that is a it has one table in it, a table of guests. And these guests are people who are coming to my wedding says their name, their email. Whether they're attending and what they're eating for dinner, whether they're bringing a plus one.

[00:17:25] Can I do an invite 

[00:17:26] to this wedding? 

[00:17:26] Kamille Parks: Oh my goodness! 

[00:17:30] Julian Post: It's I'll send it to you after Dan. And so this is a custom GPT. And so basically I'm going to show you it working first and then I'll describe more about how it's set up. But this is my custom GPT here called guest list. And I can ask it questions about the guests and get responses.

[00:17:53] So I guess maybe I'll crowdsource some questions that, that you all might have. Just keep in mind that this is the information [00:18:00] available. 

[00:18:02] Kamille Parks: Is, is Tupac gonna make it? I have my doubts.

[00:18:19] Julian Post: Oh. 

[00:18:21] Alli Alosa: Oh no. 

[00:18:22] Julian Post: All right, let's and I and this is this does highlight something that I've experienced which is that it's a bit inconsistent And I can I'll explain to you why that might be But let's see.

[00:18:52] So it does ask sometimes whether you want to access the API [00:19:00] Okay, so it looks like let's actually find Tupac in here There isn't any information, so maybe that's why so maybe we ask it about Will Smith and see what it,

[00:19:25] all right, so it's, yes, it does tell us that Will Smith is coming, yes, and then just, I'm just going to actually test this and see if we say no for Tupac,

[00:19:47] so this might indicate that it's using information that it already, 

[00:19:52] Dan Fellars: oh, 

[00:19:53] Julian Post: cast So anyway, so I think this is a good time for me to go into how it works. So the [00:20:00] steps that I took to get here were to create the Airtable base, create a custom GPT, which I'll, I'll show you in a minute. And then you need to create a personal access token so that the GPT can communicate with the Airtable API and have authorization.

[00:20:20] And then you need to get the Airtable base schema, which is something that you can get from the Airtable API. So in this, there's an, this is an automation here, a scripting automation that will allow you to do that. And you don't need to really even set up a trigger because you can just test it.

[00:20:39] But put in your your personal access token and your base ID and call this API URL, and then you get this schema here, which is a schema of your specific air table base, and then you use that to to teach the the GPT how to access it. [00:21:00] So going into this here

[00:21:05] so there are some instructions. That tells it how to. Interact with the guests. But the most kind of critical piece of this is in. So you have you're figuring your custom GPT here and you can add what's called an action. I go into this action. You are able to enter the personal access token as an API key up here.

[00:21:29] And then you put in this schema that you create. And so the schema, the prompt that I used to create the schema was, Specifically using a a GPT called actions GPT, which they provide you as a way to create these schemas. And so it's saying, please generate an open API schema based on the below example here.

[00:21:56] And that example comes from [00:22:00] these examples here, this one. And we're saying this was for a different one that I was doing, but basically. When you have it create an open API schema, it actually creates kind of its own API endpoints that you call. And so you might give it some information about what you want it to be doing.

[00:22:22] So in this case, like, we're trying to find guests based on information about them. And then you give it that example, and then you would put your Base ID, and then your air table schema below what it spits out is this open API schema. And then it kind of looks at that and sees these different endpoints.

[00:22:45] And so like what it's probably using when I say like, you know, there's Tupac coming to the wedding. It's probably using either this list guests requests or the get guest request.[00:23:00] 

[00:23:01] Any 

[00:23:01] Julian Post: questions 

[00:23:02] so far, thoughts? And so those, so it basically maps those actions to the underlying you know, API call. 

[00:23:12] Julian Post: Yeah. And so, you know, if you're having errors, it may be how these are mapped to the actual API call to Airtable. And that could be altered. The personal access token that I created, I did allow it, give it write access as well.

[00:23:31] So that is another thing that I hadn't shown, but We could see,[00:24:00] 

[00:24:13] Dan Fellars: see, did it work?

[00:24:19] Yes. There he is. I'm in. That's awesome. You can 

[00:24:24] Julian Post: see that the GPT, yeah. So yeah, that's the basics. 

[00:24:33] Alli Alosa: Can you ask it like how many people are attending?

[00:24:40] Julian Post: So I will ask it and see what it says. I have experimented with this and gotten incorrect answers, which is kind of interesting. Like, I think I asked it a more specific one, like how many people are eating. Ribi. 

[00:24:54] Alli Alosa: Right. 

[00:24:55] Julian Post: And it gave me the wrong, like it found most of them, but not all of them. I [00:25:00] wonder if it knows how to paginate.

[00:25:02] Kamille Parks: I was just about to say paginated questions. And then we saw, like, it got whether Tupac was going incorrect. So it might have been like a cache thing as well. 

[00:25:16] Julian Post: So this is saying that 30 people are attending, and if we go ahead and say where attending is, yes. 

[00:25:26] Alli Alosa: 26. It can't count well. I mean, isn't it well known that chat GPT is not very good at math?

[00:25:35] Julian Post: Yeah. It's so bizarre. 

[00:25:37] Alli Alosa: It's really bizarre, right? 

[00:25:38] Julian Post: It is. It's weird because you're like, what did it do? Yeah. To get like a close but incorrect answer. 

[00:25:47] Kamille Parks: That's so funny. Now, why would you invite Ted Bundy? Can't help but notice. 

[00:25:55] Julian Post: Judging by my wedding choices, 

[00:25:58] Kamille Parks: well, 

[00:25:59] Julian Post: This is the I [00:26:00] think it's the top 50 most searched names on Wikipedia.

[00:26:05] Kamille Parks: Got it. Okay. I was like, what, what do these people have in common?

[00:26:14] Can uninvite him through chat. That's right. Yeah.

[00:26:26] Julian Post: I wonder if I ask it.

[00:26:34] Dan Fellars: Yeah. Had a delete record.

[00:26:50] Whoa,

[00:26:53] that's one response to 

[00:26:55] Kamille Parks: is there any info on either of these responses? [00:27:00] 

[00:27:00] Dan Fellars: Yeah, click them either response to trust thing. All right. 

[00:27:07] Kamille Parks: It 

[00:27:11] Dan Fellars: worked. Yeah. 

[00:27:14] Kamille Parks: If you, if you unfilter this view, did it say he's not attending or did it delete the record? It deleted the record. 

[00:27:22] Dan Fellars: It deleted the record. 

[00:27:25] Kamille Parks: Interesting. Cool. 

[00:27:28] Dan Fellars: Wow.

[00:27:29] Kamille Parks: So it, it did what we asked chat GPT is just being a little odd in which one of these things of nothing or better. 

[00:27:40] Julian Post: Yeah, so there's, and I think what, what other experience that I have with this is that this is the first example I did. And then I, I recreated it the other day with a good amount of confidence because it had worked the first time and I recreated the exact same steps.

[00:27:55] And I still had some troubleshooting where it wasn't working, and [00:28:00] that's the kind of problem with it is because you're relying so heavily on AI to set it up and create that schema. It's not totally consistent. So I mean, it could work better than it worked at the time before, but it also could work worse.

[00:28:12] And you really have to get into the details or just use natural language to tell it to keep modifying it in order to get the outcome that you want.

[00:28:23] Alli Alosa: Oh, Justin wrote in the chat. I don't know if you've talked about this, but he says maybe it's also counting the plus one values. Oh,

[00:28:38] I don't think so. I don't think so either. However, 

[00:28:43] Julian Post: that would be interesting. That's 15. 15 

[00:28:48] Alli Alosa: plus 15. That's 30. Oh, well, no, that's plus one. 

[00:28:54] Dan Fellars: Going. Yeah. 

[00:28:56] Alli Alosa: I misread your filter. Yeah. It's just making [00:29:00] numbers up. Like there's two R's in strawberry.

[00:29:06] Julian Post: Very interesting. Yeah. So, but I do think that this would be. That, you know, if you do spend more time kind of calibrating it, and also with the instructions, like, I think you could probably have it so if I go into here in the, in these instructions, you could maybe give it more instructions that say, like, specifically, any question that the user asks should Go to this API endpoint and specifically, you're always trying to make an API request that looks at, you know, these three or four attributes and returns a person or a return.

[00:29:45] Like, I think you could be more specific on how it does stuff that might be. End up producing more consistent results depending on what you're trying to achieve. 

[00:29:58] Yeah, [00:30:00] very cool. Awesome. Thank you, Julian, for sharing that. Where can people find you if they want to work with you? 

[00:30:07] Julian Post: You can find me at three rings dot co.

[00:30:11] That's the number three. And on YouTube and my channel is called Julian post. My name. Very cool. 

[00:30:19] Thank you. The insights. Okay. Camille learn. 

[00:30:24] Let's create a time sheet. 

[00:30:28] Kamille Parks: So this example I sort of came up with as an exploration of, is, is this an easier use case to build now that interfaces are where they are today?

[00:30:43] So I think one of the first things I tried to use Airtable for in my last job was to Do all of my timesheets during COVID and it was not a nightmare, but deeply inconvenient, even for [00:31:00] me who looks at her table all day anyway. So I think I came up with something that is decent, I think, for a very rudimentary.

[00:31:09] Use case, so just a brief explanation of the structure. I have employees pretty straightforward, except I do have a current rate field. So what is their hourly rate? Today. And then a timesheets table, which operates on a weekly basis. So there's an automation that runs every Sunday to create new records for each employee.

[00:31:36] So as long as you have fewer 1000 or fewer employees, you'll be fine with just a regular automation to create these records weekly in one run. And then, Okay. Each one is tied to an employee on a, for a particular week. It stamps the value of your hourly rate because over time hopefully you get paid more.

[00:31:59] But [00:32:00] we want, we don't want to update that for every record you've had in the past. So every week we're saying, well, what are we currently paying this person? So you have like a running history of how much they were paid over time. And then there's a link to. The log table, which would have one record for each individual day of that week.

[00:32:23] And then some, just a little math to sort of add up those numbers together and then a submitted field. So once you're done with a timesheet, normally what happens is whoever did the work submits it and then someone else either approves it or, you know, actions on it in some way. So having a field for status allows you to.

[00:32:43] Operationalize that a little bit. And then on the interface side. What, maybe what I can do is I'm going to try and start over and hope that I can force this to work. [00:33:00] So we're going to say to run this 835 right now. It won't let me get specific. No, no, everyone did. 

[00:33:13] You can 

[00:33:14] Kamille Parks: Okay, pivot, pivot.

[00:33:16] I would do test automation because there's a multiple sort of step action. It won't let me. So I'm going to run this for one person and we'll see how it goes. It's doing, as I said before, I'm stamping the value of the hourly rate and then because this is a time based trigger, I could say when the automation is run, that's the week that that timesheet is for and then instead of having a, oh, that's new.

[00:33:49] I haven't, I haven't seen that before. Instead of having one create record step per day of the week, I felt like that'd be annoying to maintain, so I created [00:34:00] a record template so it's taking the timesheet we just created and then applying a template to it.

[00:34:12] Oh, I see. Run is configured. Record

[00:34:19] created.

[00:34:23] Am I crazy? I am crazy. I did this wrong. No!

[00:34:31] This is the most chaotic demo I've done in a while and it's the simplest one. So if that worked accordingly My two tests that we don't need, I'm going to delete them. I have one timesheet for one of the employees and then log records created for one day of the week. So an advantage of using record templates in this case are you can do relative dates.

[00:34:55] So for my template. If I go into it really [00:35:00] quickly, seven day timesheet, I have these seven records set up here, but each of these are a relative date to whatever the timesheets start date is on. And so each of them is zero days after, one day after, et cetera. And so that's how I get that all in there using one.

[00:35:18] Automation run per week, which is pretty nice and I think fairly efficient. So if I go to the one person I have a time sheet for now I'll be able to enter time for this particular person. So with that basic structure in place, there's a few things that you could do that. Would kind of jazz up this sort of workflow.

[00:35:47] And it depends on what your business process is. So realistically you would probably want like. I should only be entering in my time [00:36:00] sheet and I really shouldn't see anyone else's unless I'm their manager. So additional infrastructure that you could put into the very basic structure I went over is on your employees table include a manager field that has some linked relationship between either here are my direct reports or here's all the people who report to me because employees is one table linked records aren't.

[00:36:25] Bidirectional which is a complaint I have. But if I do manager link to employees, probably only have one manager. Now I would be able to say that my guinea pig, his manager is me. So. You would be able to set up filters on the interface side to say, well, only show me, me or people whose manager is me, if that makes sense.

[00:36:56] And then from there, there's, [00:37:00] once you're on a page that you should have access to, there's things like, you probably don't want to submit timesheets for someone. Or for a week that's already been submitted previously. You don't want to continue to enter the same data over and over for the same day.

[00:37:19] And if you're submitting it to your manager to review, you don't want to make changes after they've after it's been sent to them, because that might affect whether or not they approve it. So there's some simple permissioning that you could set up. Depending on what plan level you have for inner for air table interfaces of different capabilities.

[00:37:41] So I've sort of mocked up. Here's where you would go to edit timesheets in progress, but you'll notice visibility is not something I can turn on on the current plan. I'm using for this demonstration. This is on the team's account. I believe you need to be on business or [00:38:00] higher in order to use visibility.

[00:38:02] So what you might do instead is have two different sections. One that says, Maybe this is all timesheets and it's not editable. And the one for timesheets in progress, maybe you want this one to be editable. I think I do that.

[00:38:29] I'll just turn that on for demonstration. But you would have a filter that says this is unsubmitted. So that's already. On there on this one, I would take that away because it's supposed to be all so having two different sections where once I'm done with this record status, once I changed that to submitted, it goes away, but it remains in my non editable all time sheets one.

[00:38:54] And then lastly, the actual design of. Entering in your [00:39:00] daily values itself again. This is up to user preference. Probably you want this displayed as a view, whether or not it's a list view or grid view. I think that's kind of up for, you know, again, personal preference, but you're probably going to want the date shown.

[00:39:20] I have time. I'm out and break. I've seen in a lot of time sheets that I've used personally, they have two different sections time in one time out one time into time out to you. I hate that method of entering time because it's my days are pretty, you know, So I would rather say, here's where I started overall, where I ended overall, and I took an hour break in the meantime.

[00:39:52] But again, that's up to how anyone wants to set up for their particular business. If you need that level of specificity. And then for [00:40:00] me, I used duration fields in order to do this. You could also use a single select drop down if you want, like specified time increments. I do find Duration feels somewhat annoying, because if I wanted to say I started work at 9 a.

[00:40:16] m., for instance, I would put in nine, but unless I put in a colon after it's going to read by default as nine minutes, which isn't great. So instead, I would have to do nine colon. And then hit enter, and now it's registered at 9am. And then for timeout, I could say, in military time, it would be, I think, 18, and then 1 hour.

[00:40:42] So 8 hour workday, that's how I would put it in personally. Or you could use a single select with 9am, 9. 30am, 10am, etc. And there are a few tools, I think, in the extensions library already that help you [00:41:00] create fields with time intervals, kind of already in there. So you don't have to sit there and manually create those, which could get annoying.

[00:41:10] Very cool. Yeah, this is a very. Common useful use case to walk through that. I'm just disappointed that Ed Bundy is not an employee at your company. 

[00:41:23] Kamille Parks: You know, for some reason, I used Mockaroo for this to just get a bunch of fake data. Ted Bundy is not one of the selections that just gets populated.

[00:41:38] Who would have thought? 

[00:41:40] Cool. Thank you, Camille, for that.

[00:41:44] All right. If you are not in our Built On Air community, join us with the four of us and thousands of others. We're getting close to hitting our 2, 000 mark, which would be a great milestone for us. Also, be sure to subscribe to our newsletter and subscribe to our [00:42:00] YouTube channel as well.

[00:42:00] So join us, builtonair. com slash join, get you in for free and interact with some amazing people in our community. 

[00:42:09] Finally, Allie, walk us through how to do time frame comparisons. 

[00:42:15] Alli Alosa: All right. Okay, so this is just a fun little trick that I've been working through for a while. And I just figured out a cool little hack to make some, what I think are like useful charts out of, out of this data.

[00:42:32] So this is all about using linked record fields and bending them to your will, I guess. I'm sure everybody might be a little familiar with charts and the limited capabilities of charts. And you really need to be able to have the data structured in exactly the right way to get a chart to show exactly what you need.

[00:42:53] And doing things like comparing trends over different time periods. are not super [00:43:00] easy to do in air table, and you really have to do some manipulation of the data to get it in the right spot. But this is an example that I think is relatively simple that can really do that well, and it's pretty flexible as well.

[00:43:15] So This is just a bunch of fake transactions from ChatGPT. I loaded them all in here and really where this starts is at the days table. I used to kind of skip the days and just go right to months and then quarters and years, but I've found that having this days table actually is a lot more flexible because it lets you really get as granular as you want and 300 records a year is not that not too terrible.

[00:43:48] 365. It's not going to balloon out of control very quickly. So I typically like to set up a base that's separate from my like production [00:44:00] base to do this where I'll sink in. Like the transactions table here, I might sync that in from a different base that I'm actually working in and then do all of my time interval and dashboarding stuff over here, just to save on record count and overhead for the main base, but neither here nor there.

[00:44:18] All transactions get linked up to a day, and then those days in turn get linked up to a month, months get linked to quarters, quarters get linked to years, and then years get linked to. Summary, which is just one record holding all the years. So on and so forth. So, once I've got everything linked to days, I just rolled up my gross transaction amount for that day.

[00:44:45] And then roll that up to the month table. So pretty simple so far, but then I have a couple little formulas and they are essentially just calculating based [00:45:00] on the date that this month has, what was the name of the record for this month last year. So it's basically just subtracting 1 year from the start date and outputting in the same format as the name, my primary field.

[00:45:18] What that month would have been, and I have that named this month last year, and I could stop there, but I took it a step further to make it some charts really cool, and I actually now I'm also calculating a field that has this month's name as well as this month last year's name, so each one of these is going to be You know, January of this year, January of the year before, January of the year before, then the year before that, so on and so forth all the way down.

[00:45:55] Then I've just copied the values of these two columns and pasted [00:46:00] them over these linked record fields, which are self linking. So these link right back to the table that I'm on. You could use an automation for this. Absolutely might make it easier over time. But what I like to do is I just fill up. All of these tables with like the next 10 years worth of data and then just do one paste copy and paste and then I'm done and don't have to worry about it for 10 years or waste time building an automation.

[00:46:27] But you certainly can. So why is this cool? So I link each month to the month that came the year before and then I can grab the gross from. This month last year and pull it onto this record and do a calculation to compare those two values. So, I am now down by a little over 50 percent as to where I was at last year.

[00:46:59] And I [00:47:00] basically just take that concept and roll it all the way up through all of the tables. So I'm doing the same exact thing. I've got the gross of this quarter compared to this quarter last year. So on and so forth, all the way up to the year's table where I'm calculating. All right. Overall, how how much am I up or down?

[00:47:21] So that's super cool, but the coolest part is with that self linking like this month and this month last year. The reason why this is even a thing, is if I take this field now and look it up on each table above it, so I'm looking this up here, and then same for like the quarters, all the way through. I see where you're going.

[00:47:54] It's pretty cool. It's like, so this is a, I just threw this together. It's not a hundred percent done [00:48:00] yet, but it'll show the idea that I'm going for. So I've got each year here. I can, I'm I'm using the hierarchy to show each year, then each quarter within that year. and then down into months. And I would eventually turn on detail pages for each one of these, but I only have done it for the year so far.

[00:48:21] If I open up the year, now I can look at this year, which is 2024, this is the yellow, compared to the year before at a quarter level as well as at a monthly level. So I'm Really just kind of, like I said, bending these linked records to my will and putting the data where I need it to be so that I can get these pretty looking charts.

[00:48:47] Julian Post: Super cool. 

[00:48:49] Alli Alosa: Yeah, I'm really excited about this one. I think this is super cool and it's really simple too. It like doesn't really require a lot of overhead, I [00:49:00] guess. There's a lot to explain. 

[00:49:04] Kamille Parks: I'm so glad time is linear because that the formula for just like, what, what was this month last year is very simple because you know, it's a predictable value.

[00:49:16] And I really liked this result. I think it's, it's very clever and I think this has to be one of those, like. You could make a chardonnay table, but can you and here you go with a little bit extra infrastructure. And I don't imagine this is too many records. If you think of maybe at the day level, perhaps that's where a lot of extra records have, but you could set up if you don't have transactions for that day after that day has passed, delete the record because you don't need it.

[00:49:49] Alli Alosa: Yeah, you certainly could. And if you're really only doing it. Like, I think the syncing, syncing the, the data that you [00:50:00] want to fit into these time interval buckets into this base from somewhere else definitely saves on record counts in both places, which is nice. But yeah, I, I do this all the time. I'm constantly trying to think of, of like new ways to improve upon this.

[00:50:17] One other thing that is, I think a silly, but also, also, also useful. trick is if you link each record to itself, which is a silly thing to do, but 2022 is linked to 2022. Really the whole, what this affords you is. Now on my detail page here, I've always been bothered that the number elements have to be from a linked record or a lookup of a linked record and you can't like roll that up and you can't use a number element to display a number that's on this record itself.

[00:50:57] But if you link it to itself, you [00:51:00] can, because you can just have a number and then the source is this year, because all of those records are just linked to each other. 

[00:51:09] Kamille Parks: That's so silly, but it's true. 

[00:51:15] Alli Alosa: So I've been doing that a lot as well. And what I really love the the conditional coloring, because I could do this percent change and then set up.

[00:51:29] You know conditions to be like, if it's negative go with red, otherwise green, but yeah, so having those things linked to each other is, or linking records to themselves does have a benefit. 

[00:51:55] Yeah, cool. Some good comments from people watching here. People say you [00:52:00] no longer need days, weeks, months, because interface filtering is good enough.

[00:52:04] But this is another reason why having them is powerful. You could also modify this for an analytics tracking dashboard as well.

[00:52:16] Kamille Parks: Forever ago, I did a demo of a base that's, I believe, still on the Airtable universe. If you want to get it, the solution I do to categorize my finances, it'll, like, automatically tag it to The appropriate. I only do it at the month level, but tag this record to it. It's equivalent month, so I could do some stuff in it, but there's no notion of change over time because I didn't want to figure that out.

[00:52:47] So I may steal this to update that old solution just so that it's more complete. 

[00:52:54] Alli Alosa: Absolutely. Yeah, it's super fun. 

[00:52:59] All right. [00:53:00] Thanks for sharing that, Allie, and that concludes today's show. We'll be back next week for another great episode. Be sure to join us then, and have a good week, everyone. 

[00:53:10] Alli Alosa: Thank you.

[00:53:10] Julian Post: Nice to be with you all. 

[00:53:11] Alli Alosa: Yay!

[00:53:26] Thank you for joining today's episode. We hope you enjoyed it. Be sure to check out our sponsor, OntoAir Backups, automated backups for Airtable. We'll see you next time on the Built on Air podcast.