1/23/2024 – BuiltOnAir Live Podcast Full Show – S17-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.

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.

Scott Rose – Scott Rose is an expert Airtable consultant, a Certified FileMaker Developer, and a Registered Integromat Partner with 30 years of database development experience. Scott is the Chief Geek Officer of ScottWorld.com, where he has built a career developing world-class database systems for businesses. Scott is also a member of MENSA International (the high IQ society) and is an accomplished public speaker. In the early 2000’s, Scott traveled around the country for 6 years with Steve Jobs & the Apple Executive Team as one of Apple’s top professional speakers. Scott spoke at all of Apple’s major events & retail store openings, where he introduced many of Apple’s new products to the public for the very first time. In his free time, Scott gives motivational & inspirational talks at conferences around the globe.

Show Segments

Round The Bases – 00:01:40 –

Audience Questions – 00:01:41 –

Scott Rose answers the Airtable question: “How to DeDupe Duplicate values without the DeDupe App”

Answer: Scott Will share a trick using linked records, rollup fields, and lookup fields that lets you quickly delete a ton of duplicates in one fell swoop

Field Focus – 00:01:42 –

A deep dive into the Many to Many Relationships Linked Record –  

Automate Create – 00:01:43 –

Watch as we review and work through automations. Kamille will continue her invoicing demo (check out how to do eSignatures in last weeks episode). This week she shows how to generate invoices in Stripe and connect to Airtable.

Learn more about the automation

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: Audience Questions

Start Time: 00:01:41

Airtable Question – How to DeDupe Duplicate values without the DeDupe App

Scott Rose answers the Airtable question: “How to DeDupe Duplicate values without the DeDupe App”

Answer: Scott Will share a trick using linked records, rollup fields, and lookup fields that lets you quickly delete a ton of duplicates in one fell swoop

Segment: Field Focus

Start Time: 00:01:42

Learn about the Many to Many Relationships –

A deep dive into the Many to Many Relationships Linked Record –  

Segment: Automate Create

Start Time: 00:01:43

Airtable Automations – Invoicing With Stripe

Watch as we review and work through automations. Kamille will continue her invoicing demo (check out how to do eSignatures in last weeks episode). This week she shows how to generate invoices in Stripe and connect to Airtable.

Learn more about the automation

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 community at BuiltOnAir.com. 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, On2Air 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 On2Air backups. Sign up today with promo code built on air for a 10 percent discount. Check them out at On2Air. com. And now let's check out today's episode and see what we built on air.

[00:01:38] Dan Fellars: Welcome to the built on air podcast. We are in season 17, episode three. Good to be back with you. Dan fellers here. I'm coming in, checking in from an airport. So you may hear some. Background noise for me, but good to be with you. We've got Kamille Parks with us and Scott Rose back. Welcome. 

[00:01:57] Scott Rose: Thank you so much.

[00:01:58] Dan Fellars: Good to see you all with [00:02:00] us. We are excited for the show today. We'll be walking through. I'll walk you through what we're going to be talking about. Just a second here. As always, we start off with our around the bases to get you up to date with what's going on in all the communities. What's new in Airtable, then a quick shout out to On2Air backups, our primary sponsor, then Scott's going to walk us through a couple segments going to first talk about how to dedupe duplicates without using the dedupe app.

[00:02:29] And then he's going to also talk about using many to many relationships and better understanding those. Then a quick shout out to join our community, and then we're going to wrap up with Kamille continuing her segment from last week and now talking about invoicing using Stripe. So excited to see what we've got coming 

[00:02:48] Scott Rose: up.

[00:02:49] Dan, it's so great that you are even at an airport, the show must go on. You are still here. 

[00:02:56] Dan Fellars: That is right. Even if 

[00:02:58] Scott Rose: you had to do it [00:03:00] from an airplane itself, I bet the show would. 

[00:03:03] Dan Fellars: Yeah, I purposely booked a flight. They gave me time to, to, to do the show. And so I got to the airport early, so I could, so I could finish it from here.

[00:03:15] ROUND THE BASES - 00:03:16ON2AIR SPOTLIGHT - 00:23:12

[00:03:17] All right, let's go. Let's first, we're going to spend a bunch of time in the built on air community, a couple interesting chats of what's going on first from Justin or friend of the show talks about kind of brings up an interesting point, how an automation can be triggered. When a record is updated, which includes calculated fields, meaning that the modified time of the calculated field has to be tracked somewhere, but the table based trackers for last modified time.

[00:03:49] The field type and related formula functions refuse to track changes on calculated fields. I don't get it. Scott, you replied. 

[00:03:59] Scott Rose: Oh, [00:04:00] yeah. What's interesting is, wait, yeah, the thing is, is that the automations, automations, if you're watching a record for changes, automations can monitor a formula field. Wait a second, I forgot what I wrote.

[00:04:20] Can you click on my link real quick? Yeah,

[00:04:25] it's strange because even though the last modified time field can't monitor. Oh, I see. If you, that's right. If you create a last modified time field in your base and you want it to give you the last modified time of one of the fields, it doesn't give you the option of monitoring when a formula field changes.

[00:04:45] However, what's weird about that is that Airtables automation. can monitor whether a formula field has changed or not. So if you're hoping to monitor when a formula field is changed, [00:05:00] you can't do it with Airtables last modified by fields, but you can do it with Airtables automation, automation triggers.

[00:05:10] Kamille Parks: Yeah, I, I have a guess, and I have no way of verifying if it's accurate. Knowing me, it's probably not. I feel like they're trying to get around triggering based on changes to now and today, because those would be like constant things to their servers as those are updated either. Like, every 5 minutes or every hour or whatever the interval is, it depends on if someone has the basic thing, but like, with automations, I guess they have a better hold of, like, when to actually monitor for those changes.

[00:05:52] If that makes sense. Just a guess, because I feel like with formulas, a lot of times you can derive it from whatever the, like, [00:06:00] source field, the formula is referencing changes. Because it has to be based on something to change it all, except for time based things, which I feel like is why they might not let you monitor it.

