Recurrence Pattern - how to process records generation based on different parameters/units?

Like (0) Share

Report

Posted on 15 Oct 2022 21:48:10 by EBMRay

1,710

Hello,

I would like to build a recurrence functionality that is very similar to the Agreement Booking Setup -> Booking Repeat functionality, which is the following:

The purpose of this feature is to avoid manual intervention if I have a task that repeats frequently. Here is an example:

Example: I want to add 3000$ bonus for 10 employees for 3 months which means each month an employee will earn 1000$.

Instead of going for each employee and adding 3x records per employee manually, the manager can select the employees and schedule the Bonus installment for each individual.

The recurrence will not just be monthly as we need to have the Weekly,Quarterly, Annually units.

What do you suggest as a solution to achieve the above requirements and could you please clarify how the records will be processed? Which product shall be used for the development process?

Any help is greatly appreciated.

Best regards,
Julien

Categories:

Microsoft Dataverse

I have the same question (0)

Summarized by AI

All responses (8)

Answers (1)

Verified answer

EricRegnier 8,716 Most Valuable Professional on 15 Oct 2022 at 23:09:24

Like (1)

Report
Re: Recurrence Pattern - how to process records generation based on different parameters/units?

Hi @Julien2,

As for which product used, I assume you mean Dynamics 365 apps? If so I'm not sure there's anything that comes out-of-the-box to achieve this... But this requirement can be done with a mix of Dataverse and Power Automate. At a high-level I would have something like:

A configuration table called something like "Record Creation Configuration" to configure what type of record(s) to create. It would a have text column for table/entity name. It would also have a many-to-many relationship with the Employee table to determine where to associate the newly created records to. A challenge here is if it's the manager who configures this, he/she would have to know the physical table name which might not be user-friendly, perhaps check the PCF galley to see if there's a nice control to pick a table?

"Reoccurrence" table similar to your screenshot just to configure Reoccurrence schedule. It would also have a "Last Run Date" column/field and a lookup to "Record Creation Configuration" table to know what to create.

