Old entries in invoice table become inaccurate if I update my rates

I have a base which I use for generating invoices for clients. I have a table for Line Items, a table for Invoices, and a table for Services. The services tab has rates for all services provided. The Line Items tab has all the line items for each invoice, with the rates populated via a lookup from the services tab. The invoices tab totals all the relevant line items into a single invoice and uses a Pages block to turn that into a nice looking invoice.

My issue is this: if I update my rates, it makes all the previous invoices, (that have already been sent out and paid), inaccurate. Can anyone think of any way I could set this up so that once I send an invoice, the rates and totals for that invoice are frozen, so that they continue to be accurate to what I billed at the time even if I change my rates?

The way I solve this now is to export a PDF of each invoice at the time of sending, so that I have a saved version somewhere of what I billed the client. This works, but it means that old entries in the base are essentially meaningless. Can anyone think of a solution?

Hi @Helen_Vaskevitch - One way to solve this is to have a series of “price books”, i.e. prices of services for specific dates, then apply the services for the current price book to the invoice. When you update your rates, create a new price book (for the same services) with new prices. Example:

Price Book Table:

Price Book Items Table:

Invoice Items Table:

To make things easier you could make the Items Lookup on the Invoice Items table reference a view which just shows the current items/prices rather than old prices.


1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.