[00:06:13] Scott Rose: Interesting. Interesting. Yeah, that could 

[00:06:17] Dan Fellars: be it. So, yeah, but I thought it was interesting. I didn't think about that. They actually must be tracking. You know, somewhere inherently for it to trigger automation. So somewhere they are tracking. 

[00:06:30] Kamille Parks: Has anyone tried with the web hook API? Because from my recollection, I don't remember if there's any restriction on what you can scope for changes based on, their web web hook API. I think it can also target formula fields, but I could be wrong. 

[00:06:51] Dan Fellars: Yeah, I'm not sure on that. Good to look into that next time. 

[00:06:56] Scott Rose: Yeah, maybe their documentation says it. Something worth [00:07:00] 

[00:07:00] Dan Fellars: looking into. So yeah, definitely something to be aware of. You can't really rely on those calculated fields if you want to use those to trigger views and things.

[00:07:14] All right, one more. This is just kind of a follow up from Last week we talked about it. They had some pretty major outages last week on the automation, super slow, had to rebuild them. And I think this was only, was it only for automations that use scripts? 

[00:07:33] Scott Rose: Yeah, apparently just for automations that use scripts, major failures and outages.

[00:07:39] Dan Fellars: So they did send out an update. Mecca mentions, like, this is the first time they've actually, like, sent out a message like this, kind of quasi apologizing. 

[00:07:51] Scott Rose: When you say quasi, which was the quasi part? There's 

[00:07:55] Dan Fellars: no actual, we're sorry. Oh, [00:08:00] 

[00:08:00] Scott Rose: right. Exactly. Yeah. 

[00:08:02] Kamille Parks: Yeah. As Russell kind of points out, they provided a list of your automations that were possibly affected.

[00:08:11] He asked who had the longest list. I checked mine against all odds. That's only one that surprised me, but I also don't know how exactly they're attributing automations to like people who work in teams like myself. You know, I work on a team with like 10 different creators on a workspace. So which one of us.

[00:08:33] Did we all get the same email with only one that failed, or did I get some and they got some others? I don't know how they made that determination. 

[00:08:41] Dan Fellars: Yeah, yeah, but at least it was good. I think this was probably based on feedbacks. I know. Through different channels, people were asking, like, Hey, where's where's the status update?

[00:08:52] What's going on here? 

[00:08:55] Scott Rose: Yeah, I got a ton of those emails and a ton of a list of a lot of [00:09:00] automations. Yeah, 

[00:09:03] Dan Fellars: yeah. All right, next one. So this was interesting. I don't know if this is a bug, but Max points out that single select fields are now displaying empty in the grid of records component of the Gmail send email action, having to convert them to single select fields to plain text in a formula field.

[00:09:25] So I don't know if anybody else has seen that bug with Gmail sending. 

[00:09:32] Kamille Parks: Yes. One of our, one of our automations was experiencing the same bug. We weren't using Gmail. It was applicable to anything you try to insert the grid into it as single select. And I think multi select to just simply didn't appear.

[00:09:51] Scott Rose: Interesting. It looks like that. Oh, go ahead. 

[00:09:56] Dan Fellars: I was going to say, is this only when you're configuring it, it shows, but if [00:10:00] you already had one configured, does it still send correctly? 

[00:10:04] Kamille Parks: Ours, in our case, ours was already configured. It was an old automation that had been in circulation for a while. It just stopped working randomly.

[00:10:13] It was one of the first times, like we experienced like a clear and present bug in Airtable that at no point where we're like, maybe we did something wrong, it was like a column, like all of the values disappeared and we had no explanation for it other than this is a bug. 

[00:10:34] Scott Rose: It looks like that was six days ago.

[00:10:35] Do you know if that's been fixed by now? I believe 

[00:10:37] Kamille Parks: it's been fixed at least for our automation. I can't speak to like globally. And I also don't know if we ever got like inclination of like what happened or what went wrong. 

[00:10:50] Dan Fellars: Okay. Well, if you're having issues with Gmail and we don't know for sure if it's just Gmail or if it's any if it's also the, the Airtable email sending.

[00:10:59] [00:11:00] It was the 

[00:11:00] Kamille Parks: regular Airtable email for us. 

[00:11:02] Dan Fellars: Okay. So it's more on the component level. Hopefully that's that's fixed. All right. Here's another one. Jeremy puts out a or puts a link. 

[00:11:18] Scott Rose: I won't do that, but 

[00:11:20] Dan Fellars: I'm just kind of referencing kind of cool. There is actually a book. I think it's an O'Reilly. book on their table.

[00:11:28] So it's probably the first actual physical book written about their table. I was hoping to click on it to get the author's name. I know he presented at one of the dare 

[00:11:38] Scott Rose: tables. I think you can go click on it and just type in the code to get to the next. Yeah,

[00:11:49] there we go. Yeah, 

[00:11:50] Dan Fellars: learning Elliot Adams. That's who it is. Yep. So shout out to Elliot wrote a book, first book I'm aware of, like actual physical [00:12:00] books. So it's kind of cool. 

[00:12:03] Scott Rose: I wonder how Elliot is going to keep up with the changes. Cause I know that my, I have an online course at LinkedIn learning, and I have to constantly be updating it.

[00:12:13] So I wonder how. That's going to be quite a task for Elliot to keep up with Airtable. Yeah, it's 

[00:12:20] Dan Fellars: first edition. So you just keep creating 

[00:12:23] Scott Rose: additions. But that's wonderful that when I was first starting off, I was looking for some significant reference. So it seems like that'll be a good one. Yeah. 

[00:12:36] Dan Fellars: Okay, 1 more for Max.

[00:12:37] Also, this also in the email vein. And I think Kamille, you want to you help them out. So he's talking about how to do markdown in sending emails through Gmail as well. 

[00:12:49] Kamille Parks: Yeah. So he specifically wanted to highlight something and Airtables. Markdown is a. Pretty pared down version of Markdown. There's a trillion versions, but [00:13:00] this one is specifically having very like basic text formatting and highlighting's not included.

