2 Replies Latest reply on Mar 11, 2012 7:44 AM by davidanders

    Summary Table Issue

    Lantonicus

      Title

      Summary Table Issue

      Post

      Dear All,

      I am a little bit lost and before I take further steps, I want to make sure that I am doing the right thing.

      I am developing a system that records my daily expenses (cash, credit card, debit card, Lending, Borrowing, Income, etc)

      currently, I have one big table that has all the records ( based on their daily occurences) 

      I created different  layouts related to this table that shows my daily cash expenses, credit card expenses and etc. I have summary based on daily basis, monthly bases and year basis. This is great!

      But

      I am now trying to create a layout ( or a table, this is what I am confused about) that

      summurized as follows

      year 2009  Total Cash  , Total Credit Card, Total Debit card , Total of all

      year 2010  Total Cash  , Total Credit Card, Total Debit card , Total of all

      year 2011  Total Cash  , Total Credit Card, Total Debit card , Total of all

      year 2012  Total Cash  , Total Credit Card, Total Debit card , Total of all

       

      Same for Monthly Totals as well

      Year 2010

      January,  Total Cash  , Total Credit Card, Total Debit card , Total of all

      Ferbuary Total Cash  , Total Credit Card, Total Debit card , Total of all

      .

      .

      Year 2011

      Jan   ....

      Feb ...

       

      I dont want to see daily records

      I want to summurize interms of years and months

       

      Does any body  has any suggestion what I should do?

      Do I need to create a new table that gets and totals the values in itself

      or can I use my current single table that has all the daily records in it?

       

      Thank you all in advance.

       

      Orkun

      .

       

       

       

       

        • 1. Re: Summary Table Issue
          philmodjunk

          A layout is a means for viewing and interacting with records stored in one or more of your tables. When you create a new table in FileMaker, the system automatically creates both a layout and a table occurrence for you with the same exact name. This is very useful, but the identical names often confuse new users as to the differences. A table occurrence is a "box" in Manage | Database | Relationships. It's there so that you can define relationships between your table and any other tables in your system. You can actually change the name of this box and the name of your table and matching layout will not change. You can also create new occurrences of your existing tables here to create additional relationships without defining additional tables. Your table of matching name is set up for you to refer to the table occurrence of same name--which then refers to the table you defined. You can open Layout Setup... .while in browse mode and see what table occurrence is specified for your layout in the "show records from" drop down list. Just as you can create more than one table occurrence for a given table, you can create more than one layout and still select the same table occurrence in this drop down list. Each table occurrence has its own found set, sort order and current record so layouts that refer to the same table occurrence share the same found set, etc.

          For this report, we'll create a new layout based on your existing table. No need for a new table just to see your summary report. I'm also going to describe a table structure that may be different from what you have. I'm suggesting this structure as it makes for an easy to set up report an yet should be one you can use with your different layouts or you can manage all your expenses from the same layout.

          Structure your table like this to track expenditures in different categories:

          ExpenseAmount (Number)
          ExpenseCategory (Text)
          ExpenseDescription (Text)
          ExpenseDate (Date)
          ExpenseTotal (Summary, Total of ExpenseAmount)

          With this set of fields, you can create a summary layout like this:

          Create a new layout based on the above table and enter layout mode.
          DoubleClick the Label for the Body layout part to bring up the Part Set Up dialog.
          Change the body part into a SubSummary part "When sorted by" ExpenseCategory.
          In this sub summary part, put the ExpenseCategory and ExpenseTotal fields.
          You can position these fields just below the dotted line separating this part from the header and drag the bottom boundary line up to make a  narrow sub summary part.
          Select Part Setup from the layouts menu and add a trailing grand summary layout part.
          Put the same ExpenseTotal field in the trailing grand summary part.
          In browse mode, this layout will be blank, but when you sort records by ExpenseCategory, you'll see one row of information for each category with a sub total showing the subtotal for that category. The copy of the same field placed in the trailing grand summary will display the grand total.
          To set up your report manually, you can perform a find on a different layout based on the same total (where you can put the date field and other fields for entering find criteria, then switch to this layout to see the report. Just be sure to sort your records by category after performing the find.
          Oh yes, when viewing this layout, also make sure to select the View as List viewing option.

          • 2. Re: Summary Table Issue
            davidanders

            If the date of the transaction field is a date field (01/01/2001),

            you could use scripts to pull the date apart and create summary fields to get your desired display.

            or you could create a calculated field to pull the month out and another to pull the year out and another to pull and concantenate the month / year.

            Then create your summary fields based on the Month, the Year, and the Month/Year. fields.

             

            You may want to Google "summary field site:filemaker.com" and "summary site:forums.filemaker.com"

            https://www.google.com/search?q=summary+field+site%3Afilemaker.com

            https://www.google.com/search?q=summary+site%3Aforums.filemaker.com

            There is a Custom Google Search for Filemaker, also.

            http://www.google.com/cse/home?cx=001044389222327874554:vi8it1bulm8&hl=en

             

            Sometimes looking at other designs can be useful.

            Free Individual Accounting Runtime
            http://filemakeraccounting.com/products/single/

            MoneyWorks Demos
            http://www.cognito.co.nz/download/


            http://www.database-systems.com/ads/

            David Anders
            The Computer Guy, Seattle