Set Price Based on Service field?

[slyfox]

I use Airtable to track my weekly services for a company. I would like to automate invoicing by also attaching price associated with the service. For example:

Service A = $60
Service B = $240
Service C = $70

Currently, I filter all of the services in the table (filter specific service) and paste a price to all the entires. I repeat this with each service type.

What I would like is some kind formula or something whereby I can set what Service A, B, and C cost so that the price automatically appears in the field next to the service column. At the bottom, I can see the total. I will then use this for invoicing.

Maybe creating a new Table where each service rate is defined and then somehow connecting this rate to the “Service Log” table?

[Justin_Barrett]

slyfox:

Maybe creating a new Table where each service rate is defined and then somehow connecting this rate to the “Service Log” table?

You’re on the right track here. I built my own invoicing system in Airtable last year, and use a [Rates] table to track rates for different types of services and clients. Some rates are tied to specific clients, others can be used for any client. Each rate also has a date range. If there’s a start date but no end date, that rate is active, and can be linked to records in my [Line Items] table. If a rate changes, or I stop working for a specific client, I add an end date, and that rate is effectively archived and no longer available to use for new invoices. That way old invoices don’t get messed up when rates change.

[slyfox]

Can you please share a sample of how the linking works?

[Justin_Barrett]

In my case, it begins with an entry on the [Activities] table. Not all activities are billable, though; I track some activities for personal reasons, but those that are billable are marked as such and have a time entered into a duration field.

I then move to the [Line Items] table. Each record on that table links to a billable activity in one field and a rate from the [Rates] table in another. Rollup fields use those links to pull in the activity duration and the specified rate, and the total amount for that line item is calculated.

Finally I move to the Invoices table, where each record links to a client from the [Clients] table, and one or more records from [Line Items]. All line item amounts are added to get the final invoice total.

[slyfox]

Can you please take a look at this table? Am I linking correctly?

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

[Justin_Barrett]

Yes, that looks correct. One thing to consider is adding a date range for your rates, like I mentioned above. Here’s why that may be important down the road. If you ever raise your rates, changing the value in the [Rates] table will change all entries in [Service Log] that are tied to that rate.

In my case I used two date fields—{From} and {To}—and have a view named “Current Rates” with a filter to only show records where {To} is empty. Back in my [Line Items] table (which would be [Service Log] in your case), I set the options for the rate link to only show records from that “Current Rates” view.

When a rate changes, I put the last active date for that rate into the rate’s {To} field, then add a new record for the new rate, putting the start date in the {From} field. That new record automatically becomes part of the “Current Rates” view for use on new invoices, and the old rate is still tied to older invoices.

[slyfox]

Thanks for the idea.

By adding an automated rate to my table I solved one problem but created another problem. Adding linked records broke updating Airtable from Siri Shortcuts as I can’t just paste the service name, it has to pull the record ID instead. I have to figure this out.

[Justin_Barrett]

I haven’t used Siri Shortcuts that much, but in the Scripting section I see an item named “If” that you might be able to use to take the service name and convert it to your Airtable record ID. I also see a Dictionaries feature, which would allow you to associate a service name with an ID.

[Justin_Barrett]

On a related note, how are you using Siri Shortcuts with Airtable? Airtable doesn’t appear on the list of apps to connect to, so I’m guessing you’re using some back door method. This has got me thinking about shortcuts I could make myself, but I don’t know how to take the shortcut output and feed it into Airtable.

[slyfox]

Here is a good video how to quickly setup Siri Shortcuts with Airtable using API and URL Get: How I Use AirTable + API Automation in Shortcuts. This is enough for me.

I use Airtable API. Inserting record ID (for linked records) directly via API does not work either as it has to be done using a more sophisticated approach.

For more sophisticated use of the API, take a look here: Anyone using the API for Siri Shortcuts?