List Function Result as Searchable Dates

[xmoose]

Hey, Folks,

I need some help. I have a Courses table that links to a Calendar Sessions table, where each Course has a Calendar Session for every time it meets.

For a particular report, I want to be able to search within the Courses table for all courses that meet (not just start or end) during a given interval of dates.

My original approach was to create a calculation in Courses like:

List (CalendarSessions::Date)

to give me a field with carriage-returned dates that could be searchable. The resulting calculation delivers the desired list. Unfortunately, when I attempt to search for a date range in this field, FileMaker returns ALL YEARS for the date range (for example, if I search for 10/1/2007...10/30/2007, I get all courses that have met in October of any year, instead of just 2007.)

I'm sure this has to do with the fact that the field is stored as text, but if I store it as a date, I just get the first date and not the list.

What am I missing? This can't be this hard.

Many thanks for any help!

[Fitch]

Why not search the CalendarSessions::Date field directly?

[Fenton]

As you say, what you've really got there is a text list. So it's going to behave like a text list. As Fitch says, why not just search for the range directly? That's what the "..." range operator is for.

Alternatively you could put your desired start and end dates into global fields, create a relationship with greater than or equal to starting date, less than or equal to end date, and Go To Related Record.

[xmoose]

See, this is where it gets tricky. A course may meet once every other month over the course of a year. Yet it may not meet during the time period over which I'm running the report. So, building a relationship off of the range is meaningless (for this one report - I do use it in other places.)

Meanwhile, another course may meet several times during that period - yet I need the report only to show it once (which is why I'm searching in the Course table rather than in the Calendar Sessions table).

I suppose I can search for the calendar sessions and run the report from that table without a body using sub-summaries for Courses.

However, I'd still really like to have a direct way to do this, if possible. Relying on the sub-summary just feels sloppy.

PS - Just noted that you mentioned using the "..." operator. That's exactly what I'm looking to do - I just need the field in which to type that range. :)

Edited October 29, 2007 by Guest
Expansion

[comment]

The field to search is the date field in the Sessions table. You can do this in two ways:

Go to a layout of Sessions;

Find the records in range;

Go to related record (from Courses, match found set).

Or search the same date field on a layout of Courses in a portal to Sessions. I believe this would be slower than the first method.

[xmoose]

Comment - I'm feeling a little dumb now. I've used "GoToRelatedRecord" hundreds of times to get to records from a portal, but I've never even noticed that you could pull up a whole new matched set from a current found set.

This should work perfectly to get me a set of courses that meet in the time period that I can then constrain based on the other course criteria selected by the user.

Many thanks to all!