Key Value Pair type of table (named something like Default Values Configuration) with columns "Column Name" to determine what column to set the desired value (ie new_BonusAmount) and Default Value (ie 1000). This would be linked (1-Many) with "Record Creation Configuration". This is where you would configure the default values like the 1000 bonus upon creation. Check as well check the PCF galley to see if there a nice control to pick existing columns from a table (ex https://pcf.gallery/entity-field-optionset).

The magic would happen in a cloud flow which would run every day and look at the active "Reoccurrence" records, compare the setup with the last run date. If due it would then look at the related configs to create the desired records. At the end it would update the "Last Run Date". If the "End Date" has past then the flow would mark the "Reoccurrence" record as inactive.

Hope this helps a little!

Was this reply helpful? Yes No
EBMRay 1,710 on 16 Oct 2022 at 11:40:42

Like (0)

Report
Re: Recurrence Pattern - how to process records generation based on different parameters/units?

Hello ,

I took the recurrence pattern screenshot from the field service module and yes it is not included as OOB if you want to implement it for your custom model-driven app as we need to build it from scratch.

For the first option, you're right because you might have recurrence across different tables and it will be a waste of time to re-build the same logic across different tables. How Power Automate will generate the records for different tables dynamically? (Example: It can be employee table for Bonus, another one a Task for an employee which is the task table) Shall the logical table name pass through the flow dynamically or for each table we have to create a workflow?

You mean the last run date will help us to know when the workflow triggered right?

Yes, the third table will be linked to the first one (Records generation), however, do you think it is better to store them as records instead of columns which can be a lookup and selecting the value for it (ex: Bonus, Loan, Deduction, etc..)

The part to set the "Reoccurrence record" to inactive is clear to me, but I want your suggestion on the following if we have such a scenario:
If I want to give a Bonus to an employee every Sunday and Tuesday with no end date, how would we handle it?

As far as I know, in the flow we can check the dayofweek, etc., but what about records without an end date?

2. Another one is the End date behavior can also be set after a certain number of occurrences.

The same as the above example but for X number of occurrences only. (It might be 4,3 or whatever..)

In your opinion, do you think that one cloud flow will be huge to handle all of the logic? Shall we have different child flows and each will be checking on a different repeat unit + end date behavior scenarios?

If so can you suggest how we may structure it in a more effective way?

Appreciate your assistance and I will be awaiting your feedback.

Best regards,
Julien

@EricRegnier

Was this reply helpful? Yes No
EBMRay 1,710 on 19 Oct 2022 at 19:35:32

Like (0)

Report

Re: Recurrence Pattern - how to process records generation based on different parameters/units?

Hello @EricRegnier ,

I would highly appreciate it if you had the chance to check my reply.

Best regards,
Julien

Was this reply helpful? Yes No
EricRegnier 8,716 Most Valuable Professional on 20 Oct 2022 at 07:33:07

Like (1)

Report
Re: Recurrence Pattern - how to process records generation based on different parameters/units?

Hi @Julien2, apologies for my late reply. Here are answers to your questions following your numbering:

Don't think you need a flow for each table, a single flow that will have an action retrieve the "Record Creation Configuration" config and from the results get the name of the table which can then be used as a dynamic value in a subsequent step to create the new rows.

Correct, it's used to determine if it should create new rows again when the flow triggers. Because, the flow will trigger daily but it doesn't mean it needs to create new rows daily, that depends on the conditions for creation.

Yes, you can store as columns but then everytime there's a new type of auto-creation and/or when the users want to populate a new field, you'll need to add that new column and redeploy everytime. Which is why I prefer as records to make it more "flexible".

With no end date, that "Record Creation Configuration" config will never be set to inactive. If it doesn't have an end date but has a number of occurance, then you'll need a new column named something like "Number of Executions" that the flow would increment each time it runs and satisfies the conditions for creation.

It can be large for one flow, so I would split it up in child flows where it makes logical sense

Hope this helps!

Was this reply helpful? Yes No
EBMRay 1,710 on 20 Oct 2022 at 14:53:06

Like (0)

Report

Re: Recurrence Pattern - how to process records generation based on different parameters/units?

Hello @EricRegnier ,

Please accept my sincere thanks for your detailed responses.

To summarize, I just created a very simple excel sheet and added a sample row for the "Record Creation Configuration table" as follows:

I think we were missing the column "Next refresh date" (highlighted in bold in the above screenshot) on the record creation where on daily basis at 6:00 AM the flow is checking if the record should be generated and comparing it to today's date. If they are equal -> generate the record -> update the next refresh date based on the number of days specified (in the example they are 3).

I believe the next refresh date for any unit will achieve the requirement.

Another complex example is an occurrence that occurs on certain days repeat every monday and friday so on monday if the record was generated it will add to today's date 4 days (Monday until Friday) and that will be updated in the "Next refresh date" column and then when it reaches "Friday" it will add 3 days for the next "Refresh date". (Friday until Monday)

The challenge is how are we going to split it into child flows in a logical sense.

Do you agree with the above? I'm doing some brainstorming before I start the real-testing.

I would highly appreciate your input.

Best regards,
Julien

Was this reply helpful? Yes No
EricRegnier 8,716 Most Valuable Professional on 20 Oct 2022 at 20:22:11

Like (1)

Report

Re: Recurrence Pattern - how to process records generation based on different parameters/units?

I think next refresh data is optional, it all depends how you want the logic. It can run daily (or several times) a day and then determine if the conditions to create are valid based on the last run date. Or you check the condition against next refresh date but note you still need to check that the last run date >= next refresh date to ensure its doesnt fire more than once between intervals...

Was this reply helpful? Yes No
EricRegnier 8,716 Most Valuable Professional on 22 Oct 2022 at 04:28:06

Like (0)

Report

Re: Recurrence Pattern - how to process records generation based on different parameters/units?

HI @Julien2, one other thing I wanted to recommended is to just go ahead and give it a try. Inspect, adapt and improve afterwards. Sometimes we tend to try build a perfect solution, and think of all the possible scenarios when sometimes can be overkill. The key is to keep it simple with future proofing in mind without necessary catering right away for all scenarios:)

Was this reply helpful? Yes No
EBMRay 1,710 on 24 Oct 2022 at 18:51:35

Like (0)

Report

Re: Recurrence Pattern - how to process records generation based on different parameters/units?

Hello @EricRegnier ,

Appreciate all your answers.

You are definitely right I will give it a try and start development. I will get back to you with an update when I generate the first output result.

Best regards,

Julien

Was this reply helpful? Yes No