Creating Recurrence flow for updating column based on number of days

[thomasw1964]

• In the flow, add a condition to check the "Start Date" field:

  • If the "Start Date" is within the first 30 days, set a variable to "Phase1."
  • If the "Start Date" is over 30 days but within 365 days, set the variable to "Phase2."
  • If the "Start Date" is over 365 days, set the variable to "Phase3."

• Update the SharePoint item's phase column with the value stored in the variable.

• I have been trying different stuff all day with Conditional Formatting , but then learned it won't automatically update
• I am lost on the logic here in Powerautomate

[wskinnermctc]

How many rows/items do you expect to be in this sharepoint list? 

Your plan is not complicated, but it will be doing a lot of duplicate update if you don't do some additional checks. 

For example if you have 30 Items with a StartDate in 30 days.

• The schedule flow runs today and updates all 30 items to Phase1
• Tomorrow the schedule flow runs and updates all 30 items to Phase1
• Thursday the schedule flow runs and updates all 30 items to Phase1

That is a lot of wasted data updating. Also your items will look like they were modified everyday.

[FLMike]

Hello

Inside your Apply to Each

1. Create a Compose. Rename it to Get Days

Add this expression, remembering to replace where I have Your Date Column

This calculates the days between today and your date column.

int(split(dateDifference(formatDateTime(utcNow(), 'MM-dd-yyyy'), formatDateTime(items()?['Your Date Column']), 'MM-dd-yyyy')),'.')[0])

NOTE: If your Date Column is a String then do this instead
int(split(dateDifference(formatDateTime(utcNow(), 'MM-dd-yyyy'), formatDateTime(DateValue(items()?['Your Date Column'])), 'MM-dd-yyyy')),'.')[0])

2. Create a second Compose. Rename it to Set Phase

if(lessOrEquals(outputs('Get_Days'), 30), 'Phase 1', 
   if(and(greaterOrEquals(outputs('Get_Days'), 31),lessOrEquals(outputs('Get_Days'), 365)), 'Phase 2', 'Phase 3')
)

Now the Output of Compose two will be Phase 1 or 2 or 3.

But as @wskinnermctc  says you could be updating a lot.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey 

[wskinnermctc · Accepted solution]

You need to use the field in the Get Items that says Filter Query. Use that to only get items less than or equal 30 days and the phase column does not equal "Phase1".

This way it only gets items that need the Phase1 updated.

Look at this link Microsoft Learn In Depth Get Items that explains how the filter query is used in Get Items.

To just update the Phase1 items it will look something like this below and use the filter query:

StartDate le 'addDays(utcNow(),30)' and PhaseColumn ne 'Phase1'

The Get Items looks like below with the filter query above.

Get Items with StartDate less or equals 30 days and Phase Column does not equal Phase1

Then after the update less than 30 day items. You can add another get items that is filtered by between 30 and 365 days. So this will only update the Phase2 items.

The Get Items will use the Filter Query:

(StartDate gt 'addDays(utcNow(),30)' and StartDate lt 'addDays(utcNow(),365)') and PhaseColumn ne 'Phase2'

In the flow it will look like this below:

Get Items between 30 days and 365 days and update with Phase2

After the Phase2 you can add another Get Items that will only update items greater than 365 days and need to be changed to Phase3.

StartDate gt 'addDays(utcNow(),365)' and PhaseColumn ne 'Phase3'

It will look like this below

Get Items greater than 365 days and PhaseColumn not equal to 'Phase3'

So basically you have 3 different Get Items that will only get the items within a date range and update them if they have not already had the phase updated.

Please review Microsoft Learn In-Depth Get Items that has a great example to reference.

[thomasw1964]

Thank you guys for the assistance,Ii will be trying tomorrow and update this thread , again thanks!

Kinda burned out for the day today

[thomasw1964]

This has me the closest but the output on the list came back as

if(lessOrEquals(outputs('Get_Days'), 30), 'Phase 1', if(and(greaterOrEquals(outputs('Get_Days'), 31),lessOrEquals(outputs('Get_Days'), 365)), 'Phase 2', 'Phase 3'))

When I use any expression like output() or triggeredoutput() against the output of compose2 the flow fails

[thomasw1964]

I tried many different ways to add the filtering always get a failure on the filtering

[thomasw1964]

Trying these expressions next

concat(slice(outputs('Compose_2'),

add(nthIndexOf(outputs('Compose_2'), ' ', 12), 2),

nthIndexOf(outputs('Compose_2'), ' ', 13)),

substring(outputs('Compose_2'),

add(nthIndexOf(outputs('Compose_2'), ' ', 8), 2), 1))

