Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 3365 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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

Posted (edited)

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 by OlgerDiekstra
add detail

This topic is 3365 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.