Is there a way to fix values once they have been calculated

1

Hi everyone,

I’m trying to work on a project management tool, allowing us to follow the cost of a project based on time passed and the daily rate of an employee.
The problem I have is that if today my employee has a role of junior developper, and tomorrow of senior developper, I don’t want the change to impact all the previously calculated values.
Do you guys have any idea how I could fix the value of a formula once it has been calculated ?

Thanks

2

Hi @Thoma_BIGUERES1,

The idea here would be to have a rate table for developers and have a date column as well as a rate column. Then you can base it on the invoice date.

One of the first Coda docs I ever created was a template to track pay for hourly work. This one is a little complex, but includes things like overtime pay rates and different pay rates per department. Look for hidden columns that do some of the work.

3

Thanks for the quick answer !
I had something like this in mind indeed but didn’t wanted to go through the formula :stuck_out_tongue:
I’ll have a look at this idea anyway :slight_smile:

Thank you

4

A small note: working with date/rate tables works for many projects, but if the rate changes are very frequent (for example with stock prices or exchange rates), this would become a bit problematic.

I have solved a similar type problem by working with two tables. One is a very small (number of rows) table in which we make new records. At the end of the day (but this could be every hour or, if you would like, upon finishing the new record (by pushing a button or with an automation)) we write all the records to an other table (we call it ‘log table’) and we make sure that all references or lookups are written to the log as a value, not a as a lookup or reference (with the exception of the name of the persons and some other fixed items, like the type of activity). From the log table we make reports and rate changes won’t influence the results.

You have to look at your data to figure out which method works best - I (have to) use both methods for different situations.

5

You cannot fix a formula’s value, but you don’t have to make it a formula.

You can have a regular input column (not a formula), and a button that you’d click to update it with a calculated value once.

This is more logical than making a table of historical rates and depending on that in a formula throughout the whole history — that’s because you still risk messing up historical payment records by e.g. deleting old rates entries or editing them erroneously. Whereas by stamping the effective rate into a cell of the payouts table, it’s harder to mess it up because if one ever edited that value they’d immediately see the effect of such change (the whole row would recalculate).

Rule of the thumb: given the multitude of possible solution, choose the one where any mess-ups could be spotted and fixed as soon as possible and affect as little as possible.

6

Thanks for the idea, I do like it ! I’ll have a look at the possibilities here :wink: