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! Go to Solution.
If the previous screenshot still does not work, then try this instead.
Just remove [_thisrow_after].
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]
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.
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:
Happy to hear if you see any other solution to it / if I have missed something out.
User | Count |
---|---|
41 | |
31 | |
29 | |
16 | |
14 |