Smart Ranges
2nd edition
 2nd edition |
1st edition |
Smart Ranges
|
Developer Tools | www.onegasoft.com
|
This article explains the inner mechanics of an advanced FileMaker Pro technique, Smart Ranges. Though FileMaker can find dates or numbers that falls into a range, it isn't easy to create a relationship that does the same. Smart Ranges solve the problem: it's the cheapest and most universal method of linking ranges of dates or numbers. The technique is also rather complex and difficult to grasp from bare samples. This article has all the theory you might need.
This is the second edition; it was reworked to make it simpler and clearer. The large calculation listings were removed while explanations were extended and detailed. The way the calculation works with ends of a range was changed; the method of adding prefixes and suffixes was greatly simplified.
It's a long article, so if you'd like to have it printed get 
the PDF version with FileMaker samples (206K).
May be you can have the best of both worlds: get the power of "smart ranges" avoiding their complexity. Check the first plug-in that utilizes the "smart range" principle: DataFilter 1.0 by 
Datamatic Software.
You should know that FileMaker actually stores dates as sequential numbers (Jan 1st, 1 is number 1 and Dec 31st, 3000 is 1,095,727), be more or less familiar with FileMaker text and number functions and understand how to build a relationship from several values, separated by carriage returns (this is often named "a compound key").
A method of establishing relationship to a ranges of dates or numbers is a long term wish of FileMaker developers. In FileMaker you can link only to exact number or to exact date. What to do if you have only the start and end numbers and want a relationship to all numbers between?
An offhand solution is to add missing numbers. It's rather good and it works, but unless your ranges are tiny you will need a method to add all missing numbers. Some solutions use plug-ins to generate these numbers, some use a pre-built set to pick from.
Both the approaches are not quite good. First, they are limited: the size of the compound key cannot exceed 64 KB. This is the limit of text field in FileMaker. How many is 64 KB? It depends. If you work with date ranges and one date is a 6-digit-long number (the dates of the present are 6-digit) the maximum range will be about 9,000 days or 25 years. This span would be enough for many business application, even if you used complex compound key with prefixes or suffixes. But if you have to be more precise, say, up to an hour, the effective time span will rapidly shrink to just one year.
If you work with financial information you'll run out of numbers soon after $10,000 (a range from 1 to 9,999 takes about 48 KB). If your database contains historical or scientific information and you need large ranges these methods may be of no use at all.
This is the main inconvenience of this too straightforward idea. There are also some other drawbacks. It's difficult to tell how to link to "all dates before" or "all dates after". Using a plug-in often means "one more thing I can forget to install" to say nothing of the additional cost.
By the way, I know two "Ranges" plug-ins:
Ranges toolbox from Protolight software. It's free.
Ranges from Troi Automatizering and ISO Productions. The plug-in is $169.
Is there another method to specify a range? Yes, and more effective one.
What's the root of the problem? FileMaker links are "exact": a value in the main file gets only exact matches from the related file. On the other hand it can get any number of matching records with just a single link... What a pity all dates or numbers in a range are different and we need all of them in the main file! If they were same... perhaps, we can find what could make all these different numbers alike?
What is same in numbers, say, from 85 to 216? Hmmm. Of course, all of them are different; but some parts of the range do have a lot in common. For instance, all numbers from 100 to 199 start with "1" (or, in other words, they belong to the 1st hundred).
That sounds promising. Let's try to make compound keys for numbers from 0 to 999 that utilize the principle. Such a key should include the whole number, the first two digits and finally the first digit or, in other words, the number, the ten and the hundred it belongs to:
| 000 | 001 | ... | 009 | 010 | ... | 099 | 100 | ... | 999 |
| 00 | 00 | 00 | 01 | 09 | 10 | 99 | |||
| 0 | 0 | 0 | 0 | 0 | 1 | 9 |
You can see that there are 1000 three-digit keys from 000 to 999 and they are all different as the numbers are. At the same time there are only 100 two-digit keys from 00 to 99 and each of them gets 10 values so these 100 keys represents the same range of 1000 numbers! And this's just a beginning, because the same range can be got with just 10 one-digit keys from 0 to 9.
To get a range we need, say a range from 65 to 216 we should combine keys of different length. We can still use full three-digit keys to get single numbers. But when we see a range includes 10 numbers from the same ten (that is their first two digits are same) we will use one two-digit link to this ten and get rid of ten three-digit links. If we see the range contains a whole hundred, we will use one single-digit link to this hundred instead of one hundred links to all its numbers. The result will be that:
|
065 066 067 068 069 07 08 09 1 20 211 212 213 214 215 216 |
= |
065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 |
081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096 |
097 098 099 100 101 102 103 104 105 106 107 108 109 110 111 112 |
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 |
129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
161 162 163 164 165 166 168 168 169 170 171 172 173 174 175 176 |
177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 |
193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 |
209 210 211 212 213 214 215 216 |
That's quite good. A range of 152 numbers is specified with only 16 keys. The "1" key gets 100 numbers from 100 to 199.
This is the essence of the technique, Smart Ranges. We only need to know how to construct these keys in FileMaker. We are going to discuss this in other sections.
Note that though we are talking about numbers we mean dates just the same, because FileMaker really stores them as sequential numbers.
To make these ranges in FileMaker we should develop formulas of two kinds: one should turn single numbers into keys and another should make a range between two numbers. The first one is simple; the second looks more complex, but consists of small modules which are quite alike in fact. Keys' length. The basic length of the keys is the length of the maximum possible number you could have in the data you're going to link to using the technique. The exact value of the number doesn't matter: you only need to know how many digits does it have.
In the article we'll build calculations to work with numbers from 0 to 999, that's our keys will be three-digit-long. This will make the explanations simpler; as soon as you get the principle, you'll be able to make keys of any length and work with any numbers.
The compound key for single numbers that reveals the hidden information of their tens, hundreds and so on should look like a staircase:
015 01 0
The principle is clear: the first line is the number itself with some leading zeros so its length is equal to the basic length, 3. To make all the other lines we should remove one, two, three and so on digits from the right side of this basic number until only one digit is left. The FileMaker formula that does it is quite simple:
Left(Right("00" & Number, 3), 3) & "¶" &
Left(Right("00" & Number, 3), 2) & "¶" &
Left(Right("00" & Number, 3), 1)
Of course, the first line is a bit redundant: getting three left characters of a string that has exactly three characters looks, er, strange. This is made on purpose, because an "optimized" version draws my attention away from the inner mechanics of the formula to "why these lines are different and what it means". We need to keep it clear and amplify only what is important until we're familiar with it.
Adopting the formula to a longer key is simple; for example, to work with seven-digit-long keys the first string should be changed as shown:
Left(Right("000000" & Number, 7), 7) & "¶" & ...
and then repeated seven times, decreasing the last 7:
Left(Right("000000" & Number, 7), 7) & "¶" &
Left(Right("000000" & Number, 7), 6) & "¶" &
............................................
Left(Right("000000" & Number, 7), 2) & "¶" &
Left(Right("000000" & Number, 7), 1)
While single-number link is quite simple, a range link looks much more complex; at this moment we can tell only which result we'd like to have. For example, a range from 85 to 314 should be that:
085 086 087 088 089 09 1 2 30 310 311 312 313 314
Though the result is quite complex, it seems we should be able to split the whole calculation into smaller parts, that are almost same. We can see the big range of complex form consists of smaller ranges of three-, two- and one-digit numbers. These ranges are ranges of ones, tens, hundreds and so on; let's call them "units".
If we were able to write a calculation that makes a range of units, we could then join their results with the & operator and get the whole range.
Let's look at these mini-ranges closely. All them are quite alike. They are small; none of them can have more than 10 digits. They are simple, because their numbers form simple arithmetical sequences, such as 085, 086, 087, 088, 089.
There are two kinds of these mini-ranges: some go from the start up, some go from the end down. They form two "streams": the rising and the lowering one. Most rising ranges end with "9" while lowering ones end with "0". The range the "streams" meet one another includes the numbers of whole units between the start and end.
Considering all that stuff we can write a list of "requirements" a mini-range calculation should meet:
The last condition may look strange: why should we omit them? If we omit the start and end numbers we won't have links to them, so they won't fall in out range. But if we look at our ranges we'll see that none of the mini-ranges except the range of ones includes the start and end units. For example, 085 belongs to ten number 08, but the range of tens starts from 09; it also belongs to hundred number 0, but the range of hundreds doesn't include neither 0. The end number belongs to hundred number 3, but the range of hundreds stops on 2. We can clearly see the start and end numbers are exceptions, so we will handle them as exceptions and add them separately:
Right("00" & Start, 3) &
the calculations & "¶" &
Right("00" & End, 3)
The first version of this technique contained different calculations for calculating mini-ranges of ones, but now I think it's too confusing and difficult. By the way, the new modification allows us to easily include and exclude the ends of a range; it might be useful in some situations.
Ok; now we should try to build such a calculation.
First, let's see how one can make a mini-range of 10 numbers, say, from 170 to 179, separated by carriage returns. I think it can be done in many ways, but one of the simplest would be
Substitute(".0.1.2.3.4.5.6.7.8.9", ".", "¶17")
It works! It gives us a range of 10 keys, separated by carriage returns! This is a rising range, it goes up; to have a range that goes down we need to reverse the order of the numbers in the constant.
Substitute(".9.8.7.6.5.4.3.2.1.0", ".", "¶17")
By the way, we don't need all the 10 numbers; since we decided a mini-range should not include neither the starting nor the ending unit, a rising range won't ever start from 0 while a lowering one won't start from 9. So let's discard the first dot-and-number in both the constants.
".1.2.3.4.5.6.7.8.9" instead of ".0.1.2.3.4.5.6.7.8.9" and
".8.7.6.5.4.3.2.1.0" instead of ".9.8.7.6.5.4.3.2.1.0"
First we don't need a static range, but a dynamic one: it should use our start and end numbers. To do it we should use the numbers-or, to be precise, some parts of them, because we already have the digits that make the range-instead of the sample "¶17":
Substitute(".1.2.3.4.5.6.7.8.9", ".", "¶" & Left(Right("00" & Start, 3), 2))
and
Substitute(".8.7.6.5.4.3.2.1.0", ".", "¶" & Left(Right("00" & End, 3), 2))
What happens? The emphasized parts make sure Start and End are exactly three-digit long, cut out the first two digits, combine them with our pre-made range and get series of three-digit keys, that's ranges of ones. Do you see how we can make a range of tens? Of course; we should do the same, but cut out one first digit instead of two:
Substitute(".1.2.3.4.5.6.7.8.9", ".", "¶" & Left(Right("00" & Start, 3), 1))
This calculation combines only one digit from Start with the pre-made range and gets a series of two-digit keys, that's a range of tens. To get a range of hundreds-in our case it will contain one-digit keys-the formula should be
Substitute(".1.2.3.4.5.6.7.8.9", ".", "¶" & Left(Right("00" & Start, 3), 0))
Now we can make mini-ranges of keys of any length, but they include all the possible numbers while we want only some of them. The solution is to use not the whole ".1.2..." constant, but only a part of it. If we found we need a range from 14 to 17 we should cut out the ".4.5.6.7" part somehow and then pass only this part to the Substitute() function.
The natural way to extract a part of our text constant is to use the Middle() function.
Substitute(
Middle(".1.2.3.4.5.6.7.8.9", <start>, <size>),
".",
"¶" & Left(Right("00" & Start, 3), 2)
)
Calculating the <start> value. The calculation should examine the start or end number and extract the digit that represents its unit: for example, if the calculation builds a range of tens, it should extract "8" from "085" or "1" from "314". Then it must calculate the position in our ".1.2..." constant the range should begin, keeping in mind that we shouldn't include the start number itself.
I think it can be done in many ways; you can treat numbers as strings, add leading zeros and extract the required digit using the Middle() function
<start>=Middle(Right("00" & Start, 3), 2, 1)*2+1
Another method is more numerical:
<start>=Int(Mod(Start, 10^2)/10^1)*2+1
It does the same, but less clearly. I prefer the second variant and will use it in this article but it can be just my own taste. (The only advantage is that it looks better with the <size> part and allows us to build ranges of alphanumeric keys, but it doesn't matter in most cases.)
The emphasized parts determine the unit the formula works with:
Int(Mod(Start, 10^1)/10^0)*2+1: ones
Int(Mod(Start, 10^2)/10^1)*2+1: tens
Int(Mod(Start, 10^3)/10^2)*2+1: hundreds
The calculation that builds a lowering range is almost same, except that it has to "reverse" the number:
start=(9-Int(Mod(End, 10^1)/10^0))*2+1
Calculating the <size> value. This calculation is simpler. The size is of text we should cut out is the number of units between the start and end numbers without these numbers themselves, multiplied by 2, that's in case of tens
<size>=(Int(End/10^1)-Int(Start/10^1)-1)*2
This simple formula fits our requirements surprisingly well. Though it calculates the number of units between the numbers which can be quite big (that's in the case of ones it just subtracts Start from End), it doesn't matter because our pre-made range has only 9 numbers. On the other hand if the number of units is small, the formula gets exactly the part we need.
We have all the parts; let's construct the modules. Here's a rising module for ones:
Substitute(
Middle(
".1.2.3.4.5.6.7.8.9",
Int(Mod(Start, 10^1)/10^0) * 2 + 1,
(Int(End/10^0) - Int(Start/10^0) - 1) * 2
),
".",
"¶" & Left(Right("00" & Start, 3), 2)
)
At this moment we're familiar with all members of the formula: a mini-range constant, <start> and <size> scissors that cut out a piece and a calculation that gets the necessary part of Start number. The emphasized numbers determine which unit is processed. To make it working with the next unit we should increase the numbers in the <start> and <size> parts and decrease the number that extracts a part of Start:
Substitute(
Middle(
".1.2.3.4.5.6.7.8.9",
Int(Mod(Start, 10^2)/10^1) * 2 + 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2
),
".",
"¶" & Left(Right("00" & Start, 3), 1)
)
To make the next module we should continue the process in the same direction. As soon as we have enough modules we could join them with the & operator and we are almost ready-the whole rising stream is complete. The lowering stream is done the same way: construct a lowering module for ones
Substitute(
Middle(
".8.7.6.5.4.3.2.1.0",
(9 - Int(Mod(End, 10^1)/10^0)) * 2 + 1,
(Int(End/10^0) - Int(Start/10^0) - 1) * 2
),
".",
"¶" & Left(Right("00" & End, 3), 2)
)
duplicate it, change critical numbers and repeat it until you have modules for all mini-ranges.
The last thing we should do is to join all these mini-ranges together and add the start and end numbers. The whole formula even for small 3-digit keys is rather big to be put here; you can see all the parts of it in the sample files. I believe at this moment it's no longer a mystery and you could easily construct it from the following short scheme:
Right("00" & Start, 3) &
<rising range for ones> &
<rising range for tens> &
<rising range for hundreds> &
"¶" & Right("00" & End, 3)
<lowering range for ones> &
<lowering range for tens> &
<lowering range for hundreds>
Now we know how to build the keys we need to link ranges; it's time to know how to link them. Basically this is simple: the keys you have are ready to be linked to each other, so this section is devoted mainly to practical and advanced topics, such as real-world keys, links between ranges, prefixes and suffixes and open ranges.
The only difference between the sample and real-world calculations is that real-world keys are longer, so the real-world calculations include more modules. But let's talk about the keys first. If you work with numerical ranges, that's Number, Start and End are number fields, they would fit the formula naturally. You might wonder if there's something special with date and time ranges. Almost nothing; the only thing you should do is to find a way to represent them as sequential numbers.
"Native" FileMaker dates. FileMaker dates are sequential numbers really, so you don't have to do anything with them: just use the date fields as if they were numbers. The maximum number is 1,095,727 (Dec, 12, 3000), so the keys should be seven-digit-long.
Hours, minutes and seconds. Many business applications require time ranges of higher precision: up to an hour or even a minute. It is not a problem: just find a way to number these time units sequentially, evaluate the maximum length of a key and adapt the calculations for it.
Let's see how one can number hours. We'll need two fields to get the information: Date and Time. You know that FileMaker stores time values as number of seconds from midnight; for example, 1 AM is really stored as 3,600 (1×60×60). To get a number of an hour within twenty-four hours we could use the following formula: Int(Time/60/60); to get an unique number for every hour since Jan 1st, 1 we should calculate how many hours has passed since then and add these two values:
(Date - 1) * 24 + Int(Time/60/60)
The highest possible number of an hour is 24×1,095,727 = 26,297,448, which is eight-digit-long, so it requires eight-digit keys. Look, they are only one digit longer than the date keys!
Of course, we can be even more precise. The formula that numbers every minute is
(Date - 1) * 24 * 60 + Int(Time/60)
and the maximum key is ten-digit-long; every second is
(Date - 1) * 24 * 60 * 60 + Time
and the maximum key is eleven-digit-long.
Bits of this topic was scattered here and there in the previous sections, so you might already know how to do it. This section just gathers all them together and uses a sort of mathematical notation.
To adapt the singe-key formula to a key of length N, assemble the first string so
Left(Right("<N-1 zeros>" & Number, N), N) & "¶" & ...
and then repeat it N - 1 times, decreasing the last N:
Left(Right("<N-1 zeros>" & Number, N), N) & "¶" &
Left(Right("<N-1 zeros>" & Number, N), N - 1) & "¶" &
Left(Right("<N-1 zeros>" & Number, N), N - 2) & "¶" &
.....................................................
Left(Right("<N-1 zeros>" & Number, N), 1)
To adapt the range formula to a key of length N, assemble the first rising module so
Substitute(
Middle(
".1.2.3.4.5.6.7.8.9",
Int(Mod(Start, 10^1)/10^0) * 2 + 1,
(Int(End/10^0) - Int(Start/10^0) - 1) * 2
),
".",
"¶" & Left(Right("<N - 1 zeros>" & Start, N), N - 1)
)
and then repeat it N - 1 times, increasing the first four numbers and decreasing the last one. The last rising module should look so
Substitute(
Middle(
".1.2.3.4.5.6.7.8.9",
Int(Mod(Start, 10^N)/10^N - 1) * 2 + 1,
(Int(End/10^N - 1) - Int(Start/10^N - 1) - 1) * 2
),
".",
"¶" & Left(Right("<N - 1 zeros>" & Start, N), 0)
)
The same is for lowering modules; here are the first and the last one:
Substitute(
Middle(
".8.7.6.5.4.3.2.1.0",
(9 - Int(Mod(Start, 10^1)/10^0)) * 2 + 1,
(Int(End/10^0) - Int(Start/10^0) - 1) * 2
),
".",
"¶" & Left(Right("<N - 1 zeros>" & Start, N), N - 1)
) & ...
Substitute(
Middle(
".8.7.6.5.4.3.2.1.0",
(9 - Int(Mod(Start, 10^N)/10^N - 1)) * 2 + 1,
(Int(End/10^N - 1) - Int(Start/10^N - 1) - 1) * 2
),
".",
"¶" & Left(Right("<N - 1 zeros>" & Start, N), 0)
)
The whole calculation includes both the streams and the start and end numbers.
Right("<N - 1 zeros>" & Start, N) &
<rising stream of N modules> &
"¶" & Right("<N - 1 zeros>" & End, N) &
<lowering stream of N modules>
Since our ranges are "compressed" they cannot be linked directly one to another, because it might happen there's no common keys there. Fortunately it's rather easy to avoid. Let's examine how two ranges may relate to each other.
Figure 1: All the possible cases two ranges may intersect or not intersect.
We can see that if two ranges intersect either the start of the first one will fall into another or vice versa (the same is for ends; it doesn't matter which end of a range to use as soon as they are same). That is to get all ranges that intersect with a given one we should
So we have split the problem of linking two ranges into two problems: link a range to a number and link a number to a range and we know how to solve both. To link two ranges we should use combined keys on both sides
Range key from start to end → Single-number key of start
Single-number key of start → Range key from start to end
To prevent the keys from mixing, we should separate one from another using a prefix or a suffix
Range key from start to end → Single-number key of start
Single-number key of start & "X" → Range key from start to end & "X"
Here "X" represent a suffix or a prefix, added to the keys; the easy way to do it is discussed in the next section.
The first edition of the technique used an awful method to adding prefixes and suffixes to the calculations until Vince Lackner suggested a much simpler way. This formula adds a prefix
Prefix & " " & Substitute(<key>, "¶", "¶" & Prefix & " ")
this one adds a suffix
Substitute(<key>, "¶", " " & Suffix & "¶") & " " & Suffix
and this one adds both
Prefix & " " & Substitute(<key>, "¶", " " & Suffix & "¶" & Prefix & " ") & " " & Suffix
Here the <key> is a placeholder for either a reference to field containing a range or a single-number key or the formulas themselves.
Open range is a range that represents all numbers before or after a given one. To make such a range you can use the highest or the lowest possible number at the "open" side; that's if your keys are three-digit-long and you're making a range to all numbers greater than or equal to 123, you could use "999" as the end number. Perhaps your Start and End fields might be turned into calculations like this
If(IsEmpty(StartDate, <the lowest date>, StartDate)
I think this method is simpler than modifying the formula itself as it was done in the previous version.
If you don't thing so, here's a short description of that modification. While a full formula has both start and end numbers, an open range has only one of them, so it doesn't need a whole half of the formula. A range to all numbers after a given one doesn't need the lowering part and a range to all numbers before a given one doesn't need the rising one.
The remaining part includes references to unnecessary field in the <size> part. But an open range doesn't need the part at all: the <size> part limits the range, but an open range isn't limited. We only need to know the start of it and include all the rest, that is replace the whole <size> part with any number that's big enough to get the whole ".1.2..." constant despite of what the <start> is. Any number bigger than 18 would work.
Middle( ".1.2.3.4.5.6.7.8.9", Int(Mod(Start, 10^1)/10^0) * 2 + 1, 18 )
I'd like to thank in no particular order
It's a long article, so if you'd like to have it printed get the PDF version with FileMaker samples (206K).
Copyright © 2000-2004
Mikhail Edoshin
If you have problems viewing this site, please drop a note to the 
webmaster