Searching Question

[T-Square]

I don't know where to place this question. I have a layout with a date field from a related table. The relationship is one client (current file) to many (related table) service dates. The relationship is sorted in reverse chronological order, so the latest date is the one that displays on screen, showing me the last scheduled date of service for each client.

Now for the wierdness. I want to be able to search for people with a particular last service date. When I put in a single date, the search works pretty well. However, if you use the Filemaker range feature (11/15/2005..11/30/2005), the software returns every client with a date later than the first date.

I'm thinking that what is going on is that even though the layout only shows the latest date in the sequence, the find is actually using the entire set of related records. So any client who has a service within the range is returned, even if their last service is in 2007.

Am I getting that right, and if so, how best should I work around the situation?

TIA,

David

[Ender]

I'm thinking that what is going on is that even though the layout only shows the latest date in the sequence, the find is actually using the entire set of related records. So any client who has a service within the range is returned, even if their last service is in 2007.

Yes, this is how searches on related fields work.

You could define a Last Service Date calc in Client that pulls the last related date:

Last Service Date (calculation, date result) = Service Date::Date

A search on this should work, although it may be slow on large record sets. If you need a faster find, you could perform the date range search in the Service Date table, and sub-summarize by Client, or jump back to the related Clients.

[T-Square]

Thanks. I'll try one of these.

[T-Square]

Ender--

Your fix "worked," although the search is, as you hinted, much slower. I was hoping not to have to build a new search routine just for this feature, but I will have to consider this.

David