Can I create a snapshot of a formula field within the same table?

Hi Lynne. Matthew's solution is perfect for data coming from parent tables, but if I understand you correctly, you want to create a snapshot of a field within the same table, meaning the field isn't coming from a parent table, and therefore there is nothing to look up. Here's a way to accomplish that.

We will need to initialize the field with current values and also have a way to take a snapshot of any records added in the future. That will be accomplished using a dynamic form rule. (If records will be added using grid edit, sync, the API, or similar, the form rule won't apply, but you could create a webhook to take a snapshot in those cases.)

These are the steps to follow:

1. Create a new field that will hold the snapshot data. Since we want to initialize it with current values (I assume), make it a formula field and make the formula return the value of the field you want a snapshot of.
   
   For example, if you want to take a snapshot of a numeric - currency field called Price, make a formula - numeric field called Price Snapshot, with the formula being simply [Price].
   
   
2. To make the data in the snapshot field you just created static, change the field to its corresponding non-formula type (e.g., from formula - numeric to numeric - currency). Now the information in Price Snapshot will not change if Price is changed.
   
   
3. To prevent users from changing the value of the snapshot field, don't restrict their permissions. They will need to have modify permission in order to create a snapshot when records are added. Instead, make sure the field doesn't appear on forms the users will be working with.
   
   This is not as secure as the native static fields based on lookups, but it should suffice in most cases. If you need a higher level of security than this, the snapshot data could be logged in a separate table that non-admin users wouldn't have access to. This would allow you to detect if someone had sneakily found a way to change the snapshot data.
   
   
4. Now, to take a snapshot of the value in the dynamic field when a new record is created, add a dynamic form rule so that (a) when the record is saved, (b) if the snapshot field is blank or empty (i.e., it hasn't been initialized yet), then (c) change the value in the snapshot field to the value in the dynamic field (e.g., change the value in Price Snapshot to the value in Price).

I hope these steps are clear and helpful. Contact details are in my profile if I can be of any additional help.

Phillip Dennis