4 Replies Latest reply on Feb 22, 2012 7:14 AM by MattLeach

    Creating a Summary Layout

    MattLeach

      I've been asked to create a layout that is an overall summary of certain information in the database.

       

      I have 2 tables which include the data that needs to be summarized:

      Transactions

      Properties

       

      These tables are related.

       

      The summary layout needs to include things such as:

       

      Count of Transactions

      Count of Transactions in past two weeks

      Sum of transactions for 2011, 2012

      Sum of sales by property type (sale price in transaction, property type in properties)

      and various others

       

      Would i be safe in assuming that the layout would needs it's own summary table, somehow related to the other two tables and then use filtered portals to display information such as 2011 transactions, 2012 transactions, etc...

       

      Or would i be better served created the layout based on the transactions table using summary fields?

       

      Kind of new to this type of layout, just want to make sure i do it the appropriate way to avoid issues down the road.

       

      Thanks

        • 1. Re: Creating a Summary Layout
          karendweaver

          Hi Matt

           

          If this is just a dashboard display that doesn't require interaction, and especially if it does not have to be updated live, then you could capture the data in $$variables on startup and just display it on any layout you choose. 

           

          A simple script to perform finds and populate the $$variables on startup would be fast and easy.

           

          If the data needs to be updated during the day - so you have a live result every time there is a new transaction, then this method would not work as well, although you could still do an hourly update or a server side update script. 

           

          You can certainly base the layout on the transaction table and use summary fields as well, but you may run into performance issues if there are many summary fields or unstored calcs on a layout, especially if there are any users with WAN access trying to view the data.

           

          Hope that helps!

           

          warm regards,

           

          Karen

          • 2. Re: Creating a Summary Layout
            MattLeach

            I could certainly do a startup script althought at this point i do not know if it needs to be live or not. I could always add a button that would basically re-runt he script to get updated information.

             

            Server side scripting is out as the users will be running the database through IWP with FM Gateway as their host and do not see an option in their control panel to setup scripts.

            • 3. Re: Creating a Summary Layout
              techt

              Matt,

               

              If you haven't tried using the Report layout format (from New Layout), you should give it a try. It certainly has made some of these reports much quicker to generate, it'll even guide you through summaries and grand total calculations, including adding fields if needed, and will initialize a script as well at the end.

               

              Looks like transactions would be the table from which to start this report.

               

              HTH,

               

              Tim

              • 4. Re: Creating a Summary Layout
                MattLeach

                Just as a follow up, i decided to go ahead and create a summary table with a single field. I then did a cartesian join to the transactions table.

                 

                On the summary layout i've put together a few filtered portals to display the information needed.

                 

                I don't forsee much or a performance issue as the database will be accessed by 5 users at the most with only 1 or 2 having access to the summary.

                 

                Thanks for all the replies.