6/21/2022 – BuiltOnAir Live Podcast Full Show – S11-E08

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

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:03:12 –

Field Focus – 00:29:57 –

A deep dive into the Formulas & Linked Records Formulas, Linked Records – How to use Formulas and Linked Records to display unpaid balances for work performed in an Invoicing base. 

Automate Create – 00:43:43 –

Watch as we review and work through automations. Using Make.com (formerly Integromat) to bill customers through Stripe

Learn more about the automation

An App a Day – 00:59:30 –

Watch as we install, explore, and showcase the Barcode Generator App from the Airtable Marketplace. The app is described as “Use this app to quickly generate barcodes using data from your tables. Once generated, barcodes appear as attachments in a separate field.”.

View App

Full Segment Details

Segment: Round The Bases

Start Time: 00:03:12

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

Segment: Field Focus

Start Time: 00:29:57

Learn about the Formulas & Linked Records – How to use Formulas and Linked Records to display unpaid balances for work performed in an Invoicing base.

A deep dive into the Formulas & Linked Records Formulas, Linked Records – How to use Formulas and Linked Records to display unpaid balances for work performed in an Invoicing base. 

Segment: Automate Create

Start Time: 00:43:43

Airtable Automations – Billing Automation

Watch as we review and work through automations. Using Make.com (formerly Integromat) to bill customers through Stripe

Learn more about the automation

Segment: An App a Day

Start Time: 00:59:30

Airtable App Showcase – Barcode Generator – Use this app to quickly generate barcodes using data from your tables. Once generated, barcodes appear as attachments in a separate field.

Watch as we install, explore, and showcase the Barcode Generator App from the Airtable Marketplace. The app is described as “Use this app to quickly generate barcodes using data from your tables. Once generated, barcodes appear as attachments in a separate field.”.

View App

Full Transcription

The full transcription for the show can be found here:

