revenue table stuff

[whatwater]

Less than a year ago I created a revenue table to keep track of projects and revenue. It mirrors the way our financial officer likes to view excel sheets. So this is essentially a flat, non-relational aspect of our database that the FO can export to excel and chart things the way he is used to. The caveat here is that I have to keep creating fields for every month and altering a calculation for every field.

 

<figure 1>

| ProjName | FY14_Sep | FY14_Oct | FY14_Nov ...
| ProjA    | $x       | $y       | $z
| ProjB    | $x       | $y       | $z

 

However, I understand that the proper way to set this up is to have a table for Projects with a one-to-many related table for Revenue.

 

So my new related revenue table looks like this:

 

<figure 2>

|::ProjName| Date    | $$$ |
| ProjA    | FY14Sep | $x  |
| ProjA    | FY14Oct | $y  |
| ProjA    | FY14Nov | $z  |
| ProjB    | FY14Sep | $x  |
| ProjB    | FY14Oct | $y  |
| ProjB    | FY14Nov | $z  |

 

So while this is all very neat and proper within FileMaker, exporting this table to excel yields a vastly different display of data. I must come up with some solution to convert my revenue table data <figure 2> into calculation fields in <figure 1>.  GetNthRecord was suggested, but in place of record number argument I need to filter by month and year... and filemaker doesn't seem to allow a calculation there.

[Wim Decorte]

You definitely do not want calculation fields for this.  Most of this data can be pre-aggregated into static data to make this report fast.

 

What you need is a new reporting table that looks like your figure 1 and collect aggregated data from your real table to display as per figure 1.

[comment]

So this is essentially a flat, non-relational aspect of our database that the FO can export to excel and chart things the way he is used to.

 

I am having trouble understanding what is the real problem here. Do you want to implement this "flat, non-relational aspect" in Filemaker - or do you want to export your data in a flat, non-relational tabular structure to Excel? The former task is NOT a prerequisite for the latter.