I'm looking to see if there's a better way to do what I'm currently doing, which is effectively making cross tab tables from a single column of data.  My main data table columns are (in a very simplistic format for digestion here):

Date     Amount     TrueFalse1     TrueFalse2     TrueFalse3

 

There are around 70,000 records, each with entries in every field above.  Records tend to be grouped into months and I need to get columns per month on a report. 

To do this, I use CASE statements initially and then summarise the results:

• JanTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Jan" ; Amount)
• FebTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Feb" ; Amount)
• MarTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Mar" ; Amount)
• sJanTotal
• sFebTotal
• sMarTotal

I now have 6 fields just for three months with this logic testing.  I also need to have other variations on TrueFalse1/2/3 per month as well, so the numbers of fields can easily bloat.

In the humble opinion of those with more FM knowledge and experience of me, is this an efficient way to do things?  Is it more efficient to use a 'temp' table and have this reload data each time I need it to?

Any input appreciated, thanks in advance

Filemaker can't easily do a column based summary. It can do a row based summary though.

For that, you need to create a month field calc that calculates the month from the date (ie month ( Date). Then create a summary field total that totals Amount.

Next create a list layout with a subsummary part (sort it by month), that holds the month and the total. Now sort by month.

You can forgo the case statements.

Edited September 7, 201510 yr by OlgerDiekstra
add detail