3 Replies Latest reply on Oct 15, 2008 2:28 PM by davidhead

    How can I create a 'dashboard' with varying queries on one layout?



      How can I create a 'dashboard' with varying queries on one layout?


      Hello - I have created a sales activity database that includes account detail, rep assignments, marketing events, etc.  I would ultimately like to create a dashboard-like view for some information within the database.  


      For example, on one screen I'd like to see information such as the total number of active accounts in the system, the current quarter forecast, upcoming events, etc.  Some of this information is related to other data, whereas some is in a table on it's own.  To get a single view of this detail, I would effectively have to create multiple queries and display the results in various regions on the layout.


      Does FileMaker support this?   I can imagine doing this in a custom web page with ODBC queries back to the database, but I'd rather do this within the FileMaker application.



      Mark Tepsic


        • 1. Re: How can I create a 'dashboard' with varying queries on one layout?

          Hi Mark

          Yes this can certainly be done. The details of exactly what you want to do will need to be worked out but here are some examples.


          Most developers would create some kind of Dashboard table. That table will have certain fields to enable relationships to other tables and calculations of various results.


          Example:  display total number of active accounts

          We would assume here that there is a way to determine if an account is active - let's say that it has a status field with the word 'active'. So in the Dashboard table we create a calculation field, constant_active, with a text result = "active". Then we create a relationship to the accounts table where Dashboard::constant_active = Accounts::status. Then we create another calculation field in the Dashboard table with a number result = Count ( Accounts_active::AccountID ). Display this field on your Dashboard layout.


          Example: display the current quarter forecast

          I have no idea how you are deriving that so cannot help on that one!


          Example: display upcoming events 

          We would assume here that there each event has at least a start date field. In the Dashboard table, we create a calculation field, today, with a date result = Get ( CurrentDate ) , and make the result UNSTORED. Then we create a relationship from the Dashboard table to the Events table where Dashboard::today ≤ Events::StartDate. Then we create a portal on the Dashboard layout using that relationship and displaying event information.


          Effectively, you are setting up ODBC type queries using relationships and then calculating results or displaying records from those relationships.


          Simple? Any questions just ask. 

          • 2. Re: How can I create a 'dashboard' with varying queries on one layout?

            David -


            Great info, thanks!  I never considered creating a separate table to aggregate information from other tables.  I think I understand this concept, although I'll have to play around with the relationships a bit.


            Another question - is there a way to graphically display some of this information?  For example, if I wanted to show a pie chart with the number of accounts that meet a specific critieria (let's say forecast value 0-100k, 101k-250k, 250k and up) where the total is 100% of the accounts.





            • 3. Re: How can I create a 'dashboard' with varying queries on one layout?

              Yes of course. ;)

              Creating charts is done either with one of the charting plugins available or by using web technologies such as Javascript or Google Charts through a web viewer. Either way, you first need to generate in this case, three figures that will be the basis of the chart.


              Each figure will be a calculation result, which will be a count of records through a specific relationship.


              First you need fields in the Dashboard table to store your two cutoff values - 100,000 and 250,000 - I will call these cutoff1 and cutoff2. 


              Next you need a relationship for your lower range (0-100K). Something like:


              Dashboard::cutoff1 ≥ Account::forecast


              Create a second relationship (new table occurrence) for the middle range:


              Dashboard::cutoff1 < Account::forecast

              Dashboard::cutoff2 ≥ Account::forecast 


              Then a third relationship (new table occurrence) for the upper range:


              Dashboard::cutoff2 < Account::forecast 


              Looking through each of these relationships will show all accounts in each specified range.

              Now you need the calculations to count up the account records for each:


              countLowerAccounts = Count ( lowerAccount::AccountID ) 

              countMiddleAccounts = Count ( middleAccount::AccountID ) 

              countUpperAccounts = Count ( upperAccount::AccountID ) 


              With those three figures in hand, you can pass them to whatever graphing tool you use and produce your pie chart.


              The technique is somewhat dynamic in that you can adjust the cutoff field values and alter the counts.