Expiration date workflow reminder based on different units

(0) Share

Report

Posted on by EBMRay

903

Hello everyone,

I have created a custom entity that contains (Document Type, Document Name, and frequency reminder Choice (Yearly, Weekly, Quarterly, Monthly).

What I want to achieve is the below:

Scenario:
Let's say I created a new record, I set the frequency reminder to Quarterly and the record status is active. After 3 months, the owner of this record should get a reminder about this record through email and if the user did not change the status of this record to Inactive he will always receive a notification every 3 months.

I thought about the below and you might have a better way to achieve this requirement:

Create a new read-only field on the form called "Next reminder Date" that will automatically calculate Today's Date + the Frequency option set value selected (Quarterly, monthly, etc..).
Create a Power Automate cloud flow that triggers on daily basis and check in this table the next reminder date field. If there is/are any record that matches today's date and active record(s)-> send the list of records to each user.
During the process, if the List records action returned data as result (meaning some records require reminder)-> the flow should check the frequency provided for the record and automatically the flow should update the new "Next reminder date".
What I mean by this condition is:
Suppose the reminder frequency for one of the records retrieved is quarterly this means that the flow should do this calculation below:
Example: The next reminder date is: 3/21/2022 and today is 3/21/2022 and the reminder frequency for that record is Quarterly which means the flow should do this calculation Today+3months = next reminder date (6/21/2022) etc.. so the flow should always calculate the next reminder date based on the frequency set for the record.

Do you think Azure Logic Apps are much better than power automate to build this scheduled workflow?
I would like to have your advice if the above logic and Power Automate are good to proceed with based on the above requirements.

Please let me know if you have any other methods. I am open to any suggestions.

Any help is highly appreciated.

Best regards,
EBMRay

I have the same question (0)

All responses (6)

Answers (2)

Suggested answer

Joergen 455 on at

Like (1)

Report
RE: Expiration date workflow reminder based on different units

Hi EBMRay,

I think there are different solutions but maybe the following could be suitable for you.

Why just send an email with a list of records - instead you could use e.g. Power Automate or Logic Apps to create a task for the owner if the due date has been reached.

In addition, I would create a choice field with the frequency option in days

Use a rollup field "Last reminder date" in Dataverse on the related tasks with the latest date.

Use a calculated field "Next reminder date" in dataverse to add the frequency to the "Last reminder date"

Power Automate/Logic App is checking on a daily basis if records in the table exist where "Next reminder date" equals today and then creates a task. based on the new created task, the next date will be calculated.

If you want to enhance this a bit, you can check e.g. a week in advance for the next date and cfreate a task with due date in 7 days. And the calculation of the "Next reminder date" also refers to the status of the task and only calculates a new date, if task is set to completed.

For the decision if you should use a Power Automate or Logic App - maybe you should have a look at the current changes added to Power Automate, when running as a service account:

docs.microsoft.com/.../change-cloud-flow-owner

"You can change the owner to an individual (not a distribution list) or a service account. If the flow uses a service account, it's considered as a department/organization flow and will need a per flow license." (last change to the article on 17.03.2022)

If this was helpful for you, please mark as verified.

Best regards,

Joergen

Was this reply helpful? Yes No
EBMRay 903 on at

Like (0)

Report
RE: Expiration date workflow reminder based on different units

Hello Joergen ,

Thank you for your reply and for providing the below key points.
Please find below my comments regarding each point:

Why just send an email with a list of records - instead you could use e.g. Power Automate or Logic Apps to create a task for the owner if the due date has been reached.

Very nice idea so first the task will be created based on the fifth step logic that you mentioned and we should have a different Power automate cloud flow to create a task for every owner if the due date has been reached.

In addition, I would create a choice field with the frequency option in days

If I created a choice field of frequency in days do you mean something like this (7 days, 2 months, 3 months, 1 year, 3 years..) or the unit is always in Days? If so, there will be a lot of values in this option set.

Use a rollup field "Last reminder date" in Dataverse on the related tasks with the latest date.

Do you mean the rollup field "Last reminder date" should be in the custom table and not in the task? What will be relationship between this rollup and the related tasks of this record? To be honest, I have never used rollup fields in dataverse to understand how it works.

Use a calculated field "Next reminder date" in dataverse to add the frequency to the "Last reminder date"

Could you please provide a simple example based on this point to understand how it should work the calculated field exactly?

Power Automate/Logic App is checking on a daily basis if records in the table exist where "Next reminder date" equals today and then creates a task. based on the new created task, the next date will be calculated.

This point is clear on the flow logic to check if "Next reminder date" eq today -> create a task, but could you please provide an example how the next date will be calculated?
It should be handled in the flow logic or it will be based on the calculated/rollup fields that we mentioned previously?

If you want to enhance this a bit, you can check e.g. a week in advance for the next date and cfreate a task with due date in 7 days. And the calculation of the "Next reminder date" also refers to the status of the task and only calculates a new date, if task is set to completed.

Could you please just provide a sample example so I can understand how it should work?

For the decision if you should use a Power Automate or Logic App - maybe you should have a look at the current changes added to Power Automate, when running as a service account:

I noticed these changes to Power automate, does that mean If I have an unmanaged solution on a development environment on a different tenant that has this workflow and I am the owner and I want to migrate this flow to a new tenant which will be a different owner/user does this user need a power automate per-flow license??

THank you again for your assistance. Please let me know if you need any further information.

Looking forward to your response.

Best regards,
EBMRay

Was this reply helpful? Yes No
Verified answer

Joergen 455 on at

Like (2)

Report
RE: Expiration date workflow reminder based on different units

Hi EBMRay ,

1. I created a table called e.g. documents with a custom 1:N relationship to tasks

2. New column of type date and rollup called Last reminder date:
In the configuration I have already included the optional filter, that the task needs to be completed - therefore I use the Max End Date

3. Frequency field - based on your Input I decided to use a whole number field instead of choice, so the user can specify the amount of days directly

4. Calculated field - new column of type date. Using the filter to validate, that there is already a last reminder date, and a frequency set not equal to 0

Based on the above steps I have already received following result:

- Defined a reminder frequency of e.g. 90 day(s) (or later for the Power Automate test 1 day, so I can use the calculated date)

- Based on the task set to completed today --> can be seen in the rollup last reminder fate

- Next reminder is calculated for 21. of June 2022

Next steps:

Scheduled Power Automate staring each day at 6:00 am

Use a FetchXML query in step 2 to list the records e.g. due tomorrow

Get the information for all document records found in step 2 (Apply for each will be created automatically)
Inside Apply for each also create a new task for each document with e.g. due date today using expression to add days to the date
To use today's date inside a power automate you can use an Expression:

]
formatDateTime(utcNow(), 'yyyy-MM-dd')

