10/22/2024 – BuiltOnAir Live Podcast Full Show – S20-E03

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 –

Field Focus – 00:01:41 –

A deep dive into the Pivot Tables – Scott Rose will highlight how to be a power user of Pivot Tables. 

Learn More Here

Scripting Time – 00:01:42 –

Explore Scripting with “Linked Record Fields Consolidation”.

Kamille will showcase a script to consolidate the value of multiple linked record fields into a single one.

Scripting Time – 00:01:43 –

Explore Scripting with “Field Searching”.

Alli will share a script that will allow you to search across the entire base for fields including a given string.

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.

Segment: Field Focus

Start Time: 00:01:41

Learn about the Pivot Tables – Scott Rose will highlight how to be a power user of Pivot Tables.

A deep dive into the Pivot Tables – Scott Rose will highlight how to be a power user of Pivot Tables. 

Learn More Here

Segment: Scripting Time

Start Time: 00:01:42

Scripting Time: Linked Record Fields Consolidation

Explore Scripting with “Linked Record Fields Consolidation”.

Kamille will showcase a script to consolidate the value of multiple linked record fields into a single one.

Segment: Scripting Time

Start Time: 00:01:43

Scripting Time: Field Searching

Explore Scripting with “Field Searching”.

Alli will share a script that will allow you to search across the entire base for fields including a given string.

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 11:00 AM Eastern and join our active [email protected]. Before we begin, a word from our sponsor on. On2Air Backups provides automated Airtable backups to your cloud storage for secure and reliable data protection. Prevent data loss and set up a secure Airtable backup system with On2Air Backups at on2air.

[00:00:49] com. As one customer, Sarah, said, Having automated Airtable 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, On2Wear 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 On2Wear backups. Sign up today with promo code built on air for a 10 percent discount. Check them out at onto air. com. And now let's check out today's episode and see what we built on air.

[00:01:37] Dan Fellars: Welcome in to season 20, episode three of the built on air podcast. Good to be back with you this week. Myself, regular hosts, Ali and Kamille with us. And semi regular host, Scott Rose. Welcome back, Scott. Thank you so much. It's great to be back. Yeah. Good to have you back. It's fantasy season. You, you been traveling the world while, while we've been.[00:02:00] 

[00:02:00] Holding down the forts. 

[00:02:01] Scott Rose: That is true. I was traveling the world for your entire last season, but I was getting you guys not live, but you know, a day, a day later. 

[00:02:11] Dan Fellars: Very good. Cool. Well, we're glad to have you back in this season. And Scott's going to be sharing some cool stuff with us later in the show. So let me walk you through what we're going to be covering today.

[00:02:21] We always start, start with our round the bases, keep you up to date on. Community news and updates and then a shout out to our sponsor onto air to get signed up there. And then Scott's going to walk us through pivot tables and some advanced features and, and how to, how to really utilize pivot tables.

[00:02:42] Then Kamille is going to walk us through a script to consolidate linked record fields. And then talk about how to join our community. And then finally more scripting time. This is going to be scripting heavy with Ali's going to walk us [00:03:00] through how to implement a script to search across all fields in a base and.

[00:03:06] Yes, I have new digs. I'm in a new office. So you can see I've got more sunlight now in this new office. So 

[00:03:13] Alli Alosa: new 

[00:03:15] Dan Fellars: New setup here. It's probably more louder here. I can hear the traffic more, but, it's good. Okay. 

[00:03:22] ROUND THE BASES - 00:03:22

[00:03:23] Let's start us with our around the bases. 

[00:03:26] Scott Rose: Dan, Dan, Kamille also has a different outfit on today.

[00:03:29] Kamille 

[00:03:29] Dan Fellars: representing. 

[00:03:30] Scott Rose: What are you representing 

[00:03:31] Kamille Parks: there? The Los Angeles Dodgers, the greatest sports team ever. 

[00:03:38] Dan Fellars: That begins the World Series. We'll be in speaking of which, we'll be on the other side where the Yankees are from, 

[00:03:46] Alli Alosa: New 

[00:03:46] Dan Fellars: York. I was wondering, they play in LA first, so they won't be playing while we're there.

[00:03:55] But yeah, they'll be, they'll be in LA this weekend and then New York next week. [00:04:00] But we'll be in New York this week for Dare table. All four of us are going to be there. It's exciting. First time that Scott, I believe this is your first dare table. 

[00:04:10] Scott Rose: Yes. My first air table in person. I was at the virtual one back in 2020.

[00:04:16] Yeah, that's right. 

[00:04:18] Dan Fellars: So excellent. We're all excited for it. And please, if you are going to be there, introduce yourself. We'd love to meet you and talk to you and see what's going on with you. So, so let us know in the comments, if you're going to be there and reach out. So we'll see everybody on Friday at their table.

[00:04:39] Hey, next one up. Very big announcements from air table. They just well, they didn't release it, but they announced a full end to end product operations product, called product central, central, and there's a video that [00:05:00] goes with it that you can watch. They announced it on product hunt. And, yeah.

[00:05:06] What's your take on this? 

[00:05:08] Kamille Parks: I don't know what it is. Yeah.

[00:05:13] It's, it's very similar looking to interfaces, although there are things on it from like the screenshots that don't appear possible in like interfaces today. So it's like a, a, a customized version of an interface with a lot of the UI looks really good. I mean, it looks very nice. 

[00:05:38] But I just don't know, I, what is it?

[00:05:43] What's happening? Like, like a very advanced template, I guess, is my quickest summary I could possibly give it, given my understanding. Yeah, 

[00:05:56] Alli Alosa: I'm lost. I mean, like, there's a lot of stuff here. Like Kamille pointed [00:06:00] out, like, looks like desperation. Like I see a button in the top right corner that says add chart, which is not currently possible.

[00:06:10] You can't let your user create their own chart, but I mean, it might be cool. I mean, it reminds me of like, you know, like Google Analytics vibes where you like open this up and you can create new things for your own dashboard. As the end user. So, I mean, that, that could be interesting. I mean, I would hope, I don't know, maybe sometimes you wouldn't want someone to do that, but there's got to be pluses and minuses, I guess.

