Track all changes?

Hi all,
Is there a recommended method to track & view changes of a row, say on a monthly basis?

For example, if I have a list of 500 records and I want to see where a column has changed from 1 value to another over the last month, is that possible?

Thanks

Solved Solved
1 21 2,101
1 ACCEPTED SOLUTION

If the previous screenshot still does not work, then try this instead.

Just remove [_thisrow_after].

View solution in original post

21 REPLIES 21

LeventK
Participant V

How do you want to see them? In the app or in the editor or as a report?

Hi @LeventK. Most likely 2 use cases; monthly report as well as being able to see in the app.
For the former (report) a summary of where a column has changed value.

For the latter (in the app), if I were to click into a record (row), can I see where over time this record has been updated.

Are you considering a specific column value to be tracked or any column value to be tracked and recorded upon change? Whatโ€™s your consideration?

I can say both options are possible.

All changes in that column. Iโ€™m unsure as to where I would record the change

You need to add one physical column to your sheet to track all those changes.

So I now have a โ€˜Priorityโ€™ column and a โ€˜Priority_Historyโ€™ column.

A few further Qs,
Is โ€˜Priority Historyโ€™ a change column?
Can it hold every instance of when โ€˜Priorityโ€™ changed?

I guess Iโ€™m looking for a complete record/history of when โ€˜Priorityโ€™ has changed, and what the change in values were.

Thanks!

@Colin_Lough
[Priority_History] column shall be a Long Text type column. You can record the history of the [Priority] column like this:

IFS(
	IN([Key],TableName[Key]),
  IF(
    NOT([Priority]=LOOKUP([_THISROW].[Key],"TableName","Key","Priority")),
    IFS(
      LEN([Priority_History])>0,
      CONCATENATE([Priority_History],"
      ","{","
      ","Previous Value: "&LOOKUP([_THISROW].[Key],"TableName","Key","Priority"),"
      ","Changed Value: "&[Priority],"
      ","Change Timestamp: "&NOW(),"
      ","}"
      ),
      TRUE,
      CONCATENATE("{","
      ","Previous Value: "&LOOKUP([_THISROW].[Key],"TableName","Key","Priority"),"
      ","Changed Value: "&[Priority],"
      ","Change Timestamp: "&NOW(),"
      ","}"
      )
    ),
    [Priority_History]
  )
)

Oh wow, thank you @LeventK
Iโ€™ll try this out shortly and let you know how I get on.

May be another option is to use an event action on form save such as follows

Event action of type โ€œData: Set the values of some columns in this rowโ€

and the expression of the action to set [PriorityHistory] column as

CONCATENATE([PriorityHistory], ", ", "Priority- ", [Priority], " set at ", NOW())

Edit: Initial value of [PriorityHistory] is " "

Hereโ€™s my output.
Letโ€™s say I wanted to list out all instances where rows went from the highest changes, i.e. priority 4 to priority 1, am I better in writing these priority changes to a separate table and pulling records from there?

@Colin_Lough
You can easily differentiate it inside the expression actually. For example; if you only want to track the changes when priority level increased or decreased by at least 3 points (I donโ€™t have any idea about that priority and its max level, just exemplifying here), then you can construct a condition for it:

IFS(
	IN([Key],TableName[Key]),
  IF(
	AND(
		NOT([Priority]=LOOKUP([_THISROW].[Key],"TableName","Key","Priority")),
		OR(
			NUMBER(RIGHT([Priority],1)) - NUMBER(RIGHT(LOOKUP([_THISROW].[Key],"TableName","Key","Priority"),1)) >= 3,
			NUMBER(RIGHT(LOOKUP([_THISROW].[Key],"TableName","Key","Priority"),1)) - NUMBER(RIGHT([Priority],1)) >= 3
		)
	),
    IFS(
      LEN([Priority_History])>0,
      CONCATENATE([Priority_History],"
      ","{","
      ","Previous Value: "&LOOKUP([_THISROW].[Key],"TableName","Key","Priority"),"
      ","Changed Value: "&[Priority],"
      ","Change Timestamp: "&NOW(),"
      ","}"
      ),
      TRUE,
      CONCATENATE("{","
      ","Previous Value: "&LOOKUP([_THISROW].[Key],"TableName","Key","Priority"),"
      ","Changed Value: "&[Priority],"
      ","Change Timestamp: "&NOW(),"
      ","}"
      )
    ),
    [Priority_History]
  )
)

Hi @LeventK
Coming back to this one after trying a few things. Iโ€™m triggering a workflow for all updates in โ€˜Priorityโ€™ column from Table โ€˜New Legal Obligationsโ€™ where [_THISROW_BEFORE].[PRIORITY] <> [_THISROW_AFTER].[PRIORITY]

My action is adding a new row to Table โ€˜ChangeLogโ€™ Table and Iโ€™m having issues bringing through the priority field prior to being changed.

The PriorityBefore expression Iโ€™ve added is
LOOKUP([_THISROW].[UniqueID],โ€œNew Legal Obligationsโ€,โ€œUniqueIDโ€,โ€œPriorityโ€) but this brings through the new value.

LOOKUP([_THISROW_BEFORE].[UniqueID],โ€œNew Legal Obligationsโ€,โ€œUniqueIDโ€,โ€œPriorityโ€) throws an error. Unable to find column โ€˜_THISROW_BEFOREโ€™

Can you see what Iโ€™m missing? Thanks!

You are trying to do it with a workflow rule, so the new value will already be recorded to the sheet and therefore LOOKUP expression will fetch the new value from the sheet. Try with this:

[PriorityBefore]
[_THISROW_BEFORE].[Priority]

[PriorityAfter]
[_THISROW_AFTER].[Priority]

@LeventK

That was my 1st attempt but I still get an error.
Workflow

Action

Error

When the action is fired, App is losing the state and value which will be returned by [_thisrow_before] expression, that s why this error comes.
To solve this, you need to create another physical column to store the previous value, using the same expression [_thisrow_before].[Priority] which are placed to initial value expression. This initial expression will be reset on edit conditionallly, [_thisrow_before].[Priority] <>[_thisrow_after].[Priority] only. Once the value in priority is changed, this field will hold the value before the change. Once the form is saved, still value is down there as it is physical column.

On the action to add new row, pass this value as PriorityBefore value, then you would be able to get out of this maze.

Thanks, @tsuji_koichi

It does not seem like this workflow is being triggered, nothing is added to the ChangeLog table when I update a value in the โ€˜Priorityโ€™ column. Added everything as you stated (I believe)


Probably you are missing RESET ON edit set up.

It should look like this.

If the previous screenshot still does not work, then try this instead.

Just remove [_thisrow_after].

I need assist with enable help with volunteers 

Hi @Koichi_Tsuji thank you for your solution! Exactly what I was looking for. If I can add up to this: I realised that my action gets triggered every time I save my edit, even if I didn't do any changes to the column of interest. Reason is that the column that holds the previous value does not update in the data sheet. So it always remains different to the value of the column that gets changed.

Ksenia1_2-1658414868792.png

However, my condition in the action compares the values of the before column with the column of interest (as [_THISROW_BEFORE].[...] <> [_THISROW_AFTER].[...] does not trigger my action):

[data_object_approval_before] <> [data_object_approval]

Hence, after saving the form, we need another action changing the value of the before column to the value of the column that gets changed:

Ksenia1_1-1658414120789.png

Happy to hear if you see any other solution to it / if I have missed something out.

Top Labels in this Space