[00:13:06] But your email actions I believe all three Outlook, Gmail, and Airtable's generic one all support HTML formatting. So if you use a span with a style attached, you can, you know, force your own highlighting. 

[00:13:24] Dan Fellars: Yeah, so it's really weird. This mix of like some Markdown with some HTML. I know I've done both in the same email template.

[00:13:34] Kamille Parks: Yeah, I was never able, supposedly Markdown tables should work. I was never able to get them to work. So they're always HTML for me. 

[00:13:44] Scott Rose: Yeah, 

[00:13:46] Dan Fellars: that would be a good guide on, on what you can do with, with email templates. There's lots of nuance there. All right, here's one from Ben. Ben Bailey asking about list [00:14:00] views.

[00:14:00] Why do records have handles, but we can't manually rearrange them? Good 

[00:14:05] Scott Rose: discussion there. 

[00:14:12] Dan Fellars: So and that's where Josh said so you can move them to another group if you're using levels, but I think you still, they still have the handles by handles. It means like those, those three lines that make it look like you can drag them. Up and down. So 

[00:14:30] Kamille Parks: you, you can drag them to different groups and to different levels, but yeah, you can't resort.

[00:14:39] Scott Rose: Within the group, I guess, correct. 

[00:14:41] Kamille Parks: Within the group, they can't be resorted. 

[00:14:43] Dan Fellars: Yeah, so they're for grouping, but not sort of, yeah, 

[00:14:47] Scott Rose: that's disappointing. 

[00:14:52] Dan Fellars: Yeah, that can be confusing if you're trying to reorder order is kind of a tricky thing, especially in the view [00:15:00] world. Like, sometimes you can keep order, but sometimes you can't.

[00:15:05] It's kind of tricky. As far as like manually ordering 

[00:15:10] Scott Rose: things, right? Yeah, like, yeah, galleries, let you reorder grid view, let you reorder if it's not already sorted or grouped. Kanban, you can 

[00:15:23] Dan Fellars: order. So 

[00:15:27] Kamille Parks: I think, well, no, I was gonna say not, not in calendar. I think possibly, maybe if events have the exact same timeframe, it might let you reorder them, but I can't be sure about that.

[00:15:42] I think it really is. Yeah. Great. Kanban and Gallery are the, manual resort ones. Hmm. Yeah. 

[00:15:49] Scott Rose: Yeah. 

[00:15:51] Dan Fellars: Very good. All right. Two more. This was one on, on Twitter. This Ken is kind of a, oops, [00:16:00] expert on Tableau and brought up. And I'd also seen this on other channels. If you're using Tableau and you're using Airtable, Airtable does have kind of a, a way to get data out of Airtable into Tableau.

[00:16:14] But this team here at Easy Apps created a Tableau To Airtable software bridge web data connector. So if that's important to you, I thought this was worth checking out. I know if you're heavy data user and using tableau getting your data from Airtable into theirs is not the most straightforward thing.

[00:16:37] So Shout out to easy apps for, for building that bridge. So you can check that out if that's relevant to you. And finally, the last one coming from the table forums and Scott helps Scott runs this forum, check it out to air that table forums. com. But actually Scott, you posted this, you found a cool feature that [00:17:00] was.

[00:17:00] Not announced. Tell us what 

[00:17:02] Scott Rose: this is. Yeah. There's a brand new piece of metadata that you can use in your Airtable automations called user 

[00:17:13] Dan Fellars: action. Again, you may want 

[00:17:15] Scott Rose: to mute yourself and hear that. Yeah, thanks so much. The, yeah there's a brand new metadata you could do in the automations.

[00:17:24] It's called user who took the action, and I think they meant to call it user who triggered the automation. Like if they're using, oh, they don't. Okay. Kamille's gonna help. 

[00:17:34] Kamille Parks: This is related to parity with their webhook API. 

[00:17:38] Scott Rose: Okay, so maybe you can explain this, but basically it looks like whoever starts the automation, whoever was the user that caused the automation to start, you can grab their name, their I.

[00:17:51] D, their email, and their photo. Well, their profile photo as well. So the four elements that you can get through the API and what were [00:18:00] you going to say Kamille 

[00:18:00] Kamille Parks: about this? You can also get what type of user they are. So there's regular users who are like people, their service accounts. And then there's if it was triggered via like your personal access token that can be derived from that as well.

[00:18:16] The, the reason it's called user who took action and not who triggered automation is because it's, it's looking at Regardless of what the trigger for the automation is, it's let's say your your trigger is when record matches conditions, you know animal equals cat. What Airtables actually looking at is, is like intrinsically the change in data value.

[00:18:43] And so it's, it's really saying user who changed the Animal to equal cat. It doesn't care that there is an automation. It's just providing you that additional information, right? It's 

[00:18:57] Scott Rose: the person who [00:19:00] caused it to trigger. Yeah, 

[00:19:02] Kamille Parks: because it could be, it could be a lot of different things. This, this appears in, in a, in a couple of different triggers.

[00:19:13] And like, from a terminology standpoint, I think they're trying to stay close to their. Okay. Some language that they've already used in their their docs where it's, like, even calling it a user is, you know, it's they've moved away from the term collaborator because that's not the case. Really? When you have a service account and these other kind of like, quasi people who can make actions on your base.

[00:19:42] And so they're. Like, they're trying to skirt the line as well as they can to describe what it is they're doing, but it does let you, like, fake a last modified field. If you don't want it always [00:20:00] filled in, for instance, you can now pull from this property user who took the action. To, like, populate a regular user field if you want it,

[00:20:12] Scott Rose: right? So whether it comes through the API or whether it comes from the service account, basically, all those things are tied to an account. Of some purpose accounts, our accounts. So whatever user basically triggered this whole thing to take place, that's the data you can get from it, even if it comes from an external 

[00:20:31] Kamille Parks: source.

[00:20:31] Correct. Now it's not going to tell you from make, you know, it's not, it's not that specific because Airtable doesn't have that kind of information, but it will tell you like, if it was triggered by an API action and not like. You know from interacting directly within our table 

[00:20:46] Scott Rose: itself, right? Well, the make you will be through a personal access token, which will be linked to an account basically 

