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

(

[

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

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.

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

Thank you for your efforts.

Ralph

Attached is a demonstration file.

SmartRangesCF.fp7.zip

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

Oops. Thanks for catching that, Roger. Fixed.

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?

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.

Thank you Ender.

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

Thank you for your efforts.

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.

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

(

[

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

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!

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(

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

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

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

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

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

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

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.

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

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.

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.

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

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.

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

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

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.

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.

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

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.

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

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.

Perhaps I should outline my purpose a bit. I am after a scheduling/booking solution, where:

1. Events can be both ranging AND recurring.

2. Conflicting events must be flagged.

3. Confirmed events are billed (this means that recurring events must not extend backwards).

4. Single occurances of a recurring event can be deleted/re-scheduled.

This is what I started with, and now I am gradually lowering the specs, as I run into FMP limitations.

You have surprised me by suggesting I "still use the FM ranges (or SmartRanges) for the date range part". Let me put SmartRanges aside for a moment, and state the question thus: can this be done without spelling out the actual dates? After all, SmartRanges is but a method to compress the information, AFTER dates have been spelled out.

As I stated earlier, I don't see how it's possible to relate two ranges by start and end alone. I believe this would require compounding OR criteria, which FMP cannot do. Compounding OR criteria CAN be done by multi-line keys, which in this case means - spell out the dates.

The other specifications lead me also to the same conclusion. I don't see how a "smarter recurring key" would deal with the problems I raised earlier:

- it too extends backwards;

- it cannot catch a conflict between two events defined in a different way but conflicting on a day that satisfies both criteria.

Moreover, since you are on Mac, take a peek at iCal's capabilities in defining a recurring event - esp. the Custom options. Of course, I realize that I will be never able to match that in FMP. I have already given up on the option of having a recurring event that never ends. But even a simple "every two weeks" seems impossible to do in this fashion.

Finally, the 4th specification leads me to conclude that dates need to be spelled out by a script, rather than by a calc. Although I have a rather neat calc using a repeating field, it's only by putting the dates in a text field that can I provide the ability to manipulate individual results.

Thoughts, comments, contradictions - please!

Just my two cents,

If you don't store the actual dates for recurring events, you can't always (or

even usually) flag which specific dates conflict, just know that they do (and

which event first conflicted -- if it is a specifically dated event, you know

the actual date).

The way I am thinking to store recurring events without every date is with

modulus operations ... you need a few calculations for regular years, leap

years, per type of recurrence (like On_day_of_week, monthly, etc), and a single

calc per type to deal with offset from the start/end range of the recurrent

event to the actual first date the event occurs. You store the actual first

date, and the given end date. When scheduling a new event, you iterate through

the recurrences, run the mod for this type of recurrence against the date index

(starting at the start date for the new event) and again against the actual

first date of the proposed schedule: pray for different numbers. You continue

to do this and increment the dates by the period of the recurrence until you get

to the same index in the recurrence (usually 400 years is the largest period in

the calendar, unless you're really scheduling ahead!). --That's not as bad as

it sounds, since to compare this proposed event to all other events, each

comparison is 2*400*recurrences_per_year -- never bigger than ~292,200 .. which

is not a lot of processor time with simple math equations.

You can find the modulii necessary in a good algorithms book ... or maybe try

googling words like modulus/modulii, algorithm, calendar/schedule, dates. Or,

you might want to go to CPAN.perl.org, or a php library site, and look at the

date modules. Perl is often written rather like a short-hand of a normal

algorithm notation. Find a module that schedules, skip around to the comments

for the functions you are looking for, and look for the <number>%<number>

sections.

You can actually find the specific date that clashed ... my math's a bit rusty

(I came back and edited when I realized the modulus result itself is not what

you don't want to match, that doesn't matter, it is that result applied to the

date index). That resultant date is the date that clashed.

For your fourth requirement, you needn't store the calculated result. You do

need to generate events of specifically queried dates; for the layout or

whatever GUI you've in mind, and that element of your code needs to check an

exceptions table and exclude certain records/generated text before passing

control to the user.

Cheers

Hey comment,

Lots of good stuff. Let's see if we can't figure some of this out.

As I stated earlier, I don't see how it's possible to relate two ranges by start and end alone. I believe this would require compounding OR criteria, which FMP cannot do. Compounding OR criteria CAN be done by multi-line keys, which in this case means - spell out the dates.

Range to range relationships can be done natively in FM7 with the example I showed before. However, I was thinking this range to range relationship would be used for defining the date range of recurrences when trying to add a new event. So when adding an event that recurs every Friday from today on, the range would be from today through 12/31/4000 (or some such,) If there are no conflicts in that range, then the record gets added, with a recurrence match field that will let future additions know that Fridays are already taken from 3/7/2005 through 12/31/4000.

I'm not sure how to fit an event that lasts more than one day into this recurrence theory of mine.

The other specifications lead me also to the same conclusion. I don't see how a "smarter recurring key" would deal with the problems I raised earlier:

- it too extends backwards;

- it cannot catch a conflict between two events defined in a different way but conflicting on a day that satisfies both criteria.

Using the range-to-range relationship starting from the first date of the recurring event should stop it from looking backward. And I think if the match key always contains all of the possible recurrence types, the conflicts would be seen.

Moreover, since you are on Mac, take a peek at iCal's capabilities in defining a recurring event - esp. the Custom options. Of course, I realize that I will be never able to match that in FMP. I have already given up on the option of having a recurring event that never ends. But even a simple "every two weeks" seems impossible to do in this fashion.

I think the trickiest things to reproduce would be an event that last more than one day, and events that recur every n days/weeks/months. I still think these types of recurring events are possible with a clever calc for the match field (I'm not sure how yet.)

Finally, the 4th specification leads me to conclude that dates need to be spelled out by a script, rather than by a calc. Although I have a rather neat calc using a repeating field, it's only by putting the dates in a text field that can I provide the ability to manipulate individual results.

Yes, this one is tough. I think a recurring event that gets changed would need to be pulled out of the recurrences to become a separate event. So maybe do this by 1. adding a new record defined for that date with no recurrences that has all the other info that the original event had, and 2. having another criteria in the relationship that does an exclusion of records for that date. I haven't worked out how all this would work, but I'll see if I can get something going.

I need to ponder on this for a while, but one thing sticks out:

If I want to display events in a calendar fashion, I need the actual dates for the current calendar period. I don't recognize the mechanism you describe in the last paragraph. That is, I do - but not in Filemaker. I know that's how it's done in other applications, where the events table holds only the rules, and everything is rendered on-the-fly.

Filemaker is not that good a browser. If every time I shift the calendar's month I need to run a script to generate the dates of ALL events for the current period, I may be better off pre-generating them once and for all.

I think we are still not understanding each other regarding the range-to-range relationship. Let's forget recurring events for now, and consider an event that starts on StartDate1 and lasts n days, thus ending on EndDate1.

Now we have a new event that we'd like to schedule, this one starts on StartDate2 and lasts m days, thus ending on EndDate2.

The 6 possible juxtapositions of the two events are shown on the graph above.

I don't see how I can build a relationship that will show a conflict, using only these four fields. That was my original question in this thread.

Using the range-to-range relationship starting from the first date of the recurring event should stop it from looking backward.

OK, that should be possible - like:

Wednesday = Wednesday

AND

CurrentDate >= first occurence

AND

CurrentDate <= last occurence

But what if, instead of last occurence, the user prefers to specify number of occurences (repeat every week for 5 repeats in total)?

Again, it seems to me that generating the actual dates is the lowest common denominator to all the possible scenarios.

But by all means, do continue to attack my position.

Putting the calendar overview aside for now, I would use the range to range relationship for identifying conflicts while adding the new event. As I said before, this covers all the possible ways the ranges can overlap. See the attached example.

Unfortunately, I don't think there's a good way to get this Date Range to Date Range thing to work with recurring events.

I'll work on an example of the recurring events for events that do not span multiple days.

I should think specifying the number of repeats rather than the End Date should be simple enough; just calculate it based on the Start Date, recurrence type, and number of repeats.

OK, point taken and humble pie eaten. I thought you were referring to SmartRanges, when you described this relationship earlier.

Still, the other issues weigh heavily in favor of multikeys.

Mike the template doesn't behave absolutely correct - say test 8th of july 2002 to 9th of july 2002, this produces a portal full of conflicts, but the same and more conflicts would be likely to show up in the portal if you removed the 8th ...instead does the portal clear???

--sd