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

I have looked at several of the answers to this question but I am having some difficulty creating the snapshot field.

PhillipDennis
8 years ago
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:
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].

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.

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.

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

MCFNeil
Qrew Captain
8 years ago
The native version of a snapshot, takes a value from a lookup field, and pastes it into your snapshot field when the record is saved. This original value will not change, even if the information from the parent lookup field does change.

This is helpful when you are using some type of currency exchange, because the exchange rate (parent value) may change down the road, but the rate at the time of purchase was 'x'.

Just make your snapshot field match the field type of your lookup field, then change the setting of the snapshot field to connect to the lookup field. A

Any new records after that will be snapped into the field, and saved.

I can send screen shots if that'll help.
PhillipDennis
Qrew Cadet
8 years ago
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:
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].

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.

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.

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
- LB
  Qrew Cadet
  28 days ago
  What does your step 4 look like in setting up this rule in the new form format?
  - MarkShnier__You
    Qrew Legend
    28 days ago
    In New Style Forms, there is a radio button on the form rule to fire on record save.
MarkShnier__You
Qrew Legend
28 days ago
My hero Kirk Trachy, now retired from Quickbase always said.
"If you can say it we can do it".
Can we restart from the beginning here and have you say in clear words what you're trying to do?
- LB
  Qrew Cadet
  28 days ago
  Sure, I have a field in the parent table that can be changed but also need to have another field in that same table reflect what the previous entry was once the information in the main field changes. For example one field holds the initial entry of an ID and if that ID were to change and someone change the info in that field I need to reflect in the Previous ID field to reference later.
  - MarkShnier__You
    Qrew Legend
    28 days ago
    In New Forms, I don't think that they have the rule about when the value in a field has changed.
    I believe you will need to use a Pipeline. There is syntax you can use like this
    {{a.$prev.est_start_date}}
LB
Qrew Cadet
25 days ago
Do you have any links on any help docs directing me in how to set this up. I still find pipelines quite intimidating.

Forum Discussion

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