5 Replies Latest reply on Feb 7, 2009 2:13 AM by Sorbsbuster

    displaying month totals in separate columns

    littlejon

      Title

      displaying month totals in separate columns

      Post

      I am testing FMP by trying to create a home budget db starting with two tables - budget and transaction. The transaction table has a date in mm/dd/yyyy format, category and amount fields, among others. 

      How do I create a layout/report that shows each month's transactions in a separate column?

       

      I have succeeded in getting a layout with the first three columns - categoyr, budgetedamt and january transaction totals by category. I have created a field using a  month(Date) column that returns the month number.  I also have a summary total by category. So far, the table only has transactions for January.

       

      I'm not sure how to filter and display for multiple months. I have it basically working I already have a. What I'd like is something like:

      Category    BudgetedAmt          January          February          March...
      Cat1                        250.00            250.00              180.00        320.00
      Cat2                    1,200.00         1,320.00           1,050.00     1,149.00

      Having a separate table for each month seems rather silly, albeit possible.

      Do I need calculated fields that return transactions for each month? Does that require 'if' logic/functions to filter the data? That doesn't seem dynamic over many months and years.

       

       I'm used to being able to load this type of data into an array and loop thru it for display & printing. FMP has some unique ways of doing things. I'd like to get a handle on it before my trial ends in 15 days. 

       

      Thanks for your help. 




        • 1. Re: displaying month totals in separate columns
          TSGal

          littlejon:

           

          Thank you for your post.

           

          Since you are getting January transactions, you probably have a link from your BUDGET table to your TRANSACTION table based upon Category and Month Number = 1.  It appears you are getting your proper totals for that month, so I don't think this needs to be changed.  True?

           

          Pull down the File menu and select "Manage -> Database...".  Click on the Relationships tab, and you will see a graphical representation of your BUDGET and TRANSACTION tables.  At the bottom, there are a number of icons.  Click the left icon that will add a new table.  Select TRANSACTION table, and you will notice another table occurrence of TRANSACTIONS display, and titled "TRANSACTION 2".  Using the same principles previously, set the relationship between BUDGET and TRANSACTION 2 based upon Category and Month Number = 2.  This will allow you to retrieve the February totals, while also displaying the January totals.

           

          You can then create the calculation to display the February totals and place this on the Layout.

           

          Repeat the steps above for each of the months. 

           

          This should get you pointed in the right direction.  If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: displaying month totals in separate columns
            littlejon
               There's not a way to filter them on the fly out of the original, single table? That's a lot of tables over the years.
            • 3. Re: displaying month totals in separate columns
              TSGal

              littlejon:

               

              Sorry.  I assumed you would only have 12 months.  You've just added another factor into the equation.

               

              So, if you wanted January 2005 through December 2008, you want 48 columns for those four years?

               

              The key field would then involve the year AND the month.

               

              I can think of a way using scripts and global variables, but it would be too time-consuming for me at this time.  Does anyone else want to help on this one?  I feel like I'm overlooking something very simple.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: displaying month totals in separate columns
                littlejon
                   Thanks for trying. I appreciate your feedback. I probably will only show the current calendar year (january to december) on certain layouts. I was just trying to avoid the manual intervention to create a new table each month. Thanks, again.
                • 5. Re: displaying month totals in separate columns
                  Sorbsbuster
                    

                  I have used TSGal's technique many times for columnar month-by-month analyses and it works a dream.

                   

                  Instead of having 12 fixed 'Column Header Dates':

                  1/1/2008

                  1/2/2008

                  ...

                  21/12/2008

                   

                  ...and hence 2 sets of 12 derived fields:

                  Month ( 1/1/2008 ) etc

                  and

                  Year ( 1/1/2008 ) etc

                   

                  - just set the one date for data entry: DateStart. Make it a global field.

                  - set all the others as the series:

                  - Year1 = Year (DateStart), Year2 = Year (DateStart + 30), Year3 = Year3 (DateStart + 60) etc

                  - Month1 = Month (DateStart), Month2 = Month (DateStart + 30), Month3 = Month3 (DateStart + 60) etc

                  - make them all global results

                  - make your relationships the same way, using these Year and Month calculations to link to the  true Year and Month of the record

                  - Have the DateStart available for setting somewhere convenient, and set it to be the 15th of the month you want to start with.  The data will all update to show any 12 months you want, even across years, just by changing one date.

                  - If you always want it to show the whole current year, or the next 12 months from now, or the last 12 months, or whatever, it is easy to have a script that will set those options either as buttons or on start-up.

                   

                  HTH,

                  Alan.