Jump to content

List Function Result as Searchable Dates


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

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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