3 Replies Latest reply on Jul 23, 2014 1:43 PM by philmodjunk

    Show Related Record Totals in Portal

    ZakButcher

      Title

      Show Related Record Totals in Portal

      Post

           This may be a slightly complicated topic, however I will include pictures and do my best to explain what I am trying to do.

           I have a database that tracks bid results across the US.  On one layout I have a portal that I want to display the states that a company won bids in as well as the total bids from that state and the total amount those bids were worth.  The portal is accurately pulling the states in which the company made bids however I am only able to get the total bids across all states or the total bids for each state across all companies.  These two scenarios are shown in the pictures.  Also I didn't change the field that gathered the total bid amounts because it is behaving identical to the total number of bids field.  If someone can help me fix one then I can fix the other.

           I have included a picture of the database structure as well as the portal results I am getting.  If I didn't explain something clearly or if I need to explain something please let me know.

           NOTE:  The blue box highlights the table on which the portal is located and the red box highlights the table which the portal takes information from.

      Submission_Picture.png

        • 1. Re: Show Related Record Totals in Portal
          philmodjunk

               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.

          • 2. Re: Show Related Record Totals in Portal
            ZakButcher

                 Phil,

                 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)

            • 3. Re: Show Related Record Totals in Portal
              philmodjunk

                   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.