[00:20:52] Kamille Parks: Yes, it'll it'll say like, you know, the context is that it was a personal access token But it won't it won't tell you [00:21:00] it's a make thing unless you named your personal access token make 

[00:21:03] Scott Rose: of course, right?

[00:21:04] Right. That's what I do. I typically name my personal access tokens based on the external App that's using them good practice. So this is cool because in the past you, you would have to create a whole bunch of. Last modified fields. If you wanted to know who modified a certain field last, which then triggered an automation.

[00:21:24] This so this could save a lot of time.

[00:21:29] Dan Fellars: Very cool. It would be 

[00:21:31] Scott Rose: cool. People had someone said in slack. It would be cool if Airtable had very specific release notes. So we would know things like this came up. But I 

[00:21:43] Kamille Parks: think Okay. I think it was you who sort of, you know, maligns that Airtable doesn't really have very good release notes. They sort of do a bulk update at the end of the month and it, it doesn't cover like everything.

[00:21:57] It's normally the big stuff and little stuff like this [00:22:00] kind of flies under the radar all the time. And then I brought up Retool's release notes. I spent a lot of my days complaining about Retool now, but one of the things that they're. Like excellent about is their release notes. Literally everything is in there.

[00:22:15] They're very long, very detailed. But they're searchable and things like this are always included. So, you know, it's not like there's no precedent, at least for the tools that I work with. Now Retool has versions and Airtable does not. So I suppose it's like, you get even longer release notes because you can't be like, oh, you're on this version versus these other customers are on this version, but that still.

[00:22:45] Doesn't mean that you can't have a more consistent list of updates published like this 1. 

[00:22:51] Scott Rose: right? 

[00:22:53] Dan Fellars: Yeah, well, let me, let me jump in here while there's no backgrounds going on. So, [00:23:00] that that's a good, good summary of what's going on. Keep you up to date on everything on around the bases. Quick shout out, Scott, if you want to get your, your screen ready for your segment, but for that, I'll talk a little bit about On2Air.

[00:23:14] ON2AIR SPOTLIGHT - 00:23:14

[00:23:15] If you're using Airtable for your business, best practice is to make sure your data is backed up. We now back up schema information as well, which also includes, Formula information. So if you want to make sure you're keeping your, your formula backed up in case and worst case scenario, you can sign up for onto where backups that On2Air.

[00:23:38] com use promo code built on there to get a discount. So check it out On2Air. 

[00:23:45] AUDIENCE QUESTIONS - 00:23:46

[00:23:46] With that, we're going to go to Scott and it's going to answer a common question, de duping, there you 

[00:23:54] Scott Rose: go. All right. So one, issue that comes up a lot for [00:24:00] people, particularly when they're migrating their data from old systems to Airtable.

[00:24:06] Is how to clean up any potential duplicates. Of course, the duplicates could come up at any time, depending on how your system is set up, but this came up the other day with a client of mine who was Migrating into Airtable from a whole bunch of Excel spreadsheets that had his clients on them, but the client's names and their emails and their phone numbers were the same on many different spreadsheets, and then there were many different people, too.

[00:24:36] So we imported all of them in. And then it became a question of how do we clean up the duplicates and what would be an easy and quick way to clean up the duplicates once that once you've got them all into Airtable. So one way to do it is to use Airtable's dedupe extension. But the problem with the dedupe extension [00:25:00] is that you have to go through each group individually.

[00:25:05] So in this particular example here, you could see down here at the bottom, we have 243 groups of duplicates, and so that could take quite some time to go through here, pick which one you want to use as your primary record, then merge the other two into it, and then you have to go through that 243 times.

[00:25:26] To get rid of all the duplicates in the system. Now, the good news about, or the good thing about the dedupe extension is that it lets you edit the records here. You can see if there's any data that's different. There's a thing here called hide identical fields. So this actually rolls up all your data and only shows you the data if it's different.

[00:25:51] Between the three. So you can sort of see if there's a slight deviation or slight variations from one record to the [00:26:00] other. So that's sort of one of the advantages here, but if you know that all your duplicates are the same, you know, it's pretty much just the, in this example, first name, last name, phone number, email, street address, and city.

[00:26:14] If I would sort this by first name here, you will see that. That we've got a bunch of duplicates here. And in this particular case, the data is the same. So we don't actually need to cherry pick and look through and see, Oh, well, we have John Smith here has one email address, but John Smith here is a different email address.

[00:26:36] That would actually mean two completely different John Smith's in this case. So how could you quickly go through all your duplicates and clean them up? in a situation like this? Well, it's actually very easy. What you need to do is figure out which one of your fields is going to [00:27:00] serve as a unique value.

[00:27:03] Now, in this particular case, we actually have a couple of unique values here. We have a phone number, which would be unique for each for each contact. And we have an email field that would be unique. Now, if you didn't have something like that, You could just create a we also have street address as well And if you didn't have that you could just create a formula field that combined A couple of your different fields together until you have something that's unique.

[00:27:30] So in this particular case let's use email as our unique identifier for each record. And this is where the trick comes in. The trick is in Airtable, what you want to do is you want to take your unique identifier field and you want to convert it into a linked record field. So I'm going to turn this into a linked record field, and then I'm going to create a new table.

[00:27:58] And you want to [00:28:00] make sure that this is also turned on, allowed linking to multiple records. And then you're going to click on save. Oh, actually that one doesn't make a difference in this case. So you could have that on or off right now. And then you are going to click on save. And so what Airtable has done is it has converted this field right here into a linked record field.

[00:28:23] But the trick is that Airtable does these one at a time. It goes from. I'm assuming top to bottom. And the thing is, is that it will only create this record once in the other table. So here's the new table that we just created of all of our email addresses. And so even though this email address exists twice here, Airtable only creates it for the first record, and then any subsequent records It will match that record to the record.

[00:28:58] It already [00:29:00] created. So if we go over to the email table right here, you'll see that each email address. So here's the email address. That, actually serves as the linked record field, cause this is the primary field. And then here you can see that some of these email addresses are actually linked to more than one contact.

