cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Billy_C
Helper V
Helper V

How to calculate whether time is between 2 time values

Hi there,

 

I have created a new Entity called Reminders and I would like to know if it is possible to determine whether the time of a field is between 2 other time values.

 

For example send a reminder every hour in between the office hours so I want to know if I can check whether the current time is equal to or larger then the start time but equal to or smaller then the end time. This is so that users won't receive reminders in the middle of the night.

 

Is it possible to achieve this with a calculated field, cause I don't want to execute flows too often, especially since there is a limit to the amount of API calls.

 

Thanks in Advance,

 

Billy Cottrell

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Billy_C,

 

Unfortunately calculated fields don't work like that. The calculation only occurs when retrieving the record. Changes to calculated field values do not trigger workflows or plugins.

 

The only reliable way is to use a scheduled flow and retrieve the records that require follow up.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

View solution in original post

5 REPLIES 5
ChrisPiasecki
Super User
Super User

Hi @Billy_C 

A calculated field can determine this, but they cannot trigger events or workflows as they don't actually store any values, they are just evaluated at runtime such as when retrieving the record. 

 

A scheduled flow is probably your best bet, and in your List Rows action you can add an OData filter query where the current time is ge (greater than or equal) to start time and le (less than or equal) to the end time. This will ensure you only get the records needed to send a reminder for. If you have no reminders then your API calls used will be minimal.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Billy_C
Helper V
Helper V

Hi @ChrisPiasecki,

 

I know that they can't trigger flows except when they are filled in for the first time (on create), but if it is possible to set a calculated boolean to null then it is possible to trigger flows as they go from "no value" to a "value" and this does trigger a flow I think.

Even if this doesn't trigger the flow it is still possible to do this indirectly by using a second field and a business rule that checks whether the value of the second field is equal to the calculated field and thus updating that value if necessary and also triggering the flow.

 

The only missing piece was if it was possible to determine whether a reminder could be sent based on the time (not the date). If this wouldn't be possible I would have to use a different method with flow so it would be possible to send reminders.

 

Thanks in Advance,

 

Billy Cottrell

 

Hi @Billy_C,

 

You can use a boolean to toggle the state and be your trigger yes, but this would require you to interact with the record directly, whether that is creating the record, editing, etc. If you want this to happen automatically for all your records without interaction then you'll need a flow.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Billy_C
Helper V
Helper V

Hi @ChrisPiasecki,

 

Well normally the user creates a reminder and when certain conditions are met a reminder will be sent. So my calculated field would change value when a reminder should be send and when the time occurs it should update the calculated field, execute the business rule and set a regular field to the same value as the calculated field. When that calculated field is updated it would trigger a flow send a reminder and then update that record again to keep track as to when the next reminder needs to be send.

 

Or won't it function like that?

 

Thanks in Advance,

 

Billy Cottrell

Hi @Billy_C,

 

Unfortunately calculated fields don't work like that. The calculation only occurs when retrieving the record. Changes to calculated field values do not trigger workflows or plugins.

 

The only reliable way is to use a scheduled flow and retrieve the records that require follow up.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,438)