1 Reply Latest reply on Jul 23, 2013 10:44 AM by philmodjunk

    Summary By Value List



      Summary By Value List


      Hello-  I have a small database a friend of mine asked me to build.  He is a home builder and wants to keep track of his actual costs versus the budget he set forth.  My problem is this.  When he enters a new item, say "Spool of Wire" and the price, he then selects the category that it fits under from a dropdown list.  The value list contains all of the categories (Electrical, Flooring, Framing etc.).  I need to list the value items with a summary next to them showing the total of that category and then a grand total at the bottom of all the categories together.  This needs to be done on the dashboard layout so he can quickly glance and see where things are at...  I thought of using a portal, but I am not sure how to group the records as one for each category with a total.  I don't want a list of every item, just the category followed by it's total and I don't want to use the reporting layouts, I want to use a standard layout...  The other problem is that if he adds a new category to the value list, I need it to be added to list on the dashboard... How can I go about this.?


           My database consists of two tables - Renovation and Budget.  There are two additional table occurances for review lists using portals (Reno2 and Budget2).

        • 1. Re: Summary By Value List

               There are several possible approaches. One that you may want to consider for contexts other than your dashboard layout is to set up a list view summary report with a Sub Summary layout part "when sorted by" your category field and with the category and summary fields placed in your sub summary part. Such a report can list the indivdual records or you can remove the body part and just list the categories and their totals. This is simple and flexible, but probably not what you want for a dashboard layout where you likely have many other layout objects not part of these totals.

               For the following alternative methods that should work for a dashboard layout, I recommend setting up a table where you have one record for each category. You can define your value list to list values from this table and you can add/remove/change your categories by editing the records in this table.

               If you have FileMaker 12 and aren't afraid to try your hand at using SQL, the ExecuteSQL() function can be used: FMP 12 Tip: Summary Recaps (Portal Subtotals)

               Without using SQL, you could set up a portal to your table of categories and use a relationship from that table to your table of expenses that match by the category field to compute a total for each category.

               With both approaches, some thought should be given to how you will set up criteria that further filter down the records used to compute these totals so that they compute totals for specific date ranges such as all entries for the current year, the current month and/or the current quarter. Whith Execute SQL, this can be specified in a WHERE clause. With a portal to the categories table, a portal filter or a global match field can be set up to get the same "filtered result".