Jump to content
Sign in to follow this  
enghult

Counting, summary and reporting problem.

Recommended Posts

I suppose this is a pretty simple problem, but I’m a beginner and have some problems with this anyhow.

I have a DB (let’s call it avr.fp5) that handles occurred accidents.

It contains fields like:

Sequence_number: (a unique ID)

Date: (when it happens)

Class: (What type of accident)

Signature: (Who reported)

And some other fields…

What I want to do is to count all unique ocurrences of the field Class and put it into a repeating field. And I also want the Class name in another repeating field.

Example:

Sequence_number Class

12341 Technical

12343 Medical

12344 User

12345 Medical

12349 Medical

12351 User

12352 Medical

I want the result to be stored in the resulting repeating fields like this:

Class_label Class_count

Technical 1

Medical 4

User 2

The number of classes may vary.

The idea is then to use the excellent plug-in xmChart to generate a diagram.

I have tried to make a self-join on the sequence_number-field and I have tried to use the “status flag” for first occurring name but I just can’t get it right.

I can get it to work with a sub-summary report, but since I want the results in chart I need to get the values stored somehow.

If someone can help me with a detailed description I will be most grateful.

Share this post


Link to post
Share on other sites

Forget the repeating fields. They are too inflexible to do what you want.

Instead, create a related file (call it AVRSummary.fp5) with a Class and ClassCount field. Create a relationship from your original file to the new one based on the class field and select the "allow creation of related records" option. Then, create a reverse relationship from the new file back to the original file based on the same Class field. Set up the ClassCount field as a calculation with this formula:

Count(AVR::Class)

Then, the records that are automatically created in AVRSummary.fp5 should give you what you want.

[ October 03, 2001: Message edited by: BobWeaver ]

Share this post


Link to post
Share on other sites

Thanks Bob!!

Worked just fine for me.

I skipped the repeating fields as you said.

I even got the summary DB to produce the proper posts in an automatic script.

With your solution you still have to enter the right Class name to be summarized. Right?

I get a lot of help by reading these forums and now I have learned something new again!

Keep up the good work!

/Martin

Share this post


Link to post
Share on other sites

Actually, I thought that the "allow creation of related records" option would automatically create the summary records, but maybe not. No problem though, if you got it to work.

Share this post


Link to post
Share on other sites

quote:

Originally posted by BobWeaver:

Actually, I thought that the "allow creation of related records" option would automatically create the summary records, but maybe not. No problem though, if you got it to work.

Nope, I

Share this post


Link to post
Share on other sites

Allow Creation of Related Records permits creation of one or more records in a related file FROM A GIVEN PARENT RECORD by entering data in a field in a portal. It removes the necessity to enter the foreign key in the related file.

I have no idea what you mean by a "summary record". In FileMaker Pro we have summary fields, summary function, and summary and subsummary parts. We also have aggregate functions. All these items perform arithmetic computations on found sets of records.

HTH

Old Advance Man

Share this post


Link to post
Share on other sites

quote:

Originally posted by Old Advance Man:

Allow Creation of Related Records permits creation of one or more records in a related file FROM A GIVEN PARENT RECORD by entering data in a field in a portal


OK, I see. That explains why the related fields won

Share this post


Link to post
Share on other sites

quote:

Originally posted by Enghult:

A new problem ocurred:

The related db that does the summary works just fine as long as you want to make a summary of all records.

If you have a selection of records the summary still counts on all the records and not just the selection.

Ex: I have a total of 200 records entered in year 1999,2000,2001 and i select just the records of 2000. Then when I get the values from the related summary db I still have a summary of all records.

You have to define a summary field for the units you are summarizing. Then sort by what is called the "break field", perhaps the Year. Then on a layout with a subsummary part defined to match that sort field, place the subsummary field. When you preview or print, you will see the total for each year.

I know this is a little confusing. Take a look at the FMP manula or the tutorial for an example.

HTH

Old Advance Man

Share this post


Link to post
Share on other sites

If you just want to count or sum related records or a found set, use a calculation field with Count[] or Sum[] function insteaad of summary fields. Much, much faster and not dependent on the sort state of the database.

Share this post


Link to post
Share on other sites

quote:

Originally posted by Vaughan:

If you just want to count or sum related records or a found set, use a calculation field with Count[] or Sum[] function insteaad of summary fields. Much, much faster and not dependent on the sort state of the database.

Well, what I want is the count to be dependent of the found set.

I got it to work by doing the calcotaion in the main DB, not in the related DB.

The reason why sub-summary layouts won

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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