9/21/2021 – BuiltOnAir Live Podcast Full Show – S09-E02

Duration: 54 minutes

Be Sure to Subscribe to the podcast!

To get all the latest videos and demonstrations from the BuiltOnAir Podcast, subscribe and get notified on our Youtube channel here and our newsletter/community here.

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.

Todays Hosts

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:02:58 –

Following Articles Used in this Segment:

[Airtable Community] Airtable front-end builder release? – Ask the community – Airtable Community Forum

[Airtable Community] Create scripts / automation through AirTable API – Development and integrations / API – Airtable Community Forum

[Airtable Community] Problem on my Script Suddenly – Ask the community / Automations – Airtable Community Forum

[Reddit] Can I sell my airtable? : Airtable

[Airtable Community] New Scripting API calls: Selecting records by recordId – Development and integrations / Scripting app – Airtable Community Forum

[Twitter] (18) Amy Hoy on Twitter: "airtable is so awkward" / Twitter

[Reddit] Linking Records Across Bases / Pages : Airtable

An App a Day – 00:28:54 –

Watch as we install, explore, and showcase the Matrix App from the Airtable Marketplace. The app is described as “The Matrix app from Aitable allows for viewing 2 dimensional data by creating lanes for the intersection of 2 fields.”.

Audience Questions – 00:32:08 –

Kamille answers the Airtable question: “Estimated Delivery Date from todays date or date Payment is Received (nested if formula)”

View the question in the community

Answer: Kamille shows how to optimize the formula to be shorter and not repeat the field used (i.e. the DRY concept of Dont Repeat Yourself)

Scripting Time – 00:41:10 –

Explore Scripting with “Meta API”.

We highlight how to use the new meta apis in the scripting app to create new tables and fields. We discuss the shortcomings of the api and what is possible. We use live code examples to showcase these apis..

Full Segment Details

Segment: Round The Bases

Start Time: 00:02:58

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

Following Articles Used in this Segment:

[Airtable Community] Airtable front-end builder release? – Ask the community – Airtable Community Forum

[Airtable Community] Create scripts / automation through AirTable API – Development and integrations / API – Airtable Community Forum

[Airtable Community] Problem on my Script Suddenly – Ask the community / Automations – Airtable Community Forum

[Reddit] Can I sell my airtable? : Airtable

[Airtable Community] New Scripting API calls: Selecting records by recordId – Development and integrations / Scripting app – Airtable Community Forum

[Twitter] (18) Amy Hoy on Twitter: "airtable is so awkward" / Twitter

[Reddit] Linking Records Across Bases / Pages : Airtable

Segment: An App a Day

Start Time: 00:28:54

Airtable App Showcase – Matrix – The Matrix app from Aitable allows for viewing 2 dimensional data by creating lanes for the intersection of 2 fields.

Watch as we install, explore, and showcase the Matrix App from the Airtable Marketplace. The app is described as “The Matrix app from Aitable allows for viewing 2 dimensional data by creating lanes for the intersection of 2 fields.”.

Segment: Audience Questions

Start Time: 00:32:08

Airtable Question – Estimated Delivery Date from todays date or date Payment is Received (nested if formula)

Kamille answers the Airtable question: “Estimated Delivery Date from todays date or date Payment is Received (nested if formula)”

View the question in the community

Answer: Kamille shows how to optimize the formula to be shorter and not repeat the field used (i.e. the DRY concept of Dont Repeat Yourself)

Segment: Scripting Time

Start Time: 00:41:10

Scripting Time: Meta API

Explore Scripting with “Meta API”.

We highlight how to use the new meta apis in the scripting app to create new tables and fields. We discuss the shortcomings of the api and what is possible. We use live code examples to showcase these apis..

Full Transcription

The full transcription for the show can be found here:

