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

Can't sort on calculated "Elapsed Days" column in a View

I have a calculated column to show the elapsed days between two dates, but when I place this field on a View, the sorting options are disabled. 

I see in the documentation that this is a known limitation in the following conditions:

  • Sorting is disabled on:
    • A calculated column that contains a column of a parent row.
    • A calculated column that contains a logical column (for example, address column)
    • A calculated column that contains another calculated column.

My situation doesn't seem to satisfy any of these 3 conditions, as I am only comparing 2 date fields on the main entity.  I have added an attachment showing the details of my calculated field.

 

As a work-around, I created a separate non-calculated field, and added a business rule to copy the data from the calculated field to the non-calculated field, but of course this doesn't keep the non-calculated field up-to-date because the business rule only runs when the record is opened in the form, therefore the values get out-of-sync very often.

 

Questions:

- Is there a way to have a calculated field sortable on a View?

- Is there a different reliable method of showing an "Elapsed Days" value in a View, and that is a sortable column?

 

Thank you

 

2 REPLIES 2
kleinda
Regular Visitor

Hello, did you have any luck regarding solving this issue?

DaveStorey
Advocate I
Advocate I

Hi, the only way I could resolve this issue was to create a second field and update it nightly using Power Automate.  Here are my notes:

 

Problem:  Calculated fields that reference other calculated fields (or even functions such as Now()) are not sortable in a View

 

This workaround uses these steps:

  1. Create a second ‘copy’ field “elapseddays_nbr”
    1. Ensure this ‘copy’ field is not included in Auditing
  2. Create a Business Rule to populate this ‘copy’ field with the value from the calculated field
    1. This just makes sure that when a user makes a change to a record using the Main Form, the field on the View will also be correct immediately
  3. Add the actual calculated field to the Main Form(s)
  4. Add the ‘copy’ field to the respective View(s)
  5. Create a scheduled Power Automate Flow to copy the calculated field’s value to the ‘copy’ field nightly

 

 

DaveStorey_0-1656423978613.png

 

 

 

DaveStorey_1-1656423978616.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (5,295)