Smart Ranges Custom Functions

[Ender]

Here's a custom function implementation of Mikhail Edoshin's Smart Ranges technique for relationships based on date, time, or number ranges.

Though complicated, the technique was quite useful in previous versions on FMP, so I thought I'd try to make the implementation easier in FM7 by creating plug-and-play custom functions. In testing it with a large record set (500,000 related records,) I was not able to determine if there was any speed difference between the Smart Ranges technique and FM7's built-in relationship range criteria matches. In my tests, they both performed quite well over a LAN and over a WAN (2-3 seconds to show 100-200 matches out of 500,000 in a portal.)

But since I went through the trouble...here are the functions:

SmartRange(Start;End) =

//SmartRange(Start;End)

//By Mike Hackett

//9-15-2004

//This is the main SmartRange function, a custom function based on Mikhail Edoshin's

//Smart Ranges technique from http://www.onegasoft.com/tools/smartranges/

//It builds the multikeys for a range relationship

Let

(

[

[Charles Delfs]

Has anyone done s speed comparison with this tech to FMP7's new greaterthan lessthan abilities for relationships. Cuz if they work fast they are certainly much easier to use.

Charles

[MoonShadow]

I recall recently reading speed comparisons. Using FM7's native > relationships were much slower than Smart Ranges. Sorry I can't remember where I read them ... I read everything.

[ralph.nusser]

Please, upload an example file with the custom functions implemented.

Thank you for your efforts.

Ralph

[Ender]

Attached is a demonstration file.

SmartRangesCF.fp7.zip

[rogermax]

You may want to edit the parameter requirements for SmartRangeSub(start;end) to SmartRangeSub(start;end;i;n)

Unless I am mistaken the other two parameters are needed. And for those that are less familiar with the practice, a note about the last two parameters.

Great work, by the way, I was just about to try figuring out how to move my calcs to a custom funtion for this. Thanks

[Ender]

Oops. Thanks for catching that, Roger. Fixed.

[Kurt Knippel]

I am trying to implement this...not as a key match...but as an actual date range creator. I would like to then use this text field filled with actual dates for other functions.

Any idea how I might modify your function to do this?

[Ender]

Hi Kurt,

The Smart Ranges technique looks at dates as numbers, and breaks them down into keys for each digit's place: 1's, 10's, 100's, 1000's, 10000's, and 100000's. The advantage of this is to use a few keys to represent a range of dates (for most date ranges, the multi-key won't be more than 20 lines.)

There are other date range custom functions that build actual dates (or numbers representing dates) into a multi-key. I'm not sure if this is what you had in mind, but you can use Andrew Persons' DateRange custom function on briandunning.com to make a multi-key of actual dates. Given two dates, it will create a multi-key like this:

3/4/2004

3/5/2004

3/6/2004

3/7/2004

3/8/2004

Functions like these that put each date (or number) of the range into the multi-key have performance issues when the Start and End dates are more than a few years apart.

If that's not what you're looking for, maybe you can say more.

[ralph.nusser]

Thank you Ender.

Can you expand your example to prefixes an suffixes as Mr. Edoshin did it?

Thank you for your efforts.

[Ender]

Hi Ralph,

I don't think there's much need to include prefixes and suffixes within the multi-key, as IDs or other filter criteria can be included in FM7's multi-criteria relationship capabilities.

[Ender]

On an unrelated note, there was a bug in my SmartRanges() function in that an extra pilcrow should be added at the end (so it can be substituted with an x in the RangeToRange functions.) This was causing some RangeToRange keys to fail to match.

The correct function should look like this:

SmartRange(Start;End) =

//SmartRange(Start;End)

//By Mike Hackett

//Rev 12-25-2004 (added pilcrow at the end.)

//This is the main SmartRange function, a custom function based on Mikhail Edoshin's

//Smart Ranges technique from http://www.onegasoft.com/tools/smartranges/

//It builds the multikeys for a range relationship

Let

(

[

[ralph.nusser]

Dear Ender

Is it difficult to implement the prefixes and suffixes in the Custom Function as it was implemented by Mr. Edoshin? I would be very happy if you could build a perfect replacement for the old system with CF.

Thanks in advance Ralph

[RalphL]

Smart Ranges are not needed in FMP7. All of this can be handled in the relationship and you don't need calculated key fields. Think 7!

[Ender]

Is it difficult to implement the prefixes and suffixes in the Custom Function as it was implemented by Mr. Edoshin? I would be very happy if you could build a perfect replacement for the old system with CF.

Taking the result of the SmartRange() function, you can substitute() the prefixes or suffixes into place.

Substitute(

[ralph.nusser]

Dear Ender

I don't get it. Can you complete the missing parts in the extended example?

Thank you for your efforts.

DateKey Prefix

DateKey Suffix

DateRangeKey Prefix

DateRangeKey Suffix

Sincerely Ralph

SmartRangesPrefixCF.zip

[ralph.nusser]

The ExplodedKey CF from Darren Terry I copied doesn't work. What went wrong?

Case (StartDate = EndDate; ID & " " & GetAsNumber(StartDate);

Let ( CurrStart = Case(StartDate + 1 = EndDate; EndDate; StartDate + 1);

ID & " " & GetAsNumber(StartDate) & "<cr>" &

ExplodedKey(ID; CurrStart; EndDate)

)) /*<cr> is the carriage return symbol */

Sample Input:

ID = "Darren"

StartDate = "4/1/2004"

EndDate = "4/3/2004"

Sample Output:

Darren 731672

Darren 731673

Darren 731674

DateRangePrefixTerry.zip

[Ender]

You can't mix Darren's ExplodedKey() with SmartRanges(). Smart Ranges uses a small number of keys to represent a large range of dates, while the ExplodedKey() function simply lists each key between two dates.

The simplest method to add additional keys to a Smart Ranges relationship is to use FM7's multi-criteria relationships. If you haven't attempted this, you should try this first.

If you really want the additional keys all in one field, you can try applying the keys using substitute functions to the SmartRanges result in the calculation where it's used (like I showed above.) The same idea can be used to apply a prefix and suffix within the custom functions, but this requires more work. You'd need to add Prefix and Suffix parameters to SmartRanges(), SmartRangeToRange(), SmartRangeToRangeMatch(), and SmartRangeMatch().

[Ender]

The reason I didn't include Prefix and Suffix options in my version of SmartRanges() is that they do not need to be included in the multi-key. And why limit this relationship to only the range, a prefix and a suffix? Using FM7 multi-criteria relationships, you can have as many filter criteria in there as you want, simply by adding another field pair to the relationship (no monkeying with the calcs or functions required.)

[RalphL]

I would go one step farther and say that you do not need SmartRanges in FMP7. Used <= & >= relationships to define the range.

[Søren Dyhr]

But Ender stated somewhere else, that Smartranges somehow indexes faster than the < = and >= operators. Not that I understand why ...it's perhaps some of the same explanation where old fasion lookups are slower than autoenter calc's including the lookup function.

--sd

[Ender]

I found in my testing that Smart Ranges was noticeably faster looking into large files (100,000's of records) when using local files, but that Smart Ranges and FM ranges performed equally well when hosted by Server. My testing was with just me logged in as I haven't had an opportunity yet to test the performance with lots of clients working in it. There was no difference between using Mikhail's original formulas and using FM7's multi-criteria relationships with a custom function range and the additional keys.

I'm not sure why there was a performance difference with local files, but my guess is that Server does a better job of making those FM range relationships available than Client.

[Søren Dyhr]

The ExplodedKey CF from Darren Terry I copied doesn't work. What went wrong?

Why pre- and post-fix'e in the first place, since you can expoit custumfunctions can AND stacking of relations be made as well. Do yourself a test with two multilinekeys fetching some of the records containing pairs of values. Two multiline keys makes a cartesian product of the values in the two! Not that I can explain it -as to why, but you can try to alter my template http://www.fmforums.com/threads/download.php?Number=140165

and then make date and time two key's instead of the prefixed. This shows that I as well were slow to recognize this fact!

--sd

[comment]

I would go one step farther and say that you do not need SmartRanges in FMP7.

I am wondering how one would relate a range to a range, either inclusively or exclusively - when a relationship cannot be compounded using OR.

[Ender]

Hi comment,

My demo earlier in this thread shows Range to Range relationships using FM ranges and Smart Ranges. I'm not sure if this is what you are wondering about.

I suppose in general, you could say OR relationships can be created by using multi-keys, providing both sides are built correctly.

[comment]

you could say OR relationships can be created by using multi-keys

That is my opinion, too, or rather that OR relationships MUST be created by using multi-keys. But Ralph's categorical statement has me wondering if perhaps he has found a method to do this with relationships only.

My logic tells me it cannot be done, but in these forums one learns to never say never...

[RalphL]

I don't see a connection between OR relationships and Range relationships in FMP7. In prior versions you have to use multi-keys for both. In 7 we can use >= to the low end of the range AND <= to the high end of the range. Smart Ranges was a godsend in the past but I don't see a need for them any more.

I don't have a replacement for multi-keys in an OR relationship.

[comment]

I don't see a connection between OR relationships and Range relationships

You are speaking of relating a single date to a range, or vice versa. I am thinking of relating a range to another range, where e.g. overlapping multiple-days events should be related.

Logically, this dictates an OR statement:

start1 >= start2 and start1 <= end2

or

end1 >= start2 and end1 <= end2

[Søren Dyhr]

Yeah Ralph, I've snipped the image from Michails page of all instances ranges relates to ranges ...please show each of them:

.....With the new syntax?

--sd

[comment]

Let me just clarify that I don't mean to attack or challenge. I am reworking something here, and I am seeking final reassurance before I eliminate the possibility of doing this via relationships.

[Ender]

As Mikhail explained, the cases where the ranges intersect can be matched with one algorithm (no OR relationships required.)

For FM ranges, the relationship would look like this:

Interface::Start <= Data::End

AND Interface::End >= Data::Start

This handles the four possible ways the ranges can intersect.

[Søren Dyhr]

But often do we need such things for freestored items shown in a portal, how would you circumvent the need to have multilinekeys either made with customfunctions, Smartrange keys or repeating fields??

It's the items returned just before the desired span of day in question OR only booked to a span just after the duration of this request, that becomes freestored. With a short list of items to rent/book is it even easier to make a scripted replace thru the entire stock to make an indexable either foreingkey or not.

My problem is that I can't see how to DeMorgan such a relations def. in the new terminology in a listing of availiable items are totally independent of the number if ID's involved - if multilines (hashed or not) really in every form is paste tence.

--sd

[comment]

Soren, after all you put me through I still don't know what you mean by "freestored"...

I am coming to the conclusion that multi-line dates are inevitable.

Consider a recurring event, such as "every Wednesday". OK, so we can add DayOfweek() to the key on both sides. But then questions come up:

How to prevent "every Wednesday" from extending backwards to the past, instead of from now on only?

What to do about "every second Wednesday"?

How do you catch a conflict between "every Wednesday" and "every month on the 15th day of the month"?

And that's only for recurring events, I haven't even touched overlapping ranges yet. After seeing Edoshin's diagram, I realized that I have ignored the 2 cases of full overlap. So that is 4 OR statements, instead of 2.

So these are my interim conclusions - if anyone cares to disprove them:

1. Events must have keys with all actual dates generated in advance;

2. As a result, there can be no truly open-ended recurring event.

As you can see, I am somewhat behind you, because I have just come to the question of how to to identify, in case of conflict, the date/s of the conflict.

[Søren Dyhr]

Soren, after all you put me through I still don't know what you mean by "freestored"...

It's an old Denny Schlessinger expression - which I've been using without knowing if it was a common term for things/items becomming "availiable"

--sd

[Ender]

comment, having thought about your questions a bit, I think I'm onto something.

You could still use the FM ranges (or SmartRanges) for the date range part of a relationship. Then use a multi-key for the recurrence period. On the parent side, the recurrence period would be fairly simple; specify the recurrence type and the day:

Weekly Wednesdays

or

Monthly Wednesday 1

The match key would be a little trickier. It would need to have all the possible recurrences listed out. So lets say for a particular date, Wednesday, Feb 2nd, 2005, the match key might have all this:

Daily

Weekly Wednesdays

Monthly Wednesday 1

Monthly Day 2

Yearly February 2

So with the relationship built of both the date range, and the recurrence period, you should be able to see related records in the specified range. For an open-ended range, you would simply close the range with a date far into the future.

Well, that's my theory anyway. Let me know what you think.