[00:01:41] Welcome to the BuiltOnAir podcast,
[00:01:44] season 11, episode eight,
[00:01:46] myself Dan Fellars and Kamille
[00:01:49] with us today. Welcome Kamille.
[00:01:52] It's just the two of us today,
[00:01:54] Alli couldn't make it,
[00:01:55] she'll be back with us next
[00:01:56] week and
[00:01:57] should have a guest with us next week.
[00:02:00] We were gonna have one today
[00:02:02] but wasn't able to make it
[00:02:03] so we'll reschedule him for another day.
[00:02:06] So Kamille and I are gonna
[00:02:08] fill the airwaves with you
[00:02:11] every week we come
[00:02:12] with a one hour podcast,
[00:02:14] talking about all things Airtable.
[00:02:16] I'll go through briefly what we're gonna
[00:02:17] be talking about today.
[00:02:19] We always start off with
[00:02:21] our Round the Bases
[00:02:22] covering everything to keep you up
[00:02:24] to date on what's going on
[00:02:26] in the Airtable communities,
[00:02:28] then we'll do a quick
[00:02:29] spotlight on our primary sponsor On2Air.
[00:02:33] Then Kamille is gonna take over
[00:02:35] and gonna do two different segments,
[00:02:37] We're gonna break them up
[00:02:39] into the first one,
[00:02:40] talking about the formulas and
[00:02:42] linked records in our field focus segment
[00:02:45] in a base that she's built and uses
[00:02:48] for invoicing and billing
[00:02:51] and then she'll go into
[00:02:53] how to automate the billing
[00:02:55] aspect using make.com
[00:02:58] and then quick shout out
[00:03:00] to our community
[00:03:01] and how you can join and then finally
[00:03:03] we're going to do an app highlight
[00:03:05] on the barcode generator app.
[00:03:07] So with that we will start
[00:03:10] with Round the Bases
[00:03:12] and the first one
[00:03:14] is a new contest
[00:03:16] that Airtable just announced last week
[00:03:19] on the Interface Designer contest.
[00:03:23] So Jordan, the manager of the community
[00:03:27] has posted that they are
[00:03:29] giving away money.
[00:03:30] So it looks like three different
[00:03:32] categories, best design,
[00:03:35] best use case/ solution focus
[00:03:38] and a wild card.
[00:03:40] And so it looks like they're gonna
[00:03:42] have nine money winners.
[00:03:44] So for each category
[00:03:45] there's a $5000 1st place,
[00:03:48] a $2000 2nd place and $1000 3rd place
[00:03:51] plus swag and an invitation
[00:03:54] to a special meeting
[00:03:56] to give feedback. Thoughts
[00:03:58] Kamille?
[00:03:59] Well, I put in my comment
[00:04:01] that on this thread that the
[00:04:05] the real prize is
[00:04:07] being at the feedback, you know,
[00:04:11] hearing because
[00:04:12] presumably the people who
[00:04:15] win prizes for this contest
[00:04:17] will have used Interface
[00:04:19] Designer enough
[00:04:20] to create a good, you know,
[00:04:23] interface for it,
[00:04:24] meaning they probably would have
[00:04:26] run into a roadblock or two
[00:04:27] and would have had,
[00:04:28] I wish I could have done
[00:04:30] such and such to make this design a
[00:04:32] little better. And so
[00:04:34] it's an interesting way of sort of
[00:04:37] picking out who would have
[00:04:39] the most sort of fleshed out
[00:04:41] feedback to give about
[00:04:43] Interface Designer, which is still
[00:04:45] technically in beta,
[00:04:46] they still call it a beta,
[00:04:48] even though it's more or less
[00:04:49] fully functional,
[00:04:50] but there's a lot of things that I think
[00:04:52] many people
[00:04:53] want that are missing.
[00:04:55] So
[00:04:58] yeah, I liked
[00:04:59] I'm hoping they respond to this one
[00:05:04] if there's any planned new features
[00:05:09] before the competition time.
[00:05:12] We'll see if they
[00:05:13] have anything planned
[00:05:15] because it's been a while since
[00:05:17] they've updated Interface Designer
[00:05:20] with new features. The button element is I
[00:05:22] think the newest piece
[00:05:26] I don't anticipate them releasing,
[00:05:28] be weird if they released a new feature
[00:05:31] during the time of the contest,
[00:05:33] Can you scroll back up
[00:05:34] please to see when the
[00:05:36] deadline is?
[00:05:37] July 31.
[00:05:38] Okay, so you know,
[00:05:40] a month and a half essentially to
[00:05:48] yeah,
[00:05:50] as much as I want new features,
[00:05:51] you should probably wait
[00:05:53] until this contest is
[00:05:54] over because
[00:05:55] it's gonna delay people
[00:05:57] as they figure out how to use it
[00:05:59] unless it's very simple.
[00:06:02] Yeah,
[00:06:04] yeah, so this will be cool.
[00:06:06] Be interesting to see
[00:06:08] if submissions Scott posted
[00:06:11] free money. I kind of agree.
[00:06:12] I was mentioning before the show,
[00:06:14] it'll be interesting how many submissions
[00:06:18] they get. Because like you were
[00:06:21] mentioning like
[00:06:22] there's only so much you can do
[00:06:24] in Interface Designer,
[00:06:26] so it'll be interesting
[00:06:27] how they judge
[00:06:28] because I think a lot will look
[00:06:30] fairly similar.
[00:06:32] I have seen
[00:06:36] some creative use cases
[00:06:38] and I'm not gonna reveal
[00:06:39] other people's secrets
[00:06:40] in case they want to,
[00:06:42] you know, compete themselves,
[00:06:44] but I've seen people get
[00:06:45] fairly creative how to
[00:06:46] work around some of the
[00:06:48] limitations of Interface Designer and
[00:06:51] we've all seen examples
[00:06:54] from Chris Dancy who has
[00:06:57] very complex Airtable solutions
[00:07:00] including nowadays some fairly involved
[00:07:04] Interface Designers.
[00:07:06] So interested, I'm sure he'll
[00:07:09] Submit one or at least you know,
[00:07:12] he publishes some of his interfaces
[00:07:14] every now and I can so
[00:07:16] yeah if I were to pick
[00:07:18] who's to beat, it's probably Chris Dancy.
[00:07:20] Yeah I think
[00:07:22] Alli's got some good ones too.
[00:07:25] She might submit if there that's possible.
[00:07:28] So anyways. Yeah we'll
[00:07:30] when they announce it
[00:07:32] looks like so July 31st is the
[00:07:34] deadline, so get your interfaces in
[00:07:37] by July 31st at five p.m. Pacific time
[00:07:40] and then they'll announce
[00:07:42] the winners in August
[00:07:43] so we'll definitely
[00:07:45] check out
[00:07:46] keep our eye on that
[00:07:47] and showcase the winners and the cool
[00:07:50] interfaces that people submit.
[00:07:54] So looking forward to that in August
[00:07:57] alright,
[00:07:58] next one, as far as new features,
[00:08:02] there was a big one
[00:08:05] so we have we have a couple.
[00:08:07] There's two that I want to highlight.
[00:08:11] So the big one here is the
[00:08:13] what they're calling document automator
[00:08:16] and they kind of announced this
[00:08:18] is like a new app
[00:08:19] in the email announcement that
[00:08:21] I got was a kind of announced it as like
[00:08:24] this big new app but it's really just
[00:08:26] built into automations.
[00:08:28] And so I don't know
[00:08:30] if it's worthy of calling it an
[00:08:33] app but it is a cool new feature
[00:08:35] but it's basically the ability to
[00:08:37] create a new Google doc
[00:08:39] based off of data from your
[00:08:42] Airtable data
[00:08:43] and it's built into automations.
[00:08:46] So along with all the other tasks
[00:08:48] there's now they had Google forms
[00:08:51] and Google sheets,
[00:08:52] tasks before and now they
[00:08:54] have Google docs
[00:08:56] and so you can see that in automations,
[00:08:59] I'll do a quick demo of it but
[00:09:01] any input there Kamille?
[00:09:07] Yeah there's
[00:09:08] yeah I'm sure you'll get into
[00:09:09] in your demo there's some
[00:09:10] limitations.
[00:09:11] It's a nice addition to create a
[00:09:16] the Google doc from,
[00:09:18] you know, just some simple data
[00:09:20] if you need a quick export things and
[00:09:22] you know, I'll wait till the demo to
[00:09:24] get into specifics.
[00:09:26] Yeah. Yeah, so good
[00:09:28] good stuff there. There's a couple,
[00:09:32] you know, I'll be very unbiased
[00:09:35] in my in my evaluation
[00:09:37] of it but I am biased.
[00:09:38] I have a product in On2Air
[00:09:40] that's similar,
[00:09:41] more advanced than what can
[00:09:43] be done here.
[00:09:44] So if I'll just give a quick plug
[00:09:47] before we do the demo,
[00:09:48] if you're using this
[00:09:49] and it's not quite what you need
[00:09:51] and you need more power
[00:09:52] then check out the On2Air product
[00:09:55] that also integrates with Google docs.
[00:09:58] But yeah, let me do
[00:10:00] a quick demo of it. Actually over here.
[00:10:03] So basically you would set up
[00:10:05] your automation like you
[00:10:07] would normally you can
[00:10:08] trigger it off of a record
[00:10:10] or at a scheduled time.
[00:10:12] The cool thing that this one
[00:10:13] does that we don't yet do
[00:10:15] will do eventually
[00:10:17] but the cool thing is this one
[00:10:19] you don't need to trigger it
[00:10:21] off of a record
[00:10:22] so you can actually you can
[00:10:23] actually insert a view
[00:10:25] a table and it doesn't have to be
[00:10:27] tied to a single record
[00:10:29] which I know our product
[00:10:30] and I think some of the other
[00:10:32] competitive ones and like
[00:10:33] Page designer
[00:10:35] are kind of triggered off of
[00:10:37] a single record,
[00:10:39] so that's kind of cool.
[00:10:40] But basically
[00:10:41] you would just set it up,
[00:10:43] you have to connect to your Google
[00:10:45] account and then you specify
[00:10:48] a folder
[00:10:49] and then you can insert dynamically the
[00:10:51] title based off of any fields
[00:10:54] that you retrieve through a find record
[00:10:56] or look up record
[00:10:58] and then your content here,
[00:11:01] this is what's going to
[00:11:04] get inserted into your
[00:11:06] into your Google doc.
[00:11:08] And so you can actually
[00:11:10] insert grids of like the full
[00:11:13] view.
[00:11:14] And another cool thing that it does
[00:11:17] is if your view has grouping it will
[00:11:20] actually insert that same grouping
[00:11:22] into the Google doc,
[00:11:24] so you'll have a table
[00:11:25] of each group.
[00:11:26] So that's kind of a cool feature.
[00:11:32] The other the other thing,
[00:11:33] I don't know if they announced this
[00:11:35] or we just found
[00:11:36] it but I'll showcase it here
[00:11:39] is they updated the token thing
[00:11:42] and so now you can
[00:11:44] click on this
[00:11:45] is really cool because
[00:11:47] before you weren't able to change
[00:11:49] like where that token was coming from
[00:11:52] and now you can so this feature is cool
[00:11:55] before and this works
[00:11:56] in all editor types
[00:11:58] where whenever you're inserting
[00:12:00] dynamic data.
[00:12:02] There's now this
[00:12:05] thing and so you can change it,
[00:12:07] you can modify it so you can convert it to
[00:12:09] uppercase lowercase
[00:12:10] trimming means if there's any
[00:12:13] spaces at the beginning or end
[00:12:15] then it'll cut those off
[00:12:17] and then length will return
[00:12:19] the number of characters
[00:12:20] in there
[00:12:22] and so you can copy it as well.
[00:12:24] So that's another feature that yeah.
[00:12:27] Did you see a post
[00:12:28] or any mention of that?
[00:12:30] I didn't see any mention.
[00:12:31] I just pointed it out in the comments
[00:12:33] of that red because that's when I
[00:12:35] I in their screenshots I think
[00:12:37] I noticed the down arrow and I was like
[00:12:40] I don't remember down arrows
[00:12:42] being there and then
[00:12:43] I looked in yeah editing tokens.
[00:12:45] It's just really really useful especially
[00:12:48] for the grid of records
[00:12:49] type thing where you're selecting
[00:12:51] which
[00:12:54] you know which columns
[00:12:55] that you want in there
[00:12:57] before you have to do it all,
[00:12:59] and then oh
[00:13:00] I actually want to add this other
[00:13:01] column and you have to delete
[00:13:03] the old token and go back and add
[00:13:05] you know each one again
[00:13:07] and it was kind of cumbersome
[00:13:09] and annoying and now it's
[00:13:10] not cumbersome or annoying
[00:13:11] and it's like you said
[00:13:13] it works not just in the
[00:13:14] Google doc
[00:13:15] step,
[00:13:15] it works in all of the steps
[00:13:17] where you can insert a token in the first
[00:13:20] place.
[00:13:20] Yeah.
[00:13:22] Andrew and the community mentions
[00:13:24] I was hoping you could modify
[00:13:26] the date format
[00:13:27] with the new token editor. But
[00:13:29] yeah that'd be super useful.
[00:13:33] Right now the
[00:13:34] editing options are basically for
[00:13:38] text based values.
[00:13:41] there's no like number formatting either,
[00:13:44] wanted to chop off the decimal
[00:13:47] values for a number.
[00:13:49] You couldn't do that either. So,
[00:13:51] you know,
[00:13:52] still would probably be reliant
[00:13:54] on a formula field or a script to
[00:13:56] handle your formatting options.
[00:13:58] But
[00:13:58] I think that'd be a good feature to add
[00:14:01] some simple
[00:14:03] formatting options for numbers or dates.
[00:14:06] Yeah. Yeah, for sure. So
[00:14:09] yeah, so that's kind of
[00:14:11] what you would do there and then,
[00:14:13] you know, you run it
[00:14:14] like any other automation
[00:14:16] and then what it returns is the UR L
[00:14:19] to the Google doc.
[00:14:21] open this up
[00:14:25] to that you'll see
[00:14:27] the output
[00:14:28] and it should just have a table.
[00:14:33] There we go. And so
[00:14:36] it so this one
[00:14:37] was grouped by so it generates
[00:14:42] those groups and it has the empty section
[00:14:45] couple of things
[00:14:46] I don't like is that it puts this
[00:14:48] at the bottom of each table
[00:14:51] and that's not something
[00:14:53] you can turn off, which is odd.
[00:14:55] Yeah, it also
[00:14:56] always inserts a link back
[00:15:00] I believe to the record.
[00:15:03] So those are a couple of things
[00:15:06] that are kind of annoying.
[00:15:07] There's lacking formatting
[00:15:11] things like this obviously are not ideal.
[00:15:16] So
[00:15:20] yeah, so room for improvement,
[00:15:22] but for the basics
[00:15:24] of just generating text into a
[00:15:26] Google doc. It's definitely
[00:15:29] it's a quick win. So comes in handy.
[00:15:35] Finally I'll show
[00:15:37] I know you
[00:15:38] you and others pointed out ways to,
[00:15:42] you know, one of the big things
[00:15:44] that doesn't do by default is
[00:15:47] is saving it as a pdf
[00:15:49] back into your Airtable.
[00:15:52] But there's an easy way to
[00:15:54] do that.
[00:15:55] So if you just
[00:15:57] so I'm saving this back
[00:15:59] into a new table so I'm
[00:16:01] creating a new record
[00:16:02] and I believe this is the right format.
[00:16:05] So you just insert the U.R. L.
[00:16:07] That came from
[00:16:09] that came from the
[00:16:11] save the generated create doc task
[00:16:14] and then just put a
[00:16:16] slash export
[00:16:17] question mark format equals P. D. F.
[00:16:21] Does that look right?
[00:16:23] Sure, something like that.
[00:16:25] I'm pretty sure that's it.
[00:16:28] So let's so I did that,
[00:16:29] let's run a test.
[00:16:36] Go above the blue.
[00:16:38] But yeah you want to do a fresh one?
[00:16:42] Let's do that one. Yeah
[00:16:46] it takes a second, I know to
[00:16:51] I didn't
[00:16:52] I don't think I've ever noticed that
[00:16:54] Goes one step at a time.
[00:16:56] Okay so here we got
[00:16:58] let's see if this works.
[00:17:02] It did
[00:17:03] there's our pdf. So
[00:17:05] if you're trying to save
[00:17:08] that Google doc back into a pdf,
[00:17:11] it's as simple as that
[00:17:12] so that's a nice little trick.
[00:17:17] It just requires adding that to the end,
[00:17:20] it does also require that whatever
[00:17:23] folder you told the
[00:17:25] Google doc step to create
[00:17:27] the document in
[00:17:29] that has to be a public or shared folder
[00:17:31] or public folder.
[00:17:32] Otherwise
[00:17:36] Airtable can't
[00:17:38] go to that U. R. L.
[00:17:39] To convert it into a pdf and then add it.
[00:17:45] Yeah. Good point.
[00:17:46] I forgot to check that.
[00:17:48] So luckily I used a folder that must
[00:17:50] be public
[00:17:52] for that to work so.
[00:17:53] Yeah. Very good point.
[00:17:54] Yeah it has to be public.
[00:17:56] The folder that you're saving
[00:17:58] your Google docs too so.
[00:18:01] Alright so that's very cool.
[00:18:03] I know a lot of people will
[00:18:05] will find that useful.
[00:18:06] And there are third parties,
[00:18:09] On2Air's one
[00:18:10] documint's another for more advanced
[00:18:13] document creation. So
[00:18:15] if this doesn't cut the bill
[00:18:17] for what you need then then
[00:18:19] there are alternatives
[00:18:20] that you can check out. So
[00:18:23] that is a nice feature.
[00:18:25] Okay moving on
[00:18:27] to the BuiltOnAir community,
[00:18:30] the next item I wanted to highlight.
[00:18:33] So Justin who's a friend of the show
[00:18:36] posted this
[00:18:37] I thought this was cool I think
[00:18:40] people
[00:18:41] he was just thinking
[00:18:43] maybe just some late night
[00:18:45] Airtable dreaming
[00:18:47] says last night while
[00:18:48] replying to someone's query
[00:18:50] on another forum about how to
[00:18:51] monitor for deleted records.
[00:18:53] A solution occurred to me
[00:18:55] it wasn't possible at the
[00:18:56] time of the original discussion
[00:18:58] so basically he's talking about
[00:19:01] how to keep track if a record
[00:19:04] got deleted
[00:19:05] because there isn't any trigger and
[00:19:08] there's no way I know I've tried
[00:19:10] to find ways and we do have kind of a
[00:19:11] workaround to
[00:19:12] get notified
[00:19:13] when the record gets deleted
[00:19:16] but it's not ideal.
[00:19:17] But what his suggestion is
[00:19:20] is you use a sync table
[00:19:23] and you check it.
[00:19:25] And there's an option
[00:19:27] when you sync a table to keep it on
[00:19:30] the destination
[00:19:32] even if it gets deleted
[00:19:33] from the source.
[00:19:34] So you make sure that
[00:19:35] you have that setting on. And
[00:19:39] then what happens is
[00:19:41] if it gets deleted from the source,
[00:19:44] the source I. D. Will get removed
[00:19:46] from the destination
[00:19:48] because now there's no
[00:19:49] longer a source record
[00:19:50] associated with that.
[00:19:52] So then you can easily create a
[00:19:54] view that says if there's no source I. D.
[00:19:57] Then that means these records were
[00:19:58] deleted.
[00:19:59] So it's a nice nice trick
[00:20:02] on how you can keep track of records that
[00:20:05] that got deleted from your source table.
[00:20:08] And so that's a pretty cool.
[00:20:11] Pretty cool approach. Jan says genius.
[00:20:14] We agree.
[00:20:18] Nice trick. Thank you Justin
[00:20:20] for sharing that.
[00:20:21] So if anybody's ever wondering or
[00:20:24] wants to monitor their
[00:20:26] deleted records that is the current way.
[00:20:29] The only really way
[00:20:31] I know how to do it
[00:20:32] because I don't even think Airtable
[00:20:34] has any kind of view
[00:20:36] of that because you can't
[00:20:38] in the history.
[00:20:39] I mean you can look at
[00:20:41] the archive and like
[00:20:43] you know reinstall
[00:20:45] but that's not ideal.
[00:20:48] Yeah it's not.
[00:20:50] Yeah
[00:20:51] this is probably the easiest way
[00:20:53] that you even can do it
[00:20:55] and I don't really know
[00:20:56] of another way to do it either.
[00:20:59] So shout out to Justin for that
[00:21:02] cool tidbit.
[00:21:04] Alright, moving on to the Reddit
[00:21:07] community.
[00:21:08] This one I thought was interesting
[00:21:12] It says how to use
[00:21:13] both notion and Airtable.
[00:21:15] This person uses a notion for
[00:21:18] almost all their daily life
[00:21:19] but it's becoming cumbersome
[00:21:21] and so they're
[00:21:22] exploring using Airtable
[00:21:24] in addition to notion.
[00:21:26] So I know a lot of people use notion.
[00:21:30] It's a very popular tool especially for
[00:21:34] document
[00:21:35] documentation.
[00:21:36] So this I use a lot as well.
[00:21:39] I'm gonna show in the On2Air
[00:21:41] spotlight how we
[00:21:43] use notion with Airtable.
[00:21:45] So this is an interesting thread.
[00:21:48] I don't Are you a notion user Kamille?
[00:21:51] No, I keep
[00:21:53] starting and stopping
[00:21:55] using notion slash coda.
[00:21:56] To me they're functionally the
[00:21:58] same.
[00:21:59] for
[00:22:01] documenting
[00:22:03] when I create an app
[00:22:05] giving them like a user guide
[00:22:07] or here's you know how to
[00:22:08] use this product
[00:22:10] but I've never actually
[00:22:12] pulled the trigger
[00:22:13] because I've never really sat down
[00:22:14] and
[00:22:15] learned
[00:22:18] too much about it.
[00:22:19] But it seems like a nice product.
[00:22:21] I just haven't personally
[00:22:22] used it so much yet.
[00:22:24] Yeah. Yeah I'd say
[00:22:25] the main difference is notion and I
[00:22:28] think coda kind of fits in
[00:22:29] this category as well.
[00:22:31] The coda is probably more of a
[00:22:32] hybrid of the two.
[00:22:35] But notion is better at
[00:22:38] content first,
[00:22:40] documents
[00:22:41] interlinking between
[00:22:43] documents and then
[00:22:45] Airtable's more data first.
[00:22:47] And so that's where,
[00:22:49] you know, there's a use case for
[00:22:52] both notion has a database feature
[00:22:54] but it's nowhere near
[00:22:56] as advanced as Airtable.
[00:22:58] and gets, it does get cumbersome
[00:23:00] if you're trying to do a lot of
[00:23:03] database type stuff in notion.
[00:23:06] So I think there is a play
[00:23:08] for using both like
[00:23:10] I said, I'll showcase
[00:23:12] in a little bit how
[00:23:13] how we use notion with On2Air
[00:23:16] and so yeah, so if you're curious,
[00:23:18] it's something that I'm exploring.
[00:23:20] I've done some integration work
[00:23:23] with notion.
[00:23:24] We haven't released any product
[00:23:27] probably won't any time soon,
[00:23:29] but maybe down the road
[00:23:31] we'll explore some
[00:23:33] notion type apps.
[00:23:35] So yeah,
[00:23:36] if anybody's interested in notion,
[00:23:39] I'd be curious to hear your thoughts
[00:23:42] on how you use notion with Airtable.
[00:23:47] Alright, finally
[00:23:48] we'll end with a little bit of humor.
[00:23:50] I don't know if you saw
[00:23:51] this sergio posted this
[00:23:53] in the BuiltOnAir community.
[00:23:55] I saw it on twitter first.
[00:23:57] I'll show the twitter version
[00:24:00] but it's a it's a picture of New York City
[00:24:04] and there's billboard from click up
[00:24:09] a competitor to Airtable
[00:24:12] that says save one day every week
[00:24:15] and it has the click up logo and then down
[00:24:18] below it Airtable has their billboard
[00:24:21] that says this is how
[00:24:23] and so it's kind of,
[00:24:25] I don't think this was intentional.
[00:24:27] Maybe the placement was
[00:24:29] but this is how is Airtable's
[00:24:32] slogan that they're using in their new
[00:24:35] marketing campaign.
[00:24:37] So I don't think they purposely
[00:24:39] came up with that
[00:24:40] to put right there underneath
[00:24:42] click up
[00:24:43] but it's kind of funny
[00:24:46] that they're kind of
[00:24:48] throwing some shade at click up
[00:24:50] and I think click up even
[00:24:52] noticed that I think
[00:24:53] somebody wants a C. Level executive
[00:24:56] at click up tweeted it as well and kind of
[00:24:59] making fun. So
[00:25:00] a little bit of back and forth
[00:25:02] between Airtable and click up
[00:25:03] going on. I thought this was funny,
[00:25:08] fairly incredible.
[00:25:10] Also they have just similar
[00:25:12] enough a color scheme
[00:25:13] in their logo where if you
[00:25:15] didn't know what click up
[00:25:17] or Airtable was
[00:25:18] you might think that's just one ad.
[00:25:21] Yeah. Yeah.
[00:25:23] Cool.
[00:25:26] Yeah you'd be like click up.
[00:25:28] I don't know why I need to
[00:25:30] click up but maybe if
[00:25:32] the Airtable one was above it
[00:25:33] then you'd be clicking up or something.
[00:25:35] Yeah
[00:25:38] that might make more sense. So
[00:25:40] so yeah so Airtable,
[00:25:42] you'll start to see
[00:25:44] I think somebody Rebecca in
[00:25:46] the BuiltOnAir community said
[00:25:50] they saw the ad in Texas and so
[00:25:55] I think they started they promoted it
[00:25:57] during the golf tournament.
[00:25:58] The U. S. Open is when they started
[00:26:02] promoting it on air.
[00:26:04] So you'll start to see
[00:26:06] Airtable commercials on tv and
[00:26:08] billboards apparently.
[00:26:10] And at least in New York.
[00:26:13] And I think this was,
[00:26:15] yeah, Sergio posted
[00:26:17] the same picture
[00:26:19] in the BuiltOnAir community as well.
[00:26:23] So it's going around
[00:26:25] all right. That concludes
[00:26:27] the Round the Bases of everything,
[00:26:29] keeping you up to date
[00:26:30] on the BuiltOnAir community.
[00:26:33] Now let's continue on to
[00:26:35] spotlight on On2Air.
[00:26:36] It's an all in one toolkit
[00:26:38] to run your business on Airtable,
[00:26:40] a suite of apps
[00:26:41] that allow you to do
[00:26:42] a variety of different things.
[00:26:43] And today because of that notion,
[00:26:46] I wanted to kind of talk about
[00:26:48] something that
[00:26:49] if you're an
[00:26:50] On2Air customer
[00:26:52] or interested in in some of our apps,
[00:26:56] we'd love to hear
[00:26:57] from you on ways that we can improve
[00:27:00] different new features
[00:27:01] that you want to see
[00:27:02] either in our existing apps or
[00:27:04] future apps that you want us to build.
[00:27:06] So this is our customer feedback portal.
[00:27:09] I was evaluating, I looked at some of
[00:27:11] the off the shelf solutions for
[00:27:14] getting feedback
[00:27:15] from customers and I said,
[00:27:17] I wonder if I could build this
[00:27:19] with Airtable and notion.
[00:27:21] And so I built this.
[00:27:23] It took a little bit of time.
[00:27:25] But our content,
[00:27:27] our help desk are all of our
[00:27:30] customer support and documentation
[00:27:33] is built inside of notion
[00:27:35] and then we use a
[00:27:36] product that converts it
[00:27:38] to a website
[00:27:39] so that we can put it on our
[00:27:41] domain
[00:27:42] and so as part of that
[00:27:45] notion we are tracking all of the customer
[00:27:49] feedback. So
[00:27:50] this is an example of using
[00:27:53] Airtable and notion,
[00:27:54] so this is kind of a database.
[00:27:57] So when you click on this
[00:27:58] it goes to a page
[00:27:59] and then we ask for feedback.
[00:28:03] So this is somebody that posted,
[00:28:06] it might have been
[00:28:07] yeah this is somebody
[00:28:10] posting a new feature for amplify
[00:28:12] and we have the ability to up vote this.
[00:28:15] So when you click on this,
[00:28:17] it actually goes to an Airtable form.
[00:28:19] So not as clean as a built a fully
[00:28:22] integrated one but
[00:28:23] since we're in the Airtable world,
[00:28:25] I think people are
[00:28:26] okay filling out an Airtable form
[00:28:29] and so you can up vote this.
[00:28:31] And what happens is
[00:28:33] this goes into an Airtable base.
[00:28:37] I then can approve or if
[00:28:39] it's spam or something
[00:28:40] that I won't approve it
[00:28:42] once I approve it an Airtable,
[00:28:44] then we have a script
[00:28:46] that talks to the notion A P I
[00:28:49] and updates the notion database
[00:28:51] with this new information.
[00:28:54] And then it would
[00:28:55] then show up
[00:28:56] back on here as an up vote
[00:28:59] as a comment. And so it works both
[00:29:02] with up voting an existing one
[00:29:05] or if you submit a new feature
[00:29:07] that goes to a different Airtable form
[00:29:10] that has the same kind of functionality.
[00:29:12] So
[00:29:12] kind of cool way of using notion
[00:29:15] that so notion is really good for
[00:29:17] this kind of
[00:29:18] stuff where it will automatically
[00:29:21] convert to a website.
[00:29:22] So we use that for
[00:29:24] all of our documentation.
[00:29:26] So we'd love to hear your feedback
[00:29:28] fill out these
[00:29:29] forms and submit new ideas
[00:29:32] of things that you want to see
[00:29:34] in our existing apps
[00:29:35] or new apps that you'd love
[00:29:36] for us to build in the future.
[00:29:38] So check that out,
[00:29:40] you can find that on On2Air
[00:29:42] and in our menu,
[00:29:44] you can get to our
[00:29:45] customer feedback section. So
[00:29:49] interesting way to use both there.
[00:29:52] All right, Kamille
[00:29:54] share your screen
[00:29:55] and we'll talk about some fields.
[00:30:01] Okay,
[00:30:03] hold on.
[00:30:06] I actually have to stop
[00:30:08] sharing because I have
[00:30:10] done this in a live base
[00:30:13] which is a bad idea,
[00:30:15] don't be like me,
[00:30:16] kids
[00:30:17] don't
[00:30:19] don't follow in my footsteps.
[00:30:24] That's all right now, I'm ready.
[00:30:31] Okay,
[00:30:32] so I have
[00:30:36] a business where I will sometimes
[00:30:38] once a blue moon
[00:30:40] create an app for people or
[00:30:42] do some Airtable
[00:30:43] related something or other
[00:30:45] and
[00:30:47] with that comes, you know,
[00:30:49] the issue of billing
[00:30:50] and invoicing and all of that
[00:30:52] and
[00:30:53] similar to Dan,
[00:30:54] I could have used
[00:30:56] an out of the box solution
[00:30:58] and I decided
[00:30:59] nah, I'm just gonna build one
[00:31:00] and so I built one in Airtable because of
[00:31:03] course
[00:31:04] I have a
[00:31:06] fairly long demo
[00:31:07] that's been split into two parts
[00:31:10] of sort of going in to what
[00:31:12] I've done in Airtable.
[00:31:13] The first part is more
[00:31:15] just sort of pointing out an
[00:31:17] issue I ran into
[00:31:18] and how I got over it
[00:31:19] and the second half is how I actually
[00:31:22] billed people
[00:31:23] or how I will start building people
[00:31:27] in the future
[00:31:28] and I use stripe as my payment processor.
[00:31:30] So in the first part
[00:31:32] just to give you an idea
[00:31:35] of what how I've set up my
[00:31:37] base, I have a table for clients
[00:31:40] and each client may sign on
[00:31:43] for one or more
[00:31:44] contracts where I will be asked
[00:31:46] to build something or do something
[00:31:48] and each contract
[00:31:49] will have multiple tasks,
[00:31:52] one or more tasks assigned to it.
[00:31:54] There's many hidden fields
[00:31:56] in here
[00:31:57] that will help me calculate how
[00:31:59] long I think
[00:31:59] that task will take but I have a
[00:32:03] column for how long I've actually spent
[00:32:06] doing that task and status.
[00:32:08] Pretty simple.
[00:32:10] And
[00:32:11] then comes the notion of invoicing for
[00:32:15] a client and
[00:32:16] if you're like me
[00:32:17] you might run into a couple
[00:32:19] of different things
[00:32:20] where
[00:32:21] if you have more than one
[00:32:23] active contract with somebody,
[00:32:25] what if you want an invoice
[00:32:27] for both of those contracts
[00:32:29] in the same invoice
[00:32:30] or you could do it another way
[00:32:33] where you have multiple projects
[00:32:35] with the same
[00:32:36] person going on at the same time
[00:32:38] but you want each project to get their own
[00:32:40] invoice. Well I wanted flexibility.
[00:32:43] So I set it up where
[00:32:45] it's not a given necessarily
[00:32:48] which contract is being billed for
[00:32:52] instead.
[00:32:52] What I've done
[00:32:54] is I've
[00:32:55] billed
[00:32:56] straight to the client
[00:32:58] and each
[00:33:00] invoice has invoice line items
[00:33:04] where I could assign
[00:33:07] the contract to an individual line item
[00:33:11] and that's the basic sort of flow.
[00:33:14] And
[00:33:15] the problem I had
[00:33:16] when I was sort of
[00:33:19] going through and thinking of
[00:33:21] how would I want to do this?
[00:33:25] It's very easy if a project is over
[00:33:28] to say, okay, well this is how much time
[00:33:31] I've spent total for the project,
[00:33:33] you know, multiply that
[00:33:35] by whatever your rate
[00:33:36] is and say invoice the client.
[00:33:39] But if you have a long ongoing project,
[00:33:41] that's you know, many, many months,
[00:33:43] you might not want to wait until,
[00:33:44] you know, the very start of the project
[00:33:46] where you're just billing for what
[00:33:48] you've estimated for
[00:33:49] or the very end of the project,
[00:33:50] which is the actual amount
[00:33:51] of time you spent on it.
[00:33:53] If you want to bill on
[00:33:55] a monthly or weekly basis,
[00:33:56] you have to be able to do to determine
[00:33:59] how much time have I spent this month
[00:34:02] or how much time have I spent
[00:34:05] since the last time I've invoiced
[00:34:07] the client and that was
[00:34:09] more cumbersome
[00:34:10] than I thought it was going to be.
[00:34:12] I'm gonna talk through
[00:34:14] the several fields
[00:34:16] that sort of make that work.
[00:34:18] If I go back to my clients table,
[00:34:22] you'll see I have
[00:34:23] this one client in here.
[00:34:25] They have two different contracts
[00:34:27] associated with them.
[00:34:28] And then I have a roll up field
[00:34:30] for the record IDs
[00:34:32] of my contract records
[00:34:34] from that client
[00:34:35] that have outstanding balances.
[00:34:37] This is done
[00:34:39] with a fairly simple condition applied.
[00:34:42] There's a
[00:34:43] field that just gives me the record I. D.
[00:34:46] For each contract and I'm only asking
[00:34:48] it to give me
[00:34:49] the record I. D. S.
[00:34:51] Of that record if the
[00:34:54] amount that hasn't been billed is
[00:34:57] greater than zero
[00:34:58] and then the status
[00:35:00] of that project is not canceled.
[00:35:04] And then this is going to be useful
[00:35:07] when we talk about stripe but I have a
[00:35:09] similar condition applied to
[00:35:12] the invoice memo
[00:35:14] field.
[00:35:14] Lastly I have another roll up
[00:35:17] similar for the condition status is not
[00:35:21] canceled.
[00:35:22] And then it's summing up
[00:35:24] all of the next amounts to invoice.
[00:35:27] What's the next amount invoice?
[00:35:30] If I go back to
[00:35:34] my contracts table
[00:35:36] you'll you can notice I have the
[00:35:40] actual hours
[00:35:41] and actual project value.
[00:35:43] So this is a roll up summing up all of
[00:35:46] the hours from the task table
[00:35:48] where I'm logging how many
[00:35:50] hours I've spent thus
[00:35:52] far on that task.
[00:35:54] I have another roll up
[00:35:55] that's saying
[00:35:56] how much have I invoiced and how much have
[00:35:59] I paid or has been paid so far
[00:36:03] and then what the next amount
[00:36:05] to invoice
[00:36:05] which is what the clients table is
[00:36:07] looking at,
[00:36:08] it's looking at the status first
[00:36:10] and saying if it's complete then
[00:36:11] I've already been paid
[00:36:13] and you know, just zero it out otherwise
[00:36:16] give me
[00:36:18] the project value,
[00:36:20] which is the my fee times
[00:36:22] the amount of hours I've worked
[00:36:24] minus the amount that has been invoiced.
[00:36:28] And what that effectively
[00:36:31] allows me to do is as you know,
[00:36:33] a project goes on and I'm
[00:36:35] adding more hours to a project
[00:36:38] and I've billed for some
[00:36:39] and then another month
[00:36:40] goes by and I'm
[00:36:42] billing more towards that project.
[00:36:44] It's not allowing me to
[00:36:46] bill for the same hours twice.
[00:36:49] That is effectively what's happening
[00:36:51] and
[00:36:53] I could see if I could break my own system
[00:36:57] if I try and create a new project
[00:37:01] on the fly, so we could see
[00:37:03] this work in action.
[00:37:05] I'm sure something will go
[00:37:06] wrong.
[00:37:12] Hopefully that makes sense so far
[00:37:14] yep
[00:37:17] best and then I'll give it some,
[00:37:22] I'll give it some tasks,
[00:37:28] do, this is for the new contract.
[00:37:32] Right,
[00:37:34] Well that's annoying.
[00:37:37] Okay. I would
[00:37:42] you know, just search for
[00:37:43] that record, but again, this is a
[00:37:46] live base and I don't want to
[00:37:49] have stuff just out there.
[00:37:52] Task number one,
[00:37:54] this is a test and let's say I did
[00:37:59] six hours
[00:38:02] now, that's in there
[00:38:04] and hopefully
[00:38:05] if I go back to my contracts table,
[00:38:08] you'll see I have, just,
[00:38:10] you know,
[00:38:11] random rate applied in here for the
[00:38:13] purposes of demo
[00:38:16] six hours for that task I have logged
[00:38:20] and then you know what's that rate?
[00:38:23] I haven't invoiced for
[00:38:25] this
[00:38:29] This project at all
[00:38:30] because I just made it
[00:38:32] so none of that has been paid.
[00:38:33] And so it's simple.
[00:38:35] I know that the next amount
[00:38:37] invoice is 750.
[00:38:39] If I go back to my clients base
[00:38:42] I now have
[00:38:43] in here based on my conditional roll up
[00:38:47] that one record that has an
[00:38:49] outstanding balance, that's 750.
[00:38:53] The memo is just
[00:38:55] helpful for me and for my clients to see,
[00:38:58] you know the last time
[00:39:00] you have been billed was June 21
[00:39:06] or
[00:39:07] that was the last time
[00:39:09] I said I started work. There's a
[00:39:12] there's a table,
[00:39:13] another table, a field
[00:39:15] in this table
[00:39:16] that will give me the date where I put in
[00:39:18] the number six,
[00:39:19] which is how my clients know,
[00:39:22] when did I start work for this
[00:39:24] invoice.
[00:39:28] and so that's all sort of ready to go
[00:39:31] ready to be packaged into my,
[00:39:34] you know, invoicing system
[00:39:37] that uses stripe.
[00:39:40] I don't know if there's
[00:39:42] questions from the jet
[00:39:44] but that's sort of the thought process
[00:39:46] that I had going through and then I could
[00:39:49] demo it again with another contract
[00:39:51] in here if you wanted to see two different
[00:39:54] projects being billed at the same time.
[00:39:58] Yeah, go for it.
[00:40:03] No act
[00:40:08] yet another.
[00:40:14] I'm just gonna do it from here
[00:40:20] actual hours.
[00:40:22] Let's just say 10
[00:40:27] That you could see in here.
[00:40:29] Give it a moment.
[00:40:30] So now I should have 16 hours to
[00:40:33] bill for and then the sum
[00:40:35] of these two together
[00:40:37] for amount outstanding.
[00:40:39] And then going back to my base
[00:40:42] you can see I now have to record IDs
[00:40:45] in here and then this roll up
[00:40:48] updated as well to give me
[00:40:51] that value. And then the amount
[00:40:54] invoice has changed as well.
[00:40:56] This is all sort of leading up to
[00:40:59] getting the right amount
[00:41:01] of information I need
[00:41:02] to transition into
[00:41:06] going into stripe and looking at the
[00:41:09] you know a sample
[00:41:12] invoice that stripe can create.
[00:41:15] This is another test
[00:41:16] I had done previously.
[00:41:17] This is essentially
[00:41:19] what we're trying to bill.
[00:41:20] So I want to know the quantity,
[00:41:22] the number of hours,
[00:41:23] the unit price
[00:41:24] which is a fake rate I applied
[00:41:26] in here and then multiplying
[00:41:27] that together,
[00:41:28] Getting all that in there.
[00:41:30] And then for the memo,
[00:41:31] I wanna say
[00:41:33] you know what are the dates
[00:41:35] that I'm billing since
[00:41:37] And then what are the 2
[00:41:38] 2 projects that are supposed
[00:41:40] to be on this invoice?
[00:41:42] And that's all of that
[00:41:43] information is being collected by
[00:41:45] my system of
[00:41:47] 1000 roll ups it seems.
[00:41:50] Could you show the memo?
[00:41:52] I'm curious what that roll up looks like.
[00:41:57] the roll up itself is
[00:42:02] like this.
[00:42:03] So we've talked about on the show
[00:42:05] before that you could have full
[00:42:06] formulas in a roll up field.
[00:42:08] Sometimes it's you know finicky.
[00:42:10] So it's not always the
[00:42:11] recommended solution
[00:42:12] but I wanted to save myself on some
[00:42:14] fields.
[00:42:15] So if you know,
[00:42:16] if there's anything
[00:42:18] to invoice in the first place,
[00:42:20] then you know give you this
[00:42:23] simple statement,
[00:42:24] this invoice bills for yada
[00:42:26] yada yada. And then I'm formatting
[00:42:29] the date of my
[00:42:31] my work date
[00:42:32] which is again the earliest date
[00:42:35] for the task
[00:42:37] that has hours which
[00:42:39] have not yet been billed for.
[00:42:41] It is a complicated sentence
[00:42:43] but that's what
[00:42:44] that is, that's what invoice workday is
[00:42:47] and then it's a bullet character
[00:42:49] that I've added. So it's not a,
[00:42:50] you know, perfectly formatted list
[00:42:52] if you have hanging lines
[00:42:54] but it looks nice
[00:42:55] for shorter values
[00:42:56] and then I'm joining
[00:42:58] the remaining values
[00:43:00] with a line break and then another
[00:43:03] bullet character as well.
[00:43:05] Yeah.
[00:43:07] Yeah. That's a that's awesome example of
[00:43:10] what you can do with the formula section
[00:43:13] of a joint or a roll up.
[00:43:16] So I had a lot of roll ups
[00:43:17] in this portion of the demo.
[00:43:19] That's the only one
[00:43:20] that's particularly special.
[00:43:22] All of the other ones are very simple.
[00:43:24] They just happen to have,
[00:43:26] you know, a condition applied to them.
[00:43:28] Yeah,
[00:43:29] awesome.
[00:43:31] Very cool stuff.
[00:43:33] Let me do a transition here.
[00:43:35] So that was the formulas and all
[00:43:37] the link records and roll ups
[00:43:40] so lots of advanced uses
[00:43:42] of formulas and now we're
[00:43:44] gonna create an automated invoice.
[00:43:47] So let's move on to the automate create
[00:43:50] section
[00:43:52] and how you're doing that back to you
[00:43:55] is my screen up? Okay.
[00:43:57] So I did this in make
[00:44:00] I know Scott was in the chat.
[00:44:02] He's our resident
[00:44:04] make slash Integromat expert.
[00:44:07] But I
[00:44:09] did this actually for another client
[00:44:11] and then said I should steal that.
[00:44:13] And so I've adopted a similar
[00:44:15] sort of workflow.
[00:44:16] And I'm gonna explain it
[00:44:18] first before I show it running
[00:44:20] because again
[00:44:21] this is a live base and if I've
[00:44:22] messed something up
[00:44:24] I want to make sure that you know
[00:44:26] what it's supposed to do and what it's
[00:44:28] supposed to do
[00:44:30] is run off a web book. So there is a
[00:44:33] automation
[00:44:35] in here that's fairly simple.
[00:44:39] I have a single select
[00:44:42] field that when I change it
[00:44:45] to create and send invoice
[00:44:47] it is supposed to do one of two things.
[00:44:51] I have a condition applied
[00:44:53] for making sure that there's
[00:44:55] you know the number
[00:44:56] is greater than zero.
[00:44:58] I don't want to invoice the client for $0.
[00:45:00] Make sure that there's actually
[00:45:02] that roll up
[00:45:03] that had the list of record IDs
[00:45:06] make sure that's not empty,
[00:45:08] it has to associate it with a contract
[00:45:10] and make sure that they already
[00:45:12] have a customer I. D. In stripe.
[00:45:13] So in stripe.
[00:45:14] Which is my preferred payment processor.
[00:45:17] Each customer has their own unique I. D.
[00:45:20] And if they don't have one that
[00:45:22] means they're not a record
[00:45:23] in stripe yet.
[00:45:24] Which means this automation is gonna
[00:45:26] fail because it's not gonna be able to
[00:45:28] send anything to anyone.
[00:45:29] So making sure all of that is
[00:45:33] in there. If so run a script.
[00:45:36] The script is very simple.
[00:45:38] All it's doing is
[00:45:39] posting the record I.D. Of the
[00:45:44] of what I want to bill for to make.
[00:45:47] And then
[00:45:48] from there
[00:45:49] I'm updating that same record again
[00:45:51] and saying that the invoice has been sent
[00:45:55] otherwise if none of those
[00:45:57] conditions happen
[00:45:58] then you know
[00:45:59] I'm changing that same single select
[00:46:01] into something that looks like an error.
[00:46:02] So I know that oh this person was never
[00:46:06] invoice which is just a tip that I think.
[00:46:08] You know it is good practice
[00:46:10] if something fails,
[00:46:12] show it somewhere.
[00:46:13] So back into make,
[00:46:15] what's happening is the web hook
[00:46:18] is received and then it's
[00:46:19] getting the record
[00:46:21] from that record I. D.
[00:46:22] So again I'm triggering off of the
[00:46:24] client
[00:46:25] not the contract.
[00:46:27] All of this information top level
[00:46:29] is and clients so it's getting the client
[00:46:31] record.
[00:46:32] Once again making sure
[00:46:34] that I have contracts to bill for.
[00:46:37] And because I set it up so that
[00:46:39] one client can have multiple
[00:46:41] contracts that
[00:46:42] they're being billed for, I have an
[00:46:44] aerator that's setting up
[00:46:47] or splitting up rather
[00:46:49] which contracts that they have
[00:46:51] that have outstanding balances
[00:46:54] so it's taking
[00:46:56] this array
[00:46:57] and if there's more than one value
[00:47:00] it's splitting that apart.
[00:47:04] So it's getting the contract
[00:47:06] for each record ID
[00:47:07] I have in there in this
[00:47:08] example I have two
[00:47:10] and then it's creating a invoice item
[00:47:14] in stripe. So in stripe
[00:47:18] you can have one invoice
[00:47:19] and each invoice
[00:47:20] can have multiple line items.
[00:47:22] This is creating those line items.
[00:47:24] It is fairly straightforward
[00:47:27] I'm doing it by unit amount and quantity.
[00:47:30] Something to note
[00:47:31] if you're working in stripe
[00:47:33] is they require that the numbers
[00:47:35] you give them are in cents
[00:47:37] and not whole dollars.
[00:47:38] So there's formulas in my base that
[00:47:43] will I'll have a regular number
[00:47:45] or currency field
[00:47:47] that gives the amount times
[00:47:49] 100. And then a formula field
[00:47:52] is taking that amount and dividing it by
[00:47:55] 100. The reason why I did it that way
[00:47:57] and not the other way around is because I
[00:47:58] want my automations to fill in values
[00:48:02] and the values that stripe is going to
[00:48:04] you know spit out are going to be
[00:48:06] in cents
[00:48:08] which means I have to
[00:48:09] use a formula to convert it.
[00:48:10] I did it the other way around. I wouldn't
[00:48:12] it would I wouldn't be able to get
[00:48:14] information from strike in here.
[00:48:18] So anyway that's pretty simple
[00:48:20] for description
[00:48:21] I'm just tacking on the words
[00:48:23] per hour at the end
[00:48:24] because that's how I bill
[00:48:27] and then I am creating
[00:48:30] you know that same information
[00:48:32] back into my Airtable
[00:48:34] in my invoice line items
[00:48:36] table like so
[00:48:38] it just helps me keep track
[00:48:41] of the amount
[00:48:44] just making sure that I know
[00:48:46] oh yeah on this date
[00:48:48] this contract was was billed
[00:48:50] for because again
[00:48:51] I'm splitting things up that way
[00:48:53] and then
[00:48:55] I'm aggregating everything back together
[00:48:59] and then finally creating
[00:49:01] the actual invoice itself.
[00:49:03] So what stripe does by
[00:49:04] default
[00:49:06] is
[00:49:07] it will create an invoice with all of the
[00:49:14] un invoiced line items.
[00:49:16] So you could create
[00:49:18] 50
[00:49:19] line items and then never create
[00:49:21] an invoice
[00:49:22] and those will just sit pending in
[00:49:24] stripes system
[00:49:25] and then the next time you create
[00:49:27] an invoice you don't even
[00:49:28] really have to tell it which line items.
[00:49:30] It will just look at all of the pending
[00:49:32] line items it has and attach that
[00:49:35] to the next invoice. So you can see,
[00:49:38] I'm not really telling it
[00:49:39] here are the line items
[00:49:41] that I want you to include.
[00:49:42] It just knows
[00:49:43] because those are the ones
[00:49:44] that haven't been touched yet
[00:49:46] auto advance for your references
[00:49:48] if you want to automatically
[00:49:51] billed or bill
[00:49:51] that client if they have a payment
[00:49:55] payment
[00:49:57] already on file
[00:49:58] like if they've already paid you
[00:50:00] with credit cards or their bank
[00:50:01] or whatever.
[00:50:02] You can tell stripe to say
[00:50:04] just bill them automatically if
[00:50:06] they already have something on file.
[00:50:09] And then for description
[00:50:11] that's where I'm putting in
[00:50:13] that memo field that had
[00:50:14] things as a bulleted list.
[00:50:17] From there
[00:50:19] it is finalizing the invoice
[00:50:21] which when you create an
[00:50:23] invoice in stripe it'll
[00:50:24] start off as draft
[00:50:26] and won't actually go anywhere
[00:50:28] until you finalize it.
[00:50:29] That's what this is
[00:50:31] very simple.
[00:50:32] It's just sort of stamping it ready and
[00:50:34] that's where when it will be sent
[00:50:36] and then last but not least
[00:50:39] it is creating an invoice record
[00:50:42] in Airtable itself
[00:50:43] with all of this information. So
[00:50:46] stripe will give you a name
[00:50:49] for each invoice
[00:50:50] Every client in stripe will
[00:50:52] have their own prefix
[00:50:53] and then a number attached to them.
[00:50:55] That's what the number
[00:50:56] looks like.
[00:50:57] The unique invoice I. D.
[00:50:59] Which would be in like the U.R. L.
[00:51:01] Invoice date all that kind of stuff.
[00:51:04] I'm hooking it back to the client that
[00:51:06] started this automation first place.
[00:51:10] And then I am getting
[00:51:13] all of the invoice line item
[00:51:15] Airtable records
[00:51:16] that I had made previously and
[00:51:18] attaching them to this invoice record.
[00:51:21] And then what's nice about stripe
[00:51:25] is that it gives you a
[00:51:27] URL that your customers can click on
[00:51:29] and pay through like an online portal
[00:51:31] and it'll also give your invoice
[00:51:33] as a pdf which is what I showed earlier.
[00:51:35] And I'm just hooking that
[00:51:38] straight into the
[00:51:43] into the Airtable record and that's
[00:51:45] that is what the process is
[00:51:48] and now I'm going
[00:51:50] to see
[00:51:52] if it works.
[00:51:54] So I hit run
[00:51:55] and now
[00:51:57] what I do is I go back into this base
[00:52:02] and change the
[00:52:04] single select into create
[00:52:06] and send invoice.
[00:52:08] So if I do that, my Airtable automation
[00:52:10] should fire
[00:52:14] and when it does
[00:52:16] this web hook
[00:52:18] should get
[00:52:20] a response
[00:52:22] there it goes,
[00:52:24] is it going
[00:52:25] is it going yeah it worked
[00:52:29] or did it so you kind of saw it
[00:52:32] flash really really quickly but
[00:52:36] all of these are now empty
[00:52:38] and my amount to invoice is now zero. And
[00:52:41] I'm gonna walk through what happened
[00:52:44] just now on the Airtable side.
[00:52:47] So
[00:52:48] you'll see I have a second
[00:52:50] invoice in my invoices tab
[00:52:53] the second part of my Airtable
[00:52:55] automation that changed this back to sent
[00:52:57] is
[00:53:00] is there actually
[00:53:01] that's not from the Airtable
[00:53:02] automation. I think I did that
[00:53:03] here.
[00:53:06] No no
[00:53:07] no I didn't.
[00:53:08] It is from my Airtable automation
[00:53:10] that's changing this value
[00:53:11] descent but I have
[00:53:11] a second invoices
[00:53:15] record
[00:53:16] and that's great
[00:53:17] If I go to my contracts
[00:53:19] table it was these last two.
[00:53:23] I have my project value
[00:53:25] which is the same
[00:53:26] but now my amount invoiced is no longer
[00:53:29] zero. It's the amount
[00:53:31] from my actual project value
[00:53:33] because I created an
[00:53:35] invoice attaching those items to it.
[00:53:39] And then amount paid a zero
[00:53:41] because I haven't paid for anything yet.
[00:53:43] I go to my invoices tab
[00:53:45] that invoice record is there now
[00:53:49] with the amount I've invoiced
[00:53:53] nothing has been paid yada yada,
[00:53:56] but I should be able to click this button.
[00:54:03] Yes, so my memo came in here.
[00:54:06] I have my two contracts as line items.
[00:54:14] That's all nice
[00:54:16] than if I go back here
[00:54:19] and go to my invoice line items.
[00:54:21] This is grouped by client.
[00:54:23] So you can see it's these
[00:54:25] second two,
[00:54:27] the amount invoiced
[00:54:28] and the amount invoice paid.
[00:54:31] So
[00:54:33] another roll up that I didn't show is
[00:54:36] I'm pulling from the
[00:54:38] invoice record itself,
[00:54:40] how much that whole invoice has been paid
[00:54:44] and then I'm clamping
[00:54:47] the amount that the item has been paid
[00:54:50] based on,
[00:54:52] it can't be more than
[00:54:54] what this line item is.
[00:54:57] So
[00:55:00] and it
[00:55:04] I've confused myself
[00:55:06] so when you set it up
[00:55:08] like I have, I don't want to
[00:55:10] track payments
[00:55:12] by invoice line item
[00:55:13] because that's finicky
[00:55:15] and annoying I want to just,
[00:55:16] how much have they paid
[00:55:17] towards the whole invoice.
[00:55:19] So I've pulled in the whole
[00:55:20] invoice
[00:55:23] and said
[00:55:24] it's either
[00:55:26] the amount of this line item itself
[00:55:29] or it's the total amount.
[00:55:31] And that's how you end up with
[00:55:33] making sure that this value
[00:55:36] is never more than
[00:55:37] the actual line item itself
[00:55:42] if I were to go back
[00:55:45] and change this to
[00:55:51] hold on, which one is a newer project,
[00:55:53] this one's a new project,
[00:55:54] this was six hours. If I change it to
[00:55:58] 16,
[00:56:00] hopefully
[00:56:01] I should be able to go back in here
[00:56:05] and say
[00:56:07] yeah,
[00:56:09] My actual project value
[00:56:11] for this contract is now 2000.
[00:56:13] The amount of invoice to 750,
[00:56:17] The next amount to invoice
[00:56:19] is now 1250
[00:56:22] and that's how I keep my business sort of
[00:56:25] going and honest.
[00:56:27] So I'm only billing for that
[00:56:29] extra 10 hours that I just did
[00:56:31] and not the previous six hours
[00:56:34] that was already on
[00:56:36] an invoice once they pay that,
[00:56:38] that's great.
[00:56:38] But I'm not gonna invoice them twice
[00:56:40] even though they haven't paid
[00:56:41] for it yet.
[00:56:42] So
[00:56:43] that's just simple math,
[00:56:45] just subtracting the total amount
[00:56:47] that I can bill for
[00:56:49] minus how much I have already billed for.
[00:56:53] Not paying attention
[00:56:55] to how much has been paid.
[00:56:57] Yeah,
[00:56:59] that's amazing.
[00:57:01] Yet another example
[00:57:03] of an amazing mini app product that you
[00:57:06] can build inside of Airtable
[00:57:08] with a little bit of help
[00:57:10] from make.com.
[00:57:10] Yeah, and it was, there's some,
[00:57:13] there's some gotchas, you know,
[00:57:15] learning how to do this
[00:57:17] with make, but once you're
[00:57:18] sort of got it in there,
[00:57:20] it's fairly simple
[00:57:21] and the connector for stripe is,
[00:57:23] you know, pretty nice, you could do
[00:57:25] most of what you would
[00:57:27] want to do fairly easily and
[00:57:30] you know like I said,
[00:57:32] I like stripe, it allows me to
[00:57:34] create nice clean PDFs and
[00:57:36] have online payment portals with like
[00:57:39] nothing
[00:57:40] and you know there's a cost
[00:57:42] associated with using stripe,
[00:57:44] not for make if you're on
[00:57:46] such a small scale
[00:57:47] like myself but for using stripe,
[00:57:49] there's a certain percentage
[00:57:51] they take of each transaction,
[00:57:53] I think it's worth it
[00:57:55] because you could do
[00:57:56] a system like this which
[00:57:58] while a little finicky
[00:58:00] it has a lot of moving parts to it,
[00:58:02] it gets the job done
[00:58:05] and it's custom to my,
[00:58:07] the way I would like to,
[00:58:09] you know, run my
[00:58:10] system
[00:58:12] and does when you generate
[00:58:14] the invoice to stripe
[00:58:16] send an email? Or do you,
[00:58:17] would you then set up
[00:58:19] another automation
[00:58:20] to send the pdf? If this were a real
[00:58:23] This is all using test data
[00:58:25] stripe is a really nice test mode,
[00:58:26] so I'm not actually charging myself
[00:58:29] the, you know thousands
[00:58:30] of dollars that
[00:58:31] I've racked up over
[00:58:32] the course of this demo.
[00:58:33] If this wasn't a test
[00:58:35] it would send them an actual email
[00:58:38] so they would get an email
[00:58:40] with that link to it
[00:58:41] and this pdf attached to the
[00:58:43] email as well as well
[00:58:45] and I mentioned earlier
[00:58:46] that you could elect to auto
[00:58:48] charge existing customers.
[00:58:50] They still get an email, they just,
[00:58:51] you know, are charged
[00:58:53] with whatever is on file,
[00:58:55] awesome.
[00:58:56] Well done Kamille you've
[00:58:59] showcase the power
[00:59:02] of Airtable very nicely.
[00:59:05] Alright. We will
[00:59:08] conclude, I'm just gonna do
[00:59:10] a quick demo
[00:59:11] before that join our community.
[00:59:13] We'd love to have you,
[00:59:14] if you're not already in there,
[00:59:15] builtonair.com/join
[00:59:17] and there are thousands
[00:59:19] of other Airtable users in there and we'd
[00:59:22] love to have you join with us. So
[00:59:26] in conclusion I'm gonna do just
[00:59:28] a quick demo of an app
[00:59:30] that's in the app store.
[00:59:32] I'm not affiliated with this app
[00:59:35] but I've been thinking
[00:59:37] a lot about barcodes
[00:59:39] because I don't use barcodes
[00:59:42] or QR codes
[00:59:43] actually haven't done any client
[00:59:45] projects that required it. So
[00:59:48] so I'm obviously familiar with them
[00:59:50] or know what they are
[00:59:51] but I haven't actually
[00:59:52] like done a whole lot of work
[00:59:54] with the bar code
[00:59:55] field but so I thought this would
[00:59:58] be interesting because I'm actually
[01:00:01] starting to get familiar with it
[01:00:03] and so basically
[01:00:05] this barcode generator from
[01:00:06] little apps is the publisher of It
[01:00:11] or tiny apps. Little apps
[01:00:18] Anyways you just install this
[01:00:21] from the marketplace,
[01:00:23] they do have a free tier
[01:00:24] that allows you to run
[01:00:26] one at a time
[01:00:28] or you can upgrade and get a license
[01:00:30] that allows you to do bulk updates.
[01:00:33] But basically you'll have to set this up
[01:00:36] a bit
[01:00:37] every field is required
[01:00:39] so you are required to select a view
[01:00:42] and then what you're doing is
[01:00:44] you're converting a text field into a bar
[01:00:47] code. So usually you're gonna
[01:00:49] have like a number like a serial number or
[01:00:51] something with a product.
[01:00:53] And then you want to convert that
[01:00:55] to a barcode.
[01:00:56] I'm just converting the value
[01:00:58] of the name of these projects.
[01:01:00] And then
[01:01:01] you're going to set up
[01:01:03] an attachment field.
[01:01:04] So not a barcode field.
[01:01:06] This is an attachment field
[01:01:07] because it's actually going to generate
[01:01:09] an image
[01:01:10] and save it to the attachment field.
[01:01:13] And then it does also require
[01:01:15] another text field
[01:01:16] for any errors that might be generated.
[01:01:19] Which I did create an error.
[01:01:21] And then once you have
[01:01:23] that all set up there's
[01:01:24] also some barcode options.
[01:01:26] So there's different types
[01:01:28] of barcodes that you could
[01:01:30] set up. They have another app
[01:01:32] for QR codes.
[01:01:33] So I don't know if QR codes are
[01:01:35] supported in this one
[01:01:36] or if it's just barcodes. I had no idea.
[01:01:39] There's so many different
[01:01:41] barcode formats
[01:01:42] but apparently there are.
[01:01:44] And so you can choose the format that
[01:01:46] you need
[01:01:47] and you can if you upgrade
[01:01:49] there's more customizing
[01:01:51] you can do with the
[01:01:53] colors and things like that
[01:01:55] but once you have it set up
[01:01:57] than any record that
[01:01:58] you're selected on,
[01:02:00] you just can do this one at a time
[01:02:02] with the free version.
[01:02:04] And here it generated
[01:02:05] an error basically saying
[01:02:07] that this text was too long.
[01:02:09] So usually you're doing
[01:02:11] like a serial number.
[01:02:12] So apparently there's
[01:02:14] a there's a text limit.
[01:02:15] These ones that are shorter worked.
[01:02:18] So you see here it generated the barcode
[01:02:21] and so this barcode
[01:02:23] somehow represents this text
[01:02:25] value of the name.
[01:02:26] So you could then use this for
[01:02:28] barcode scanners.
[01:02:30] And
[01:02:30] and then it and then if you use the app,
[01:02:34] I believe you can scan a code
[01:02:36] and it will convert that
[01:02:38] into the text value
[01:02:40] of that code and save it into a
[01:02:42] barcode field.
[01:02:44] So that's useful if I know
[01:02:48] some you know industries
[01:02:50] rely heavily on barcodes and QR codes,
[01:02:52] so nice to know that there's
[01:02:55] there's ways that you can
[01:02:57] automate this and and set
[01:02:59] it up so check out barcode generator
[01:03:01] in the marketplace
[01:03:02] along with all the other
[01:03:03] apps that are in the marketplace
[01:03:06] that help you expand your Airtable usage
[01:03:09] and that will conclude our episode.
[01:03:12] Thank you Kamille for your amazing demo of
[01:03:16] what you've built
[01:03:17] and thank you all for joining live.
[01:03:19] If you have something interesting and you
[01:03:22] want to showcase it on a future episode,
[01:03:24] reach out, we'd love to see what you've
[01:03:26] built on air. Take care everyone