Jump to content

Everything Summary


Hurican
 Share

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

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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? grin.gif

Version: v5.x

Platform: Windows 2000

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. wink.gif

Link to comment
Share on other sites

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,

Link to comment
Share on other sites

This topic is 6512 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
 Share

×
×
  • Create New...

Important Information

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