[00:01:42] Welcome, BuiltOnAir community.
[00:01:45] We're glad to be with you
[00:01:46] today for today's
[00:01:47] live show of the podcast.
[00:01:49] It is me Dan Fellars,
[00:01:51] founder of On2Air and Openside
[00:01:55] and primary sponsor of BuiltOnAir.
[00:01:58] Today I'm flying solo
[00:02:00] just for a little bit.
[00:02:01] We should get Kamille on shortly.
[00:02:04] She is sitting in Los Angeles traffic
[00:02:07] and should be arriving to her location
[00:02:10] shortly and will be joining us.
[00:02:12] Alli is not with us today,
[00:02:15] again, still recovering from
[00:02:17] wedding and honeymoon and
[00:02:19] catching up on everything,
[00:02:20] but she should be with us next time.
[00:02:22] So for now we're going to start
[00:02:25] and get going. We are a live show,
[00:02:27] so we gotta
[00:02:28] keep the show going
[00:02:30] as they say. And so we will begin,
[00:02:33] BuiltOnAir podcast is a weekly live
[00:02:36] show where we talk about all things
[00:02:38] Airtable, see what's going on
[00:02:40] in the community.
[00:02:41] We always do four different
[00:02:43] segments where will highlight
[00:02:45] different aspects of using Airtable to run
[00:02:49] your business. So we're glad
[00:02:51] you could join us and
[00:02:52] view today's episode.
[00:02:53] As always, we begin with Round the Bases
[00:02:58] and this is our opportunity to look
[00:03:01] to see what's going on in the
[00:03:03] different communities.
[00:03:04] Talk about any updates.
[00:03:06] We're mid month, so there isn't
[00:03:09] any monthly announcements from Airtable of
[00:03:12] what's going on,
[00:03:13] but there are some new things
[00:03:15] that are being talked about
[00:03:16] that will go over first.
[00:03:19] This first one is a question
[00:03:21] about running scripts
[00:03:23] through the Airtable API.
[00:03:26] So, they're asking
[00:03:27] if you can perform a script
[00:03:30] or an automation
[00:03:32] by using the API
[00:03:36] and as Cavon mentioned
[00:03:38] there is nothing specific
[00:03:41] about running scripts via
[00:03:43] via their API resource, but as
[00:03:46] Bill mentioned there
[00:03:48] is a way where you can
[00:03:49] use the web hook functionality
[00:03:53] and be able to use that as a
[00:03:55] way to be able to call
[00:03:57] that and that will perform
[00:03:59] and automation. So, for automations
[00:04:02] you're able to trigger them off of a web
[00:04:05] hook which is a URL that they
[00:04:07] will provide when you use that as your
[00:04:09] starting trigger. And then you
[00:04:11] simply just call that
[00:04:13] from your code or other
[00:04:15] system that you might be using to
[00:04:18] to be able to perform that.
[00:04:20] So, Bill mentioned a nice way
[00:04:22] where you actually can
[00:04:23] perform automations and automations
[00:04:26] can then perform scripts.
[00:04:28] So, there is a way
[00:04:29] where you can trigger an
[00:04:31] automation off of a web hook
[00:04:33] and then get it
[00:04:33] going
[00:04:35] off of that.
[00:04:36] So, it's a nice work around
[00:04:38] if you ever are trying to
[00:04:39] automate things from an external platform.
[00:04:43] That is a way where you can automate
[00:04:45] an automation and go through
[00:04:47] there via the web hook.
[00:04:51] This next one is an interesting one.
[00:04:54] The question was posed
[00:04:56] regarding a front end builder release
[00:05:00] and so there was mention of it in
[00:05:03] different podcasts, our friend Ben Green
[00:05:06] on his podcast talked about it and
[00:05:09] other people reference that
[00:05:11] there's actually, have actually seen
[00:05:14] snippets of it through different
[00:05:17] different
[00:05:20] podcasts or webcasts that Airtable
[00:05:22] employees have done, where they've kind of
[00:05:25] given a sneak peek of what
[00:05:27] this front end UI might look like.
[00:05:29] And so when when Kamille gets
[00:05:31] on I'll ask her opinion
[00:05:33] about this one so we might
[00:05:34] revisit this one but just very
[00:05:37] curious to see you know
[00:05:39] anytime Airtable can
[00:05:40] highlight any information
[00:05:42] about what might be coming.
[00:05:43] We're always on top of that to
[00:05:45] see what it could look like.
[00:05:47] So, the gist of what
[00:05:48] people are mentioning is that
[00:05:50] it might be some sort of
[00:05:52] application builder
[00:05:53] similar to a bubble or some other
[00:05:57] type of app that are popular out there
[00:06:02] retool is another one,
[00:06:04] so that's kind of probably
[00:06:05] more what I think it might
[00:06:06] look like.
[00:06:08] Some kind of UI builder that
[00:06:10] allows you to build custom apps off of a
[00:06:14] gooey and be able to implement that. So
[00:06:18] this was very, very interesting one
[00:06:22] that we'll keep our eye on
[00:06:25] somebody actually being
[00:06:27] the member of the team
[00:06:28] at Airtable to get their input
[00:06:30] and Jordan has not responded.
[00:06:32] So it's something that
[00:06:33] I think they're still keeping
[00:06:35] under tight lips and not sharing
[00:06:38] too much information on
[00:06:40] but if we hear anything
[00:06:42] we'll definitely share it
[00:06:44] with our community
[00:06:45] and something that we'll be
[00:06:46] watching closely as it develops
[00:06:49] if at all.
[00:06:50] This was one where five days ago,
[00:06:54] if you had any issues dealing
[00:06:57] with your scripts
[00:07:00] and they suddenly stopped
[00:07:02] working or generating errors,
[00:07:04] it appears they had
[00:07:07] an outage.
[00:07:09] And so a lot of people
[00:07:11] mentioned that they
[00:07:13] had issues even just a very
[00:07:15] basic script. So, it wasn't code related.
[00:07:18] One of the things that's a challenge
[00:07:20] is knowing if it's, you know,
[00:07:22] an issue on your end or an issue on
[00:07:27] on Airtables and so
[00:07:29] this appears to have been
[00:07:31] an issue with Airtable.
[00:07:32] They must have pushed
[00:07:33] some kind of update with the
[00:07:35] scripting block and had issues there.
[00:07:38] So, as Cavon mentioned,
[00:07:42] they did notice the community
[00:07:44] about this issue, this incident.
[00:07:47] If you go to status.Airtable.com,
[00:07:50] you can see information about what's
[00:07:52] going on with different incidents
[00:07:55] and things of that nature.
[00:07:56] So, it was a Airtable issue.
[00:07:58] So, if you were pulling your hair out,
[00:08:01] trying to figure out what
[00:08:04] code bug was the issue,
[00:08:06] it wasn't your fault.
[00:08:07] So
[00:08:09] moving on, next thing
[00:08:13] this is a big one that they
[00:08:15] just announced yesterday
[00:08:17] about new scripting API
[00:08:20] so, being able to select
[00:08:23] records by the record ID.
[00:08:26] So, I think this is a great step forward.
[00:08:29] If you've done any scripting,
[00:08:31] you know that in order to
[00:08:33] retrieve a single record,
[00:08:34] you typically would have
[00:08:36] to pull in all the records
[00:08:38] and do a loop, and do a find
[00:08:41] until you found the one that you wanted.
[00:08:45] So, they've now added a function
[00:08:48] and in the future
[00:08:50] maybe next week or in the
[00:08:52] coming weeks we'll do a
[00:08:54] we'll do a Scripting Time segment
[00:08:56] where we talk about this and how it works.
[00:08:59] But essentially what it is
[00:09:00] is the ability to just,
[00:09:02] if you already have the
[00:09:03] existing record ID,
[00:09:04] maybe you're using a trigger
[00:09:06] and it gives that record ID,
[00:09:08] you can now just simply retrieve
[00:09:10] that one single record or if you have
[00:09:13] multiple records
[00:09:16] you can
[00:09:17] you can specify
[00:09:19] an array, so this will actually
[00:09:22] give you multiple items with an
[00:09:26] array of record IDs.
[00:09:28] And so this is great for using
[00:09:30] within automations or within scripts.
[00:09:33] It helps with the memory usage
[00:09:35] because now you don't
[00:09:36] have to load in all of your
[00:09:37] records
[00:09:39] which are our memory limitations,
[00:09:41] especially on the automations side.
[00:09:43] So, this will come in handy,
[00:09:46] I'm guessing the next phase
[00:09:48] of what they'll
[00:09:49] provide is the filter functionality
[00:09:52] where you can perform a query to just
[00:09:54] retrieve a subset of the
[00:09:57] records based off of
[00:09:58] filter criteria.
[00:10:00] So, this is an initial step
[00:10:03] to just get if you already
[00:10:05] know the record IDs to
[00:10:06] retrieve them and then
[00:10:08] process them as as needed.
[00:10:10] So, very useful.
[00:10:11] This will come in handy in
[00:10:14] many ways and we'll
[00:10:16] explore its usage in the
[00:10:17] future on how to do it.
[00:10:21] and it looks like they're still
[00:10:23] working through the
[00:10:24] documentation and getting
[00:10:25] that all worked out and it may
[00:10:27] not be fully phased out.
[00:10:29] So, if you are trying to
[00:10:30] use this this week
[00:10:32] and it's not quite working then
[00:10:34] it may not, it may not be available yet
[00:10:37] in your base as they roll these out.
[00:10:40] You know, kind of a segmented
[00:10:43] efforts so, feel free to
[00:10:45] play with that one and
[00:10:47] and let us know your feedback
[00:10:49] there with that new API.
[00:10:51] All right. That was kind of it within
[00:10:53] the Airtable community other than the
[00:10:55] standard questions
[00:10:56] and we might dive into
[00:10:58] to one of those in the future.
[00:10:59] But that was kind of the highlights
[00:11:03] of the Airtable community
[00:11:06] moving on to Reddit.
[00:11:08] A common question that gets asked is
[00:11:13] whether you can sell a base
[00:11:17] that you've created
[00:11:19] and so, you might have created a base
[00:11:23] and it's valuable and you want to
[00:11:27] and you want to make a little
[00:11:28] bit of money off of, you know,
[00:11:30] the effort and time that
[00:11:32] you put into that base.
[00:11:33] That's a common question of how
[00:11:35] can you monetize that.
[00:11:37] There is, somebody posted in the
[00:11:40] Reddit community that they're actually
[00:11:42] building a product.
[00:11:43] So, this paytable.io
[00:11:45] definitely worth checking out.
[00:11:47] I have not personally used it.
[00:11:50] But it's definitely an interesting
[00:11:53] concept of how you can monetize your your
[00:11:55] bases and get that.
[00:11:57] It looks like they support
[00:11:59] notion in addition
[00:12:00] and you can actually you know
[00:12:03] it provides the way
[00:12:04] to collect payments and
[00:12:06] then share that base with you.
[00:12:08] The challenge with sharing
[00:12:10] a base is that once when
[00:12:11] somebody copies it then they kind of
[00:12:14] have a replica of of your base and
[00:12:18] and whatnot. So, it does become challenge.
[00:12:21] The other bigger challenge that
[00:12:23] that's a frustration is that
[00:12:26] if you were to monetize
[00:12:28] a base trying to get
[00:12:30] updates out to people that have
[00:12:32] already purchased your base is really not
[00:12:35] possible at this point
[00:12:37] we'll talk actually in another
[00:12:39] segment on this episode
[00:12:41] we'll talk about the meta APIs
[00:12:43] and how you can use
[00:12:44] those to potentially auto
[00:12:46] create new tables and fields
[00:12:48] as you push them out
[00:12:50] but it's not quite there
[00:12:51] yet. We'll talk about why
[00:12:53] that is in the next segment but
[00:12:57] but yeah so, I think this is still
[00:13:00] pretty early too, as far as
[00:13:03] the maintenance side of managing
[00:13:06] existing bases and seeing it through.
[00:13:10] But for sure if you just have an
[00:13:12] initial base that's pretty
[00:13:14] solid and you just
[00:13:15] want to get it up there.
[00:13:16] Check out Paytable.
[00:13:18] I think that's great idea and as
[00:13:21] Airtable makes more underlying
[00:13:24] functionality come out
[00:13:27] then I think we'll see
[00:13:28] we will see this area explode
[00:13:31] more of sharing your data
[00:13:34] and your structure
[00:13:35] and maintaining it going forward. So
[00:13:39] definitely one worth checking out.
[00:13:41] Next one, from the Reddit Community
[00:13:44] is linking records across bases,
[00:13:48] I'll read this question.
[00:13:50] It says our company keeps lists
[00:13:52] of talent we like working with
[00:13:54] and those people are often
[00:13:56] duplicated on multiple lists.
[00:13:57] Is there a way to
[00:13:58] reflect all the lists that individuals
[00:14:00] show up on across tables?
[00:14:02] For instance, if I have a base
[00:14:04] with grid list of project abc
[00:14:06] in talent appears on a and c
[00:14:07] is there a way to have
[00:14:09] the record for talent one
[00:14:10] and project a to communicate
[00:14:11] with the record for
[00:14:12] talent one and project c?
[00:14:13] Ideally I'd like to be able to
[00:14:15] set it up so that on a separate grid,
[00:14:17] I can look at talent one
[00:14:18] and see all the projects we
[00:14:19] were working with them on.
[00:14:21] So this is a perfect example
[00:14:23] of understanding a relational
[00:14:26] database and people
[00:14:27] on this thread give great feedback.
[00:14:31] William who is active on the Reddit
[00:14:35] community, gives a perfect layout
[00:14:37] of how you would do
[00:14:38] this using a relational
[00:14:40] database.
[00:14:42] and I see a lot of similarities
[00:14:45] between William and Bill French,
[00:14:48] but I know they're different people
[00:14:50] I've met on both via zoom and both have
[00:14:51] been on our podcast in the past
[00:14:54] and
[00:14:56] we now have Kamille. Welcome, Kamille.
[00:14:59] Hi, I live in Los Angeles and sometimes
[00:15:02] the rumors are true and traffic is awful
[00:15:07] even early in the morning. So
[00:15:11] Hi,
[00:15:12] welcome.
[00:15:13] So, we're deep into Round the Bases
[00:15:15] talking about, we've already been
[00:15:17] through the Airtable community
[00:15:20] and we're now onto Reddit
[00:15:22] talking about a good
[00:15:24] use case of
[00:15:27] understanding how
[00:15:28] relational databases work.
[00:15:30] And the question was somebody
[00:15:32] has a list of talent that they were using
[00:15:35] assuming as a drop down in multiple
[00:15:38] tables but now they want to see that the
[00:15:41] value of that drop down.
[00:15:43] So, essentially the answer is
[00:15:45] don't use dropdowns,
[00:15:46] create another table
[00:15:47] of your list of talent
[00:15:49] and then you can link them to the
[00:15:51] different projects and the projects
[00:15:53] could either be in the
[00:15:55] same table and which
[00:15:56] would probably be the preference
[00:15:59] and create views for
[00:16:00] your different projects or
[00:16:02] if it made sense to have
[00:16:03] them in different tables,
[00:16:04] you can still link them to
[00:16:05] the same person
[00:16:06] and then from that person table
[00:16:09] you can see all the links
[00:16:10] associated there.
[00:16:13] So, William here, you know,
[00:16:15] I was mentioning that
[00:16:17] for a second, I thought this
[00:16:19] was Bill French because of some of these
[00:16:21] terminology but they're
[00:16:23] different people. I've met them both.
[00:16:26] So, I do want to go back Kamille,
[00:16:28] I mentioned that there
[00:16:29] was a couple of these I
[00:16:30] want to get your input on
[00:16:32] first was this new scripting API
[00:16:35] of using record IDs.
[00:16:37] Being able to select just by a record ID.
[00:16:40] What's your initial reaction to that?
[00:16:42] I think that is useful in both cases. but
[00:16:46] in terms of the scripting app
[00:16:48] and automation scripts
[00:16:49] I think it's especially
[00:16:50] useful for automation scripts
[00:16:52] because a lot of times your
[00:16:54] you have your trigger being based
[00:16:57] on some change or some new record and you
[00:16:59] already know exactly which record
[00:17:01] you want to deal with.
[00:17:03] But if you had a script
[00:17:04] involved in the automation
[00:17:07] you would have to query every single
[00:17:09] record in the table first and then filter
[00:17:12] out essentially the ID
[00:17:13] that you already know
[00:17:14] and then you have the record
[00:17:15] object.
[00:17:16] This is much faster if you
[00:17:18] already know what your
[00:17:19] record you want to deal with
[00:17:20] just start from there and then you
[00:17:22] know if you have thousands
[00:17:23] of records in your
[00:17:24] table. I mean you're
[00:17:26] you're wasting resources so,
[00:17:28] this is you know super, super useful.
[00:17:30] I'm glad to see it.
[00:17:33] Yeah the other the thing that
[00:17:35] this helps with is I've
[00:17:37] done automations where
[00:17:39] you know they trigger for each record
[00:17:42] and I didn't want to have to
[00:17:44] query all the data for that record.
[00:17:46] So, you'd have to pass them in
[00:17:48] as input parameters.
[00:17:50] So, now this helps so that you
[00:17:51] don't have to pass all the data
[00:17:53] for that record in as
[00:17:54] input parameters you can
[00:17:55] just pass in the one record ID
[00:17:58] and then query that one record
[00:18:00] and then you can get the data from
[00:18:03] from that instance instead of
[00:18:05] having to set up and put
[00:18:06] parameters for each
[00:18:07] data element you wanted for that record.
[00:18:12] So, it comes in handy, yep.
[00:18:15] Alright why don't we move on
[00:18:19] we will, okay so this was one
[00:18:21] on Twitter that I thought was interesting.
[00:18:24] So, this person actually
[00:18:27] no she actually has a very
[00:18:29] popular community of her
[00:18:31] own and helps companies start up
[00:18:34] and so she's pretty
[00:18:36] well respected but she
[00:18:37] has a very different take on Airtable
[00:18:40] and just basically states Airtable is
[00:18:43] so awkward.
[00:18:45] And so I thought this thread
[00:18:46] was interesting reading through it,
[00:18:48] you know, not everybody loves Airtable
[00:18:51] the way that we do,
[00:18:53] which is always good to
[00:18:54] hear other people's perspective
[00:18:56] on what they don't like
[00:18:57] about it.
[00:18:59] And so the other comment was
[00:19:01] just good enough to make
[00:19:03] you imagine the things
[00:19:05] you actually want.
[00:19:07] I think there's some truth to that.
[00:19:08] There are definitely
[00:19:09] at times where you you
[00:19:10] get to a point where like
[00:19:12] if you could just do it this way,
[00:19:15] it would be so much
[00:19:16] easier.
[00:19:18] Other comments,
[00:19:23] you know, other people come
[00:19:25] in and say, you know,
[00:19:26] it's been great for me.
[00:19:26] Mobile app is fantastic.
[00:19:28] Although I think some people
[00:19:30] would argue with that
[00:19:31] comment.
[00:19:33] so then somebody made the
[00:19:35] comment that if you're just
[00:19:36] looking at it for the
[00:19:37] data itself without any kind of
[00:19:40] automations or scripts
[00:19:42] or apps then it's pretty
[00:19:44] basic, but once you get that
[00:19:46] automation aspect of it going
[00:19:49] then you see the
[00:19:50] power of it.
[00:19:52] This might show you know
[00:19:54] where Amy's coming from.
[00:19:55] She just said you know,
[00:19:56] how is that better than having
[00:19:58] your own sequel like database?
[00:20:00] Very most people probably don't
[00:20:02] even know what sequelae is. Yeah,
[00:20:04] I mean that tells me all I need
[00:20:06] to know who wants to sit
[00:20:08] there and make an SQL
[00:20:09] database and make it good.
[00:20:11] Yeah. Yeah. So sequelae,
[00:20:13] it is kind of a standalone
[00:20:14] basic SQL database
[00:20:17] that you could run on your own
[00:20:20] but it doesn't really
[00:20:22] have much of a UI either
[00:20:23] are different,
[00:20:24] very different
[00:20:27] her reply below that worse
[00:20:28] than a spreadsheet meets
[00:20:30] worse than Filemaker.
[00:20:31] But together I think is a better sort of,
[00:20:34] you know, peek into this particular
[00:20:37] viewpoint is like Filemaker lets you do
[00:20:40] anything and you build your own
[00:20:43] user interface on top of it.
[00:20:44] It's also like,
[00:20:47] I feel like it's more expensive
[00:20:49] when you look at it. You know,
[00:20:51] just pure,
[00:20:53] it costs more and the product is dying
[00:20:57] leaving that one alone,
[00:20:58] it does have a lot more
[00:20:59] features and a lot more
[00:21:00] capability than Airtable does.
[00:21:02] And spreadsheets have like
[00:21:03] dozens more formulas
[00:21:05] than Airtable has
[00:21:07] but they are different use cases.
[00:21:09] So, you wouldn't
[00:21:11] use, you wouldn't use any
[00:21:13] really database that you would
[00:21:14] have used a spreadsheet
[00:21:15] for or vice versa.
[00:21:17] Probably if you need a
[00:21:18] spreadsheet you would use a
[00:21:19] spreadsheet and if you need
[00:21:20] a database you need the database.
[00:21:22] The Filemaker one is more
[00:21:24] interesting to me because it does,
[00:21:25] Filemaker does have a great many
[00:21:27] number of features that Airtable doesn't,
[00:21:29] it's also
[00:21:31] much harder to learn
[00:21:32] and much more expensive and dying.
[00:21:35] Yeah, yeah, yeah, we know, yeah.
[00:21:38] People that come from
[00:21:40] that world talk about
[00:21:41] everybody moving from
[00:21:43] Filemaker to Airtable.
[00:21:45] So, I'm curious for the the open
[00:21:48] source alternatives to Airtable
[00:21:51] which are very similar but have
[00:21:53] some of the missing kind of
[00:21:54] components that Airtable doesn't like,
[00:21:56] you know,
[00:21:57] infinite number of records is
[00:22:00] one of the ones that is
[00:22:01] most often in Airtable's
[00:22:03] direct sort of competitors
[00:22:04] in a similar space.
[00:22:06] Having, being able to host
[00:22:07] your own server,
[00:22:08] all of those kind of things that
[00:22:10] are improvements upon what Airtable
[00:22:12] provides but still being in the same sort
[00:22:14] of space without being a
[00:22:16] completely different product.
[00:22:18] Yeah, yeah,
[00:22:20] for sure. So anyways,
[00:22:22] it's interesting to see not everybody,
[00:22:25] you know, it's different for everyone.
[00:22:27] I'm trying to remember,
[00:22:29] I believe Amy Hoy's business
[00:22:31] partner was one of the
[00:22:33] very early people to join
[00:22:35] our podcast. So
[00:22:37] I believe they maybe they've
[00:22:38] moved off of Airtable since then
[00:22:40] it was a year or
[00:22:41] two ago but
[00:22:42] I believe they've used it
[00:22:45] in their business.
[00:22:47] So, interesting to see that
[00:22:49] reaction there.
[00:22:51] So, not everybody is a fan of Airtable.
[00:22:55] That's okay. We can make it work and
[00:22:58] we're trying to find, you know,
[00:23:00] we're always looking for
[00:23:02] ways to overcome. For
[00:23:03] sure, it's got limitations
[00:23:05] and room for improvement.
[00:23:08] But in and of itself I
[00:23:10] think it's a pretty good tool.
[00:23:12] to use, which is why we're here.
[00:23:15] So, any other any other things
[00:23:17] that you missed,
[00:23:19] some of the other ones that
[00:23:21] we've talked about about,
[00:23:22] but anything you want to
[00:23:23] highlight that's happening in the
[00:23:24] communities?
[00:23:27] I'm gonna say no.
[00:23:29] Okay,
[00:23:30] that's good.
[00:23:31] Yeah, it was relatively
[00:23:33] there was only the kind of that one
[00:23:34] announcement from Airtable
[00:23:37] about that new feature, so nothing nothing
[00:23:40] to pressing on that front.
[00:23:42] So, for the rest of the
[00:23:44] episode we're going to do
[00:23:46] I'll do a spotlight on the
[00:23:49] primary sponsor On2Air.
[00:23:51] Then I'll highlight an App
[00:23:52] a Day, and also do
[00:23:54] some Scripting Time that will give
[00:23:55] you Kamille, do you have an
[00:23:57] audience question that
[00:23:58] you're going to do for us?
[00:23:59] I do have it picked out.
[00:24:00] I just need to pull it up
[00:24:01] Okay. We'll give you time.
[00:24:03] So, I'll be I'll take the
[00:24:05] next few segments and
[00:24:06] then get you time to get ready. So,
[00:24:10] with that we're now going to
[00:24:12] go to our spotlight on On2Air.
[00:24:13] It's the all-in-one tool kit
[00:24:15] to run your business.
[00:24:16] It's a suite of apps that help
[00:24:18] you do a variety of things
[00:24:20] from backups to managing
[00:24:22] your schema to automating
[00:24:23] more things. Our latest push
[00:24:26] is in the Google drive
[00:24:28] integration space allowing
[00:24:30] you to automate document creation,
[00:24:32] populating your data into Google docs,
[00:24:35] Google sheets and Google slides
[00:24:37] and converting them into
[00:24:39] end documents like
[00:24:40] PDFs and saving those
[00:24:42] back into Airtable.
[00:24:43] So, the full suite of document
[00:24:45] management for today's spotlight,
[00:24:48] I'm going to showcase one of our functions
[00:24:50] that is under our On2Air actions app.
[00:24:54] And this is a common use case.
[00:24:55] Actually believe I saw
[00:24:56] this in the community.
[00:24:57] Somebody asking about the ability
[00:25:00] to perform an upsert in an automation
[00:25:03] and what is an upsert?
[00:25:05] An upsert is a merge.
[00:25:07] I don't think it's a real word,
[00:25:09] but if you're familiar with
[00:25:11] database nomenclature,
[00:25:13] it basically means it's a
[00:25:15] combination of update and insert,
[00:25:17] that's where you get upsert.
[00:25:19] So, it's essentially combining
[00:25:22] the ability to update an existing record
[00:25:25] and if that record doesn't exist,
[00:25:28] then creating a new record
[00:25:30] all in one step.
[00:25:31] And so that's what an upsert does
[00:25:33] now, what's interesting
[00:25:35] about On2Air actions
[00:25:36] that it allows you to do
[00:25:38] this is a sample use case
[00:25:40] of a customer and client of
[00:25:41] ours, and what we're doing
[00:25:43] is we're actually performing
[00:25:45] an upsert from two different bases
[00:25:50] that don't actually even
[00:25:53] reside in the same
[00:25:55] account. So, we're actually moving
[00:25:57] data from one account into another account
[00:25:59] that don't even have any kind of
[00:26:02] relationship between them,
[00:26:03] the users can't see data and either in
[00:26:06] the other corresponding base.
[00:26:08] And so, because of how
[00:26:10] On2Air is configured you
[00:26:11] can actually have a connection
[00:26:14] to one base that is using
[00:26:16] an API key talking to
[00:26:17] one account and that's your source base.
[00:26:20] And then you set up a trigger of when
[00:26:23] that data
[00:26:25] it changes in your source.
[00:26:27] So, we're using a last modified date.
[00:26:30] You can also use when
[00:26:32] somebody checks the check box
[00:26:34] or when a date expires, or
[00:26:35] custom formula, or if you
[00:26:37] know the record ID you could use that.
[00:26:39] So, there's a variety of ways
[00:26:41] that you can set up your
[00:26:42] trigger when the data is
[00:26:43] updated and then you can
[00:26:46] specify your destination
[00:26:49] and you can actually use a
[00:26:50] different API key for your source and your
[00:26:53] destination. So, we're actually using
[00:26:55] data from one API access
[00:26:57] and pushing it into data
[00:26:59] from another API access.
[00:27:01] And then you simply just specify
[00:27:04] what you want to do
[00:27:05] when it's trying to match data,
[00:27:08] you'll need to store some
[00:27:11] unique identifier.
[00:27:13] Maybe it's a name.
[00:27:14] Typically I recommend using the
[00:27:16] record IDs because those are
[00:27:17] always unique
[00:27:19] and then you just specify what you
[00:27:22] want to do each time.
[00:27:24] So, the upsert allows
[00:27:25] you to update if there's a
[00:27:27] match and then create if no match.
[00:27:29] You can also ignore if there's a
[00:27:32] or ignore creating a new one.
[00:27:35] So, you just want to update
[00:27:36] or only create new ones.
[00:27:38] So, if it exists then
[00:27:40] you want to ignore it
[00:27:41] or you can just always create
[00:27:44] a new record every time this runs.
[00:27:46] And then you simply set up
[00:27:48] the mapping between your
[00:27:50] source and your destination,
[00:27:52] the different fields
[00:27:53] and then attachments.
[00:27:54] If you're doing attachments you can
[00:27:56] override what's already in there
[00:27:58] or append to the existing
[00:28:00] attachment array.
[00:28:02] And with that, you can then move
[00:28:04] data from one base to another
[00:28:06] and those bases
[00:28:07] don't even need to be in
[00:28:08] the same account.
[00:28:09] They can be but they don't need to be.
[00:28:11] So, that's a very common use case
[00:28:14] for managing data between bases
[00:28:16] and easy to set
[00:28:17] up. You can actually install it
[00:28:20] to where it runs completely on our
[00:28:22] infrastructure. So, you don't
[00:28:24] even need to set up
[00:28:25] an Airtable automation.
[00:28:27] You can have it run hourly
[00:28:29] or instantly or however you
[00:28:31] want or if you do want to incorporate
[00:28:33] it into an Airtable automation you can
[00:28:35] do that as well,
[00:28:36] or call it from an Airtable
[00:28:38] script if you're doing a script.
[00:28:40] So, or Zapier.
[00:28:42] So, that is On2Air actions.
[00:28:44] There's a whole library of
[00:28:46] different functions that
[00:28:47] you can run. This is just one of them.
[00:28:50] So, check it out at On2Air.com.
[00:28:54] With that, we're gonna go to App a Day
[00:28:57] and I was looking at the different
[00:29:00] apps and actually I had a use case with a
[00:29:03] with a customer the other day
[00:29:06] where they needed kind
[00:29:08] of a matrix view of two
[00:29:10] two fields that they wanted to compare
[00:29:13] and this is an app Kamille, I don't know
[00:29:15] how much you use the matrix app
[00:29:18] but it's one I knew existed
[00:29:20] and I maybe tried it
[00:29:21] in the early days
[00:29:22] but I haven't used it too much
[00:29:24] but once I used it with the client
[00:29:26] I realized this is a pretty
[00:29:28] cool app that I
[00:29:29] should use more often.
[00:29:31] Yeah I agree. It's pretty useful.
[00:29:33] It reminds me a lot of
[00:29:35] like a can band viewed
[00:29:36] but
[00:29:36] with additional
[00:29:38] with I think they're called swim lanes
[00:29:41] where you know you have your vertical
[00:29:43] sort of groups but now you
[00:29:44] also have the horizontal
[00:29:45] groups as well.
[00:29:46] So
[00:29:48] yeah so you basically set it up
[00:29:51] and it actually does
[00:29:52] a good job of it tries to
[00:29:54] guess two different fields
[00:29:56] that you can use.
[00:29:57] So, it's one table that you're
[00:30:00] looking at. You can filter it down
[00:30:02] to a view if you want and you can you just
[00:30:04] specify which fields
[00:30:06] you want for your row,
[00:30:07] in which field do you want for your
[00:30:08] columns.
[00:30:09] And you can turn these
[00:30:12] little switches on so
[00:30:14] that it filters it and only
[00:30:16] shows you items where
[00:30:18] there's data in them.
[00:30:20] So, there must be a value in the
[00:30:23] column or a value in the row
[00:30:25] for it to display.
[00:30:26] So, it filters out any empty
[00:30:27] stuff
[00:30:28] Or if you turn them off
[00:30:30] then it will show you the empty one.
[00:30:32] So
[00:30:33] for here, and then you can also color it.
[00:30:36] You can actually even, I haven't
[00:30:39] tried this one but you can actually
[00:30:41] even like have a field that defines what
[00:30:44] color you want them to display.
[00:30:47] I think the view one will use
[00:30:49] the view. So, that's kind of cool.
[00:30:52] The other cool thing is it has
[00:30:54] these plus to add a new record
[00:30:57] and this makes it
[00:30:58] really easy to like if you were
[00:31:01] like a common use case would be like
[00:31:03] assigning
[00:31:04] people to jobs or projects
[00:31:09] and you want to easily say
[00:31:11] okay this person is not
[00:31:13] working on any projects.
[00:31:14] I'm going to add them to this project.
[00:31:17] And when you click on the add new record
[00:31:20] it brings up the editor but you
[00:31:22] notice that it's already
[00:31:24] populated those two
[00:31:24] fields
[00:31:25] with the corresponding wherever
[00:31:28] that cell resides.
[00:31:30] And so, it saves you that
[00:31:32] step of having to select the
[00:31:34] category and select the project lead.
[00:31:37] So Very cool. Very useful
[00:31:39] way for dealing with two dimensional
[00:31:42] data that you want
[00:31:43] to see in this kind of format. So,
[00:31:47] I think I'm gonna be using this
[00:31:49] more often now that I've revisited it and
[00:31:51] I'm thinking of more use cases I can find
[00:31:54] places for it.
[00:31:56] Yeah.
[00:31:58] All right. Why don't we?
[00:32:00] Are you ready?
[00:32:02] I am. Okay we're gonna move you up
[00:32:05] and give you a chance to talk.
[00:32:07] So, if you want to share your screen
[00:32:10] we will do an Audience Question.
[00:32:13] Is my screen being shared?
[00:32:15] Not yet.
[00:32:21] Now is it being shared?
[00:32:23] Yeah, okay.
[00:32:24] So, this is a question that
[00:32:27] was from last week and it was
[00:32:30] essentially
[00:32:31] the type of question where you
[00:32:34] have two different fields that you want to
[00:32:36] look at
[00:32:38] they wanted to use
[00:32:41] one field to determine how
[00:32:43] many days they wanted
[00:32:45] to add to another date.
[00:32:46] So, in this case they have the date,
[00:32:48] something that was quoted
[00:32:50] and then depending
[00:32:51] on what type of shipping they had
[00:32:53] determined how many days they want to add
[00:32:55] on to
[00:32:57] the quotation date to get
[00:32:59] this new delivery date or payment date.
[00:33:01] So, I chose this question because
[00:33:04] one of the first sort of approaches to
[00:33:07] solving it involved a nested if
[00:33:09] statement. And when I was looking at this
[00:33:12] question, the first thing
[00:33:14] I thought of was a switch
[00:33:15] because we are looking at the same
[00:33:18] field twice. So, if shipping equals
[00:33:21] boat than this,
[00:33:22] if shipping equals something
[00:33:23] else than that.
[00:33:25] Since we're only looking at shipping
[00:33:28] a switch statement is perfect.
[00:33:29] And so, you don't really need a nested if
[00:33:31] statement. And the other thing
[00:33:34] I noticed was that if you have
[00:33:38] the same
[00:33:39] type of function
[00:33:42] that has varying inputs,
[00:33:44] you don't need to put the whole function,
[00:33:46] the complete function once
[00:33:48] and then a complete function
[00:33:49] again with separate
[00:33:50] inputs like in this example where
[00:33:53] put in the whole date add function
[00:33:55] with a particular number
[00:33:57] of days and then put
[00:33:59] it again with a different number
[00:34:01] of days added.
[00:34:01] You actually write it much much
[00:34:03] more simpler by having
[00:34:05] the date add once
[00:34:07] and then determining how
[00:34:09] many days in just one line. So,
[00:34:12] again this is I chose this
[00:34:14] question just to show that
[00:34:17] when you have a function
[00:34:20] like date add and the second
[00:34:25] operator,
[00:34:26] second argument, the second argument
[00:34:29] and a date add function is how many
[00:34:32] of the particular time unit you
[00:34:35] want to add or subtract.
[00:34:37] That can be a function itself. And so,
[00:34:40] really what I wanted to get across
[00:34:43] is when you're writing
[00:34:44] formulas in Airtable,
[00:34:46] or Excel, or sheets,
[00:34:47] they're all kind of similar
[00:34:49] in this in that
[00:34:51] if you feel like something
[00:34:52] could be simplified
[00:34:54] you probably can simplify it.
[00:34:56] The notion and programming is don't repeat
[00:34:59] yourself or dry
[00:35:01] if you find yourself repeating
[00:35:03] something a lot
[00:35:04] within one formula there's a
[00:35:05] chance that you could condense it down.
[00:35:09] Yeah
[00:35:10] yeah that is great.
[00:35:12] That's an awesome shortcut.
[00:35:15] There are times where it is a
[00:35:18] challenge like I wish there is
[00:35:20] some way where you can
[00:35:22] like store the value in a
[00:35:23] variable within that formula
[00:35:26] variables would be great and the
[00:35:28] unfortunate work around would be to have
[00:35:31] whole other field that calculates
[00:35:34] whatever that form,
[00:35:35] that variable would be and
[00:35:37] you call it like you would a
[00:35:40] variable by just putting
[00:35:41] in the name of that field
[00:35:43] but you have extra fields
[00:35:45] in your base now and
[00:35:47] that is a drawback
[00:35:51] that
[00:35:52] if you tell
[00:35:54] there is a there is a field limit
[00:35:56] for tables
[00:35:57] I have never gotten even close to
[00:35:59] what that field limit is,
[00:36:01] I think it's in the hundreds. Yeah,
[00:36:04] I've never gotten that close
[00:36:06] but some people have
[00:36:07] and so, if you're,
[00:36:08] if you're hurting for space
[00:36:10] or even if you have just have
[00:36:13] too many fields to keep track of
[00:36:16] that becomes unwieldly
[00:36:19] and so,
[00:36:21] improvements to be made
[00:36:23] however sometimes if it's
[00:36:25] something as simple as
[00:36:26] as this
[00:36:28] condense down
[00:36:29] and you switch is sometimes
[00:36:31] you don't need a nested if nested ifs
[00:36:33] are
[00:36:34] hard to keep track of in Airtable's
[00:36:36] formula builder since the window is so
[00:36:38] small.
[00:36:40] Yeah, yeah, that's another area
[00:36:42] could improve in. And
[00:36:44] you know, the other thing
[00:36:46] I was thinking of,
[00:36:48] I had a use case with a customer of
[00:36:50] it would be really nice if
[00:36:52] if you could get the field name
[00:36:55] and use that in your
[00:36:56] formulas because sometimes you name,
[00:36:59] it could almost work like a variable.
[00:37:02] I would agree.
[00:37:03] I can think of a couple of different
[00:37:05] instances where that
[00:37:06] might be
[00:37:09] super useful, like if you're
[00:37:12] if you're out putting,
[00:37:14] you know,
[00:37:16] if the value of your formula
[00:37:18] results in either editor or author
[00:37:21] for instance,
[00:37:23] if you could then say all right,
[00:37:25] well the answer is author.
[00:37:26] So, pull from the
[00:37:27] author field instead
[00:37:29] of pulled from the editor field like
[00:37:31] you could do that
[00:37:32] but that's a longer formula
[00:37:34] that you would have to write.
[00:37:35] It would be nice if it was something
[00:37:37] that you could just pull in based on the
[00:37:39] name of the field itself.
[00:37:40] That would be cool,
[00:37:42] yep. Yeah, for sure.
[00:37:44] Yeah. In Google sheets,
[00:37:46] you can do that
[00:37:48] and so that is useful.
[00:37:51] I've had to move Google
[00:37:53] sheets over and they were reliant on that.
[00:37:55] We had to figure out
[00:37:56] a different way to do that.
[00:37:59] so, very cool. Alright,
[00:38:02] great way to showcase
[00:38:03] the power of optimizing.
[00:38:05] Yes, dry is a great principle.
[00:38:08] If you're getting into coding,
[00:38:09] that is one of the core principles
[00:38:12] I always tell people
[00:38:14] that are just learning
[00:38:15] how to program.
[00:38:16] Follow that principle.
[00:38:18] It is hard when you're,
[00:38:20] when you're first up kind
[00:38:22] of starting out because when you're
[00:38:25] at least with me,
[00:38:26] when I'm learning something,
[00:38:27] repetition is helpful for me
[00:38:29] if I write out something multiple times,
[00:38:31] I retain the information longer.
[00:38:33] But with dry, don't repeat yourself.
[00:38:36] You don't want to repeat yourself.
[00:38:38] You want to do it once the right way the
[00:38:39] first time.
[00:38:42] Once the right way the first time
[00:38:44] and then never do it again.
[00:38:46] So,
[00:38:47] you know, I could look at some
[00:38:49] of my older scripts or some
[00:38:51] of my older apps
[00:38:52] and seeing how they can be
[00:38:55] just shortened and condensed.
[00:38:58] But it all sort of relates back
[00:39:00] to formulas last year with the, last year?
[00:39:04] Either last year or the year before,
[00:39:06] I don't remember time anymore,
[00:39:07] at the Airtable user conference
[00:39:10] Cavan had a great session where she
[00:39:13] talked about formulas being the gateway to
[00:39:17] all of those types of coding with Airtable
[00:39:19] and I completely agree once you
[00:39:21] get really good at
[00:39:24] writing formulas in Airtable,
[00:39:26] scripting is just the next sort of step up
[00:39:29] and you can hone sort of the
[00:39:32] key principles of coding while
[00:39:34] you're still doing formulas
[00:39:36] and one of those
[00:39:37] principles is don't repeat yourself.
[00:39:39] Yeah,
[00:39:40] yeah, very good.
[00:39:41] Thank you Kamille.
[00:39:44] All right. Before our last segment,
[00:39:46] just a quick one more,
[00:39:48] I might have said I'm
[00:39:49] not gonna do this again, but I want to
[00:39:53] add my screen back
[00:39:54] I want to give one more shout out,
[00:39:56] I'm leaving it open for one
[00:39:57] more week for anybody,
[00:39:59] any experts or power users
[00:40:01] that want to join the Opensiders network
[00:40:04] Kamille and I are part of it
[00:40:07] and along with 20 plus
[00:40:08] other people we meet either
[00:40:10] one or two times a month
[00:40:12] and talk about kind of deeper dive
[00:40:14] into more technical,
[00:40:16] more advanced topics within Airtable.
[00:40:19] We'll often get people from Airtable
[00:40:22] or other outside people that are
[00:40:25] heavily involved in the ecosystem
[00:40:28] to come and give us demos or talk about
[00:40:31] what's going on.
[00:40:33] So, I highly recommend it
[00:40:34] for anybody that wants to get deeper
[00:40:37] into this world.
[00:40:38] If you're already doing consulting
[00:40:41] work within Airtable,
[00:40:42] we talk about, you know how
[00:40:44] to get more business,
[00:40:45] how to improve your business,
[00:40:46] things of that nature.
[00:40:49] So, check us out, contact us,
[00:40:51] contact me through Openside
[00:40:53] and I'll give you the information
[00:40:55] that you need or see
[00:40:57] our post in the Airtable
[00:40:58] community or join the
[00:40:59] BuiltOnAir community.
[00:41:01] It's also posted in there
[00:41:03] as well in our Slack channel.
[00:41:05] Okay,
[00:41:07] okay, for our final segment
[00:41:09] Scripting Time,
[00:41:10] I mentioned last week that we were
[00:41:11] gonna dive deep into meta APIs
[00:41:16] and let's do that. So,
[00:41:21] so, one of the cool things
[00:41:23] about that the Airtable is
[00:41:25] getting into is the
[00:41:26] ability to
[00:41:28] dynamically create tables
[00:41:31] and fields within your table
[00:41:34] and allow you to do that.
[00:41:36] You can now do that through
[00:41:38] the scripting API before
[00:41:40] it was just available through
[00:41:42] the app developer environment.
[00:41:44] so now this this makes it easy
[00:41:46] to do it through scripts.
[00:41:48] And so I'm going
[00:41:49] to walk through how you would
[00:41:51] do that and what it looks like.
[00:41:54] So, the first thing that you can do
[00:41:56] is you can actually create new tables
[00:41:59] within your base.
[00:42:00] So, that's done with the base
[00:42:02] that create table a sync.
[00:42:04] This will be the name of the table
[00:42:07] and that you give it an array of fields
[00:42:11] that will be added upon
[00:42:14] table creation to your table.
[00:42:16] So, you give it the name
[00:42:18] so this will be the label
[00:42:19] of the field and then you
[00:42:20] specify the type
[00:42:23] and one thing that is important to know is
[00:42:30] they don't support all
[00:42:33] field types yet
[00:42:35] and this is actually a big hurdle
[00:42:38] until you can really
[00:42:40] take advantage of this
[00:42:41] meta API. So, as far as
[00:42:43] the fields that they do support
[00:42:46] you creating its this
[00:42:47] list here, I won't read them all
[00:42:50] but the ones that they
[00:42:51] don't support is this
[00:42:52] list here
[00:42:53] and so, the big one is formula
[00:42:57] any calculated field, right?
[00:43:00] Let's see.
[00:43:03] Yeah, it looks like any calculated field,
[00:43:05] I think it's all calculated fields.
[00:43:07] Yeah,
[00:43:09] yeah all calculated fields.
[00:43:14] I get it for some of these formula
[00:43:16] I understand because
[00:43:18] they have a particular
[00:43:19] formula language that
[00:43:20] they use and you know
[00:43:22] what if you come up with
[00:43:23] an error or something it's fine but
[00:43:26] auto number
[00:43:27] number, why? There's no settings.
[00:43:30] Yeah. Yeah
[00:43:31] That one is odd.
[00:43:33] Yeah exactly or even created by
[00:43:35] last modified by
[00:43:37] I mean maybe the date
[00:43:38] formatting. Some of these,
[00:43:40] I feel like I should be able
[00:43:42] to be created and then
[00:43:43] maybe you don't edit the options
[00:43:46] but
[00:43:47] I don't know. Auto numbers
[00:43:49] shouldn't be on that list.
[00:43:50] Yep, yep I agree,
[00:43:51] I agree but I think they made
[00:43:52] yeah they made that kind
[00:43:54] of hard line up will only
[00:43:55] do up datable fields.
[00:43:59] So, that's why I like, we at On2Air,
[00:44:02] we kind of we keep our eye closely on this
[00:44:06] because we have a schema app
[00:44:08] that allows you to manage
[00:44:11] or track all of your
[00:44:12] fields and we're exploring how we can
[00:44:16] leverage the med API to help with
[00:44:18] automating creation of data.
[00:44:20] But really I think it
[00:44:22] would be a frustrating
[00:44:24] experience of not being able to do
[00:44:27] these because have you used any base that
[00:44:30] doesn't use these fields?
[00:44:33] I don't think I have.
[00:44:36] No.
[00:44:37] Yeah, so it would be tough
[00:44:39] to try to build some kind of
[00:44:41] automated solution that
[00:44:42] that doesn't support these to try to,
[00:44:45] you know, market that I think people
[00:44:47] would get frustrated pretty quickly.
[00:44:49] So, that's why we've held off until
[00:44:51] until these are supported.
[00:44:54] But anyway, so if by chance
[00:44:56] you can survive without
[00:44:58] those needing to be auto created
[00:45:01] and you're okay doing them manually,
[00:45:04] then this is the API to do that.
[00:45:07] So, you can do all the fields at once
[00:45:11] when you're creating a table
[00:45:13] you have to have at least
[00:45:15] one field in your array
[00:45:16] because it needs a primary field,
[00:45:19] so, it could just be a name field that's a
[00:45:21] single line text,
[00:45:23] and what not there.
[00:45:25] So, this is going to create
[00:45:27] a new field with two, with
[00:45:28] or sorry, a new table with two fields,
[00:45:31] a name and an email
[00:45:32] and then it returns just the table ID.
[00:45:36] So, if you want to do more to that table,
[00:45:39] you then need to get it from the base.
[00:45:42] Either you can actually get it from this
[00:45:44] table ID or the name
[00:45:47] and I actually haven't run this yet.
[00:45:49] So, I'm curious to see if this will work
[00:45:51] because
[00:45:52] I'm curious to see if you can
[00:45:54] retrieve a table that
[00:45:56] you just created, or if
[00:45:58] there needs to be some kind
[00:45:59] of like resync process happening.
[00:46:01] So, I'll be curious to see
[00:46:03] what happens when we run this.
[00:46:05] Then the next thing is
[00:46:07] this way is once you already
[00:46:08] have a table that's
[00:46:09] already created and you want
[00:46:12] to add fields to it
[00:46:13] then based off of the table
[00:46:15] reference, you can use
[00:46:17] the create field a sync
[00:46:19] and here you just pass in the
[00:46:22] the name
[00:46:23] so, capitalize that.
[00:46:24] So, the name will be phone
[00:46:26] and then the type is phone number.
[00:46:29] And then the other way,
[00:46:31] if it's a single select field
[00:46:33] that has choices,
[00:46:34] then you can actually pass
[00:46:37] in those choices.
[00:46:39] So, here you can this will be the
[00:46:41] selection in there and you
[00:46:44] can actually also specify the color.
[00:46:47] Unfortunately I could not find.
[00:46:50] Let me see if they document the colors.
[00:46:52] Oh, it's somewhere but I don't think
[00:46:55] it's in here.
[00:46:56] Yeah, it is in the in the app
[00:46:59] SDK documentation but,
[00:47:02] I don't know if it's in the scripting one,
[00:47:05] I couldn't find it. For everyone's
[00:47:07] reference. They follow
[00:47:09] somewhat predictable
[00:47:11] a color naming scheme. So,
[00:47:15] there's the base colors
[00:47:16] blue, yellow, orange, et cetera,
[00:47:18] and then each of them
[00:47:19] have dark one,
[00:47:21] they have light one, light two,
[00:47:24] and
[00:47:25] dark one, dark two?
[00:47:28] Yeah, just light then it
[00:47:31] will the Auto Complete
[00:47:33] will give you some choices.
[00:47:35] It's the color light, dark, bright.
[00:47:38] Those are the four
[00:47:39] sort of variants and then
[00:47:41] there's two types of light. So,
[00:47:44] just so you know,
[00:47:45] if you had to guess
[00:47:47] what the correct color would
[00:47:48] be,
[00:47:49] guess one of those, the color name,
[00:47:53] either bright, light, or dark or nothing.
[00:47:56] And then one or two at the end
[00:47:58] and then you could probably interesting.
[00:48:00] It's only given the light ones
[00:48:02] in the Auto Complete. But not dark is one,
[00:48:05] I'm pretty sure. I think
[00:48:08] Yeah, dark isn't showing up.
[00:48:10] Oh no, am I wrong? I think you're right,
[00:48:14] but I wonder maybe
[00:48:15] they're not exposed in the scripting.
[00:48:17] That's strange.
[00:48:18] Yeah, so
[00:48:20] we'll do Yeah.
[00:48:21] So, only light is coming
[00:48:23] in the Auto Complete.
[00:48:25] So, it can be that.
[00:48:26] So, that's how you would add
[00:48:29] new fields and if it has choices,
[00:48:31] so, like a single select or a multi select
[00:48:34] you just pass in this choices
[00:48:36] object with that's an array
[00:48:39] and then if you ever needed
[00:48:41] to update that, this is interesting,
[00:48:44] you can't just add one additional one.
[00:48:46] If you want to update it any time you
[00:48:48] update it, you have to give the full list.
[00:48:52] So, one way to do that and this comes
[00:48:54] from their documentation is just to grab
[00:48:57] the existing list of choices
[00:49:00] from your field
[00:49:01] and this dot dot dot
[00:49:03] that's called a spread operator.
[00:49:05] That basically extracts
[00:49:07] all those choice values
[00:49:08] and inserts them into here.
[00:49:10] So, it's kind of a shortcut
[00:49:12] way to to put all of those in there.
[00:49:15] So, this will get all the existing one and
[00:49:17] then add a new one
[00:49:19] after those existing ones.
[00:49:21] So, this is the logic for just
[00:49:24] adding one new one but
[00:49:25] you need to make sure that
[00:49:27] you get all the previous ones
[00:49:29] because without that
[00:49:30] then it will change your array
[00:49:32] to just be one selection item
[00:49:34] and it will delete
[00:49:35] those previous ones in there.
[00:49:39] This is a concept that's
[00:49:41] throughout sort of
[00:49:42] Airtable and javascript
[00:49:44] in general is if you're
[00:49:46] updating an array,
[00:49:48] you want to, if you want to
[00:49:50] include the existing items of the array,
[00:49:53] you have to declare that first
[00:49:55] and then you add the stuff at the end.
[00:49:57] You also need to spread
[00:49:59] your original options like you
[00:50:01] said with the three dots
[00:50:02] at the front.
[00:50:03] If you didn't have those three dots,
[00:50:04] you would be passing as an
[00:50:05] array item. Another array. So,
[00:50:09] Airtable wouldn't be able to read that
[00:50:12] just there's a lot, there's a lot in here.
[00:50:16] But it's the same with like
[00:50:18] adding something in an automation.
[00:50:21] Like if you wanted to
[00:50:22] append a new multi select item
[00:50:25] to some record that
[00:50:26] already had a few things
[00:50:28] selected, you have to pass in
[00:50:29] what it already had selected
[00:50:31] and then the new
[00:50:31] thing at the end.
[00:50:32] So, this is just a concept that's
[00:50:35] everywhere in Airtable.
[00:50:37] Yeah. And if you're wondering
[00:50:39] about the squiggly line,
[00:50:40] check out last week's
[00:50:41] episode, we talk about why that is
[00:50:45] and I saw this earlier,
[00:50:47] this one takes a little
[00:50:49] bit more effort
[00:50:51] requires two question marks
[00:50:52] to get rid of the squiggly lines.
[00:50:54] So, check out last week's episode.
[00:50:56] We explain those those
[00:50:58] issues that why they
[00:50:59] complain about that.
[00:51:01] Okay, let's run this.
[00:51:03] Let's see if it works.
[00:51:04] So, we should create a new table,
[00:51:05] it will have to initial fields
[00:51:07] and then we're gonna
[00:51:09] add a third field and then
[00:51:10] a fourth field with,
[00:51:12] and it should end up with three choices,
[00:51:15] type A type B and the
[00:51:16] new choice. So,
[00:51:17] everything got run smoothly.
[00:51:19] Let me do this so we
[00:51:21] can see it get created.
[00:51:26] there's our people.
[00:51:28] Yeah, I didn't, I didn't put any council
[00:51:31] dot log, and we have our email, our phone.
[00:51:34] I guess I didn't capitalize this
[00:51:36] and we have type A, type B,
[00:51:37] and a new choice.
[00:51:37] So, it ran.
[00:51:38] And we got our colors in there,
[00:51:41] blue, red, I didn't specify
[00:51:43] a color for that one. So, we
[00:51:46] successfully created a new table
[00:51:48] with four fields
[00:51:50] and different field types.
[00:51:52] Yay. All right. Have you,
[00:51:54] have you attempted a multi
[00:51:58] multiple record link
[00:52:00] creating a table with that type
[00:52:02] of field declared first?
[00:52:04] That's right because
[00:52:06] you can do that now.
[00:52:09] You can for reference.
[00:52:11] My newest app, Junction Assistant
[00:52:13] does this, where it allows you to create a
[00:52:16] new table
[00:52:19] and the whole point is that
[00:52:20] it only really deals with
[00:52:21] multiple record link type
[00:52:22] fields.
[00:52:23] If you create a new table and
[00:52:27] in the process of creation,
[00:52:29] add
[00:52:30] multiple record links
[00:52:32] at the same time.
[00:52:34] Airtable hates that.
[00:52:35] So, what the app actually does
[00:52:37] is it creates the table
[00:52:38] first waits for the table to be
[00:52:41] in existence, and then it adds
[00:52:44] the field to the new table
[00:52:46] and not like you have up
[00:52:48] at the top where you create
[00:52:50] the table with certain fields
[00:52:52] in place already.
[00:52:54] Yeah, I don't know if you can
[00:52:56] do that in scripting
[00:52:57] because scripting is all
[00:52:58] asynchronous.
[00:52:59] So, maybe that's it.
[00:53:01] Maybe it's not a problem there.
[00:53:03] But
[00:53:04] for everyone's reference
[00:53:06] if you wanted to peek behind the curtain.
[00:53:08] That's how Junction Assistant does it.
[00:53:10] Gotcha. Yeah.
[00:53:11] So, you can't add them
[00:53:13] to this initial list.
[00:53:14] I don't know, you can't
[00:53:15] do it in apps unless you make
[00:53:17] the entire app I think asynchronous.
[00:53:18] So, I'm curious to see
[00:53:21] but you're doing it with
[00:53:24] does the link go to a previously
[00:53:27] existing table or is it?
[00:53:29] So, all of the tables that you're
[00:53:32] selecting
[00:53:32] exists already.
[00:53:33] The table that you're creating
[00:53:35] of course is brand new.
[00:53:36] But I was running into
[00:53:38] I think what, I think the problem was
[00:53:41] when you create inter table
[00:53:44] record lengths, you're adding a
[00:53:47] field not just to your
[00:53:49] table that you're looking at,
[00:53:51] you're adding a reverse link
[00:53:53] to the other table.
[00:53:54] So, I think the error that it was running
[00:53:57] into was in the existing tables,
[00:54:00] it was trying to create a link
[00:54:02] to a table that didn't exist yet.
[00:54:05] So,
[00:54:07] I think that's why it had to be done.
[00:54:10] I'm not sure if you can do it
[00:54:12] in scripting, you can add
[00:54:15] you can add a new field
[00:54:17] to an existing table,
[00:54:18] that is a multi multiple record
[00:54:21] link field.
[00:54:22] I don't know if you can create
[00:54:24] a table with a multiple record link field
[00:54:27] declared.
[00:54:29] Yeah, I haven't tested it.
[00:54:31] That's let's try.
[00:54:34] I genuinely don't know.
[00:54:37] I have my suspicions that it would
[00:54:40] run into
[00:54:42] an issue.
[00:54:55] Oh, we gotta do this on the new one.
[00:54:58] Yeah. Okay.
[00:55:29] So, this will be our link,
[00:55:32] I will be a multiple record links.
[00:55:39] Better
[00:55:42] object. Yeah.
[00:55:48] So, is it this?
[00:55:56] This, right? Yes.
[00:55:58] Okay.
[00:55:59] So, we're creating a new link test
[00:56:02] and we have a link that points to this
[00:56:04] result table.
[00:56:06] Let's close out. Let's give it a try.
[00:56:12] It worked. Okay.
[00:56:14] Asynchronous.
[00:56:15] There we go.
[00:56:18] Yeah that's the difference. Yeah.
[00:56:21] Okay. Very cool.
[00:56:22] So, you can do this in scripting.
[00:56:25] If you're doing any app
[00:56:26] development then that
[00:56:28] scenario won't work.
[00:56:30] If there's a way,
[00:56:31] there's ways around it,
[00:56:32] I just didn't go with that option.
[00:56:33] This the wait for the table
[00:56:36] to be made and then add the fields later,
[00:56:39] is you run into less errors that way
[00:56:43] anyway but it is good to know that you
[00:56:46] can do it
[00:56:47] without sort of any sort of work
[00:56:49] around within the scripting environment.
[00:56:51] And that took you about
[00:56:53] two minutes to test. So, very quick,
[00:56:57] very cool. So definitely powerful.
[00:57:00] Like I said once once they get the full
[00:57:02] breath of field
[00:57:03] it will be a complete game changer
[00:57:06] of what you could do.
[00:57:08] So, anxiously awaiting that.
[00:57:11] So, that concludes today's episode.
[00:57:14] Thanks, glad Kamille, you can make it. And
[00:57:17] everybody out there glad
[00:57:19] you could join us. Feel free to
[00:57:21] give your feedback.
[00:57:23] Be sure to subscribe to our channel
[00:57:25] and join us next week.
[00:57:26] Until then
[00:57:27] we hope you enjoy what you build OnAir.
[00:57:30] Take care