[00:06:35] Kamille Parks: There's, there's just so much in here. Like, if we look at the sidebar alone, search, and then under that for you home and activity, those options are available in the sidebar now, but in a different place, but they seem like, Now they're first party, if you will, and then there's interface pages, but then there's submit feedback as its own button.

[00:06:59] What does that [00:07:00] mean? Submit feedback to whom? 

[00:07:04] Scott Rose: What's happening? I'm confused. Can we go under the hood and add fields and change things or we can't? And it's like a locked off, like, Solution like as if you bought like quick in or quick books off the shelf, for example. 

[00:07:18] Dan Fellars: That's my understanding is that it's all locked down.

[00:07:22] I don't, yeah. 

[00:07:24] Kamille Parks: Okay. Well, this looks very interesting and helpful in a way that I don't need. I mean, but I don't work, I work for product, but not for product. You know what I mean? So like the, if the template is locked down, then, you know, there's not much I personally would be able to do with it. I like some of the things I'm seeing in terms of UI components that are seeming to be available here.

[00:07:53] So if they get added to interfaces at large, like no complaints from me, that chart looks great. [00:08:00] Top right sentiment bar looks cool. I want one of those. 

[00:08:05] Scott Rose: Yep. Yep. 

[00:08:08] Kamille Parks: Absolutely. 

[00:08:10] Dan Fellars: And even the sidebar, like is a better sidebar than what interfaces have. Even if they could implement that. Yeah. 

[00:08:19] Alli Alosa: There's a lot, a lot to this that I am confused, but also excited about, but also like, I don't know.

[00:08:27] The marketing materials historically have had screenshots of things that are just not possible and never have been or will be. So I am, I'm hoping that that's not the case here. We'll see, I guess. 

[00:08:42] Kamille Parks: Can we go back in the video? I just, there's like, it's the little things where it's, it has the timeline visible a little bit further.

[00:08:53] Yeah, here. And then pause. Top right. Backlog. Yeah. [00:09:00] You know what I mean? It must be very specific. 

[00:09:04] Alli Alosa: So 

[00:09:06] Scott Rose: is that just a button that triggers an automation or I wonder what that does. 

[00:09:10] Alli Alosa: There was on the, on at the beginning of this, when like there was a little pop up and there's a button in the pop up that said add to backlog.

[00:09:19] So I don't know, backlog generally means different things for different industries or different people. So I don't really know what that means in this case. 

[00:09:30] Scott Rose: Here's the thing. I am hopeful that they will roll out all these cool little elements to us in the future so we can do these cool things. I hope. 

[00:09:40] Alli Alosa: I do.

[00:09:40] I love the look and feel of a lot of what I'm seeing here. Like even that chart had these cool like points at the top, which are just not there. Right now. And even like the look and feel of like these cards, like having the little icons and the card looks 

[00:09:59] Scott Rose: great. [00:10:00] 

[00:10:00] Alli Alosa: No idea 

[00:10:01] Scott Rose: how to make it. Yes. These cards are awesome, 

[00:10:07] Dan Fellars: but is it going to be, yeah, I don't know.

[00:10:10] Is everything configurable or it's just, this is what it is. 

[00:10:14] Kamille Parks: We're probably going to get somewhere in the middle, right? I don't know if we're going to be able, like looking at the different icons, they come in different colors. And I can see maybe we get a world where interfaces, you could put these icons, but they're all in gray or something, just historically in Airtable, you know, if you shoot for the middle, you'll, you'll be fine.

[00:10:38] But like the things on the right look like the numbers elements. So it's not like they don't have that UI paradigm already in the, you know. interface ecosystem. But yeah, in brief, it looks great, but I'm confused. 

[00:10:56] Dan Fellars: Yeah. Yeah. That'd be [00:11:00] interesting. The other, the bigger question is, are they going to do more of these and try to like create apps for every use case?

[00:11:10] Kamille Parks: Yeah, it'd be real weird if you stopped here. So I think if you do maybe like five of these, but like one for each core use case that Airtable is most often used for based on their user data or something, then I could see, yeah, sure. Give something to your customers. That's like, here's the like. built out everything, all the bells and whistles, all that kind of stuff.

[00:11:38] If they only built this, I would be beyond confused. I'd be baffled. Yeah. 

[00:11:44] Dan Fellars: And like the data in here, are you able to access the data layer for it and like sync those tables to other tables to use that data? That'd be interesting. 

[00:11:56] Scott Rose: Yeah. [00:12:00] Right. 

[00:12:01] Dan Fellars: But this is a separate priced product. So my understanding is you, you, you're buying this product with its own pricing.

[00:12:13] I don't know if it requires the base air table pricing and this is an add on or if it's separate. I don't know. It'd be interesting. 

[00:12:21] Scott Rose: Yeah. It says right there. It's air tables. First ever dedicated solution. That really seems like it's not customizable or open in any way. I'm not a hundred percent sure.

[00:12:32] Dan Fellars: Yeah. The cool thing would be is if they built this in a way to where third party developers could build similar apps with this kind of functionality to fully customize an app, then that that would be the ideal. Deploy it as a scanner. That 

[00:12:49] Alli Alosa: would be great. 

[00:12:51] Dan Fellars: Yeah, 

[00:12:53] Alli Alosa: like kind of taking the managed components and stuff and make.

[00:12:57] Spreading it, not just [00:13:00] organization wide, but you could push it to your own client base. 

[00:13:04] Scott Rose: And that could open up a whole marketplace of people selling solutions to specific industries. 

[00:13:09] Alli Alosa: Yeah, exactly. That's something that's, there's a big hole there right now. Like people try to do it, but it's not possible to update that app once you've given it to somebody, for example, unless you actually physically log into their workspace or whatever it is.

[00:13:26] Right. Thank you. Yeah. 

[00:13:28] Dan Fellars: Right. Yeah. I, I doubt that's how they built this, but 

