Expand all | Collapse all

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

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

    Posted 02-16-2017 20:59
    I have looked at several of the answers to this question but I am having some difficulty creating the snapshot field.

  • 2.  RE: Can I create a snapshot of a formula field within the same table?

    Posted 02-16-2017 21:47
    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.

  • 3.  RE: Can I create a snapshot of a formula field within the same table?

    Posted 04-05-2017 16:32
    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