SharePoint Lists + MS Flow to manage license renewals

[ganesh131982]

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.

[ganesh131982]

Here's my flow screenshot.

[ganesh131982]

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.

[vivekpande18 · Accepted solution]

Hi @ganesh131982 ,

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

 Filter Query : License_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.

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]

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]

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?

[vivekpande18]

Hi @ganesh131982 ,

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

[vivekpande18 · Accepted solution]

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]

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"

[vivekpande18]

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]

Thanks again @vivekpande18 ,

But it doesn't seem to work now. 

[vivekpande18]

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]

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.

[vivekpande18]

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.

Thanks

[ganesh131982]

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]

Hi @ganesh131982 ,

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

Thanks

[ganesh131982]

Thank you @vivekpande18 , I appreciate your help.

[ganesh131982]

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.

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

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

Here's the formula I entered into the expression.

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]

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]

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