[00:13:32] Alli Alosa: yeah, wishful thinking, but it would be cool in the future. 

[00:13:37] Dan Fellars: Shoot for the 

[00:13:38] Alli Alosa: middle baby. Yes. 

[00:13:39] Dan Fellars: So Justin says also focused on product development. So the target market is very niche. Welcome Jen. And like any template, they want to rip it apart and customize it immediately.

[00:13:52] True, true, true. Okay. Let's move on. Next one. Okay. This [00:14:00] is, mentioned here about a change coming in late November. Let me go over to the post on the community. So they're making a big change. Anybody familiar with this? It wants to explain that 

[00:14:17] Kamille Parks: briefly. I was confused because they emailed developers in the marketplace, but I didn't see the announcement yet.

[00:14:24] And I was like, why would you only tell us? But they made it a couple days later. But basically what's happening right now in Airtable, if you duplicate a base or if you take a snapshot and restore a snapshot of a base, it creates a copy of that base with all new record IDs for everything for the base, for the table, for the fields, what's going to happen is.

[00:14:50] After some date, I think it's November, if I recall correctly. They're going to make it so that when you duplicate a base or when you restore from a [00:15:00] snapshot, the table and field IDs remain the same, but the base ID is what's changing. So that means you would very easily be able to write a script that sort of, you know, copies over changes from one base to another or you know, use the APIs to have things talk to each other very smoothly.

[00:15:23] So you're not going to see really Any immediate changes until you perform one of those actions, duplicate a base, or take a snapshot. But if you've ever had to compare two bases together, you know, it's a pain. It really is a bit of a chore. And then on a wider scale, if you, Primarily for, I think, like the developers, if you're building a base, you might have like the demo mode of the base over here and then the real base when you're ready to launch it over here.

[00:15:57] If you, are [00:16:00] working with that model, your field IDs are going to match up when you duplicate it. So it'll be very easy to debug on a go forward basis. Now, once you create a new field in one of those copies, right, they're not going to line up anymore. They're not tied together. That's what managed apps are for, but.

[00:16:16] You know, outside of that very nice little quality of life improvement. If you've ever had a duplicated base and then reconciled two together. 

[00:16:24] Scott Rose: It's also for record IDs as well. You were, you were talking about table IDs and field IDs, record IDs. That's huge for, especially restoring snapshots 

[00:16:33] Kamille Parks: when you're restoring, because the record IDs are going to be the same on the, at the point that you copy.

[00:16:40] So it's important to note, as soon as the two separate bases exist, they stop syncing values together. You create a new record in one, it's not going to have a companion record in the new one. It's going to have a completely new record ID that's not going to be anywhere else in the ecosystem. Same with the fields, same with the tables.

[00:16:58] But at the point at which you [00:17:00] copy, You'll have identical IDs against all values except for the base itself. Right. One ID has to be unique. So yes. You know, 

[00:17:11] Dan Fellars: is it confirmed? Is it confirmed that record IDs will stay the same? Yep. Yes. 

[00:17:16] Alli Alosa: In the 

[00:17:19] Scott Rose: go down a little bit. Lower their, so 

[00:17:22] Alli Alosa: basically 

[00:17:23] Scott Rose: all the IDs, like Kamille was saying, except for the except for the base id.

[00:17:27] Kamille Parks: Yeah. So I didn't mention it, but in the text view IDs as well. So literally like all of your key IDs, except for the base itself, going to be the same, very useful. I suspect they made this change. You know, to be in line with how managed apps work, because in a managed app, if you've ever like dug into how they look under the hood, all of those IDs, every time you deploy a managed field or like the entirety of a managed table, those [00:18:00] IDs are the same across all instances.

[00:18:02] So it's, you know, nice feature parity there. And again, restoring way easier. 

[00:18:10] Scott Rose: Amazing. This I've been wanting this for so long. This is so exciting. 

[00:18:16] Kamille Parks: We used to have to do things like if you knew you had to copy a base, you would have, you would, for each table in your base, you would have a field that's a single line text field and then like stamp the value of the record ID so that if you ever had a restore from, a snapshot, the snapshot would have had its own new record IDs, but you had the original record ID stored in a cell value.

[00:18:42] Well, you had to do that for every single table for every single base. And it was really annoying. And what if the automation fails? Because sometimes they do. So, you know, this solid, no notes,

[00:18:57] Dan Fellars: comments, Jen says, I like this because [00:19:00] scripting best practices that you should use field IDs, which is a pain when you want to clone the base. Yeah. Yeah, Alicia says it would be cool if this could be also be done for sync tables. Having the same record ID in the same table would be awesome. 

[00:19:14] Kamille Parks: I would agree.

[00:19:16] There's like a, you could bring in the source record ID along with you. However, that's really easy to get lost, especially if you're in an enterprise instance where the same data is being synced 1000 places. And I would also go as far as to say, if you sync a view down with all of the fields in it, I want those fields to have the same field IDs when they get synced down as well, because it's the same sort of principle, right?

[00:19:43] It's the same data. It's that's being continuously copied over. So you know, maybe in the future, they would consider that adjustment as well. 

[00:19:55] Alli Alosa: There's to that end, I think. It was on one of our, like, in like our [00:20:00] calls a while back, Julian Post pointed out if you have it on, if you have the setting on to be able to create a linked record in the synced table, so you have to be on business or enterprise in order to do this, where you've got the two way sync turned on and you've enabled creation of records in the synced table.

[00:20:19] If you create a record in that synced table, also gets created in your original. It shares the same record ID, which is interesting already, like right now, but it's just an interesting thing. I mean, it's like to that, to the point that Alisha is trying to make, I think maybe this would be a thing in the future.

[00:20:39] We'll see. 

[00:20:41] Kamille Parks: I don't know if they'd be able to change it. It like reverse. And yeah, that would break so many things. If every single available now, if they change the record IDs to be consistent with the source, that would break a lot of people's workflows, but on a go forward basis, that'd be nice. Yeah.

