Invoicing with Shortcuts & AirTable

Using AirTable as a database for Shortcuts

AirTable has a chameleon-like ability to become whatever I need it to be. In the realm of No-Code platforms, it remains my favorite.

My oldest and largest Base in AirTable is one that I use to track projects and associated clients. This Base allows me to follow the lifecycle of a project (campaign) all the way from proposal through to invoicing. But there's some friction in that last step – invoicing. AirTable's paid tier includes Blocks that can generate invoices from a dataset, but I've always used the free tier. So instead: I manually review my projects monthly, write my invoices, send them, and then mark them as complete.

Certainly this can be improved? Time for my favorite pastime: Smashing No-Code tools together.

In this case, AirTable and Shortcuts on iOS. I want to have Shortcuts take data out of AirTable, parse it, and generate an invoice for the client.

The AirTable Base

First, an overview of how this AirTable base is setup: The Base has 6 tables, but the only two that matter for this project are Client and Campaigns.

Client is a rather simple table, acting mostly as a contact manager. It includes fields for Name, Email, Linked Campaigns, Query, and Friendly Name (I'll expand on those last two later).

Campaigns is a sprawling table with lots of information about individual projects/contracts. For the purposes of this project, I only care about the Name, Linked Client, Cost, and Invoice Status.

One of my favorite features of the AirTable API is the ability to filter your query down to a specific Table View. Views are sets of saved options, including hiding/showing fields, filters, grouping, and sorting. This can be leveraged to simplify the Shortcut workflow that we'll be building by keeping much of the filtering logic on the AirTable side.

The Shortcut

The Shortcut needs to let me pick a Client from a list, find their Campaigns, list them and then add up their costs for a grand total. First, a demo of the finished product. After that I’ll go through it step-by-step.

Building the Shortcut

The first actions receive the list of Clients from AirTable via a GET request. Through nested repeat actions, I'm able to cycle through the JSON response and build a list. I store the client's information in plain text with each piece of data on a new line.

The process of parsing JSON in Shortcuts has always been one of trial-and-error for me. Sometimes I’m not completely sure why a certain repeat function needs to be nested the way it is. It’s logical, but I think this highlights the shortcomings of Shortcuts and the inability to debug a workflow. The best you can do is throw in a Quick View action to check your results.

My solution for storing the data – as multiple lines of text – is a bit inelegant. I’m sure this could be done with a Dictionary. But I appreciate the clarity of plain text and line numbers.

I then present a menu where I can pick from my list of clients. That selection (which remember: is just plain text with the different fields on new lines) is then broken-up line-by-line so I can create variables of their values to reference later.

Setting explicit variables is a bit unnecessary since iOS 13. But, like the plain text solution above, I appreciate the verbose clarity it offers me. If we’re doing visual “coding”, then I want some “code” to be visible.

Next, I make another API call to AirTable, this time to the Campaigns table.

This is where I start to benefit from AirTable's View filtering. Before I started building this Shortcut, I created client-specific views. In my case, I have 8 clients so I made 8 different views, each filtered down to only show Campaigns associated with that client.

And now the Query field in my Client table comes into play. The value of this field matches the name of the View in the Campaigns table. In Shortcuts, I use this value to modify the API request for the Campaigns table. I only receive the data I need.

Client-specific views in AirTable. These are referenced by the query variable in my GET request in Shortcuts

Next, I cycle through the data – just as I did with the client list – and begin to format the individual line items using Markdown.

I then cycle through the data a second time, this time only processing the costs, which I then sum up to create the total.

I have an If statement throw up a warning if that client had no un-invoiced campaigns.

Finally, I bring together all the text blocks into a Markdown document, which I then convert to Rich text and bring into an email. That email uses elements from both API calls to automatically address it, use the Client's friendly name, and add the current date.

Here’s an example of the Markdown formatting:

# Invoice
---
Campaign | Cost
:---------- | ---:
Bergstrom, Boyle and Witting | **$2000**
McLaughlin, Thompson and Muller | **$525**
--- | ---
**Total** | **$2525.00**

You can see the table formatting and alignment makes a big difference in the final result below.

Final Result

Certainly the result is not nearly as sophisticated as what you could do the AirTable’s Page Designer Block. And of course this is a poor solution if you need something like FreshBooks. But for casual account tracking and invoicing this is both feature-rich, and surprisingly easy to manage after everything is setup.

Once iOS 14 is released, then you could theoretically run these invoices automatically on a schedule with no intervention. I think that’d be a big improvement.

If you have any ideas for this Shortcut, or if you have any questions, please drop me an email.

More AirTable

Paul SahnerJuly 19, 2020