How Can I Trigger Workflow Every day (With MS Flow or other solution)

(0) Share

Report

Posted on by onurizgi

360

Hi Community,

I need to calculate the Created Date and Completed Date difference and automatically update it in days to the Date Difference field I just created. For example, consider a contract. If I write a custom workflow that calculates the difference of Stard Date and End Date every day and updates the Remaining Day of all active records, how can I run it automatically every day? Does anyone have any other suggestions for this process? I tried MS Flow but I'm new to this field. Your suggestions and directions are important to me.

Thanks in advance.

All responses (4)

Answers (1)

Verified answer

- Matt Bayes - 890 on at

Like (0)

Report

RE: How Can I Trigger Workflow Every day (With MS Flow or other solution)

Hey Onur,

It looks like you're really close and the only thing remaining is getting the proper field values from the CRM.

I believe the issue with the function is the "iterate_though_Accounts" is pulled from the name of the iteration block in the flow.

Based on your screenshot, I believe the proper format for the function should be:
div(sub(ticks(formatDateTime(utcNow(),'yyy-MM-dd')),ticks(formatDateTime(items('Her_birine_uygula')?['dnc_lastdate']))),864000000000)

The bolded area in the function above contains the change, and is reflective of the name of the iteration block that you have in your screenshot.
I believe the error in the flow (without translating to English) probably states that the "iterate_though_Accounts" step could not be found or was invalid, so hopefully this change will fix it for you.

Keep me posted and I'm sure we can get it to work in time!

Thanks!

Matt Bayes
onurizgi 360 on at

Like (0)

Report

RE: How Can I Trigger Workflow Every day (With MS Flow or other solution)

I've made some progress. Calculation was made when I wrote the date directly without pulling the field value from CRM. But I still get an error when I pull the data from CRM and run it. I think I'm pulling the data wrong.

Wrong function = div(sub(ticks(formatDateTime(utcNow(),'yyy-MM-dd')),ticks(formatDateTime(items('Iterate_through_Accounts')?['dnc_lastdate']))),864000000000)

Success function = div(sub(ticks(formatDateTime(utcNow(),'yyy-MM-dd')),ticks('2022-28-04')),864000000000)
onurizgi 360 on at

Like (0)

Report

RE: How Can I Trigger Workflow Every day (With MS Flow or other solution)

Hi Matt,

Thank you very much for your detailed explanations, it was very helpful. I want to do this operation on Account entity. I created 2 custom fields, Last Date [dnc_lastdate](type of only date) and Date Diff[dnc_datediff] (type of string). I get an error when I try the formula you created. I changed and updated the entity names, still didn't work. Could you please check the formula?

Formula : div(sub(ticks(items('Iterate_through_Accounts')?['dnc_lastdate']), ticks(items('Iterate_through_Accounts')?['createdon'])), 864000000000)

Flow :

What I want to do is extract the Last Date field from today's date and set the data to the date diff field on a day-by-day basis.

Sorry for error language :)

Thanks in advance.

Regards.
Suggested answer

- Matt Bayes - 890 on at

Like (0)

Report
RE: How Can I Trigger Workflow Every day (With MS Flow or other solution)

Hey Onur,

You may be able to get by with a calculated field for your Date Difference and use the DATEDIFF function to find the difference between the date fields. This is how we have build any formulas to calculate the difference between two dates, however, the caveat is that being a calculated field a user would have to open up the record in order to have up to date data (which may not be sufficient in your case).

If you wanted to run something daily to calculate the difference, you would have to use either a Custom Workflow (Development), Azure Function (Development), or Power Automate/Flow (Configuration).
Depending on your skill set that may swing your decision, but here is some information for each of them:

Custom Workflow

The only way in the CRM system to run a scheduled job (without any external tools) is using a combination of a placeholder table and Bulk Delete Jobs. Bulk Delete Jobs are the only way for us to schedule processes on a specific date, time, and recurrence. You would create a table (say Scheduled Job) and create a single record in this table. You would then have to write a custom workflow to calculate the date difference, which you would then call within a regular workflow. The trigger for this workflow would be on DELETE of the Scheduled Job record. You would then add a line in the workflow to create a new Scheduled Job record so the process can run again. Once the workflows were created, you would then create a Bulk Delete Job that would delete the Scheduled Job records (based on whatever criteria) on the specific date, time, and recurrence you want.

This method is a little old school before we had things like Azure and Power Automate, but can still work using a CRM-only approach.

Azure Function

Creating a timer Azure Function allows you to set a specific recurrence for the process to run, and you can write all of the code within the Azure Function to calculate the date difference. The benefit of an Azure Function is that the process would be decoupled from CRM and run within Azure, thus not taking any resources from the CRM system. The downside is that it requires development and a lot of initial setup (and potential cost) to run this.

Power Automate

As you stated, Power Automate (AKA MS Flow) should be used for this sort of process, as it allows us to configure the process via a point and click builder and still set the recurrence for the process. Similar to Azure, the process would run outside of CRM thus not affecting any of the resources for the system.

Here is a screenshot for the Power Automate:

In this example, the formula to calculate the Date Difference would be:
div(sub(ticks(items('Iterate_through_Contacts')?['msdynce_expectedcompletiondate']), ticks(items('Iterate_through_Contacts')?['createdon'])), 864000000000)

This was found from the following blog: Date Difference in Power Automate

If you have any questions please let me know!

Thanks!

Matt Bayes