2 Replies Latest reply on Apr 3, 2014 8:24 AM by XavierGodron

    Beginner // pivot table



      Beginner // pivot table



           I am new to FileMaker so the answer to my question may be really stupid...

           I am developing a file to follow the OPEX of different project companies.

           I have 2 tables: ProjectCompany & Invoices with a one to many relationship. The Invoices table has many fields in particular YearOfOperation, Amount and CategoryOfOPEX (8 categories). For each Project Company, I want to create something that looks like this:

                          Category OPEX                     Yr 1                     Yr 2                     Yr 3                     Yr 4                     Yr 5
                          1.                     Sum(amount)                                                                                        

           I'd like the Sum(amount) to be usable to draw some charts and to compare with Business Plan.

           This would be very easy with Excel but I have not managed to do this w/ FMP.

           Can anyone help?



        • 1. Re: Beginner // pivot table

               This is also referred to as a "cross tab" report where the rows and columns of data come from different groups of records.

               If I understand your example correctly, the intersection of year 1, row 1 is the total of amount from all invoices with a YearOfOperation of year 1 and in an OPEX category of 1. (I have no idea what OPEX is.)

               If that's correct, you can set up a third table OpexCategories with these fields:

               CategoryOfOpex, gYear1, gCompanyID, cYear2, cYear3...cYear5, cSum1, cSum2 ... cSum5

               You'd create 8 records in this table with values of 1...8 in the CategoryOfOpex field.

               The two fields that start with g will have global storage specified.

               cYear2 will be a calculation field defined as gYear1 + 1, cYear3 will be defined as gYear1 + 2 and so forth through cYear5.

               Before we can define the cSum1...cSum5 fields we need to set up the needed relationships:

               OpexCategories------<Invoices|year1,   ----<Invoices|year2, on thru Invoices|Year5

               Invoices|year1 thru Invoices|year5 are additional Tutorial: What are Table Occurrences? of the Invoices table.

               The match fields for OpexCategories to Invoices|Year1 would be:

               OpexCategories::gCompanyID = Invoices|year1::_fkCompanyID AND
               OpexCategories::gYear1 = Invoices|year1::YearOfOperation

               The match fields for the OpexCategories to match to Invoices|year2:

               OpexCategories::gCompanyID = Invoices|year2::_fkCompanyID AND
               OpexCategories::cYear2 = Invoices|year2::YearOfOperation

               and this pattern would continue for the other 3 relationships.

               Then you can define cSum1 as a calculation field:

               Sum ( Invoices|year1::Amount )

               The other 4 calculation fields would follow the same pattern: Sum ( Invoices|year2::Amount ), Sum ( Invoices|year3::Amount ), etc.

               You can then set up a list view layout with gCompanyID and gYear in the header with the OpexCategory and 5 cSUM fields in the body to form the 8 rows of yearly totals arranged in 5 yearly columns. gCompanyID can be formatted with a value list of Company ID's and company names. So that you can select the year for year 1, select a company and then the layout updates to show totals for that company over the 8 categories and over 5 years of data.

               Note: There's a simpler approach using summary fields and filtered portals that can produce the same totals, but then you can't chart from that data like you can using this approach.

               Note: This is the "nonSQL" method for getting what you need. If you have a working knowledge of SQL, you can dispense with the 5 added relationships, the cYear fields and define the cSum fields as calculations using the Sum in SQL queries.


          • 2. Re: Beginner // pivot table

                 Thanks a lot, that was very clear, you saved me a lot of time! Your link to yout Table Occurence Tutorial was useful too.

                 I just had to add a relationship between OpexCategories and Invoices Year x:

            OpexCategories::__ID = Invoices|year1::_fkOpexCategoriesID

                 I am new to databse management but I guess I'll have to learn SQL, it seems to be more straight forward...

                 FYI OpEx stands for Operational Expenditures (every expense after the initial investment is an OpEx), I should have stated it