### Title

Beginner // pivot table

### Post

Hi,

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) | ||||

2. | |||||

3. | |||||

4. | |||||

5. | |||||

6. | |||||

7. | |||||

8. |

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?

Thanks!

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.