cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ganesh131982
Helper II
Helper II

SharePoint Lists + MS Flow to manage license renewals

Hello,

 

I have experience in MS SharePoint and building lists but I'm fairly new to MS PowerAutomate and I'm seeking help from the community to help me build a solution. 

 

I'm trying to build a solution to hold and manage the renewals of site licenses. Each license has a different renewal date. 

 

Firstly, I have created a list with following columns.

 

1. License Name

2. License Holder

3. License Review Date

4. Superseded [Yes,No]

 

Requirement 1: I would like the PowerAutomate workflow to periodically [maybe daily] review the list and query if the License Review Date is less than 30 days from today. If yes, I'd like to send an email to the License Holder reminding them to initiate the license renew process.

 

Requirement 2: If the superseded field is checked, I would like PowerAutomate to duplicate the content from the current list item and create a new one.

2 ACCEPTED SOLUTIONS

Accepted Solutions
vivekpande18
Solution Supplier
Solution Supplier

Hi @ganesh131982 ,

 

You can apply the filter directly in Filter Query as shown below.

vivekpande18_2-1652504901529.png

 

 Filter QueryLicense_x0020_Review_x0020_Date  lt '@{addDays(utcNow(),30,'yyyy-MM-dd')}'

Note: above-highlighted part should be your column's internal name.

 

Output: you will get only those details whose  License Review Date is less than 30 days from today.

 

vivekpande18_3-1652505095952.png

 

In condition you can check if licensee is Superseded or no.

 

Thanks,

Vivek

 

If this post helps answer your question, please click on  "Accept as Solution" to help other member find it more quickly. 

If you thought this post was helpful, please give it a Thumbs up.

 

View solution in original post

Hi @ganesh131982 ,

 

I'm running this workflow daily. So does this mean the query will continue to pick up dates that are less than 30 days and keep sending emails till the difference is zero?

-No it will keep sending the email until the license is renewed

 

How can I configure the query if I wanted to rule to only send emails when the difference between today's date and the license renewal date is equal to 30 days rather than less than 30 days?

-you can change the comparator in the expression in Filter Query from "gt" to "eq"

 

Thanks

Vivek

 

If this post helps answer your question, please click on  "Accept as Solution" to help other member find it more quickly. 

If you thought this post was helpful, please give it a Thumbs up.

View solution in original post

20 REPLIES 20
ganesh131982
Helper II
Helper II

Here's my flow screenshot.

 

ganesh131982_0-1652485977163.png

ganesh131982_1-1652486033420.png

ganesh131982_3-1652486124172.png

 

ganesh131982
Helper II
Helper II

I tried a slightly different option as well.

 

I created a calculated field called Date Difference in SharePoint list that calculates the difference between the license renewal date and today's date.

 

In flow, I introduced a condition that says, if Date Difference is equal to 30 then send an email. This doesn't seem to work either.

 

ganesh131982_0-1652490380002.png

 

vivekpande18
Solution Supplier
Solution Supplier

Hi @ganesh131982 ,

 

You can apply the filter directly in Filter Query as shown below.

vivekpande18_2-1652504901529.png

 

 Filter QueryLicense_x0020_Review_x0020_Date  lt '@{addDays(utcNow(),30,'yyyy-MM-dd')}'

Note: above-highlighted part should be your column's internal name.

 

Output: you will get only those details whose  License Review Date is less than 30 days from today.

 

vivekpande18_3-1652505095952.png

 

In condition you can check if licensee is Superseded or no.

 

Thanks,

Vivek

 

If this post helps answer your question, please click on  "Accept as Solution" to help other member find it more quickly. 

If you thought this post was helpful, please give it a Thumbs up.

 

ganesh131982
Helper II
Helper II

Thanks @vivekpande18 ,

 

I appreciate your prompt response. The above filter query worked perfectly!

 

As for the superseded option, its a secondary workflow I guess, that only kicks in if the superseded option is ticket. If it is ticket, I'd like flow to create a new list item by copying the existing one. Is this possible?

ganesh131982
Helper II
Helper II

Also @vivekpande18 , I'm running this workflow daily. So does this mean the query will continue to pick up dates that are less than 30 days, and keep sending emails till the difference is zero?

 

