Everything Summary

[Hurican]

Greetings All,

I'm just getting into the meat and potatoes of what FileMaker can do (suddenly I'm hungry), but I have always found some limitations in ease of use when it comes to working with summaries that rely on multiple criteria.

Any summary guru around here, or possibly hints or links to stuff online that I can read about, reverse engineer, etc...?

Any help would be greatly appreciated.

Many thanks,

Hurican

Version: Developer v7

Platform: Windows XP

[Ender]

Are you talking about summary parts on a layout or summary fields, like totals and averages? Both have their own trickyness.

--Mike

[Hurican]

Sorry about the delay, the board seemed down for a couple of days for me.

I was thinking more in the range of complex summary fields.

For instance, Summarizing multiple criteria, ie...

A field that defines state, MD, VA, CA, OH, etc...

A field that defines date created.

So I would like to total all the records that were created this year from Maryland. Very much like constraining a found set based on multiple find requests.

[CyborgSam]

One easy way to do this: create a calculation field for each eventuality & summarize these.

Ex: If( State="NY" and Year=ThisYear, theAmount, 0) Not fun for 50 states...

Another would be to put the states in a separate table/file and perform the summaries in that file using a relationship back to the first. In the first file make a calc field c.ThisYearAmount: If( Year=ThisYear, theAmount, 0). In the State file make the summary using Sum(theRelationship::c.ThisYearAmount).

Either work for you?

[Ender]

It doesn't seem like this should be that hard. It sounds like you are asking for counts based on several different fields. If so, this can be done with multiple parts on a List layout:

Define a Count of Records field that is a summary: Count of Record ID

Add a part to the list layout defined as Sub-Summary by State, another part for Sub-Summary by Date, additional parts for any other fields that the report should be broken down by. Put your Count of Records field on the parts you want counted. Then enter Preview mode, sort the records by State, Date, etc.

Does this work? Or did I miss your question?

--Mike

Version: Developer v6

Platform: Mac OS X Panther

[-Queue-]

If I'm interpreting this correctly, you'll want a calculation field that combines the specified criteria. Create a calculation field (text) equal to NumToText(Year(date created)) & State and a self-relationship from this field to itself. Now you can either create a Count(selfrel::id) calculation to find the number of related records or you can create a calculated field equal to 1 and create a summary field based on this calculation. Put the summary field in a subsummary part, then sort by the combined criteria field. When previewed, you should see the desired data.

Or did I miss the question too?

Version: v5.x

Platform: Windows 2000

[CyborgSam]

The original request: "I would like to total all the records that were created this year from Maryland."

I interpreted that as meaning "total of some field", Ender & Queue interpreted it as "total # of matching recs".

Queue's method can also work for "total of some field", replace the Count(selfrel::id) with a Sum(selfrel::FieldToTotal).

Queue-> what are the pros/cons for the self-join method over my calculation method?

[-Queue-]

Do you mean your first or second calc method? Obviously, a self-join is much easier to implement and upkeep than the first; I haven't tested the second, so I can't really form an opinion about it.

Have you tested your second one and mine to see speed differences?

[CyborgSam]

I haven't tested these two against each other. I agree that the self-join is the best.

I often recommend the simplest solution to make it easier for the poster to understand. I assume that anyone knowing how to optimize my gibberish will.

I've never seen a project where the requirements were well known before coding started (a person can dream, can't they?). At first I'll leave things simple enough to be "self-documenting". It's way to easy to obfuscate calculations... Once I know more I optimize & document the algorithms, structures, et al.

I'm working on a project for my sister's school. I'm over 90% done. And my sister thinks of something new everytime we talk...

[-Queue-]

I can understand that. I just have an itch to streamline when I can. If it's a difference of five or fewer fields, then I probably wouldn't worry about it. In this case though, it requires 49 extra fields, which I wouldn't want to push on anyone if there's a simpler method.

Your sister sounds like my CEO.

[Hurican]

Thanks for all your input. It will take me a while to experiment with this, as I have a number of other things going on at the same time. (Summaries are a weak spot for me, and I really have to wrap my mind around it before truly understanding it.)

Thanks,