Can a formula field be converted to read only after a condition is met?

  • 1
  • 1
  • Question
  • Updated 9 months ago
  • Answered
We have a formula field that calculates the correct pay rate based on several factors such as date, location, and service type. We want that value to be locked in once the time card is marked paid in order to accurate historical data. Is possible to make that formula value read-only after the condition of it being marked paid is met?

The big problem we face is that when client's move (location changes) then all of the existing time cards have altered pay rates. Due to volume and complexity, our team can't manually input the pay rate each time into a static field.
Photo of Wesley Moss

Wesley Moss

  • 132 Points 100 badge 2x thumb

Posted 9 months ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
Re: Big Problem  No problem!

Snapshots to the rescue.  It's not really the "formula" you want locked, it is the lookup of the rate that you want frozen.

So I would rename your current lookup fields to be called like [Rate (lookup)] and then per the help text below, make a new field called [Rate] or if you prefer [Rate (frozen)] or perhaps [Rate (snapshot)].  Set the field properties to snapshot (freeze) the value of the lookup field.  Remember to initialize the field with the checkbox when you create the field.

Then look for all uses of the field [Rate (lookup)] and replace with [Rate (frozen)]

Here is help text on Snapshots.

https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html
Photo of Wesley Moss

Wesley Moss

  • 132 Points 100 badge 2x thumb
Thanks, this is definitely educational and new information for us, but we are working with a formula numeric field within on table and the snapshot feature doesn't exist for this field type unless I'm missing something. Also within our formula numeric field, there aren't any lookup fields that we could utilize snapshot for that would solve our problem.

Is there a different solution for a formula numeric field, where we are plugging in specific numeric values based on other lookup fields?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
OK, so plan B.  I guess that you have a hard coded formula field with the calculated rate which depends on the location.

I suggest setting up an Action to be triggered when the record is marked as Paid.  This would copy the calculated amount into a plain numeric field which of course would be frozen.  An Action will be more reliable than a form rule and also will work when multiple records are updated by say Grid Edit.

Now, the issue you will run into that on the surface it will appear that a Action can only Add a record or edit child records and what you want to do is to act on the record itself.

The Technique, which is perfectly fine to use is to set up a Report Link field which will define the set of records to be edited to be the record itself.  So you configure on the left side of the Report Link field properties to be the [Record ID#] field and then navigate on the right side to the app and the same table and the same [Record ID#] field. 

Note that the Action will operate under the Permissions authorities of who created the Action, so you may want to consider setting up a userid owned by the IT department to be used for Actions and also any Sync Table connections.
Photo of Wesley Moss

Wesley Moss

  • 132 Points 100 badge 2x thumb
Thanks a lot! This is very useful. We started to experiment with that solution and your additional information here will allow us to perfect that idea.