[00:29:23] So that is the only thing that. That that you need to know because that's what makes the trick work. Basically the conversion will only take these values and it will only make them appear once In your other table. So now we can look through here and I could this says name, but of course I could change this to say email address.

[00:29:50] So what we have here is this is in essence. A D duplicated list of email addresses. There are no duplicates [00:30:00] going down this field right here. So we have 500 unique email addresses, but each email address might be linked to more than one contact. So that helps us on this table, right? So now we know we only have 500 unique contacts.

[00:30:18] But how does that help us back here? If we want to clean up the contacts here, what we need to know is we need to try to figure out whether this person or this person or whoever, if they are part of, a group of an email address that has more than one contact. So this is how you can do that from this table.

[00:30:41] You can create a count field. And I'm going to make this count field type and this will give you the count of how many linked records you have in whatever linked record field that you point to. So if I create this field here, this will now give [00:31:00] me the count of how many email or how many contacts. Are linked to this particular email address.

[00:31:07] So in this particular case, we have three of these people who are linked to this one email address. So again, that helps us on this table, but how does that help us back on this table? Well, it doesn't yet. So what we need to do is, cause we don't know. Right now we can see this because this is sorted alphabetically, but we don't necessarily know just by looking at this.

[00:31:28] If these weren't sorted alphabetically, we wouldn't know whether this person was duplicated more than once in this table. So all we need to do is create a lookup field to that new table. And now we're going to bring in that count field. And so I'm going to add that field here. And now we can tell so now no matter what order these contacts are in We could see that if we look at this person right here This person is part of a group of two.

[00:31:59] And then this [00:32:00] person here, she's part of a group of three. And again, we could see right here, one, two, three, but these might not be in this order. So that's getting us a little closer to where we want to go. If we group this now by the count what we can see here is these are all the people Where they're only part of a group of one so none of these people are duplicates So we can actually just collapse this group.

[00:32:28] We don't need to worry about them at all But we do have additional groups here. We have people who have been duplicated twice And we have people who have been duplicated three times in our base. So now what we want to do is we want to leave just one person from each grouping. So how do we do that? Well, what we want to do next is we want to figure out how we can identify only one person from each group and.

[00:32:59] All the [00:33:00] rest of those people, we just want to get rid of. So what we can do is assign a unique value to, to each line. And I'm going to do that by doing an auto number field here. So I'm going to create auto number and I'm going to create a field. And now Airtable has automatically gone in and given us a unique number for each person that is in this particular table.

[00:33:29] So now what we can do is we can go back to our email table here and we can figure out just one of the numbers from the group. And we'll say that that is the person that we want to keep. So what we'll do is we're going to come in here and we're going to create a rollup field. And we're going to say, let's just say we want the minimum number from that group.

[00:33:51] So i'm going to just say the minimum number and so we're going to create a roll up field here and We're looking at the contacts [00:34:00] table and we are going to choose the auto number field And we're going to choose the minimum value And you can do this in other ways too, but I just figured this would be the easiest way to show in a demo.

[00:34:13] And so I'm going to create this field here and now what it's giving us is the minimum number that appears in that group. So for example, we have three Adina's right here and out of these three Adina's, if we go back to our contacts. We'll see that we have number, they've been assigned six, seven, and eight.

[00:34:33] And over here on our email table, we can see that the minimum number for the three Adina's is six. And so for each one of these groups, we'll now know the minimum number. So if we go back to the contacts table, now we just need to tag each contact. That is the minimum person in each group, because those are the ones we want to keep and we want to get rid of.

[00:34:59] [00:35:00] All the extra ones. So what we can do is we're going to create another lookup field here from the email table, and we're going to bring in the minimum number. And so now what we can do is I'm going to move this down here next to our auto number. And so this is the minimum number that's been brought in from the email table.

[00:35:22] And you can see that here are three Adina's again. So here's a Dina six, seven, and eight, but all three Adina's show us the number six for the minimum number, because they're all part of the same group. So then we're getting very close to the final solution here. Now we just need to create a formula field.

[00:35:43] That's that asks the question. Basically, you know, is this the minimum number? Is this the minimum contact or whatever? Actually, let's not even try to get fancy with the name. Let's just call it formula. And so what we're going to do is we're going to go into the formula field here. [00:36:00] And all we're going to do is we're going to compare.

[00:36:02] And we're going to say, if the auto number field is equal to the minimum number from the email table, then we know that that was the. Lowest numbered person in the group. And that's the one we want to keep. We're going to use that as our key person. And then if it's not the lowest number, that means they're an extra, they're a duplicate, so we're going to delete that person.

[00:36:30] So I'm going to create this field. And now you could see in this group of three for Adina, the first one is marked as keep, and the other two are marked as delete. And then this same with every group that we've got. So now it's very, very easy to see what's going on here. We could actually, if we wanted to, we could group by this formula field now.

[00:36:52] And now we could see that these are all the records that we want to delete. And then these are all the ones we want to keep [00:37:00] and and then what we also can do is instead of doing that, alternatively, I mean, what we can do is we can just filter this to only show us where the formula. is keep. And now we are down to our 500.

[00:37:20] Oh, these are the ones we want to keep. So these are the 500 that we determined earlier are the 500 unique people. But what I actually meant to do was formula contains delete. And we know now that we have 391 duplicates. These have all been marked to delete. And then I'm just going to select all of them.

[00:37:38] And I'm just going to right click on all of them and delete all selected contacts. And now if I get rid of this filter, we are now left with just the 500 unique contacts. So it seems like it might be a lot of work, but it's significantly quicker and significantly faster than going through 157 [00:38:00] sets of duplicates in the Ddupe app.

[00:38:04] And that's it. That's how you would do that. Any questions?

[00:38:12] Kamille Parks: The airport has a question.

[00:38:18] Scott Rose: Dan, give, Dan, give a thumbs up. 

[00:38:19] Kamille Parks: Yeah, it makes sense to me. It works great if you have like, if you know, you have a lot of duplicates, like, and sometimes that happens. Scott, you mentioned this could happen anytime in your, time working with a base, not just when you're importing, but I often find that when you are importing, that's when you get the, you know, hundreds and hundreds of duplicates, whereas over time you might have like a set of 20.