[00:20:59] Dan Fellars: Where [00:21:00] this doesn't work, unfortunately, which impacts us onto where with our backup restore is if you're creating a new base via the API, you can't tell it to use this ID for the table ID. So anything, if you're dynamically creating a new base with new tables and new fields, even though you're replicating the, an existing one, it will all be new IDs, no way to repurpose IDs.

[00:21:29] Scott Rose: That's interesting. That's interesting. They should you almost bring up a great point that they should almost give us the ability to edit record IDs and under certain circumstances. 

[00:21:39] Dan Fellars: Yeah, yeah, I 

[00:21:42] Kamille Parks: would just expose the duplication as a clone as a API endpoint and then I wouldn't need to ever touch an ID, but I'm performing the same action I would in the UI, but programmatically with the API.

[00:21:58] Right. It, you know, the [00:22:00] fact that you can't do it with the API means it's like a UI specific action, 

[00:22:04] Alli Alosa: which 

[00:22:05] Kamille Parks: don't necessarily understand. 

[00:22:08] Dan Fellars: Yeah. 

[00:22:09] Which in our case, sometimes we're restoring, you know, a version of the base from months ago, and so it's not going to be the current one that you could clone if they had versioning where you could specify maybe snapshots or something.

[00:22:22] Kamille Parks: Yeah. Like snapshots aren't available in the API as far as I know. So, If there was I could definitely see get a base and all of its snapshots. It's like the unique ID for each snapshot. You would click it, not click, but select it in the API and say, restore this snapshot, but that's not available. 

[00:22:45] Dan Fellars: Okay.

[00:22:46] So that could impact people. So be aware of that. That's coming late November. It says, all right, let's move on. Okay. This one was kind of interesting. Zapier. Is getting in the game. They now have linked [00:23:00] records in their Zapier tables product. So I didn't realize this. I played with it when it first came out, but I didn't realize it didn't have linked records.

[00:23:09] So now 

[00:23:11] Kamille Parks: it wasn't not a relational database. Not really. I 

[00:23:18] Dan Fellars: think they had multi tables, but you couldn't link them. So it was just

[00:23:27] Alli Alosa: they're coming for air table too. They like explicitly put, I, I got the same email from Zapier like 18 times because of all the different accounts I'm on. But like, I noticed that one of them, not all of them, but only a couple had. In the like subtext of like, you know, it was like email subject and then how it gives you like a preview, it said something like, get, get the core air table functionality you've been looking for in Zapier and like that.

[00:23:56] That sentence wasn't actually in the email. It was just in the like metadata for the [00:24:00] thread. But that's interesting that like, I don't know, maybe they like figured out who uses Airtable a lot and put that in the email for like their SEO or what. 

[00:24:09] Kamille Parks: Oh, the, the number of Airtable actions, I guess, attributed to your account.

[00:24:14] Alli Alosa: Maybe. I found it interesting. They were like calling them out. They were like, we have it now. They're like, what? We can compete. Okay. Well, 

[00:24:25] Kamille Parks: as much smack as we talk about Airtable, they've had linked records, I think, from literally day one. It was kind of their whole deal. Yeah. And recently there have been several improvements to the link record field where you can limit link selection, Based on certain conditions that can be dynamic.

[00:24:46] You know, so at this point, if you didn't have it in your product at launch and you're now adding it, 

[00:24:54] Dan Fellars: you got to catch it up. 

[00:24:58] Kamille Parks: It's not like Zapier is [00:25:00] one of these free open source alternatives, right? There's a difference there. Like the standard I hold competitors to is if I could self host it, it's open source, give me literally anything and I'll give you kudos.

[00:25:15] If you're not, you're going to have to beat this standard. 

[00:25:17] Alli Alosa: I think the cool advantage to the Zapier tables, and I haven't played with it, but I'm pretty sure that like, if you have your data stored in Zapier's stuff, and you run zaps on it, those zaps don't count towards your limits or towards your tasks.

[00:25:41] Yeah. So like they're trying to incentivize you to use their product. I was saying, Hey, you can run automations for not for free, but for less. 

[00:25:51] Dan Fellars: Yeah. If you're just using it as a data storage for your automations, then, then I could see a use case. I, we [00:26:00] have lots of clients that use Zapier. I don't think any of them use Zapier tables that I'm aware 

[00:26:05] Alli Alosa: of.

[00:26:08] Dan Fellars: So I don't, I don't know who's, who their target is, who's using it, but. But I guess that's also because they all use air table as well. So likely some out there that don't use air table.

[00:26:24] All right. Similar. This is from softer, which is one of the big air table portal builders add ons on top of air table now support notion, which is a big deal. So I thought that was worth mentioning. So if you're already using softer with air table, you can now incorporate notion into that in the softer world.

[00:26:51] Alli Alosa: That's a really cool one. They, they also in the last week, I believe released, tabs. [00:27:00] So you can actually have an element on your page with tabs that you can click across, which are really cool. And also filtered user sync, which is huge. And I can't believe they didn't have it from day one, but they do now.

[00:27:14] So if you're like, if you have a big list of members and, or people, and you've got a tag that's like, these people are members and these people are prospects, for example and you only want members to be able to log in previously, you'd have to do some, like, bending of time and space to prevent the other people from being able to log in.

[00:27:32] Now, you can actually say only allow people with this tag to have access. So cool stuff. 

[00:27:41] Dan Fellars: And it's, it's public, it's out there on the DareTable website that Airtable is coming out with its own client portal solution. And so, that is coming, I don't, I don't know the specifics of what it looks like, but that'll be interesting to see how this whole [00:28:00] softer Portal world shakes up when their table launches its own 

[00:28:06] Kamille Parks: previewed at the deer table conference and at least some of the sessions at deer table will be recorded.

[00:28:13] I'm not sure if that one will be but if not, well then the next episode we'll have more information. I've been there. 

[00:28:23] Dan Fellars: Yeah. Awesome. Let's wrap up a couple more. These are always good. If you'd like to see the behind the scenes. People are using air table to build cool things for their companies or use cases.

