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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (77,380)