Smart date ranges demo

[Jim McKee]

This is a demo for Mary Silvanos who posted a question on FileMaker Cafe about how to grab a price, defined by a date range and a service code, from a Prices file when entering a date and service code in a Billing file.

This demo uses smart date ranges to match the service code/date combination entered into a record in the Billing file with a date range key (all key values prefixed with the service code) in a record in Prices. The relationship also works in reverse, displaying all records in the Billing file whose service code/date key match a given service code/date range key in Prices. Also, when entering service code/price records in Prices, a self relationship of the service code_date range calculation will flag overlapping date ranges for a given service code.

I am in the very beginning stages of learning how to implement smart ranges for a solution I'm building that will have a feature to schedule multiple construction crews, and which will flag scheduling conflicts. My solution uses dates & times, whereas this demo solution uses only dates.

The use of prefixes and/or suffixes in the range key values enables virtually unlimited options for matching different entities (e.g., my multi-crew scheduling solution). So a crew "A" scheduling event will only match to other date/time ranges for crew "A" where there is an overlap in date/time.

This process could probably be handled by scripted finds, but using smart ranges is much faster and doesn't require any scripts. The only limitation is that the number of characters in the range key field is limited to 64,000. If only matching a date with a date range or a date/hour/minutes value with a date/hour/minutes range, these would both be well within the 64,000 character limit for the range parameters used in most solutions.

This article by Mikhail Edoshin has been referenced many times in postings here and on other FileMaker sites, but for people who haven't heard of it, it's a definite must-read.

Smart_Ranges_demo.zip