[00:28:38] Here's one from Daniel using AI to build kind of an AI assistant for his guitar shop and he stores everything in air table and then uses a couple of third party AI tools along with make to build stuff. So in this video he walks through how it all pieces together. So if you like a good tutorial.[00:29:00] 

[00:29:00] That's pretty cool. And then here's another one on using AI to, to generate SEO content inside of air table and store it all. So he walks through how that's all built. So if you like how to guides on, on how to build real world applications, there's a couple of videos for you. We'll link in our show notes.

[00:29:25] Yeah, let's move on. 

[00:29:26] ON2AIR BACKUPS - 00:29:29

[00:29:27] If you are already talked about backups if you're using air table as your. As a core piece of your business, best practice is to have your data backed up outside of Airtable. So that's where On3Air comes in. You can store your data in Box, Dropbox, or Google Drive, soon with with OneDrive as well.

[00:29:48] And also you can restore it. We talked a little bit about how that works, but you can restore your backups back into Airtable and recreate 80% [00:30:00] of your base. There's some things that can't be recreated due to Airtable limitations, but we have a nice workaround to help you overcome those Airtable limitations as well.

[00:30:09] So check it out onto air.com. Use como Pro promo code built on air for discount and get started today backing up your data. 

[00:30:20] FIELD FOCUS - 00:30:23

[00:30:22] Scott, you want to share your screen and walk us through advanced pivot tables. 

[00:30:29] Scott Rose: All right.

[00:30:36] Okay. Can you see my air table base? Yeah. Great. Okay, cool. So Dan, you were talking about, you were telling me that you didn't know if you guys had actually presented pivot tables on the show yet. And so I thought that I would give a presentation showing people how to access These magical things. [00:31:00] Called pivot tables that are in air table.

[00:31:01] They're sort of this, their pivot tables are cool because they give you the ability to sort of analyze large chunks of data and get, get clear insights from those pieces of data. And so I want to show you, and by the way, there's two different places where you can do pivot tables. In Airtable, you could do them in interfaces and they also have a pivot table extension as well.

[00:31:26] They're actually very similar in functionality. The interface one adds a few more options, but they're, they're almost identical. And so to showcase a pivot table I wanted to create a base that would, that might be a good example. And I came up with high school student grades. And if. You're tracking grades.

[00:31:51] Maybe you want to see what the trends are for grades over a period of years. You know, freshman, sophomore, junior, [00:32:00] senior, you want to see if the grades are trending in a certain direction, or you want to sort of get immediate insights across subjects and years of school. And that's what pivot tables do.

[00:32:12] That's why they're so magical is because they allow you to crisscross three different points of data, really two then one, which represents your numerical value that you're trying to evaluate. And so you might have a table like this where you have students and I've got them separated here, freshmen, sophomore, junior, senior, and you could see the students here in their current enrollment year.

[00:32:37] And then you might think that this would be a good way to keep track of Your student's grades, whether this is a number field or a roll up field or formula, you know, however you're doing it, you might think that you would just want your student to appear here once and then all the different courses coming off to the right as [00:33:00] different fields.

[00:33:00] So you have freshman English, freshman math, freshman science, freshman history, and then you repeat sophomore English, sophomore math, sophomore science. This is not a good way to structure your data. This is not just for pivot table purposes, but for any sort of database purposes. Whenever you find yourself repeating things over and over and over again off to the right, When you keep creating more and more columns, that usually means that you should be putting those things in another table.

[00:33:30] So this is not a great way. There is some analysis you can do here because you can get like the average per year. If you group this by student, you can get their average for each subject, but then you also have a lot of. Emptiness here. Like if this person, these people are only freshmen. So there's nothing filled in here and not a great way to go.

[00:33:50] So then you might think that this way is a little bit better. And this way actually is better. This is actually a step [00:34:00] closer in the right direction. This is. We're a grouped all of our students by student name here, and you can see that this person is a freshman, so they only have one row. So instead of breaking out the columns.

[00:34:15] like four subjects for each year, I just flatten them down to just English, math, science, and history. And then this person's a freshman. So they only have one line, but this person here is a senior. They're currently a senior. So they have four lines. When they were a freshman, this was their grades in all four.

[00:34:35] When they were a sophomore, this was their grades in all four. So this gets you a little bit closer to what we really want when we're creating pivot tables. And this actually might be a relatively good design if You know, if you weren't doing pivot tables, this actually gets you a little bit closer to really, to really good database design.

[00:34:55] But the reason that this sort of setup doesn't work for a pivot table [00:35:00] is because the key thing to remember with pivot tables, and this is sort of the key secret to pivot tables, is you have to flatten down your data. You have to flatten your data to just three columns only. The first column is what you want to be as the columns in your pivot table.

[00:35:21] The second piece of data has to be what you want the rows to be. And then the third piece of data has to be the number that you are trying to summarize in some way. And so you can see here, if we look at information that we might want on our pivot table, You'll see that we really want the year that the person was in and then the different courses.

[00:35:43] So the different subjects. So we're already up to 1, 2, 3, 4. We already have five columns here, and that's not going to work for a pivot table because we need to flatten the data down to just three columns, just three fields. So the best way to approach [00:36:00] this is to create a junction table. And Allie is going to be talking all about junction tables.

[00:36:05] On Friday at DARE table, and I'll just briefly show this junction table here. This is where, so a junction table is where you combine data from two other tables. And those two other tables become the, they intersect in this table. So what I mean by that is, Every single row here is the intersection of two pieces of data.

[00:36:37] It's the person's name. And well, it's actually, we actually have more, it's actually freshmen. This person's a freshman and this person, and then the subject is English. So for this particular student, what we've done is we've broken out every single year in class. And it's on its [00:37:00] own row. So for each row, there's only one grade.

[00:37:05] So does that make sense how I flatten this down? Basically, the student name is irrelevant for the purposes of a pivot table. I just want that there so we can see who the student is. But for the purposes of the pivot table, I have flattened this down to these three columns here, year, subject, and grade. So somebody that's a senior and has taken all four of those subjects They're going to have 16 rows here.

