Choose, Lookup or case?

[rservis]

Hi All,

Wondered if you may be able to help with a problem I have, I have to update lots of activities with a release date, the release date is important so we know we cannot schedule anything past that date. The release dates are in its own table and has the following fields filmID, Film Title, Territory, the release date and projectID. The filmID is related to the Films table and from the films layout I have a portal showing when that film is being shown in a particular territory.

I also have a project table and I can schedule activities to a particular film, within this table I have a field called release date and I would like to have the field automatically update itself, when you create a new activity you choose the Project, then Territory all via value lists.

Release date Table Record 1:

FilmID: 1234

FilmTitle: test

Territory: United States

Release Date: 1st July 2008

ProjectID: 22222

Release date Table - Record 2:

FilmID: 1234

FilmTitle: test

Territory: Japan

Release Date: 20th July 2008

ProjectID: 22222

So if I choose new activity for the film test and then choose United States the release date field automatically enters 1st July 2008, if Japan 20th July 2008 and so on - and if you updated the release date because the date changed, all records would be automatically updated as well,

I'm sure Filemaker could do this, maybe via a calculation and a case statement - but I'm just not sure how?

I hope someone could help and apologies for the long message,

Best,

Roy

[comment]

The answer is none of the above. Activities should be related as children to the Releases table, and get the release date directly from there any time it's needed.

[rservis]

Hi Comment,

I have now related the tables Project::ProjectID - Releasedate::ProjectID and can view all relevant dates for all territorys for this film all within the project, so now when I create a new activity for this project I want it to auto update the date field because the territory matches.

Hope this makes sense,

Regards,

Roy

[comment]

There is no need for the Activities table to have its own release date field. This is an attribute of Releases, and of Releases only. Simply put the related field from Releases on a layout of Activities.

[rservis]

Hi Comment,

Thank you for replying - I have related the two tables together and put the field on my activities layout, but it stays blank?

I would like it to pull the release date in depending on which Territory you have chosen. At present the relationship is based on:

[color:red][color:red]Appointments Table Release Date Table

projectID = ProjectID

best regards,

Roy

[comment]

I am not entirely clear on your tables and their relationships - for example, what is the difference between a film and a project? The way I understood it, a project can have multiple releases (one in each territory) and there will be multiple activities related to each release. If so, your relationships should look like this:

Projects -< Releases >- Territories

Releases -< Activities

The relationship between Releases and Activities can be based on ReleaseID, or on both ProjectID AND TerritoryID.

In this situation, if you place the Releases::ReleaseDate field on a layout of Activities, it will show the date of the parent release.

If I heve misunderstood the situation, then please clarify.