[00:00:13]
Dan Fellars: Alright, welcome back to the BuiltOnAir Podcast.
[00:00:16]
Dan Fellars: We are in season 26, episode 2, and we’re gonna do things a little bit different for a couple episodes.
[00:00:24]
Dan Fellars: As you can see, I’m by myself, my co hosts are away for a bit.
[00:00:28]
Dan Fellars: And you can see also I’ve got a new background behind me, trying something a little bit different.
[00:00:32]
Dan Fellars: Let me know what you think.
[00:00:33]
Dan Fellars: Anyways, for the next couple episodes, we’re going to do a deep dive.
[00:00:37]
Dan Fellars: Into the administrative side of your Airtable environment, the DevOps and Admin.
[00:00:43]
Dan Fellars: So if you’re the person that’s responsible for making sure that your business is running and working smoothly inside of Airtable, then these are the episodes for you.
[00:00:52]
Dan Fellars: We’re gonna do about 6 episodes all pre recorded.
[00:00:55]
Dan Fellars: Then we’ll come back at the end and we’re gonna do a live episode.
[00:00:59]
Dan Fellars: Bring Alli back and we’re gonna have a big announcement in that final episode, something that I’ve been working on for a while and excited to share with you.
[00:01:09]
Dan Fellars: So if you don’t know me, my name is Dan Fellars.
[00:01:12]
Dan Fellars: I am the founder of BuiltOnAir and On2Air, which is a backup solution for Airtable.
[00:01:18]
Dan Fellars: And I’ve been working in Airtable for eight years now.
[00:01:21]
Dan Fellars: I’ve kind of seen it all.
[00:01:22]
Dan Fellars: I’ve I’ve been with some very large organizations implementing Airtable, and I’m excited to share with you what we are going to be talking about today.
[00:01:32]
Dan Fellars: So with that, we are gonna focus today on protecting your data, finding it when it’s lost, and how to restore it back to where it was.
[00:01:40]
Dan Fellars: So Airtable a lot already has a lot of The tools that you’re gonna be needing to do this.
[00:01:46]
Dan Fellars: Most people maybe know about these in theory, but they don’t really know how to use them.
[00:01:52]
Dan Fellars: And you don’t really need to until the actual day that you do, but that’s the wrong to learn about it.
[00:01:57]
Dan Fellars: So we’re gonna walk through them today.
[00:01:58]
Dan Fellars: We’ll walk through a real base and talk through the pros and cons of each of them as we go.
[00:02:06]
Dan Fellars: So with that.
[00:02:07]
Dan Fellars: First, we’ll talk about how to protect before anything goes wrong.
[00:02:11]
Dan Fellars: Then we’re gonna talk about how to find things when they change or when they get deleted or missing.
[00:02:16]
Dan Fellars: And then last how to restore it back to where it belongs.
[00:02:20]
Dan Fellars: And then at the end of this episode, we’re gonna do a bonus deep dive into how to archive and delete your data properly if you have to restore or if you have to save for record limits and whatnot.
[00:02:33]
Dan Fellars: So we have a demo base that we’re gonna use.
[00:02:35]
Dan Fellars: It’s a project management base.
[00:02:37]
Dan Fellars: You have teams, projects, and tasks, and we’ll share that with you as part of this this broadcast.
[00:02:44]
Dan Fellars: So first let’s talk about protection.
[00:02:48]
Dan Fellars: What happens before anything goes wrong.
[00:02:50]
Dan Fellars: So if you click up at the top of your of your base, you’ll see the the clock icon.
[00:02:55]
Dan Fellars: And on there, when you click on it, you’ll see three things: snapshots, record revision, history, and trash.
[00:03:02]
Dan Fellars: So your snapshots is your long-term protection for your entire base.
[00:03:06]
Dan Fellars: It includes your data, your automations, your interfaces.
[00:03:09]
Dan Fellars: And then Airtable will account for them.
[00:03:13]
Dan Fellars: They will take snapshots on a regular basis.
[00:03:16]
Dan Fellars: You can also take one manually.
[00:03:18]
Dan Fellars: How often and how far back.
[00:03:20]
Dan Fellars: you can take snapshots depends on your plan, so be sure to check yours and see what is available to you.
[00:03:26]
Dan Fellars: And then we’re gonna talk in the future about how to restore from the snapshots.
[00:03:30]
Dan Fellars: Then more our record revision history will talk in our trash as well.
[00:03:34]
Dan Fellars: Another long term option is CSV exports.
[00:03:38]
Dan Fellars: If you go to any view at the data layer, you’ll see in the view menu a download CSV option.
[00:03:45]
Dan Fellars: So this is just for one table, one view, and it’s your records only.
[00:03:49]
Dan Fellars: And if your table has attachments, those are just coming through as links and they’re gonna expire after a few hours.
[00:03:57]
Dan Fellars: So and it’ll give you a warning.
[00:03:59]
Dan Fellars: But so this is fine for data, but not a way to back up your data your attachment information.
[00:04:05]
Dan Fellars: So be careful about that.
[00:04:07]
Dan Fellars: Next let’s talk about if something changed, um, what was it before?
[00:04:13]
Dan Fellars: So here you have your record revision history.
[00:04:16]
Dan Fellars: If you go to any record In the data layer, you open it up, you might see in all comments, just change that to uh revision history, and then you’ll see the data in the sidebar of everything that’s changed at the field level.
[00:04:31]
Dan Fellars: So here you see the estimate has changed from 24 to 28, and then also the due date changed from 2026 to 224.
[00:04:42]
Dan Fellars: And at the top of your panel, it will show you how far back you your history can go, and that’s plan dependent, so be aware of what your plan offers.
[00:04:52]
Dan Fellars: And next for um For the record revision history, you want to look in your record data.
[00:04:58]
Dan Fellars: We we saw at the top that there’s a toggle show history or an option to go into your record revision history.
[00:05:05]
Dan Fellars: That’s more your configuration.
[00:05:07]
Dan Fellars: So the show history and expanded records, that will just hide or not hide the history.
[00:05:13]
Dan Fellars: And then this clear revision history, that’s the one you got to be extra careful with.
[00:05:17]
Dan Fellars: If you click on that and then you confirm the possibility.
[00:05:20]
Dan Fellars: It will actually get rid of your archive history, so be careful.
[00:05:24]
Dan Fellars: I don’t think I’d recommend that.
[00:05:26]
Dan Fellars: So talking about the trash can, if you go to the trash menu, you will see all of your tables, fields, records that are deleted from this base.
[00:05:36]
Dan Fellars: And then you’ll see the restore button that we’ll talk about.
[00:05:39]
Dan Fellars: And then at the top you’ll see how far back your your trash uh storage goes.
[00:05:45]
Dan Fellars: Again, that’s that’s plan dependent, so be aware of that.
[00:05:48]
Dan Fellars: In my case it goes back seven days.
[00:05:51]
Dan Fellars: And if you wanted to clear out your trash, there’s also the empty trash button as well.
[00:05:57]
Dan Fellars: Okay, so now you know where to look.
[00:06:01]
Dan Fellars: Let’s Talk through how to put it back, starting with the small fixes first.
[00:06:06]
Dan Fellars: So if you just need to change one field history and revert just one or two field histories for one or a couple records, that’s where you’d go to the record revision history.
[00:06:17]
Dan Fellars: In this example, if we wanted to revert back to the previous drive, Um then we could just copy that October 17th date and then paste it back into our field.
[00:06:27]
Dan Fellars: And then it’ll get it back.
[00:06:28]
Dan Fellars: You’ll actually see it show up in the history as changing back.
[00:06:32]
Dan Fellars: And so that’s good for just simple things.
[00:06:34]
Dan Fellars: If you actually deleted something, deleted a record, field, or table, that’s where you go to the trash.
[00:06:40]
Dan Fellars: It’s really easy.
[00:06:41]
Dan Fellars: You just find it where where it is in there, you click restore, and then it’ll automatically bring it back to where it was before it was deleted.
[00:06:49]
Dan Fellars: Now automations may have run or may need to run that this doesn’t trigger, so you might need to look into it when you restore things from the trash, make sure everything’s linked properly and look into that.
[00:07:01]
Dan Fellars: Now if you’ve lost a lot of data, you probably need to go to your snapshots.
[00:07:06]
Dan Fellars: So inside your snapshots, you’ll see the list of dates of when snapshots are taken, and you will create a new copy.
[00:07:14]
Dan Fellars: This doesn’t revert back to the actual base, it will create a whole new instance.
[00:07:21]
Dan Fellars: And the base ID will be different, but everything else is going to be the same.
[00:07:26]
Dan Fellars: So that’s something to be very careful and be understanding about.
[00:07:30]
Dan Fellars: So all your feelings.
[00:07:31]
Dan Fellars: IDs, all your record IDs, they’re going to be the same in the duplicate.
[00:07:35]
Dan Fellars: Sometimes that’s good because it makes it easy to reference, or if you need to run a script to to link it back to where it was, you have the unique identifier to match it.
[00:07:45]
Dan Fellars: Sometimes that’s a conflict.
[00:07:46]
Dan Fellars: One thing that to be aware of that I’ve learned is if you’re ever looking at the schema information at a high level, you might see duplicate field names coming from different bases.
[00:07:56]
Dan Fellars: Because you have these duplicates.
[00:07:58]
Dan Fellars: So just uh uh an FYI, if you’re ever looking for a unique identifier, I I typically combine the base ID with the field ID into a single ID so that you’re getting the unique base ID because when we make a duplicate, we do get a new base ID with that duplicate.
[00:08:16]
Dan Fellars: So that that will be helpful as you’re doing any kind of high level comparison of fields.
[00:08:22]
Dan Fellars: So sometimes it’s good, sometimes it’s bad that that keeping of the of the IDs to be aware of.
[00:08:28]
Dan Fellars: Another way to restore data is through the CSV X import Um and it’ll bring it back.
[00:08:34]
Dan Fellars: You can either import it as an existing or into a new table or an existing table.
[00:08:39]
Dan Fellars: This is a way where you can import data into an existing table.
[00:08:44]
Dan Fellars: It will help you map your CSV fields into your table fields.
[00:08:49]
Dan Fellars: And in this scenario, you you’ll keep the same field types and it works with links or anything else like that.
[00:08:57]
Dan Fellars: If you need to go into a new table, it will import everything into a whole new table, and all the fields will just be best guess, and you might need to change field types, especially, you’ll need to relink them on how they work.
[00:09:13]
Dan Fellars: So here you’ll see an import.
[00:09:14]
Dan Fellars: The one on the left is a new table, so you’ll see the fields come in as text when you wanted them, maybe as a number.
[00:09:22]
Dan Fellars: or links come in as text and you need to revert them and change the field type.
[00:09:27]
Dan Fellars: Now be careful about links.
[00:09:28]
Dan Fellars: It’s just searching, doing a match on the name.
[00:09:31]
Dan Fellars: So if your if your link names are not unique, It might link to the wrong records.
[00:09:37]
Dan Fellars: So you gotta be careful about that.
[00:09:38]
Dan Fellars: Always try to make your primary keys unique and across your tables if you’re linking to them.
[00:09:44]
Dan Fellars: There’s also a CSV import extension that you can get in the data layer in the extension.
[00:09:49]
Dan Fellars: This is really good.
[00:09:50]
Dan Fellars: I’d recommend this over just going directly into an existing, because this helps do a merge.
[00:09:57]
Dan Fellars: So you can pick a field and do a merge.
[00:09:59]
Dan Fellars: If you want to, if you’re existing, it tells you here two records will be updated.
[00:10:04]
Dan Fellars: And the table that you’re going to, you can map all of the fields that you want to import.
[00:10:10]
Dan Fellars: You can also decide not to import and whatnot.
[00:10:12]
Dan Fellars: Another way is you can actually in the in the interface level.
[00:10:16]
Dan Fellars: you can configure a a page to allow imports from CSV as well as exports.
[00:10:22]
Dan Fellars: And the benefit of this is you can define which fields you want and you don’t need to give data layer access to people to be able to import data.
[00:10:30]
Dan Fellars: But be aware this is just for importing new records.
[00:10:33]
Dan Fellars: You can’t merge on top of existing records.
[00:10:37]
Dan Fellars: So one thing that I’ve done in the past is I will set up a whole new table that’s just for imports and then have an automation that copies it from that import staging table to my real table, and that gives you a little bit more control.
[00:10:56]
Dan Fellars: And then you can set up interfaces to allow people to to update to that.
[00:11:00]
Dan Fellars: And like I mentioned, this is update or this is create only, it doesn’t update records, so that’s where that that workaround comes in handy.
[00:11:08]
Dan Fellars: And any field that you don’t expose in the in the uh table will not get imported as well.
[00:11:16]
Dan Fellars: So that really covers how we can do things.
[00:11:19]
Dan Fellars: Um we can protect our data, we can find it, and then we can restore it.
[00:11:24]
Dan Fellars: We’ll talk a little bit about another way to restore it at the end of this show.
[00:11:28]
Dan Fellars: But first I want to talk about a bonus.
[00:11:31]
Dan Fellars: strategy that that I’ve used.
[00:11:33]
Dan Fellars: So a lot of times we all know that there are s tight record limits and sometimes you run out of records and you need to delete data when it’s old or out of date.
[00:11:42]
Dan Fellars: So here’s an archiving first and deleting data.
[00:11:46]
Dan Fellars: We have uh imagine just any kind of table.
[00:11:49]
Dan Fellars: In this case we’re using our our projects with our tasks.
[00:11:53]
Dan Fellars: Typically you have more tasks than projects.
[00:11:57]
Dan Fellars: And so at the start we have a project table and it has tasks.
[00:12:04]
Dan Fellars: And so we see here at the top the two tasks associated with this project.
[00:12:08]
Dan Fellars: Our goal is to eliminate our task records, but hold on to our projects.
[00:12:13]
Dan Fellars: Cause we maybe don’t have as many projects.
[00:12:15]
Dan Fellars: We can keep those around longer, but we can clean up our base by By getting rid of our tasks.
[00:12:22]
Dan Fellars: So we see here our tasks, and then we have a status, and we’re gonna use this status as the trigger to our automation.
[00:12:29]
Dan Fellars: As long as the status is active, nothing’s gonna happen.
[00:12:33]
Dan Fellars: And we’ll talk about what happens when we change that status.
[00:12:36]
Dan Fellars: Then you see two fields here.
[00:12:38]
Dan Fellars: You see a task.json.
[00:12:40]
Dan Fellars: That is a formula uh of of putting all of the entities in the task record into a JSON object so that we s so that we can save that data.
[00:12:55]
Dan Fellars: So we want to archive that data before we delete it.
[00:12:59]
Dan Fellars: And so here we do a roll up.
[00:13:01]
Dan Fellars: And so now at the project level, we have an array of all the task information inside this JSON representation.
[00:13:10]
Dan Fellars: But we know once we unlink these tasks and delete them, this roll up is gonna go away.
[00:13:15]
Dan Fellars: So what we have to do is we have to copy it into a long text field so that it will save once we get rid of the tasks.
[00:13:23]
Dan Fellars: So what we’re gonna do is we are going to We’re going to trigger an automation off of the status.
[00:13:33]
Dan Fellars: So once we set the archive to archive or the archive status to archive, that will trigger our automation that then copies our JSON, puts it into our placeholder text, and then we’ll um then we’ll update the tasks and prepare them for deletion.
[00:13:55]
Dan Fellars: Okay.
[00:13:56]
Dan Fellars: So here’s our status and then here’s our automation.
[00:14:00]
Dan Fellars: So when it changes to archive, we then basically just do a one one simple update and then we loop through each of the tasks and we update each task to set it ready for deletion and then we unlink it for from the project so it’s no longer there.
[00:14:19]
Dan Fellars: And we can maybe do that once we delete it, it will automatically happen.
[00:14:23]
Dan Fellars: So that that could be up to you when that once that gets unlinked from from the project.
[00:14:29]
Dan Fellars: And then lastly Here we have uh so here we have our after.
[00:14:34]
Dan Fellars: So we’ve we’ve run our automation because we set our status to archive, then our automation then sets it to archived, saying that it’s already been performed.
[00:14:43]
Dan Fellars: And then here you see in the long text field our JSON representation, and then we’ve got rid of the tasks.
[00:14:50]
Dan Fellars: You see they don’t show up in the in the JSON roll-up.
[00:14:54]
Dan Fellars: And so now we can hold on to the information that.
[00:14:56]
Dan Fellars: was in our task and then get rid of safely get rid of the tasks which frees up our our records which is likely the biggest um culprit of taking up uh a large amount of of records.
[00:15:10]
Dan Fellars: Okay.
[00:15:11]
Dan Fellars: So here is then what we want to do is Um we need to clean them up.
[00:15:18]
Dan Fellars: So one thing I think last week we talked about the new uh the new f automation to delete records, which is exciting but scary.
[00:15:27]
Dan Fellars: And if we deleted these right away, There’s a chance that we accidentally changed the status to archive and we didn’t want to do that.
[00:15:35]
Dan Fellars: And so instead the approach that I recommend is setting the status to something like to delete or to be deleted, and then setting up a timestamp of when that status gets set.
[00:15:47]
Dan Fellars: So setting a last updated.
[00:15:49]
Dan Fellars: date or using the automation to set the timestamp of when we updated it.
[00:15:54]
Dan Fellars: And then we have a second automation that looks for records with this status and the the date is at least 6 hours old or however long you want to give yourself.
[00:16:06]
Dan Fellars: So that gives us time to fix any accidental deletions that we might have configured.
[00:16:12]
Dan Fellars: We can go through and do it.
[00:16:14]
Dan Fellars: Um and then now we know that we have a 6 hour buffer before any of these tasks get deleted, which will which will come in handy.
[00:16:23]
Dan Fellars: So here’s the automation it runs.
[00:16:25]
Dan Fellars: This one runs once a day.
[00:16:26]
Dan Fellars: You could set that to run hourly or however often you wanna wanna check, and then it finds records and then it goes through and deletes them.
[00:16:36]
Dan Fellars: And again, this is only gonna delete the ones that uh have been sitting there for at least 6 hours without us modifying.
[00:16:43]
Dan Fellars: So that is a two automation way to perform archiving of data and then deleting safely after a a time period.
[00:16:55]
Dan Fellars: And here you see the the records that that it found both have the status to delete and their due dates.
[00:17:01]
Dan Fellars: um have been at least 6 hours old.
[00:17:04]
Dan Fellars: Okay, so that runs us through quite a bit of things that will help you understand.
[00:17:09]
Dan Fellars: Hopefully you never need to to restore stuff.
[00:17:12]
Dan Fellars: Hopefully you keep your your data clean.
[00:17:14]
Dan Fellars: Another way that I’m gonna recommend because it is The primary sponsor of BuiltOnAir, and I haven’t talked much about it lately, but On2Air is a great tool if you’re looking for a very simple way to make sure that your data is being backed up outside of Airtable, which is industry best standard, and it will back
[00:17:32]
Dan Fellars: up your data, your attachments, your schema, and and put it on a schedule that you can look at and make sure it’s it’s it’s there in case you ever.
[00:17:42]
Dan Fellars: Ever need it, and in case the other approaches that we talked about today don’t work for you.
[00:17:50]
Dan Fellars: So with that, that concludes today’s episode.
[00:17:53]
Dan Fellars: Next week we’re gonna go through another episode on working with a single production environment.
[00:17:59]
Dan Fellars: And then we’re here you’ll get a glimpse of what we’re gonna be talking about the rest of this series with our final one.
[00:18:05]
Dan Fellars: And very excited.
[00:18:07]
Dan Fellars: This is something that I’m very passionate about.
[00:18:09]
Dan Fellars: I love the administrative side in addition to building on Airtable.
[00:18:13]
Dan Fellars: If that’s you as well, be sure to check us out on builtonair.com.
[00:18:18]
Dan Fellars: Join for free our Slack community.
[00:18:20]
Dan Fellars: And if you have something that you think you is worthwhile to share, we’d love to have you on and learn more.
[00:18:26]
Dan Fellars: Until then, I’ll see you on the next episode of BuiltOnAir.