Seeing a number of similar requests for calculated column, which at times turns into a complex formula. Using MS Flow, it can be solved easily.
Let's first simplify the logic for the formula:
if(DteofCQ=null) then "Noncompliant"
if(Title="Driver I" OR Title="Officer I" OR Title="Inspector I") then "Certified"
if(Title="Medical" AND DateDiff(DteofCQ,CurrentDate,"d")<1095) then "Compliant"
if(Title="Fitness Test" AND DateDiff(DteofCQ,CurrentDate,"d")<365) then "Compliant"
The Final Output of [Compliance Status] using MS Flow
Steps to Create and Schedule MS Flow
- For developing and testing, create an "Instant-from blank" flow
and add "Manually trigger a flow" action. Later this can be changed to a Recurrence flow
- Initialize few variables (screenshots will follow)
- Add SharePoint action - "Get items" and configure it for your SPO site and list
- Add "Apply to each" action to process each item in the list
- Add "Condition" action to check blank [DteofCQ]
- Calculate DaysDifference (in Days) between utcNow(),which is [CurrentDate] in Flow, and [DteofCQ]
DIV(sub(ticks(utcNow()), ticks(items('Apply_to_each')?['DteofCQ'])), 864000000000)
- Add "Condition" action to check [Title] for Driver I, Officer I, and Inspector I. If true, apply "Certified" logic
- If the condition in step 7 is false, apply Compliance logic for [Title] Medical and Fitness Test and DaysDifference, calculated in step 6.
The expression for setting varcomlianceStatus is:
if(and(less(variables('varDaysBetweenCQandCurrent'),1095), equals(variables('varTitle'),'MEDICAL')) , 'Compliant', if(and(less(variables('varDaysBetweenCQandCurrent'),365), equals(variables('varTitle'),'FITNESS TEST')), 'Compliant','Noncompliant'))
Screenshots showing MS Flow to calculate Compliance Status
To change Manual Trigger:
- Delete "Manually trigger.." which will allow to select a new trigger
- Type Recurrence in the search and select "Recurrence Schedule" and configure the setting as shown in the screenshot below
Hope this helps. Let me know if you have any questions or need clarifications on any of the Flow actions
Updating to show blank for column [DaysCurrentMinusSteofCQ] when:
- [DteofCQ] is empty or null
Here is the updated screenshot; Changes from the original post are described below
Initialize variable [varDaysBetweenCQandCurrent] with zero (0), step 2 in the original post
Move [Set variable - varDaysBetweenCQandCurrent] to the path where "Medical" and "Fitness Test" is compared as shown in the screenshot below. This was in Detail 1 in the original post
- In the [Update item - update list item with the final Status], set column [DaysCurrentMinusDteofCQ] to the following expression. Detail 2 in the original post.
if(equals(variables('varDaysBetweenCQandCurrent'), 0), null, variables('varDaysBetweenCQandCurrent'))
- Add Set variable action to set varDaysBetweenCQandCurrent to Zero (0)
to ensure the next item's processing begins with a zero for this variable
Updated screenshot with "does not contain" condition