Relating to a Range of Dates

[johnrh]

I have a parent file which has a specific unique date field. One record for each date for 2004

I also have a child file which holds info regarding training seminars. These can be over 1 day or several days (upto a week). The child file has "Start Date" and and "End Date" fields.

I need a portal that will show any child records for seminars which are active on that particular parent record date.

Obviously there are three possibilities -

1) It will start on the parent date

2) continue on the parent date

3) end on the parent date

For example if there was a seminar from 12/2/2004 - 15/2/2004 then the seminar details would showe in the portal for all parent dates from the 12/2 to the 15/2 inclusive.

But i have no idea how to set up such a portal that will capture all these three possibilities.

This is really important as we are getting info ready for customers so if anyone can help I would really appreciate it.

Many thanks in advance

John

[Fenton]

I think you could go with just a calculation in the Child file, stored, result text (result looks like numbers, 'cause that's how FM stores dates internally). Match it against a Date type field (or another such calc).

This covers 1 week, but you can see how it can be shorter or longer. Use a text editor to find/replace your date field names (if different).

DateBegin &

Case(DateEnd - DateBegin > 1, "

[johnrh]

Thanks for hte quick response but I must still be doing something wrong cause I cant seem to make it work.

Example.

The parent file has a date 1/1/2004 which is 781581 in number format

Using your calc in a field called datejoin for a seminar which runs 1/1/2004 to 4/1/2004 The datejoin field finishes up with the following numbers

731581

731582

731583

731584

(all text, stored as suggested.

This is then used in a relationship to the parent date field (contianing 781581)

but obviously they do not match and so the records are not shown in the portal

So in short I'm still lost. So if you can see what I'm missing I would be very gratefull.

Many thanks

John

[ESpringer]

John,

The parent file's date shouldn't have an 8 as the second digit -- that's quite a few years away; the child file has the date range right.

[Fenton]

That's 11/23/2140 I believe. Also, the Parent's portal relationship is going to be from the single date field to the Child calculation field (single Date to multi-line Text w/date numbers is fine; 'cause FileMaker stores dates as the number).

You could also have a relationship from the Child to the Parent, but that doesn't seem useful in this case; or from one Child's multi-line to another's, but that's more useful as a conflict testing mechanism, but that also doesn't seem to apply.

[underhat]

Hi johnrh,

Looks like you've had some good advice already. Another option to consider is Mikhail Edoshin's SmartRanges. The advantage is that the calculation in the child file will work for any date range; you don't have to specifically make it work for up to a week, or up to a month, etc. as in Fenton's technique. The disadvantage is that it's more complicated.

See:

http://www.onegasoft.com/tools/smartranges/1stEdition.shtml

http://www.onegasoft.com/tools/smartranges/index.shtml

for two explanations. I prefer the first. In the first example, your child file would be what he calls "Main", and your parent file would be "Numbers" or "Dates". (What you want is the reverse of the example he gives.) You ought to be able to just copy and paste the calculations without having to fully understand them. You also want to read the part midway down the page called "How to link a single date (number) to ranges".

[johnrh]

Many many thanks to everyone - works like a dream now. I checked out the Mikhail Edoshin's SmartRanges files and now i have some serious reading.

Thanks again

John