Smart Ranges
how it works

This extra information makes it possible to fetch many numbers with just one link. For example, a link to 5th ten brings 10 numbers from 50 to 59. A link to 1st hundred brings 100 numbers from 100 to 199.

We just need to combine these links to get all the range we want. Let's specify a range from 65 to 215:

This is the essence of Smart Ranges. The last thing you should know is how to construct such a range 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 sequental numbers. We are going to split dates into tens, hundreds and thousands; it would be more human to split them into weeks, months and years, but this is harder to implement in FileMaker.

How to build a relationship from a range to single dates (numbers)

This procedure takes 2 steps

• Prepare the related file: add the extra information about tens, hundreds, thousands and so on to every date (number)
• Prepare the main file: construct a calculation to build a smart range between the start and end dates (numbers).

Step 1: Preparing the related file

The information about tens, hundreds and so on should look like this:

Note that the date has a leading zero. The leading zeros are necessary. Without them you cannot tell whether '7' is a link to 7th day or to 7th thousand; with zeros the link to day is '0000007' and the link to thousand is '0007'.

Tip: how to 'turn' a date into number

When working with dates we need only 7 digits, because the maximum date in FileMaker is 7-digit-long (Dec 31, 3000 is 1,095,727). Other ranges may require longer or shorter key.

 Create a new calculation field DateKey

 Paste the following formula into it

Right("000000" & Date, 7) & "¶" &
Left(Right("000000" & Date, 7), 6) & "¶" &
Left(Right("000000" & Date, 7), 5) & "¶" &
Left(Right("000000" & Date, 7), 4) & "¶" &
Left(Right("000000" & Date, 7), 3) & "¶" &
Left(Right("000000" & Date, 7), 2) & "¶" &
Left(Right("000000" & Date, 7), 1)

The formula is very simple. It adds six leading zeros to the date, cuts out exactly 7 digits and then strips out one, two and more digits to make the compound key we need.

 Set the result to 'text'

Step 2: Preparing the main file

The formula in the main file is rather complex. You do not have to understand how it works to use: you can just copy and paste it into your solution. If you are struggling with prefixes or suffixes, read the special section How to add prefixes and suffixes to the keys: it has detailed instructions. Aren't you scared yet? OK, make yourself a cup of coffee and take a comfortable seat.

If you know the main principle of Smart Ranges it would be very easy for you to tell what result do we need. For example, if the Start is March 1st, 2001 (740545) and the End is March 31, 2001 (730575) we need the following set of keys:

The real formula gives your the keys in slightly different order and some keys are duplicated:

The formula consist of 14 parts. All the parts are amost the same. The parts are:

1. Range of ones from Start up &
2. Range of tens from Start up &
3. Range of hundreds from Start up &
4. Range of thousands from Start up &
5. Range of tens thousands from Start up &
6. Range of hundreds thousands from Start up &
7. Range of millions from Start up &
8. Range of ones from End down &
9. Range of tens from End down &
10. Range of hundreds from End down &
11. Range of thousands from End down &
12. Range of tens thousands from End down &
13. Range of hundreds thousands from End down &
14. Range of Millions from End down

The range from March 1, 2001 to March 31, 2001 is created by the following parts:

 Let's look closer at one of the parts; once you get it, you'll almost understand the whole calculation. Here's the second part that generates mini-range of tens from Start up.