Instead of using today's date you can also use Expression addDays to calculate future days, e.g. with 1 you will use tomorrows date

formatDateTime(addDays(utcNow(),1), 'yyyy-MM-dd')

Based on these steps you can implement your solution. If you do not want to run a flow, you can use the same example to create logic app.

Results:

If this was helpful for you, please mark as verified.

Best regards,

Joergen

Was this reply helpful? Yes No
EBMRay 903 on at

Like (0)

Report

RE: Expiration date workflow reminder based on different units

Hello Joergen ,

Firstly, I would like to thank you so much for providing the above example step by step and for your time.

Regarding the frequency field, I think it is much better to make it as a choice column (Monthly, Weekly, Yearly) for the users very straightforward and it will:

1. Avoid human mistakes by typing a number that is not in our guidelines ( We can set the max number to 365 but this won't help).

2.Also, in the reports, if the frequency field will be included it should show (Monthly,Weekly..) instead of 7, 30, or 90 days for example.

If we switch the type of frequency does it affect the rollup calculation? If so, maybe I can keep the frequency hidden and set the number automatically based on the chosen value that will be selected from another frequency field.

Example: If I choose Monthly -> Automatically the hidden frequency field will set the number to 90 in that case it will not affect the calculation.

I will implement it using Power Automate and follow the same steps you mentioned.

Please let me know your comments based on the above frequency field and what should be changed I want to switch it to choice.

Awaiting your response.

Best regards,

EBMRay

Was this reply helpful? Yes No
Verified answer

Joergen 455 on at

Like (1)

Report

RE: Expiration date workflow reminder based on different units

Hi EBMRay ,

you are welcome. Instead of changing the type of the frequency field you can create e.g. a choice column "frequency selector" and set the frequency field to read-only on the form, if you want to display the number of days or hide the field, if you only want to show the choice values.

Instead of using a cloud flow, I would use a business rule in this case, that sets the value of the frequency column based on the selected choice value:

If these answers have been helpful for you, please mark as verified.

Best regards,

Joergen

Was this reply helpful? Yes No
SeLa79 5 on at

Like (0)

Report

RE: Expiration date workflow reminder based on different units

Many thanks Joergen for this detailed instruction. I have not worked with calculated fields or rollup fields before and your answer provided me an idea how to achieve a solution for my topic.

Many thanks , Sebastian

Was this reply helpful? Yes No