[00:37:32] If they took all four of those subjects for every year. So then once you have this down to these three columns, then you can create your pivot table. And by the way, I've set up this junction table typically a junction table. What it means is you're linking to two other tables. So from the year I've linked to the years here and for subjects I've linked to subjects, but you could also do this with.

[00:37:59] Single [00:38:00] select fields as well. If you want it to, that actually gives a couple of little advantages in the pivot table. So there's two different ways you can do this. So then let's go into interfaces here and I will show you, this is our pivot table. So because we flattened the data down into those three columns.

[00:38:19] Now, what we have here is we have a very nice pivot table. We're going across the top. We have the four years of school and going down. in the different rows here are the four different subjects. And now what we can do is we can actually look at trends and we could see what I've done here is this is the average grade per subject per year.

[00:38:40] So we can look at trends here. We could sort of see if we look at English, all the students who took English across the four years here, we can see that their numbers keep going up. So maybe that class is getting easier over time, or maybe they're getting better at it. And then with history, if we look at this, we can see there's a trend here that over the years, the numbers are getting [00:39:00] lower over time.

[00:39:01] So you could look at this and quickly get a glimpse at What might ordinarily seem like complex data analysis. This is another pivot table here. It's all the same data, but this is if we use it the single select fields instead of the link record fields. This is nice because you get a little splash of color.

[00:39:23] On your pivot table here. So if you want a little bit of added color, you could use the single select fields. And also these will automatically reorder based on the order that you've set your single select fields to. If you use linked record fields, it's always alphabetical. So that's why I actually put number one, freshmen, two, sophomore, three, junior, four, senior.

[00:39:46] So it'll always be alphabetical if you use linked record fields. But if you use single select fields, you can just drag and drop them into the right order in your single select field. And these are really, really easy to set up. [00:40:00] Basically, there's just a few things to select here. So basically, I've created a dashboard page here.

[00:40:07] And if you like, I'll just create one real quick. And so we're in a group here. So every group can be filtered its own way. And every group is set to a certain table that's your source. So what I'm going to do is I am going to create a new pivot table here. And this is actually pulling from our single select field, our single select table, because it's in the group there.

[00:40:29] And you can see here that it asks us, what do we want? Oh, did it just, did it just do the exact same thing? 

[00:40:40] Kamille Parks: No, it's doing, by default it'll do count as your summary. So it would change it to average you know, or median or whatever. Simba, thank you 

[00:40:51] Scott Rose: so much. Thank you. So I thought, I thought it actually duplicated what I'd done above, but thank you for pointing that out.

[00:40:56] The so basically going across the top here, you can [00:41:00] choose whatever field you want. I like using year for going across the top. Oh, I'm sorry. That's rows. This is columns. So I like using year because there's only four of them. And I think it's, you know, if you had tons and tons of subjects, I think it's better to scroll up and down.

[00:41:16] And then up here for the rows, I'm going to switch this to the subject. And then down here is where you choose what the values are in the boxes here. And so this is counting how many students took those classes. That isn't what we want. We actually want to see the average of the scores of their grades.

[00:41:38] So I'm going to go to field here. And it already knew that I wanted grades here, but this is a sum of the grades. We want the average of the grades. And you could also do median. You can also do the min max. You know, you could, they give you the basic summary options right here. And, and that's it. That's how easy it is to set up a pivot table.

[00:41:59] You know, [00:42:00] you can almost think of pivot tables as a way to actually use all these numbers. That are on the summary bars. These summary bars are not accessible to us in any formulas or in any automations, you know, we can't pull out those numbers and you still can't access them with automations or anything from the pivot table, but you can actually grab all those values from the summary bars and put them all onto a pivot table.

[00:42:31] And they also offer this ability. One X, one bonus thing here, click to see underlying records. You can click on one of these. And what I don't like is it actually shows you, it shows you that there were seven records that it used to create this box right here, but it doesn't actually let you customize what information you see here.

[00:42:52] You actually have to click in this again to actually see, oh, that was Harry Potter's grade from [00:43:00] his junior year when he took English. I would love to surface that up at this level, but you can drill down and see which records actually populated those boxes there. So that's it, just a brief overview of 

[00:43:18] Dan Fellars: We've got a question for you, Scott, from Alicia.

[00:43:21] Are number values the only type of data you can display in the grid boxes? I'd love to be able to output a word that comes from a formula field. 

[00:43:29] Scott Rose: That is a good question. 

[00:43:31] Kamille Parks: You'd be able to count the unique values of a word, but you wouldn't be able to say, like, dog in any of the cells. Unfortunately. 

[00:43:40] Scott Rose: Yeah, let me, oops, I went to the wrong thing there.

[00:43:42] Yeah, let's check. So if we have year going across the top subject going down there and yeah, let's choose student name. So these will be your options there. Yep. Filled. Is that what you just said? Filled, percent empty and percent filled. [00:44:00] So if we go filled, 

[00:44:02] Kamille Parks: it's, it's always the boxes will always show you a number.

[00:44:06] So no matter what field you are basing your summary, Value on the thing that's displayed to you is going to be some type of number. 

[00:44:17] Alli Alosa: Exactly. And you don't see the percent unique there because that's a linked record field student name is, but if it were a text field, it would show you percent unique as an option.

[00:44:27] It will only return a number. 

[00:44:30] Scott Rose: Nice. Very good question. I never played with text before with that. 

[00:44:36] Dan Fellars: Awesome. Thank you, Scott, for sharing that very good insights. 

[00:44:41] Alli Alosa: I, yeah, that was you. I love that, Scott. And I'm like, sitting here the whole time, I'm like, have you been watching over my shoulder as I've been preparing my dare table slides?

[00:44:51] Oh my god, dang it. No, it's perfect. It's great. It's a great primer. I literally, I used the same example and literally even [00:45:00] I'm like, what not to do? Exactly what you just showed. Are 

[00:45:04] Scott Rose: you actually using grades in students? 

[00:45:06] Alli Alosa: Oh yeah. 

