Linked field snapshot

[Klaas_Nienhuis]

I’m making a base to track time spent by contractors. I’ve got a table with contractors and their hourly rate and I’ve got a second table where contractors fill in a number of hours and then a formula calculates hours*rate for a total. It’s pretty straightforward and works well.
My question is this: when a contractor changes her hourly rate, the formula uses this and calculates correct totals. But old totals now also use the new hourly rate. This is not what I want.
Is there a clever way to create a snapshot of the result of a calculation or another method to make a formula use an old hourly rate? Or perhaps I need to restructure my base to make this happen.
Klaas Nienhuis

[Jeremy_Oglesby]

I would suggest a slight restructuring of your base. I’m guessing your table w/ hourly rates has contractors pair with their hourly rates as records (they are listed vertically).

I’d suggest flipping that. Make the table a “Pricing Schedules” table. Have a column for each contractor, and one “Pricing Schedule” record will contain the hourly rate for each contractor, in that contractor’s column.

I’d suggest having the name (primary column) of the pricing schedule record include a date (perhaps a formula with a CREATED_AT() timestamp) and a description. Then you can add a link to that pricing schedule in your other table (instead of a link to a contractor). You’ll have to then have lookup columns that lookup each contractor’s rate (you can hide all these) and include a conditional in your calculation formula that finds the right contractor’s rate.

This way, a calculation record is based on a static pricing schedule, not on a dynamic contractor rate. If a contractor changes their rate, you make a copy of your pricing schedule record (alt/option + drag), change the rate that needs to be changed in the new schedule, and then have a check mark field that “retires” the old one, by adding “DO NOT USE” to the beginning of the name, so that you don’t accidentally link an old pricing schedule to new calculation records. When you use the new pricing schedule, you are able to have a new hourly rate for a contractor, without affecting old records that are based on an older pricing schedule.

Here’s an example in a base I’ve built:

Retired schedules go here:

The formula in that name field is this:

IF(
  Retire=TRUE(),
  "🛑 Deprecated - Don't Use 🛑 "
)
&
"("
&
DATETIME_FORMAT(
  CREATED_TIME(),
  'l'
)
&
")"
&
IF(
  Description,
  " " & Description
)

[Klaas_Nienhuis]

@Jeremy_Oglesby,
thank you for this explanation. It works great. I now only have to specify which contractor and which pricing schedule to use for each time item and the table figures out what the total cost is. Adding updated prices works just like you say and doesn’t affect already invoiced time.
I’ve got one question about a formula. For a single time item I specify a contractor, for instance “Klaas”. I then use this name to select from the rate-lookups I take from the pricing schedules table. I have a “Rate Klaas” column for instance. Is it possible to use the string “Klaas” to access the column with the name “Rate Klaas”? I now use a bunch of nested IFs, but it feels I could do it smarter.
Klaas

[W_Vann_Hall]

In addition to what @Jeremy_Oglesby has said, you might find some code from the base described in this post helpful.

For instances where I need to follow what I guess is kind of an indirect reference (for instance, using the contractor name to know which rate field to access), I tend to use concatenated strings passed through lookup or rollup fields. For instance, in your case I would have a table of contractor rates where each record contained at least the contractor name, pricing schedule, and hourly rate. I would also define a formula field that concatenated those values into a single variable, like so:

Klaas:01:120.95

From a linked table, I would then rollup all of those combination values using an ARRAYJOIN() function with a rarely used character as a separator value. For instance,

ARRAYJOIN(values,'|')

might return something like this:

Klaas:01:120.95|Klaas:02:135.95|Jeremy:01:140.95|Jeremy:02:147.95|…

Finally, in my formula to calculate costs, I’d extract the total cost like this:

{Hours}*
VALUE(
    MID(
        {Contractor String},
        FIND(
            {Contractor}&':'&{Schedule},
            {Contractor String}
            )+LEN(
                {Contractor}&':'&{Schedule}
                )+1,
        6
        )
    )

(That assumes hourly costs are a fixed six-digit length.)

This takes a little more set-up up front, but once it’s in place, you can add new contractors without having to define new fields or modify formulas.