cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lachlanP
Advocate I
Advocate I

Calculated column - datetime that another column was changed?

Hi,

 

I'm hoping what I'm trying to do is possible. I want to have a field that  updates when another field is changed. Similar to how the modified_on column works, but for specific field(s). 

 

In my case I have two columns: "State" and "disabled_on". What I want is that when the state is changed to the string "Disabled", the column "disabled_on" updates it's datetime. I set this up with "disabled_on" as a calculated column as:

If State equals "Disabled"

Set disabled_on to Now()

lachlanP_0-1660237981039.png

 

This seems to work at first glance, but after some further testing it falls apart. It seems there are other cases where the field gets revaluated, and the date gets updated to when it was revaluated, rather than when the field was changed. 

 

Has anyone done something similar or know of another approach I might be able to take to handle this? 

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
SBax
Impactful Individual
Impactful Individual

By design, a calculated column will update more often than you need it to, they are designed to update on change of the record, this applies to any changes, not just changes to the one field 

 

For your scenario, you will be looking at either a classic workflow, or a JavaScript onChange function if you want to conditionally update a field, with no delay (like you'd get with a flow)

 

I personally would go with the latter, as there is a fair chance that classic workflows will be deprecated eventually 

View solution in original post

5 REPLIES 5
SBax
Impactful Individual
Impactful Individual

To clarify, should the field re-evaluate on save of the record, or should it only update when you change the value of state to disabled?

 

If it's the former, Calculated columns can be a bit glitchy, I would recommend the new power FX formula column instead.

 

If it's the later, I would recommend either a flow or a classic workflow, set to only trigger when state changes, and is equal to "disabled"

 

Hope this helps

 

It's the latter, I want to retain the time when the column was switched to disabled, regardless of when the record is saved.

 

I was hoping to avoid using a flow as it seems that calculated columns are updated much quicker, and I want to get the time right when the column is changed. It also seems that calculated columns are updated immediately in the Portal where users will access this data, where as if it's updated by a flow it wont reflect in the portal until the cache is cleared up to 15min later. 

 

Is there maybe some 'modified_on' metadata on a column I could leverage? 

 

Thanks for the help

SBax
Impactful Individual
Impactful Individual

By design, a calculated column will update more often than you need it to, they are designed to update on change of the record, this applies to any changes, not just changes to the one field 

 

For your scenario, you will be looking at either a classic workflow, or a JavaScript onChange function if you want to conditionally update a field, with no delay (like you'd get with a flow)

 

I personally would go with the latter, as there is a fair chance that classic workflows will be deprecated eventually 

Interesting! Is there somewhere to add javascript functions in a dataverse table?

SBax
Impactful Individual
Impactful Individual

There is indeed, reference documentation here 

 

Let m know if you need any more details, happy to help 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Users online (5,283)