[thomasw1964]

I see what this did ... ugh it made everyone phase1

[thomasw1964]

Interesting, I did 2 Get Items , the first one checks for Phase1, this allowed me to grab the Dynamic in the second for the Start Date column and do start date le addDays(), 30 and that still fails. I also realized that I need to add client status of Active, as we won't care about updating inactive clients ... we only ever have 160 clients max so I don't think it will be a lot of updating if I filter down to just Active status

[thomasw1964]

Think know why my filters are failing , seems you cannot filter on choice columns or conditional columns

[FLMike]

Hi,

I don't understand what you mean you cannot use the out put from the second Compose. It would have to be how you are using, not that you cannot use it, works fine for me.

Helps to share exactly what you did, not just say it didnt work. Pictures of the flow etc.

Your ask was to make it say Phase 1 or Phase 2 or Phase 3, thats exactly what I provided. What choice are you referring too, you didn't mention that before.

And yes you can filter on choices. Is it multiselect, single etc?

If you wanted something other than provided, please add details and re-write the ask.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[thomasw1964]

I started with recurrence, added a get items(no filter for testing), created an apply to each, inside of that  I created the 2 compose  the 2 compose and followed up with an Update Item.

[wskinnermctc]

@thomasw1964 you need to make it look exactly like the pictures and be sure to include the single ' quotes around the addDays expression. (The only difference should be the column names if you have a different name.)

You also need to be sure you are using the correct SharePoint column name within the filter query. Please look at this post about SharePoint Column InternalName and see how to determine what should be used within the filter query. Your column name might be Start_x0020_Date or maybe Start_Date depending on how you initially named it.

I really believe the example I showed with the three separate Get Items will be the easiest approach. It only requires that you get the Filter Query working. It uses very basic steps.

Please read more about the Get Items and Filter Query here.

[FLMike]

HI,

It's because you pasted them in as string, and not expressions.

Also its Get Days, not Get days for the name, the spelling matters when referencing another Action.

Also is this a String or a Choice field? 

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[FLMike]

@wskinnermctc 

I don't disagree that what you provided works. But if he adds more phases, he will have to keep repeating and adding more sections of the same loop, but with a different filter, versus only updating a single If expression.

[wskinnermctc]

@FLMike I'm trying to take into account that they don't know how to even write an expression; more less keep updating a complicated one.

If they can figure out how to use a Filter Query first, it can help with making better flows long term or any adjustments. It also simplifies any following steps since all they will have to do is select the choice option in the Update Item step.

There should not be an issue with having multiple Get Items if they are only getting the items that needs to be edited. What is less data usage long term? Getting 160 items and updating all of them everyday. Or 3 separate Get Items which may return none, or a couple of items to update?

I try to show users what I would do if it was my flow or at least very similar. I would not use an expression with nested if functions that have hard coded references. I don't believe that it is transparent or easily recognized.

Plenty of times you can find flows that are difficult to update or correct because so much of the action is happening in some frankenstein expression the last IT person cobbled together just to get the job done. You or I may be able to get in and better recognize issues, but newer users likely won't.

IT likes to set things up with the result of if it works, it works. I don't think that is helpful for guiding new users and long term success.

Either way, OP seems to be following your example. Hopefully they can figure out how to write the expression.

[wskinnermctc]

int(split(dateDifference(formatDateTime(utcNow(), 'MM-dd-yyyy'), formatDateTime(items()?['Your Date Column']), 'MM-dd-yyyy')),'.')[0])

This will error when the utcNow() date is the same as the StartDate because there will not be a period '.' in the output. 

[thomasw1964]

You are correct in the fact I am just not getting the expression writing, we will never have more than the 3 phases. But just trying examples of writing the first part of the expression fails for me

I have gone back and taken your corrections to my filtering and was missing the '' around the addTime function, I then setup the update item and it ran successfully , but did not put Phase1 in the Phase column

I apologize for the lack of knowledge , I've been working with Sharepoint,PowerApps , and Powerautomate for about a month now.. So far I've done OK I think ..Get stuck and eventually get stuff working

I changed the filter to look for the ActiveClient eq 'Active'

I am assuming my issue now is I do not know how to pass Phase1 back to the Phase column

Thank you for the help so far though I now understand how to write the filters

[thomasw1964]

ClientStatus is filtering, and that would be a good thing as we will only ever have 160 max , we could have a lot more client records for this to look at by going by the phases as we have a pretty good turn over rate on clients not making it through the first 30 days