Substitute(
  Middle(
    " 1 2 3 4 5 6 7 8 9",
    (Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
    (Int(End/10^1) - Int(Start/10^1) - 1) * 2),
  " ", 
  "¶" & Left(Right("000000" & Start, 7), 5)
  )

We analyze it step by step.

The Substitute() function produces the mini-range itself. Let's temporarily omit some parts to make it clearer:

Substitute(
  " 1 2 3 4 5 6 7 8 9", 
  " ", 
  "¶" & Left(Right("000000" & Start, 7), 5)
  )

You can see the function substitutes spaces in the pre-made constant " 0 1 2 3 4 5 6 7 8 9" with carriage returns and five digits of the Start. This gives us the mini-range of tens we need.

The range is too big at this moment — we need only two lines. We are going to use the Middle() function to cut out a part of the constant before substitution.

The start is:

(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1

The size is:

(Int(End/10^1) - Int(Start/10^1) - 1) * 2

So the whole part:

• cuts out a part of the constant. In our sample the result is " 5 6"
• substitutes spaces in the result with carriage returns and five digits of the Start. In our sample it gets to
  073055
  073056

Look at it again. The other parts are almost the same and I'm going to amplify only the differences between them.

Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 5))

 Let's compare the second and the third part. The second computes tens, the third — hundreds. Here they are and the differences are marked:

This is the second part that calculates the range of tens

Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 5))

This is the third part that calculates the range of hundreds

Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^3)/10^2) + 1) * 2 - 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 4))

Note that only the power and the number of digits being cut from the Start changes. The power changes because we need to compare another digit of the Start and the End; the number of digits changes because we need a key of different length.

Look at the fourth part that calculates the range of hundreds to get the pattern:

Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^4)/10^3) + 1) * 2 - 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7),3))

 How do parts from 9 to 14 work? They calculate mini-ranges from the End. Let's compare the second and the ninth parts. They both calculate the range of tens, one from the Start, another from the End.

This is the second part that calculates the range of tens from Start

Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 5))

This is the ninth part that calculates the range of tens from End

Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^2)/10^1)) * 2 + 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ","¶" & Left(Right("000000" & End, 7), 5))

The constant, the method of calculating start and the key string is changed (Start becomes End).

 How do part 1 and part 8 differ? These parts calculate ranges of ones. Of course, the power indices and the number of digits in the key are changed. Besides the constant is longer and one minus becomes plus.

This is the second part that calculates the range of tens from Start:

Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 5))

This is the first part that calculates the range of ones from Start and includes the Start value itself:

Substitute(
Middle(" 0 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^1)/10^0) + 1) * 2 - 1,
(Int(End/10^0) - Int(Start/10^0) + 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 6))

This is the eighth part that calculates the range of ones from End and includes the End value itself:

Substitute((" 9 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("000000" & End, 7), 6))

 OK; lets look at the whole formula:

Substitute(
Middle(" 0 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^1)/10^0) + 1) * 2 - 1,
(Int(End/10^0) - Int(Start/10^0) + 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 6))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 5))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^3)/10^2) + 1) * 2 - 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 4))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^4)/10^3) + 1) * 2 - 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 3))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^5)/10^4) + 1) * 2 - 1,
(Int(End/10^4) - Int(Start/10^4) - 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 2))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^6)/10^5) + 1) * 2 - 1,
(Int(End/10^5) - Int(Start/10^5) - 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 1))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^7)/10^6) + 1) * 2 - 1,
(Int(End/10^6) - Int(Start/10^6) - 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 0))

&

Substitute(
Middle(" 9 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("000000" & End, 7), 6))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^2)/10^1)) * 2 + 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ", "¶" & Left(Right("000000" & End, 7), 5))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^3)/10^2)) * 2 + 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ", "¶" & Left(Right("000000" & End, 7), 4))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^4)/10^3)) * 2 + 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ", "¶" & Left(Right("000000" & End, 7), 3))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^5)/10^4)) * 2 + 1,
(Int(End/10^4) - Int(Start/10^4) - 1) * 2),
" ", "¶" & Left(Right("000000" & End, 7), 2))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^6)/10^5)) * 2 + 1,
(Int(End/10^5) - Int(Start/10^5) - 1) * 2),
" ", "¶" & Left(Right("000000" & End, 7), 1))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^7)/10^6)) * 2 + 1,
(Int(End/10^6) - Int(Start/10^6) - 1) * 2),
" ", "¶" & Left(Right("000000" & End, 7), 0))

