cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thomasw1964
Helper III
Helper III

Creating Recurrence flow for updating column based on number of days

 

  • 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

thomasw1964_1-1695755953697.png

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
wskinnermctc
Super User
Super User

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 Phase1Get 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 Phase2Get 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'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.

View solution in original post

35 REPLIES 35
wskinnermctc
Super User
Super User

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
Multi Super User
Multi Super User

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
Super User
Super User

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 Phase1Get 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 Phase2Get 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'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
Helper III
Helper III

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

 

Kinda burned out for the day today

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

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

thomasw1964_0-1695811911675.png

 

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))

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

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
Helper III
Helper III

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

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
Helper III
Helper III

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.

PA1.JPGPA2.JPGPA3.JPGPA4.JPGPA5.JPG

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

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

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

 

 

 

 

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

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. 

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'

thomasw1964_0-1695827459506.png

 

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

 

thomasw1964_1-1695827525211.png

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

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

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!    

Welcome to the Power Automate Community

You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun.   Now that you are a member, you can enjoy the following resources:   Welcome to the Community   News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate.  Be sure to subscribe to this board and not miss an announcement.   Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows.  You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook.  Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today.   Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome.   Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback  board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together.     Power Automate Community Team

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Users online (3,608)