Set Price Based on Service field?

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.

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

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.

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

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.

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.

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.

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.

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?

That’s pretty cool! I didn’t know that Siri Shortcuts supported sending CURL commands to the web! I thought that Siri Shortcuts only worked with supported apps.

Sounds like I’ve got a new personal challenge to work on. :slightly_smiling_face: Something tells me there’s got to be a way to make it work without a huge amount of effort.

On a related note, that first example you shared is pretty slick, but that same data could also be entered using an Airtable form. I’ve got a couple bases where I enter new records this way, and have home screen shortcuts for them. If you’re using more advanced Siri Shortcuts features, though, then this might not work for you.