8 Replies Latest reply on Jun 11, 2015 4:50 AM by MicheleOlson

    Summary data in a portal

    tmslayton

      Hi,

       

      I am creating a dashboard and would like to display the top n records grouped in a portal. In SQL I can do this in about 10 minutes. I am wondering how to approach this in FileMaker.

       

      I have two tables:

       

      Vehicles > Services. Services contains the repair and maintenance records for each vehicle. I would like to summarize the repair information by vehicle and order by total repair cost in descending order and limit the results to the five most costly vehicles. Basically I want it to look like this:

       

      Vehicle                      Cost

      -----------------     -------------

      2008 Camry       1,000.00

      2005 F-150           950.00

      2012 Sentra          545.55

      2015 Camaro        344.34

      2014 Tacoma        122.44

       

      I know there must be a way to do this.

       

      Thanks,

       

      Tom

        • 1. Re: Summary data in a portal
          nicolai

          If you are more comfortable with SQL, you should probably do it in SQL. Is there a particular reason you need to use a portal (e.g. you want to click on a raw to go to the Vehicle record)? If it is just a style issue, you can load the results in a webviewer as html table.

          • 2. Re: Summary data in a portal
            nicolai

            Sorry, I just realised, I did not actually answered your question for FileMaker solution with no sql

             

            The usual approach:

             

            Add a calculated field to Vehicles table to calculate total costs of Services agains every vehicle

             

            Add a self relationship to Vehicles table, cartesian (X) and sort it by your calculated field.

             

            Add a portal based on the new relationship, no scroll bar and display 10 rows only.

             

            If you have a lot of records in both tables, this is going to be very slow.

             

            An alternative is to run a script at set times (e.g every hour) to rank your Vehicles records - e.g. sort them by total cost, flag top ten and create a relationship to display the flagged records only. I would also calculated the Services costs as a part of this script instead of calculation on a field.


            • 3. Re: Summary data in a portal
              Mike_Mitchell

              A third alternative:

               

              Create the Dashboard as a single-record table (or perhaps a Users table, depending on your need). Create the relationship from there to Vehicles as either a Cartesian join or via a global field. Create the total field in the Vehicles table as a number field. Use Script Triggers to update that field whenever conditions warrant (such as when a repair / maintenance record is added / updated). Then, your dashboard can sort the Vehicles records on an indexable field, which will be very fast, irrespective of the number of maintenance records added.

              • 4. Re: Summary data in a portal
                tmslayton

                Thanks for your responses I'll give them a try. I watched a video on youtube for creating dashboards by the Anvil Dataworks / FM Academy but the author didn't cover how he was able to create the top 12 records in portals. He had two of them on his dashboard. I'll give your suggestions a try and see how I make out.

                 

                Best,

                 

                Tom

                • 5. Re: Summary data in a portal
                  beverly

                  Portal(s), filtered or not, can definitely display summary fields. see my article on how I did this. you may or may not include ExecuteSQL(), but I simply used summary fields (from the child data). Along with the other answers here, you may have some good ideas.

                  Aggregates (Summary Fields) in Filtered Portals | FileMakerHacks

                  beverly

                  • 6. Re: Summary data in a portal
                    tmslayton

                    This seems like a good idea. How do I access the buffered field values in FM? So if a person makes a change to one of the records I'd need to subtract the old value and then apply the new value to the total.

                    • 7. Re: Summary data in a portal
                      Mike_Mitchell

                      Correct. One method you can use is to use the OnObjectEnter trigger to capture the original value in a variable. Then, use the onObjectSave trigger to compare the old value to the new one. If they're different, you can take the difference and apply it to the total.

                      • 8. Re: Summary data in a portal
                        MicheleOlson

                        What a fantastic demo!!! Thanks so much Bev for sharing.