Counting, summary and reporting problem.

[enghult]

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.

[BobWeaver]

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 ]

[enghult]

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

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

[enghult]

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

[Steven H. Blackwell]

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

[enghult]

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

[Steven H. Blackwell]

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

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

[enghult]

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