Jump to content

Relating to a Range of Dates


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

Recommended Posts

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


Link to comment
Share on other sites

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, "

Link to comment
Share on other sites

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


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





(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


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.




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

Link to comment
Share on other sites

This topic is 7501 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.