How can I configure the query if I wanted to rule to only send emails when the difference between today's date and the license renewal date is equal to 30 days rather than less than 30 days?

Hi @ganesh131982 ,

 

This is possible, You can make use of Create Item action present.

Hi @ganesh131982 ,

 

I'm running this workflow daily. So does this mean the query will continue to pick up dates that are less than 30 days and keep sending emails till the difference is zero?

-No it will keep sending the email until the license is renewed

 

How can I configure the query if I wanted to rule to only send emails when the difference between today's date and the license renewal date is equal to 30 days rather than less than 30 days?

-you can change the comparator in the expression in Filter Query from "gt" to "eq"

 

Thanks

Vivek

 

If this post helps answer your question, please click on  "Accept as Solution" to help other member find it more quickly. 

If you thought this post was helpful, please give it a Thumbs up.

ganesh131982
Helper II
Helper II

Thank you @vivekpande18 , 🙂

 

-you can change the comparator in the expression in Filter Query from "gt" to "eq"; 

 

Did you mean "lt" to "eq". As in your original expression you had "lt"

Hi @ganesh131982 ,

 

Yes,

lt----> eq

 

Mark  the other  post also  as "Accept as Solution", which contain the expression else others might confuse.

 

Thanks,

Vivek

ganesh131982
Helper II
Helper II

Thanks again @vivekpande18 ,

 

But it doesn't seem to work now. 

ganesh131982_1-1652509343092.png

 

 

 

Hi @ganesh131982 ,

 

It should work and its working fine at my end.

 

Do you have any license, whose renewal date 13-06-2022 ?

 

Thanks,

Vivek

ganesh131982
Helper II
Helper II

Thats strange @vivekpande18 , please see my list content below.

 

Essentially, what I'm trying to do is send a reminder email if the license renewal date is 30 days away from today's date.

 

ganesh131982_0-1652509841867.png

 

Hi @ganesh131982 ,

 

The license renewal date should be  13-06-2022, In order to get the details.

 

Your calculated date field is wrongly calculating the days.

 

vivekpande18_0-1652510189523.png

 

 

Thanks

ganesh131982
Helper II
Helper II

Interesting @vivekpande18 , I'll re-check my calculated field formula.

 

But the License Renewal Date field is manually maintained. So its fixed. So I dont understand, why when put 'eq' on the expression is doesn't send the email. Is the logic wrong somewhere?

vivekpande18
Solution Supplier
Solution Supplier

Hi @ganesh131982 ,

 

If you change the renewal date value to 13-05-2022 it will send you an email.

 

Thanks

Thank you @vivekpande18 , I appreciate your help.

ganesh131982
Helper II
Helper II

Hi @vivekpande18,

 

I'm afraid the logic in the expression isn't working as per my requirement.

 

I only want it to send an email for items whose expiry date is 30 days from today. Not less than, just 30 days from today. Based on the expression that I use, while it sends me an email when the difference between today's date and license expiry date is 30 days, it also sends me an email when the expiry date is 29 days from today. Please see example below.

 

ganesh131982_0-1653000473904.png

The difference between today (20/05/22) and the license renewal date (18/06/22) is 29 days.

 

ganesh131982_1-1653000559135.png

and I get an email saying it is up for renewal.

 

ganesh131982_2-1653000657913.png

Here's the formula I entered into the expression.

ganesh131982_3-1653000712392.png

Seems like it is calculating less than or equal "le" even though I entered "eq".

 

Can you please help me understand what I'm doing wrong here?

 

Thanks.

ganesh131982
Helper II
Helper II

Could time difference be the issue here?

 

I'm in Australian Eastern Standard Time (AEST) UTC+10, whereas the expression is calculating is UTCnow time. My workflow runs each day at 0600 AEST. So, when it is calculating the difference, it is still the previous day in UTC whereas it is the next day in AEST.

 

ganesh131982_0-1653002162627.png

 

ganesh131982
Helper II
Helper II

Update - I manually ran the workflow after UTC time rolled over to 0000 and the workflow ran properly and picked up the right conditions. I have now scheduled my workflow to run at 1100 AEST instead of 0600 AEST.

 

You learn new things every day. 🙂

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.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,656)