What's the relationship/report here?

[Kingme]

Okay I've got ONE database with the following fields:

[Theatre]

[MovieName]

[Date]

[Gross]

I would like to create a report that looks like this:

[Theatre]

     [MovieName] [FRIGross] [satGross] [sunGross].. (etc)

There's multiple theatres, so the report should group it by that first, then there's mutiple MovieNames and the corresponding days and grosses.



Here's some real data so you can see:

                    [FRI]  [sAT]  [sUN]  ...etc

[sUNSET 5 theatre]

    [batman Begins] [$125] [$450] [$325] ...etc

    [The Thing]     [$45]  [$55]  [$100] ...etc

  

[MONICA 4 theatre]

    [Capone]        [$152] [$789] [$999]...etc

    [Men in Black]  [$78]  [$34]  [$45] ...etc

Any help would be appreciated. Thanks

[Ender]

I would recommend adding another file where each record is about each Movie at each Theatre. This will make it easier to get totals for each particular Date (rather than just each day of the week.)

Your current file is really about the gross for each Theatre, for each Movie, for each Date. This is a good thing, as it makes it easier to run a variety of reports (if your data were shoved into repeating fields or lots of fields in the same record, it be much harder to deal with.) Let's call your current file "Theatre_Movie_Date" since this describes its records.

So we have a Theatre_Movie file and a Theatre_Movie_Date file. Now by adding some fields to each, and relationships to tie them together, we should be able to get summaries in the format you specified.

These should be the fields in Theatre_Movie:

Theatre (text)

MovieName (text)

gSelectedDateMonday (global, date)

cTheatre_Movie_DateMonday (calculation, text result) = Theatre & " " & MovieName & " " & gSelectedDateMonday

cTheatre_Movie_DateTuesday (calculation, text result) = Theatre & " " & MovieName & " " & numtotext(gSelectedDateMonday + 1)

cTheatre_Movie_DateWednesday (calculation, text result) = Theatre & " " & MovieName & " " & numtotext(gSelectedDateMonday + 2)

cTheatre_Movie_DateThursday (calculation, text result) = Theatre & " " & MovieName & " " & numtotext(gSelectedDateMonday + 3)

cTheatre_Movie_DateFriday (calculation, text result) = Theatre & " " & MovieName & " " & numtotext(gSelectedDateMonday + 4)

cTheatre_Movie_DateSaturday (calculation, text result) = Theatre & " " & MovieName & " " & numtotext(gSelectedDateMonday + 5)

cTheatre_Movie_DateSunday (calculation, text result) = Theatre & " " & MovieName & " " & numtotext(gSelectedDateMonday + 6)

This creates a key field on the Theatre_Movie side, so we can later create a relationship for each day of the week.

Now in Theatre_Movie_Date, we need these fields:

Theatre (text)

MovieName (text)

ShowDate (date)

Gross (number)

cTheatre_Movie_Date_Link (calculation, text result) = Theatre & " " & MovieName & " " & ShowDate

We'll next need to add several relationships to the Theatre_Movie_Date file from the Theatre_Movie file:

Theatre_Movie_Date Monday =

Theatre_Movie::cTheatre_Movie_DateMonday = Theatre_Movie_Date::cTheatre_Movie_Date_Link

Theatre_Movie_Date Tuesday =

Theatre_Movie::cTheatre_Movie_DateTuesday = Theatre_Movie_Date::cTheatre_Movie_Date_Link

...etc., etc.

Now to add the summary calcs. In the Theatre_Movie file, add a calc to sum() each relationship's related Gross:

cTotalMonday (calculation, number result) = Sum(Theatre_Movie_Date Monday::Gross)

cTotalTuesday (calculation, number result) = Sum(Theatre_Movie_Date Tuesday::Gross)

...etc., etc.

Now you can use a Columnar Report layout in Theatre_Movie, with the MovieName and each of those calcs on the Body part, and add a Sub-Summary by Theatre to get the Theatre name listed above each group.

[Kingme]

Brilliant, absolutely brillant!

Just a note: I've changed the "Global Monday field" to a "Global Friday field" since in the biz, weeks are Friday to Thursday.

This solution would also work for people looking for Horizontal Portal fixes in Filemaker 6 or below.

Thanks again.

[Ender]

Thanks, I do what I can. :

This type of summary works when a traditional summary doesn't show the data in the desired layout arrangement, BUT the performance is not very good, and it's more work to put together. It may be too slow over a WAN, especially with lots of related records.