[00:38:44] Or so, like, obviously, depending on the size of the database, but, you know, the longer, you know, when you dump a whole bunch of stuff in there, you might run into more duplicates up front. 

[00:38:59] Scott Rose: [00:39:00] Exactly. Yeah. Yeah. Very good. Yeah. It's typically a migration problem. 

[00:39:06] Dan Fellars: Cool. Thank you, Scott, for showing you've got 1 more for us, right?

[00:39:10] FIELD FOCUS - 00:39:10

[00:39:11] We're going to talk about many to many relationships. You want to share your screen on that? All right, jump right into 

[00:39:17] Scott Rose: that one. All right, here we go. So this is something called many to many relationships. This is something that has been coming up quite a bit. You gotta, 

[00:39:28] Dan Fellars: you gotta share your screen, Scott.

[00:39:30] There 

[00:39:30] Scott Rose: we go. Thank you so much. So this is something that has come up quite a bit in the community forums lately. Where people get a little bit stumped about creating a more advanced type of relationship called a many to many relationship and. I'm going to give an example of where that might come up.

[00:39:51] So let's say I've got this database here. Let's say we have a schedule of events of all these different lectures that are going to be happening during the day. [00:40:00] And so here's a list of all the events that are happening during the day. And we also have a list of speakers. So these are people that are going to be speaking at these events.

[00:40:11] And so a one to many relationship is typically what people work with. In air Airtable, and it's what they're accustomed to. And what that means is that one speaker might be speaking at many events. So for example, if I add a linked record field here and I link this to our schedule of events, this let's just call this events What's called events and I'll create this field here.

[00:40:45] And then let's say that this particular speaker is speaking at the welcome ceremony, and she's speaking at managing virtual teams, and she's also speaking at makers [00:41:00] making money, then this guy here, he's only speaking at one event. But she is speaking at many different events as well. So we're going to assign her to a bunch of events.

[00:41:12] So this is the relationship, the type of relationship that most people are accustomed to working with in Airtable. This is called one to many. So one speaker, Allie Bode can be speaking at many. And now this guy's only doing one to one. Technically, that's a one to one, but because we actually give them the ability to have more than one, you know, that's, it's really a one to many.

[00:41:40] And so these are normal relationships that people work with. And if we go to the schedule of events, now we have the back link here. And you can see that, you know, they've been assigned, each event has been assigned to the appropriate speaker. So, that's good. That's, that makes sense so far. [00:42:00] But then we have another thing too.

[00:42:01] There could be more than one speaker speaking at each event. So, for example, Allie Bode, we've got her speaking at the welcome ceremony, but we might also have this guy right here. Speaking at the welcome ceremony as well. And let's say actually all three of them are speaking at the welcome ceremony So if we come back here now, we will see that we have three speakers that are all Speaking at the welcome ceremony now right now We're actually getting into the realm of a many to many relationship, but it's still structured as a one to many So you may be wondering, so what's the big deal?

[00:42:40] Why, why do we care what this terminology is and what's, what's the problem? This seems actually like it's a working functional database here, right? We have three speakers that are linked to this event. And then if we go to the speakers table, we can see all the events that are linked to each speaker.

[00:42:57] So for a lot of people's needs, [00:43:00] that might be good enough. However, this is where things start breaking. This is where people start getting a little bit confused What happens if you want to? Manage or keep track of the intersection between speakers and schedule events. And what do I mean by that? What do I mean by the intersection between the two?

[00:43:24] Well, let's say that we want to keep track of ratings. Let's say that we're, we're judging these speakers. Maybe it's a contest, you know, we're judging these speakers and we want to give each one of them a rating. And so we want to create a rating field here and. I'm going to put this we're going to put five stars here and we want to give them a rating So here's ally and we could give her a five star rating here But this rating what if we have to judge her?

[00:43:56] separately for each event That she's speaking [00:44:00] at this is only one rating field for alley And then let's say we wanted to rate the schedule of events Let's say let's we wanted to rate it on the event side instead, right? We're like, oh, okay Well, yeah, that makes sense, right? We can only give alley one rating Here and but we really want to give her three different ratings one for each event that she spoke at and then you're like, okay Well, no problem.

[00:44:25] We'll fix it on the schedule of events side instead, right? We'll create a rating field here Instead and i'm going to create a rating field here One through five stars and you're like, okay, so the welcome ceremony, we're going to give the welcome ceremony five stars, but then you're like, Oh, wait a second.

[00:44:41] We can't do that because we want to judge each one of these people separately. We want to give a rating to each speaker at each event that they're at. So that's where the sort of the clash happens. That's where we have a problem. So the solution to [00:45:00] this. Is Creating a junction table. This is where we start getting into the many many many to many relationships And so what you would do is you would actually create a third table So you're going to break free from just these two tables.

[00:45:15] So i'm going to create a brand new table here And I'm going to call this, the ratings table, because this is where we're going to keep track of the ratings. And the way that this works is every single record in this table is going to link to one person. So I'm going to change, I'm going to call this speaker and I'm going to link to another record.

[00:45:44] I'm going to link this to the speakers table. And you only want it to link to one person. So, every single row in here is going to link to one speaker. Let me get rid of these two fields here. And, in addition to [00:46:00] that, every single record is going to link to one event. And you're going to only have one per line.

[00:46:09] So, what you have here, And for this right here, this doesn't make a difference. At the moment, I'm just gonna make this an auto number field, but I typically turn that into a formula field that gives us more information about the record that we're looking at. Just for now, I'm just gonna give it auto number, but know that I normally recommend turning that into a formula field.

[00:46:29] So now what we have here is different rows here. And what you can do is every speaker, Allie Bode, can, this is where you would actually assign her to every single event that she's speaking at. So, in essence, what you're doing is you're breaking this up. You're breaking up the links cross this table. [00:47:00] And when I'm done with this, I'll have Kamille talk about some, some cool ways that you can actually automate this a little bit, but for now, I'm just going to show you the manual way of, of doing this.

