0

I am looking for where to plop the following formula in a 'Recurrence' Flow for an SP list. I need the Flow to fire once every 24 hours.

The SP calculated column formula (see below) currently works off the date in the SP default Modified column, and two list columns, "DateofCQ" and "Title".

I was told I could get rid of the calculated column on the SP side and perform the same task through a Recurrence flow on the Automate side.

Thoughts?

=IF(ISBLANK(DteofCQ),"Noncompliant",IF(OR(OR(OR(OR(Title="Driver I",Title="Officer I"),Title="Inspector I"),"Certified",IF(ISBLANK(CurrentDate),"Noncompliant",IF(AND(Title="Medical",DATEDIF(DteofCQ,CurrentDate,"d")<1095),"Compliant",IF(AND(Title="Fitness Test",DATEDIF(DteofCQ,CurrentDate,"d")<365),"Compliant","Compliant","Noncompliant")))))

Improve this question
1
  • Can we do a new one? Same process, but no multiple 'Titles'. There is an Apparatus List. Each piece of apparatus has a unique license number ('LicNo' - Text). There is a Status ('Status' - Text) column. There is a 'LastPmpTst' column ('LastPmpTst' - Text). Each piece of apparatus needs to be pump tested every 365 days. I liked to create a Recurring flow to the SP list (once every 24 hrs).
    – Curtis
    Oct 11, 2020 at 20:50

4 Answers 4

Reset to default
0

Matiur Rahman:

I've gotten the flow to work. However, it shows every line with a date as 'Certified'. None are shown as 'Compliant' or 'Noncompliant'. I am providing screenshots of the flow.

Flow Image 1

enter image description here

SP List Image

Improve this answer
2
  • I'm finally back to this; I re-wrote the flow. Got it to run successfully (so it says), and save. However, when I delete the Manual trigger and try to create the 'Recurrence' trigger I get the following error when I run the flow: Request to Azure Resource Manager failed with error: '{"error":{"code":"WorkflowTriggerNotFound","message":"The workflow '95e64206-c04a-40ab-b4a2-713025c67a75' trigger 'manual' could not be found."}}'.
    – Curtis
    Oct 11, 2020 at 3:53
  • @Matiur Rahman , any chance I can get you to assist me with finishing this flow?
    – Curtis
    Oct 21, 2020 at 15:22
0

Updated screenshot with "does not contain" condition check

enter image description here

Improve this answer
0

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"
else "Noncompliant"

The Final Output of [Compliance Status] using MS Flow enter image description here

Steps to Create and Schedule MS Flow

  1. 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
  2. Initialize few variables (screenshots will follow)
  3. Add SharePoint action - "Get items" and configure it for your SPO site and list
  4. Add "Apply to each" action to process each item in the list
  5. Add "Condition" action to check blank [DteofCQ]
  6. Calculate DaysDifference (in Days) between utcNow(),which is [CurrentDate] in Flow, and [DteofCQ]
DIV(sub(ticks(utcNow()), ticks(items('Apply_to_each')?['DteofCQ'])), 864000000000)
  1. Add "Condition" action to check [Title] for Driver I, Officer I, and Inspector I. If true, apply "Certified" logic
  2. 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

enter image description here

enter image description here

Detail 1

enter image description here

Detail 2

enter image description here

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

enter image description here

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:

  1. [DteofCQ] is empty or null
  2. [ComplianceStatus]="Certified"

Here is the updated screenshot; Changes from the original post are described below

enter image description here

Updated Steps:

  1. Initialize variable [varDaysBetweenCQandCurrent] with zero (0), step 2 in the original post

  2. 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

enter image description here

  1. 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'))
  1. Add Set variable action to set varDaysBetweenCQandCurrent to Zero (0) to ensure the next item's processing begins with a zero for this variable

enter image description here

Updated screenshot with "does not contain" condition enter image description here

Improve this answer
19
  • Wow, what a ride. I love it. I'll give it all a try today. Can you add one more expression (is that the correct term)? The formula should force the 'Days to Renewal' Column to be blank for any 'Certification', as they are one time events. All qualifications (Medical, Fitness Test, etc.) are recurring and should show the Days to Renewal. Can this last function be added in?
    – Curtis
    Sep 7, 2020 at 14:29
  • Also the formula I provided was old. I am now using the Modified column for the date, and not the Current Date. Does this matter?
    – Curtis
    Sep 7, 2020 at 14:55
  • At Detail 1 I have a problem. There are a total of 15 Certification titles. The Condition only allows for 10 'or' statements. Is there a way to write it in negative? Can we ask 'if the CQTitle IS NOT 'Medical', 'Fitness Test', 'Live Fire Refresher', 'Self-Contained Breathing Apparatus Fit Test'...then...? <----these are the four actual Qualification titles. If not, the flow can't be completed as demonstrated in the example while using all the required CQTitles.
    – Curtis
    Sep 7, 2020 at 16:43
  • Yes, you can use "not". Here's an example: if(not(equals(variables('varTitle'),'MEDICAL')), 'NOT MEDICAL', 'MEDICAL')
    – Matiur Rahman
    Sep 7, 2020 at 21:12
  • Since MS Flow doesn't need list items' "Modified" timestamp as it calculate days using the current date and time during the execution. Also, for your first question and I will update the flow and provide you a screenshot for that.
    – Matiur Rahman
    Sep 7, 2020 at 21:30
0

Made the change. The flow worked until it got to the final action "Set variable - varDaystoRenewal". The 'Set variable-varDaystoRenewal' value is DIV(sub(ticks(utcNow()), ticks(items('Apply_to_each')?['DteofCQ'])), 864000000000) and currently return '198' in the value field of the 'Yes' condition.

Variable Image Flow Error

Flow Image Flow Image 2

Improve this answer
7
  • Curtis - the last action, that is, [Set variable-varDaystoRenewal] should be set to zero (0), because the next iteration should start with zero for this variable. You need to remove the expression and set it to zero.
    – Matiur Rahman
    Sep 22, 2020 at 15:47
  • Also, maybe it's failing within [Condition Action] as the last action, [Set Verriable-varDaystoRenewal], is showing green in your screenshot. Are all 22 items are failing or a particular item only?
    – Matiur Rahman
    Sep 22, 2020 at 16:52
  • The only thing that show failing is the 'Apply to each' - "ActionFailed. An action failed. No dependent actions succeeded. On run the flow errors out with a red check on thein the last action, 'Set variable - varDaystoRenewal'. The value of that field, when in the Flow Run Failed/Edit Review Screen shows a green '0' for 'inputs' and 'outputs'. I've provided additional screenshots in the Answer Question.
    – Curtis
    Sep 22, 2020 at 20:29
  • Can you delete the last action, [Set variable-varDaystoRenewal] , and then run the flow again? I know the result will not be correct, but just to see if it runs successfully. Is there any other action/expression in the upstream of the flow that this variable has been used?
    – Matiur Rahman
    Sep 23, 2020 at 3:59
  • I hope that you've already resolved the issue. If not, I would like you to check whether you have any reference of 'Set_variable_-varDaystoRenew' in any upstream expressions in the flow as error msg states it couldn't be evaluated. It might sometimes happen in copying and pasting expressions from one action to another. Unfortunately, it doesn't catch in saving the flow.
    – Matiur Rahman
    Sep 24, 2020 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.