Stuck on relationship problem

[GAmstutz]

Perhaps it's because I'm working too late...but I'm stuck on a relationship design of which I'm sure I've done something similar, but I just can't seem to wrap my head around this one tonight. :-(

 

If anyone can help me solve this one, I'd certainly appreciate it.

 

Please take a look at the attached relationship graph to quickly explain my schema (it's the first image below).

 

In summary, I have four tables, a table with a list of features in all stores, a join table joining the feature records with store records.  In the join table, there is a count field where I store the total count of that particular feature in that one particular store.  And finally I have a list of divisions, each which contains one or more store.

 

A sample layout is attached showing my design problem.  I'm wanting the user to go to a feature record (find the 'Feature' record that they want to view), and then that 'Feature' layout will have a portal showing all the 'Divisions' that have stores that have one ore more of the user's chosen feature.  

 

Here's the rub...I need to display exactly HOW MANY of the current feature are in all the stores of each division in each row of the portal.  The number of the current feature should be calculated from the 'count' field in the jointable, but I can't seem to setup the SUM(  ::count) calculation field correctly, and am not sure which Table Occurrence should contain that calculation field.

 

I have no problem showing the Divisions...that's simple.  And I'm sure it's simple for someone that does this frequently.  If someone understands this relationship issue well, can you show me where the calculation field should go (which table, Evaluate this calculation from the context of , and the current table of that calculation field, etc.)

 

I've spent far too long stuck on this problem, and am appealing for help!!!

 

Thanks in advance!!

[Vaughan]

Use plain words: what is a feature, store, division?

 

I'll guess:

 

There are DIVISIONS of a company; a DIVISION can have one or more STORES; STORES can have one or more FEATURES.

 

To make this work you need a table that contains records which will look like this:

 

Division, Store, Feature

 

I'd say that this is the features_JOINTABLE.

 

Rather than use a portal to display what you want, use a sub-summary report based on the features_JOINTABLE TO, and sort by Division, Store, Feature.

[GAmstutz]

Vaughan,

 

Yes, you guessed right.  Sorry I wasn't more clear..  The schema is indeed:

 

There are DIVISIONS of a company; a DIVISION can have one or more STORES; STORES can have one or more FEATURES.

 

In my example, I used a Feature record of a 'cash register'.  One store may have 10 cash registers, another 12, and a third 15.  If all three of those stores were in one division (a geographic section of the country), then that division needs to show 37 total cash registers.

 

The count of cash registers per store is stored in the jointable that joins each store and feature item.

 

Your idea would indeed work.  However, I'm needing to include several portals on the feature layout (not just this one), so a list report with a sub-summary part won't work for me.  I'm needing to have a portal of division records within a layout of features, and each row in that portal needs to show the name of the division, and the total count of the feature of the current record.

[GAmstutz]

P.S. - I've already designed the database, and it's in use by the client.  I have four tables that are important in this schema:

1. Divisions (geographic sections of the country: Southeast, Northeast, Midwest, Southwest, West Coast, etc.)
2. Stores (one or more store per division)
3. A join table that joins many stores to many 'features' of each store
4. Features (i.e. cash registers, seats, light fixtures, etc)

My 'real' database has hundreds of fields per table.  I excluded all of them except the relevant fields in my first attached Thumbnail image in my original post.  I only left the fields that were relevant to this topic.

 

I can't really consider redesigning the database, and clumping all the fields into one table, as you suggested, Vaughan (if I understood you correctly).  I also need to stick with the portal shown in the layout in Tumbnail #2 (of which the layout is based on the Features table).  I cannot switch to a layout that is a List View and do a sub summary to calculate each total count per division because I need to add other portals to this same layout (again, I included a simplified layout, not the full original one, including only the portal and field that were relevant to this topic).

 

What I need to know is how to display the feature count in each division row.  Sounds simple, but for me right now, it's not.  :-)

 

I presented the question to be very similar in style to one of the design questions in the FileMaker Certification exams (i.e. I included a screenshot of a simplified Relationship Graph, and then included a simplified layout of a Form view as image attachments to the original post).

[Vaughan]

I can't really consider redesigning the database, and clumping all the fields into one table, as you suggested, Vaughan (if I understood you correctly). 

 

No, you did not understand me correctly.

 

I said that the report needs to be generated from a table that has all those fields (or can pull in the related information). The features_JOINTABLE has all those fields, or can pull the data in from related tables.

 

Sometimes it's very hard to display data through portals: often you need "portals-within-portals" which FMP cannot do. In those cases a summary report does the trick, but it's not quite the same since it needs to be generated and is not a live display.