Jump to content

Smart Ranges Custom Functions


This topic is 7047 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

(

[

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

  • 4 months later...

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.

Link to comment
Share on other sites

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

(

[

Link to comment
Share on other sites

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(

Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

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().

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7047 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.