I cannot beleive but we are at the end. To install the formula:

 Create a new calculation field Range

 Paste the formula into it

 Set the result to 'text'

 Establish a relationship between the Range and the DateKey in the Dates file.

How to link a single date (number) to ranges

This section is shortest. Take the relationship from a range to dates (numbers) and reverse it: establih a link from DateKey to Range.

How to link a range to ranges

The better wording is: 'How to find if two ranges intersect?'.

It's not so easy. You cannot simply establish a link between ranges, because their Range calculations could have no common keys:

The solution is simple. It's obvious that if two ranges intersect, either the first one will contain the start date of the second, or the second one will contain the start date of the first.

The end dates would work as well; it doesn't matter which one to use as long as they are same. To implement this idea we need to combine two relationships we have discussed in the previous sections: Range::DateKey and DateKey::Range into one. Since we are going to use the start date, I rename the DateKey to StartKey. We should create a compound key from Range and StartKey on both sides:

Note that one pair of values has a prefix; it's absolutely necessary, because the calculation combines two different formulas: a range and a date to catch. If there were no prefix, the last '0' in the StartKey would be a link to all the dates from the first million.

The actual formulas are not shown here. You can copy them from the sample file. If you need help with prefixes, read the next section.

How to add prefixes and suffixes to the keys

If you need to filter related records by additional criteria, you will have to add a prefix or a suffix to the Range and DateKey. I assume you're familiar with portal filtering principles, so I just show where to put the prefix or the suffix in the formulas.

 Formula 1: Inserting a prefix into DateKey

Prefix & " " &
Substitute(
Right("000000" & Date, 7) & "¶" &
Left(Right("000000" & Date, 7), 6) & "¶" &
Left(Right("000000" & Date, 7), 5) & "¶" &
Left(Right("000000" & Date, 7), 4) & "¶" &
Left(Right("000000" & Date, 7), 3) & "¶" &
Left(Right("000000" & Date, 7), 2) & "¶" &
Left(Right("000000" & Date, 7), 1) ,
"¶", "¶" &Prefix & " ")

 Formula 2: Inserting a suffix into DateKey

Right("000000" & Date, 7) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 6) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 5) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 4) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 3) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 2) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 1) & " " & Suffix

 Formula 3: Inserting both a prefix and a suffix into DateKey

I prefer not to do so, because the matching Ranges calculation becomes hardly readable. There seems to be no technical reason either ‹ you can combine all additional keys into a compound prefix (suffix) instead of putting them on both sides of the Date. Anyway, here's the formula:

Prefix & " " &
Substitute(
Right("000000" & Date, 7) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 6) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 5) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 4) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 3) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 2) & " " & Suffix & "¶" &
Left(Right("000000" & Date, 7), 1) & " " & Suffix,
"¶", "¶" & Prefix & " ")

 Formula 4: Inserting a prefix into Ranges

Substitute(
Middle(" 0 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^1)/10^0) + 1) * 2 - 1,
(Int(End/10^0) - Int(Start/10^0) + 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 6))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 5))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^3)/10^2) + 1) * 2 - 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 4))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^4)/10^3) + 1) * 2 - 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 3))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^5)/10^4) + 1) * 2 - 1,
(Int(End/10^4) - Int(Start/10^4) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 2))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^6)/10^5) + 1) * 2 - 1,
(Int(End/10^5) - Int(Start/10^5) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 1))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^7)/10^6) + 1) * 2 - 1,
(Int(End/10^6) - Int(Start/10^6) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 0))

&

