Using the {{OLD}} function to log history of multiple fields

Another useful application of the {{OLD}} function is to use it as a history log. You can use the {{OLD}} function to output the history of changes made to multiple fields in a single triggered field.

For this example, we have a table with the following fields:

• Single Line field called “Product Name”
• Currency field called “Price”
• Date field called “Date of Last Price Update”
• Triggered Text field called “Change History”

The goal is to utilize the OLD function in the “Change History” field’s formula to output the history of all changes made to the values in the Product Name, Price, and Date of Last Price Update fields.

FORMULA:

In order to keep a running history of all changes made to these fields instead of just the most recent change that was made, we will use the formula below:


if(or({{OLD}}.{Product Name} <> {Product Name}, {{OLD}}.{Price} <> {Price}, {{OLD}}.{Date of Last Price Update} <> {Date of Last Price Update}),

if({{OLD}}.{Product Name} <> {Product Name}, "Product Name changed from " & {{OLD}}.{Product Name} & " to " & {Product Name} & ", ", null())&

if({{OLD}}.{Price} <> {Price}, "Price changed from " & "$" & {{OLD}}.{Price} & " to " & "$" & {Price} & ", ", null())&

if({{OLD}}.{Date of Last Price Update} <> {Date of Last Price Update}, "Date of Last Price Update changed from " & {{OLD}}.{Date of Last Price Update} & " to " & {Date of Last Price Update}, null())&"\n"&

{{OLD}}.{Change History},{{OLD}}.{Change History})

In plain English, this formula can be read line-by-line as:

• If the old value in the Product Name field does not equal the current value in the Product Name field, or if the old value in the Price field does not equal the current value in the Price field, or if the old value in the Date of Last Price Update field does not equal the current value in that field, then go on to the next steps in the formula
• If the old value in the Product Name field is not the same as the current value in that field, output the text “Product Name changed from” whatever the old value was “to” whatever the current value is. Otherwise, if the values are the same, do nothing.
• If the old value in the Price field is not the same as the current value in that field, output the text “Price changed from” and “$” whatever the old value was “to” whatever the new value is. Otherwise, if the values are the same, do nothing.
• If the old value of the Date of Last Price Update field is not the same as the current value in that field, output the text “Date of Last Price Update changed from” whatever the old value was “to” whatever the new value is. Otherwise, if the values are the same, do nothing.
• The final “n\” inserts a line break to ensure that the history of changes outputs any new changes on a new line.
• The final line {{OLD}}.{Change History},{{OLD}}.{Change History} is needed to keep a running log of all changes made in this field. If you did not include this line, the output would only show the most recent change made instead of all changes over time.

OUTPUT:
In this example, I created a single record on this table with the following values:

• Product Name = Widget A, Price = $10, Date of Last Price Update = 2023-06-01

I then changed the values in those fields to:

• Product Name = Widget B, Price = $12, and Date of Last Price Update = 2023-06-03

In the screenshot below, you will see those changes logged in the bottom line. Any new changes made are logged on a new line, with the most recent change at the top of the list.