[00:45:06] Scott Rose: Oh, no way. Oh my god. Allie, I just, I did not mean, I did not even know. I love 

[00:45:13] Alli Alosa: it.

[00:45:13] I love it. That's hilarious. It's a, it's perfect. So if you want to learn more. 

[00:45:18] Dan Fellars: That's funny. Friday in person, get the full demo. Awesome. Thank you, Scott. Hey, let's move on. 

[00:45:26] SCRIPTING TIME - KAMILLE PARKS - LINKED RECORD CONSOLIDATION - 00:45:26

[00:45:30] Kamille's going to walk us through some scripts to consolidate. linked record fields.

[00:45:38] Kamille Parks: All right. So this is a conundrum that's kind of come up a couple of times in My use of Airtable and there's pros and cons to doing things in the way I'm about to describe, but I've had a number of times where we've had a table that's linked [00:46:00] to a different table. In this case, companies are linked to contacts multiple times to represent multiple different data points.

[00:46:07] So in the case of Carol Group, Abby is the CEO and Biddy is the CFO and Port is the head of marketing and Patrice is the head of HR. So we wanted to capture all of these unique relationships of a contact to a company, but we also wanted a nifty feel that gave me everyone's emails. Or I wanted to see all of these people put on a chart.

[00:46:34] easily. But because they're separated into different fields, that's somewhat difficult in Airtable to have it recognize that all four of these fields are representing people from the same or records from the same table. So we often include something that's called like all contacts in this instance, where this is my desired end result.

[00:46:57] It's all four of these people [00:47:00] placed into one field. And that allows me to have a single lookup that is pulling everyone's email in one go. So there's a bunch of different ways that you could do that. arrive at this end result. You could have a formula that concatenates all four of these together and then an automation that paste it into this field.

[00:47:20] You could go one by one. So I could, begin to type, you know, have it manually filled in, but of course this is not, ideal. So what I did was develop a script that automatically pulls people in. together. And before I do that, I did want to demonstrate why it's done with a script rather than just saying an automation.

[00:47:46] Whenever those four values are updated, update that same record in the all context table with those four values comma separated. There's a number of reasons why I don't do this. [00:48:00] One as you put these in, by default, the display is going to be name, and I had to manually change it to ID for each one.

[00:48:07] There's no way of knowing visually on this screen that it's set to name or ID. So if something goes wrong, it's kind of hard to debug. You have to click on each one, one by one. And the example that made me think of doing this on the podcast, there were 20 or so link fields, with very similar names amongst them.

[00:48:27] So clicking and investigating each little pill was a nightmare. And then the second being, if any one of these is empty, Automation oftentimes errors out because it'll see two commas next to each other and say, well, that's not a record ID or record name. I'm going to error out. That could be resolved with a formula that conditionally concatenates record IDs of all four of these fields.

[00:48:56] However, then you would need a lookup of each of these fields [00:49:00] of the original contacts record ID, which means you're at least doubling. All of the different link fields that you have, and I didn't want to do that. So instead, I wrote a script. So, In this script, what I've done is I've predicated it on a single field, context modified time.

[00:49:21] So if I unhide this one last modified time field, and I look at it, I'm only watching these four specific fields. And what it's doing is it allows me, if I look at my script, handily in Airtable scripting, if you have a last modified time or a last modified by field, there's a property returned that's called referenced field IDs.

[00:49:51] So I'm able to dynamically pull in every single field that one last modified By time field is watching for updates, [00:50:00] and then I'm able to parse out each of those fields values, consolidate them down, and then return an array of linked record I. D. S. Now I've written this specific iteration of the script to be flexible so that, if I wanted to, include this on multiple different tables, or if I wanted to include linked relationships from multiple tables using one watched field, I'd be able to do so.

[00:50:35] So in this example, I also have service areas where I have the primary service area and then the other service areas and all service areas. So, if I test out my script and hopefully this works, you'll see I started to fill this in. When I'm expecting, when I add one more person to this, [00:51:00] record, this modify time was adjusted, which means the automation should be running in the background.

[00:51:07] I'll open it up. and watch it. That's right now. It says it's complete. Ooh, cool. I have a bug to fix. So it did part of it and not the one that I just did. So I'll investigate that in the future. The end result is would be that all four of these fields values would be consolidated into this one output. And if I were to start watching for updates in these two fields as well, I'd be able to output the value into this field using a single automation.

[00:51:48] So with this sort of structure, for a single table, you could have one automation control all of your different consolidated linked record [00:52:00] fields. If I look at my last step in this automation, you can see I'm pulling in the value of just one table. But if I wanted to include my all service areas, I'd be able to add it to the automation here.

[00:52:14] That would be the only change that I would need to make on the automation side. I'll provide this script out to the community after I, Make some adjustments to figure out why that last record didn't make its way through. But you could handle the actual updating of the record from within the script itself.

[00:52:33] And then you never have to update the automation, you never have to update the update record step because you don't need it. I chose to separate it out because I like having automations fail at the script level rather than at this level because I can control exactly what happens. The error says in the automation when it doesn't work.

[00:52:52] It is a personal preference, but you are welcome to customize the script once I publish it. 

[00:52:57] Scott Rose: Kamille, is there an easy way in the [00:53:00] script to combine all four fields, even if like one or two of them are empty? So you don't get that multiple comma thing in your array that you're sending. 

[00:53:08] Kamille Parks: Yeah, so what's happening is within the script, if I look at sort of the logic I've baked in here automatically.

[00:53:15] So the first part is I'm only pulling out of all of the watched fields. If I watched every field in my table, I only want the ones that are linked record fields. That's this part. And the second part is looking at whether or not there is a value. So if the record that triggered the automation has a cell value for I'm iterating through each of the watched fields.

[00:53:37] In this case, I'm watching four. If there's a value, give me the ID of the linked record within that value because it's going to return an object. I just want the ID as a string. If there is no value, if it's blank, then give me an empty array. So I'm not really sort of treating it differently. I'm just sort of forcing it to always be [00:54:00] an array so that my script can sort of run consistently.