Substitute(
Middle(" 9 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),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 6))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^2)/10^1)) * 2 + 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 5))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^3)/10^2)) * 2 + 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 4))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^4)/10^3)) * 2 + 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 3))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^5)/10^4)) * 2 + 1,
(Int(End/10^4) - Int(Start/10^4) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 2))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^6)/10^5)) * 2 + 1,
(Int(End/10^5) - Int(Start/10^5) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 1))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^7)/10^6)) * 2 + 1,
(Int(End/10^6) - Int(Start/10^6) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 0))

 Formula 5: Inserting a suffix into Ranges

Substitute(
Middle(
Substitute(
Middle(" 0 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^1)/10^0) + 1) * 2 - 1,
(Int(EndX/10^0) - Int(Start/10^0) + 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 6))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 5))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^3)/10^2) + 1) * 2 - 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 4))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^4)/10^3) + 1) * 2 - 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 3))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^5)/10^4) + 1) * 2 - 1,
(Int(End/10^4) - Int(Start/10^4) - 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 2))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^6)/10^5) + 1) * 2 - 1,
(Int(End/10^5) - Int(Start/10^5) - 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 1))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^7)/10^6) + 1) * 2 - 1,
(Int(End/10^6) - Int(Start/10^6) - 1) * 2),
" ","¶" & Left(Right("000000" & Start, 7), 0))

&

Substitute(
Middle(" 9 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("000000" & End, 7), 6))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^2)/10^1)) * 2 + 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ","¶" & Left(Right("000000" & End, 7), 5))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^3)/10^2)) * 2 + 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ","¶" & Left(Right("000000" & End, 7), 4))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^4)/10^3)) * 2 + 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ","¶" & Left(Right("000000" & End, 7), 3))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^5)/10^4)) * 2 + 1,
(Int(End/10^4) - Int(Start/10^4) - 1) * 2),
" ","¶" & Left(Right("000000" & End, 7), 2))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^6)/10^5)) * 2 + 1,
(Int(End/10^5) - Int(Start/10^5) - 1) * 2),
" ","¶" & Left(Right("000000" & End, 7), 1))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^7)/10^6)) * 2 + 1,
(Int(End/10^6) - Int(Start/10^6) - 1) * 2),
" ","¶" & Left(Right("000000" & End, 7), 0)),
2, 99999),
"¶", " " & Suffix & "¶") & " " & Suffix

 Formula 6: Inserting both a prefix and a suffix into Ranges

Substitute(
Middle(
Substitute(
Middle(" 0 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^1)/10^0) + 1) * 2 - 1,
(Int(EndX/10^0) - Int(Start/10^0) + 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 6))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 5))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^3)/10^2) + 1) * 2 - 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 4))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^4)/10^3) + 1) * 2 - 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 3))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^5)/10^4) + 1) * 2 - 1,
(Int(End/10^4) - Int(Start/10^4) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 2))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^6)/10^5) + 1) * 2 - 1,
(Int(End/10^5) - Int(Start/10^5) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 1))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^7)/10^6) + 1) * 2 - 1,
(Int(End/10^6) - Int(Start/10^6) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & Start, 7), 0))

&

Substitute(
Middle(" 9 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),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 6))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^2)/10^1)) * 2 + 1,
(Int(End/10^1) - Int(Start/10^1) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 5))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^3)/10^2)) * 2 + 1,
(Int(End/10^2) - Int(Start/10^2) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 4))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^4)/10^3)) * 2 + 1,
(Int(End/10^3) - Int(Start/10^3) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 3))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^5)/10^4)) * 2 + 1,
(Int(End/10^4) - Int(Start/10^4) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 2))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^6)/10^5)) * 2 + 1,
(Int(End/10^5) - Int(Start/10^5) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 1))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^7)/10^6)) * 2 + 1,
(Int(End/10^6) - Int(Start/10^6) - 1) * 2),
" ","¶" & Prefix & " " & Left(Right("000000" & End, 7), 0)),
2, 99999),
"¶", " " & Suffix & "¶") & " " & Suffix

How to link to an open range ('dates that come before' or 'numbers greater than')