[00:47:11] So the way that we originally set this up was that Allie is speaking at these three events. And we put those three events right here, but the better way to do it. If we need to keep track of each one is. Forget about this column altogether. You could actually delete this column altogether. You could see now Allie is linked to our three records in the ratings table.

[00:47:36] So you could create new records for her here, but I'm just going to show it to you from this table. So what we've done is we've assigned Allie to three events. And then we're going to assign our next speaker, Andrew Rossi, to three events as well. And so Andrew is speaking at three events [00:48:00] as well. So now, basically, what you're doing here is You're splitting things apart.

[00:48:09] And so now what you could do to make this a little bit easier to look at, you could group it by either speaker or the event. I'm going to group this by the speaker. And if you want to see information about the speaker, of course we could bring in. Like that person's biography or that their person's phone, that person's photo.

[00:48:26] So we could have a little more information here. So this is what it would look like if it was grouped by the speaker. And then this is what it would look like if it was grouped by the event. So the welcome ceremony We've got two people listed there so far. Let me open this up a little bit. We're running 

[00:48:44] Dan Fellars: short on time, Scott.

[00:48:47] Scott Rose: so much. And then now what you can do is you can assign a rating field here. So now from this table, here's our welcome ceremony. And you could [00:49:00] say, Ali, we're assigning her four stars for the welcome ceremony. And we're giving Andrew five stars for the welcome ceremony. But Allie, we're going to give her five stars.

[00:49:12] For this and we're going to give her three stars for this So now we can separate out the ratings Based on person and speaker. So now we've freed ourselves from the constraints Of just having two tables here and you could also look at these things from this table as well I won't get into that now because we're running out of time But if you made this a formula field you could see You know more information at a glance.

[00:49:43] Oops. I didn't mean to delete that And you could also bring in lookup fields here to see all the ratings You could also bring roll up fields to get the average rating if i'll just do that real quick average rating If I brought in a roll up field here I could actually bring in [00:50:00] Allie's average rating and it already figured it out for me.

[00:50:04] Her average rating across her three events from here is a four. And you could do a similar thing from the schedule of events side as well. So that's it. That's a quick overview of how to set up a many to many relationship. Very 

[00:50:21] Dan Fellars: good. Yeah, that's a must have for your tool belt. If you're dealing with Airtable, very useful.

[00:50:28] All right, 

[00:50:29] Scott Rose: Kamille created an extension, right? Kamille? 

[00:50:32] Kamille Parks: Yes, this is 1 of the rare times that junction assistant is the is a good way to manage it. I built junctions assistant for a very specific purpose. And this is 1 of them. There's a lot of different ways. You could work with junction tables. If you have something where your junctions are predictable, then junction assistant isn't for you.

[00:50:56] It's more for. Like it's a manual process. [00:51:00] It's not written in the stars. Who's going to speak at what event someone's going in and assigning or they're signing up themselves, et cetera. But the advantage of using something like junction assistant, it, it allows you to say, pick your person and then on your screen and Airtable, you can use the built in check boxes and select all 3 or all 5 or however many once you can.

[00:51:25] You think that person's going to speak at and import your selection so that you can bulk create those three or five records in one go. 

[00:51:34] Scott Rose: Nice. Nice. Very good.

[00:51:36] BUILTONAIR COMMUNITY SPOTLIGHT - 00:51:37

[00:51:38] Dan Fellars: Very cool. Quick shout out to join our community at built on there. We'd love to have you join our Slack community and subscribe to our podcast.

[00:51:45] Check us out. builtonair.com/join gets you in for free. 

[00:51:51] AUTOMATE CREATE - 00:51:50

[00:51:52] Let's finish up with Kamille and Stripe. There you go.[00:52:00] 

[00:52:01] Kamille Parks: Alrighty, so last week we talked about how you can use eSignatures. io to send a service agreement to a client that had information that is being pulled from Airtable. The second step is to Perform whatever it is, you've just contracted yourself into doing. And then the 3rd step is to ask to be paid.

[00:52:25] So what I've done is I've, I've created just a 2nd project to demonstrate, some, you know, advantages to the way I've built this particular integration as just a high level overview. I have information about the contracts and therefore what I'm going to be billing for stored in Airtable. I, of course, have a Stripe account, and I'm using N8N as my connector between the two.

[00:52:55] You can do the same thing with Make or Zapier if you so chose. [00:53:00] So clicking on either one of these contracts, I had the send service agreement on the contract itself, because Each agreement or each contract will need its own agreement, of course, but the way I've built this system is that I don't really want to bill contract by contract.

[00:53:22] I want to bill the entire client anything that's outstanding. So I have a few safeguards in this particular base where I don't actually want to send, bills to people at the wrong stage, if it's already been completed, or if I'm not done yet. I don't want to, you know, fire automations off too early.

[00:53:45] So I have a status that is in here called billable. I'm just going to go in for both of them and make sure they both have a status of billable as my first step. And [00:54:00] The other one's more exciting because it has more tasks, but these tasks, which were shown in, in the previous episode, each one has an estimated number of hours.

[00:54:14] There's another field that's not shown, which is the actual number of hours that I spent on the task that I manually put in. It's dealer's choice. If you're billing by actual hours or by your estimated hours, that's a decision as a business. Owner to make, of course, that would be spelled out in your contract, which you would have put in step one last week.

[00:54:36] But that's all I really need to change on a contract by contract level. But again, I want to build the client. So now that I've marked both of these 2 contracts is billable. I have a couple of formulas and roll ups that are kind of helping me out to assemble the information that I need, to.

[00:54:55] Thank you. You know, put into Stripe, [00:55:00] the first being how much in total am I going to invoice for the second being some helper text. This is a formula that can cat needs a couple of things together. I have a lookup that has the names of my projects with the status of billable. It's a conditional lookup and then a formula that just tax on this stuff at this time.

[00:55:23] At the start you know, this invoice bills for the following projects helpful little text to add to the stripe memo from there in Airtable. What should happen is if I click this button, it should send an automation through and it and, to create an invoice and strike. Now, I've gone through my scenario and just double checking because I don't want to bill Dan for 20, 000.