[00:54:04] And then I'm using sets, which in JavaScript allow you to pass the same record ID five times, but it only will appear in the end result once, because it's possible, if I go back to my example, it's possible that Terry is both the CEO and the head of marketing. Well, I only need Terry in this last field once in that case.

[00:54:25] So, you know, that's already handled in the script as well. And Airtable wouldn't let you link to Terry twice anyway, but You know, it's helpful if the output matches what air table would resolve as in the first place. 

[00:54:38] Scott Rose: Very nice. Right. Very nice. Good 

[00:54:42] Dan Fellars: stuff. Awesome. That's a grateful. Yeah. So if you want to share that, we'll post it on the link and very useful.

[00:54:52] Thank you. Let's wrap 

[00:54:54] NA: up real 

[00:54:55] Dan Fellars: quick. 

[00:54:55] BUILTONAIR AIRTABLE COMMUNITY - 00:54:55

[00:54:57] If you are not [00:55:00] in the built on their community, please join us. Amazing people in there talking every day about How to use air table and helping each other. So it's a great place built on air. com slash join. We'll get you in also access to our new and improved newsletter to keep you up to date on everything in the no code, low code world.

[00:55:18] And you can join us. 

[00:55:19] SCRIPTING TIME - FIELD SEARCHING - 00:55:21

[00:55:21] Let's finish up Allie with field searching. 

[00:55:26] Alli Alosa: All right. Okay. So I was going to kind of write this on the fly, but to save time, I'm actually going to just go find my existing one and then just talk about. What it does. So I save a lot of the scripts that I use all the time in this little repository that I built and I'm just going to paste this in here.

[00:55:53] So this use case is say. If you're like me and you've got bases [00:56:00] that are just, like, unruly that you've been using for years and you've got fields everywhere that, like, you don't remember what where they might live or, like, you're like, I know I made a field that had this in the name. I don't know what table it's on.

[00:56:13] I don't know where it might be. This script is gonna help you with that. So essentially what it does is if we run it, we can search for a string in a field name. For example, let's just search for, I don't know, date. And so this is going to come back with a table of all of the fields in my base that have the word date in the field name.

[00:56:45] And it's going to tell me what the field type is and then what the table is that it's on. So if you needed to find, you know, something specific that you're like, I know I had it, then this [00:57:00] is gonna really help. Like if I search for location, now it's giving me all of the different fields that have that word in the name.

[00:57:07] Maybe it's an emoji, maybe it's, you know, literally any piece of a string. So the way that this works, and I don't know why this is erroring here, because clearly it doesn't. It really bothers me when I have an error that I can't figure out why it's there. It says it's not callable, but it is callable.

[00:57:30] It does, it works. I don't know why. Kamille, do you have any insight on why this might be? 

[00:57:35] Kamille Parks: No, my, the script I just showed also had an error which was impossible based on the checks that I had. It's just, it's very like pessimistic about, are you sure that your data will work? Yes, I'm sure. 

[00:57:55] Alli Alosa: So essentially what I'm doing is, side [00:58:00] note, I have this output title script.

[00:58:02] That's why I have title here twice because My output title function, I was sick of writing output dot clear and then output markdown, blah, blah, blah. So I wrote this little function that I put in all of my scripts so I can pass to it. If I just write output title, it clears everything and then outputs whatever the title is I've defined.

[00:58:23] So like right here, then I really like to have the script settings set up. So if I reload this. It'll just show up with the title right here. I could add more to this screen, but you can't access this title programmatically later in the script, which is annoying. So I had to put it twice. So anyways, that's why that's there.

[00:58:52] But basically all it does is it's just gets all the tables, it, you input, [00:59:00] text, and then. It, we've got a empty array, so I want to push all the matches that I find to an array that I can then display as a table in, My finished output, and then it just loops through every table and then loops through or doesn't loop through but grabs all the fields on each table and filters them down.

[00:59:22] And the important thing here to note is that I'm saying field dot name dot to lowercase. For both the field names that I'm looking through and the search term that I've input into here. So if I put location all in uppercase. Or if I have just an uppercase L and then, you know, proper casing here, this will still work, even if the field name was in all caps or, you know, all lowercase, I'm transforming them all to lowercase, or you could do it to all uppercase so that no matter what somebody's entered, [01:00:00] you're still going to find the proper matches.

[01:00:03] And then it just. Pushes those matches to my empty array for each table. And then I can, and this is just transforming it as what I want the table to look like. So you could change this to add other fields if you wanted to know, like, you know, the description, we could add the field description, which I'll do really quickly.

[01:00:29] Field dot description. It's probably going to be empty for all of them, but, because I'm really bad about using field descriptions. But now it's empty. Trying to get better at that. But yeah, this is a really helpful. 

[01:00:43] Dan Fellars: Very 

[01:00:44] nice. 

[01:00:45] Alli Alosa: Yeah. 

[01:00:47] Dan Fellars: Yeah, I definitely see how many hidden fields you've got across the base.

[01:00:52] Alli Alosa: Yeah. Like I saw one when I searched for the date, there's one that it says out on explore date. [01:01:00] What does that mean? Explore date. Like what is, I want to go on an explore date. Well, 

[01:01:10] Dan Fellars: you could, the next thing I would add to that is you can now link to a field so you can have a URL that's unique to that. So you could add the link to it.

[01:01:19] So you could go directly to the field. 

[01:01:22] Alli Alosa: Yeah. Absolutely. 

[01:01:26] Dan Fellars: Awesome. That concludes today's show. We hope to see everybody in a couple of days. Excited to see you all in a few days and we'll hang out at their table and try to see, we might no, no promises, but if it works out, we might try to do a live show.

[01:01:42] Get as many people as we can on and talk to you all. So come, come meet us and see if we're doing that. And otherwise we will be back next week for episode four. See y'all [01:02:00] then.

[01:02:12] OUTRO - 01:02:14

[01:02:13] Thank you for joining today's episode. We hope you enjoyed it. Be sure to check out our sponsor, Ontoware backups, automated backups for air table. We'll see you next time on the built on air podcast.