Aggregate, summary, find...?

[saulspicer]

I'm trying to get my head around how to do the following.

I have two .fp5 databases, one of TV programs (each record = a particular episode), one of schedule events (each record = one airing of one show at a particular start/end time and date). They're related by a unique program ID field in each -- schedule event records get created via a portal in the program database and the ProgramID field in the schedule database is auto-populated with the appropriate programID for that particular show. And the relationship specifies a sort order of date and then start time, from earliest to most recent in each case. It's a one to many relationship, since a single show may air several times over different days.

What I'm grappling with is this: I want to get a list of all shows that first aired in a given year, sorted by date. I optimistically (stupidly) thought maybe I could just put the related date and start time fields from the schedule db onto a layout in the program db, and since it would only show the first related record (the one I want to boot, because of the sort order), I'd be able to do a find on that (using appropriate date limiters in the date field to say less than or equal to the given year's 12/31, and greater than or equal to the given year's 1/1), but that has two problems: a) the fields' indexes don't appear to be accessible because the fields are in a related database, so the search takes a long time, and I get strange results in any case, not even limited by the date parameters I set.

I've also tried creating a FirstAirDate field in the programs database, and making it an auto-enter calculation using the aggregate function min referencing the date field from the schedule database, but it doesn't auto-enter any results.

Any suggestions would be much appreciated. Thanks!

Edited May 13, 2008 by Guest

[Ugo DI LUCA]

Hi,

Welcome to the forums.

"I want to get a list of all shows that first aired in a given year, sorted by date"

This date field may be an auto-enter lookup field, based on your relation. Just relookup to get the first related date back into the TV Program file.