[00:55:56] There are different credentials that you could [00:56:00] use when you're hooking up nodes in N8n. Again, same thing with Stripe, and I'm sure with Zapier these are linked to my test account. So not my real Stripe account. Something that's very useful when you're working with Stripe is the ability to, you know, use completely fake data to test things out.

[00:56:25] So last one. All of them hooked up to my test account. So let's go. This particular button is setting a value of a single select field that's create invoice in Stripe, which fires an automation in Airtable that fires a webhook to N8n. I could have hit that automation directly. That's not the exciting part.

[00:56:52] So in Stripe, I'm in test mode. I have a couple of previous webhooks, but the one that is relevant is [00:57:00] this one. I'm looking for 2 events when an invoice is finalized and when an invoice is paid in stripe. And I'll explain it by looking at the scenario. There's a couple of things that are going on 1. I have to read that information.

[00:57:18] That's coming in from Airtable. That's that 1st bit get the client again. That button is on the client level, not the contract level. And then I'm searching to see if that customer already exists in my Stripe. If they do, keep going. If they don't, create a customer in Stripe and then keep going. Then this step is just here to make sure that whatever I have in Stripe Matches what I have an Airtable.

[00:57:45] So if, you know, if they change their name, for instance, or they have a different stripe customer I. D. I want to update it in Airtable just so that I keep record of it and then keep going. [00:58:00] Now, I'm getting that customer by I. D. again, just in case because. Stripe has a couple of things that happen in the back end whenever you make adjustments to, and I want to make sure that I get the most up to date information as I can, but you can technically skip this step.

[00:58:15] You don't necessarily need to retrieve it again. If you've already retrieved it in 1 of these 2 steps here. So now I'm looking for contracts that have a status of billable. In this case, I had 2. And then I'm creating line items. So going back with Scott's segment earlier about many to many relationships and junction tables.

[00:58:41] I have an invoice that is linked to a client, but each invoice has multiple line items. So the line item table in this case would be my junction table. So I'm creating a line item for this singular invoice. One for each [00:59:00] contract that had the status of billable and the line item total. I'm choosing in this example to build by hour.

[00:59:08] So the quantity is the number of hours that were, rolled up into each contract and then the price would be whatever the hourly rate that was agreed upon in the, in the service agreement is. I have a couple of different instances of what's called a merge branch in N8n. What this is doing is it's take, it always takes two inputs.

[00:59:36] And what it's doing is it's saying, wait until you're done creating line items before going. So these are kind of like hold patterns to make sure that things stay within sequence. And then after I have all of my line items created, I'm creating the Stripe invoice itself, and then I'm finalizing the invoice.

[00:59:58] That's an important [01:00:00] piece of the puzzle. And something that might not be immediately obvious when you're working with Stripes. API, when you create the invoice, it's gonna show up as a draft but it's not necessarily gonna be sent to anyone. It's not going to, it's just gonna sit in your account. And you have to make sure that you finalize it if you wanna send it off too.

[01:00:21] Somebody now in strike. There's a couple of ways to do it. You can have strike automatically send that person's email address a link to that invoice where they can pay or if you want to handle that yourself. Like, if you have your own custom emails that you want to send out with Airtable or something else, you can turn that feature off.

[01:00:42] These are some of the variables that you can use to mix up this general process to fit your. However, you want to work. So if I go into my test invoices and refresh the page, hope is that I see [01:01:00] it and I do. So 20, 700. And if I look at my, look at my invoice, you'll notice that I have 2 line items, 1 for each project.

[01:01:15] I will say that I misspoke with how I, handled this particular setup. I said that the quantity was the rolled up number of hours. That's inaccurate for how I actually set it up. But that is something that you can do. If I look back at my Airtable really quickly, you'll notice that each contract has tasks and that's where the, the quantity in this case would be coming from.

[01:01:44] In under the hood, what I've done is I've just said, the quantity is 1 and called it a day, but you could use that information here. If you wanted, you would just have to pass it up, but you have individual line items. And then again, that helpful note that came from that [01:02:00] irritable formula is present in this.

[01:02:04] Present in this email. So what I can do, is I can like, I can fake marking this as paid because this is again test data. And if we take a look at the invoice page, you'll see it very helpfully remind us that's in test mode. So Dan, I'm going to pretend to be you and I think this works in Stripe.

[01:02:34] If you give it any, any valid thing, it should work. I'm going to hit pay. Oh, no. It knows that that's not real. 

[01:02:51] Dan Fellars: If it's 42424242. 

[01:02:54] Kamille Parks: Okay. That's what it is.[01:03:00] 

[01:03:01] Okay. Now, if I do it. processing marked as paid. Great. So I'm going to come back to my Airtable and I'm going to watch this field. Oh, you know what? Again, live demos, the, the scenario that's watching for when an invoice is created in Stripe is looking at my real Stripe account. And again, I'm using my test one.

[01:03:36] So that's why the invoice wasn't populated here, both when it was first created and now that it's been marked as paid. So that's my bad for forgetting to set up that other scenario to look at my test account as well. But imagine if you will, that invoice showing up here. But if I refresh the page in Stripe again, [01:04:00] you'll see that it will be marked as paid.

[01:04:02] And that information ordinarily would be passed back into Airtable.

[01:04:10] Dan Fellars: Awesome. Such a useful, you know, understanding Stripe and invoicing is critical to any business. So this is very useful. I know you're a fan of N8N, so shout out to that tool as well to help make this all happen. Awesome. Thank you, Kamille. And that concludes. Thank you, Scott, who had to jump for another call.

[01:04:31] But thank you, Scott, to join, for joining us. And we will see everybody next week. We survived the, The airport show. 

[01:04:38] OUTRO - 01:04:38

[01:04:39] Thank you. Thank

[01:04:56] Outro: you for joining today's episode. We hope you enjoyed it. Be sure to check out [01:05:00] our sponsor On2Air backups, automated backups for Airtable. We'll see you next time on the built on air podcast.