TrackVia has dozens of functions to help you build formulas for your calculated and triggered fields. One of the most powerful (and underutilized) functions in our arsenal is the {{OLD}} function, which allows you to display the previous value of a field. This function is particularly useful when you want to know the history of another field, or auto capture the date something changes.
Formula Syntax
{{OLD}}.{Field Name}
Requirements
- Must be used in a triggered field.
- Cannot reference other triggered fields.
- Can only reference fields from the same table.
- Should not be used for timestamps, as triggered fields do not make adjustments for timezones.
Examples
Freeze a date
This might be used to capture a due date. For example, if a project has a due date specified, and someone were to change that date, you can use the {{OLD}} function to freeze the original date that was entered.
if(isblank({{OLD}}.{Freeze a date}), {Date}, {{OLD}}.{Freeze a date})Note: Because this is a text field, the date will display as yyyy-mm-dd. You can use formulas to change the format.
Previous status
In this example, we want to capture the previous value of the "Status" field.
{{OLD}}.{Status}Descriptive Previous Status
Instead of displaying just the previous status, you can insert text to be more descriptive.
if({{OLD}}.{Status} <> {Status}, snip({Last User},"","<") & " changed the status field from " & {{OLD}}.{Status} & " to " & {Status} & " on " & month({Updated}) & "/" & day({Updated}) & "/" & year({Updated}) & ".", {{OLD}}.{Descriptive Previous Status})Descriptive Previous Status History
Adding onto the example above, you can also keep a running history of the changes.
if({{OLD}}.{Status} <> {Status}, snip({Last User},"","<") & " changed the status field from " & {{OLD}}.{Status} & " to " & {Status} & " on " & month({Updated}) & "/" & day({Updated}) & "/" & year({Updated}) & "." & "\n" & {{OLD}}.{Descriptive Previous Status}, {{OLD}}.{Descriptive Previous Status})Number of changes to a field
The {{OLD}} function can count how many times a field has been updated, here we're referencing a drop down, but you may also reference other data entry fields (not calculated fields).
if({{OLD}}.{Status} <> {Status},coalesce({{OLD}}.{Number of status changes},0) + 1,{{OLD}}.{Number of status changes})Example Record
Related Video:
Comments
0 comments
Please sign in to leave a comment.