There are different ways to compute those sub totals. What method are you trying to use in the example shown here?
Am I correct that a given bid will only have a related contract info record if that bid is the winning bid? Or do all Bid Info records link to contract info records?
And the table outlined in red has one and only one record for each state?
Your answers to my questions may change my answers, but to get a total for each state will need a different set of relationships that match to Contract Info records by company AND state.
That was fast!
To answer your questions, everything essentially starts with a contract. Each contract can have multiple bids but only 1 winning bid. Each bid can have 1 or more companies who are working together (such as a joint venture). Additionally each company can have multiple bids (ie bid on multiple contracts). That's why I have a join table between the bids and the companies.
That is correct; the table outlined in red does have ONE record per state.
The reason I laid the database out the way I did was to ensure the only relationships that existed were 1:Many because I was taught that 1:Many relationships are preferable. (Is this true? Or might I have been lead astray some?)
Attached is a screenshot of the State Update Report records (Highlighted in red from previous post)
If you are using FileMaker 12 or newer, you might use ExecuteSQL to produce a table of the desired states and state totals all in one calculation field instead of a portal. You'd be defining the needed relationships inside the SQL in order to produce the needed list of states and totals.
The other, nonSQL approach would be to add more table occurrences (not tables) to your relationship graph along with a global field and a script that updates the global field in order to produce the needed sub totals in a portal.
I've been playing around with that idea and it keeps producing a really complicated relationship graph.
It would be easier to use Go To Related Records from your Companies Layout to bring up a found set of Bid Info Records that you'd then sort by Contract Info::State to group them by state. A summary field place in a sub summary layout part would then show the desired sub totals for each state and if you removed the body layout part from this layout so that you only have the sub summary part, you'd get a list of subtotals, one for each state where the company made at least one bid.