You know that the main Range calculation consist of 14 parts, because it calculates 7 mini-ranges from start and 7 mini-ranges from end. An open range does not have either start or end date, so you need only a half of the formula:

You also should change the formula a bit: you do not need to calculate the size of the mini-ranges. Instead of

you need

In the general formula you should replace the strike-through part with any number, greater than the length of the '0123...' constant; '18' is OK in all cases, except the 1st and the 7th, that require 20.

 Here's the modified formula to link to all dates after:

Substitute(
Middle(" 0 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^1)/10^0) + 1) * 2 - 1, 20),
" ","¶" & Left(Right("000000" & Start, 7), 6))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^2)/10^1) + 1) * 2 - 1, 18),
" ","¶" & Left(Right("000000" & Start, 7), 5))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^3)/10^2) + 1) * 2 - 1, 18),
" ","¶" & Left(Right("000000" & Start, 7), 4))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^4)/10^3) + 1) * 2 - 1, 18),
" ","¶" & Left(Right("000000" & Start, 7), 3))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^5)/10^4) + 1) * 2 - 1, 18,
" ","¶" & Left(Right("000000" & Start, 7), 2))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^6)/10^5) + 1) * 2 - 1, 18),
" ","¶" & Left(Right("000000" & Start, 7), 1))
&
Substitute(
Middle(" 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^7)/10^6) + 1) * 2 - 1, 18),
" ","¶" & Left(Right("000000" & Start, 7), 0))

 and to all dates before:

Substitute(
Middle(" 9 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^1)/10^0)) * 2 + 1, 20),
" ","¶" & Left(Right("000000" & End, 7), 6))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^2)/10^1)) * 2 + 1, 18),
" ","¶" & Left(Right("000000" & End, 7), 5))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^3)/10^2)) * 2 + 1, 18),
" ","¶" & Left(Right("000000" & End, 7), 4))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^4)/10^3)) * 2 + 1, 18),
" ","¶" & Left(Right("000000" & End, 7), 3))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^5)/10^4)) * 2 + 1, 18),
" ","¶" & Left(Right("000000" & End, 7), 2))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^6)/10^5)) * 2 + 1, 18),
" ","¶" & Left(Right("000000" & End, 7), 1))
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod(End, 10^7)/10^6)) * 2 + 1, 18),
" ","¶" & Left(Right("000000" & End, 7), 0))

I do not have Start and End of a range; I have only Start and Duration. What should I do?

You can either create a calculated field End (= Start + Duration) and use the same formulas or replace the End in the calculation with (Start + Duration):

Substitute(
Middle(" 0 1 2 3 4 5 6 7 8 9",
(Int(Mod(Start, 10^1)/10^0) + 1) * 2 - 1,
(Int((Start + Duration)/10^0) - Int(Start/10^0) + 1) * 2),
" ", "¶" & Left(Right("000000" & Start, 7), 6))
&
...
&
Substitute(
Middle(" 8 7 6 5 4 3 2 1 0",
(9 - Int(Mod((Start + Duration), 10^7)/10^6)) * 2 + 1,
(Int((Start + Duration)/10^6) - Int(Start/10^6) - 1) * 2),
" ", "¶" & Left(Right("000000" & (Start + Duration), 7), 0))

Credits and thanks

I'd like to thank

Geoffrey Gerhard, who tested the first version of the Smart Ranges, spotted a mistake and gave an excellent fix idea.

Jason L. DeLooze, who suggested many optimization ideas. I plan to write a section about optimizing the Smart Ranges later.

Susanne Waher and Vince Lackner, who spotted many mistakes in the text.

All the people who persuaded me to write these papers (bytes?).

Please drop me a note if you spot any errors on these pages. English is not my native language so there should be a lot. Bye!

Mikhail Edoshin

Get the samples ( 100k).

The Smart Ranges works in all versions of FileMaker Pro (3, 4 and 5). The sample files are in FileMaker 4 format.