What you'll want is the Case function. This will allow you to set up a series of conditions, along with their outcomes if true. For example:
Get ( CurrentTime ) - local_appts::appointment time > 60 ; ">60" ;
Get ( CurrentTime ) - local_appts::appointment time > 30 ; ">30" ;
Get ( CurrentTime ) - local_appts::appointment time > 0 ; ">0" ;
This tells FileMaker to choose the first true condition. (That's why they're in the order they're in.)
For better (faster) execution, use the Let function to call the Get ( CurrentTime ) function and evaluate the related field only once:
Let ( [
rightNow = Get ( CurrentTime ) ;
apptTime = local_appts::appointment time
rightNow - apptTime > 60 ; ">60" ;
rightNow - apptTime > 30 ; ">30" ;
rightNow - apptTime > 0 ; ">0" ;
Edit: For Conditional Formatting purposes, you can put the same conditions in. Just remember that Conditional Formatting tests are executed in the order they appear, and all true tests are applied. So you'll want to put the tests in the reverse order of the Case statement above. That way, the >60 test will evaluate last and whatever formatting you apply through it will be sure to appear.
Duh. Missed the "minutes" part. Pixi is right; time calculations work in seconds, so you have to multiply your minutes by 60 to get the correct answer.
thanks to both of you for this rapid and awesome answer. much appreciated.