Mutiple Break Fields

[Chuck]

I've got a database of class evaluations where I want to get totals and summary data for the evaluations based on different criteria. Most of the reports have each record fall into a single category for the break field. For instance, each class has a single title (and there are many classes with the same title). So getting average evaluations with summary fields and parts is pretty straight forward.

But one report is on the category for the class, and a class can fall into multiple categories. Each category is a separate field (set to true or false). How can I get the average evaluations for this kind of category, where I need the record to count toward multiple sub-summaries? Do I need to have a third file which is made up of class/category combinations?

I think that I'm not being very clear, so here's an example.

Evaluation 1

Class X

Category 1: true

Category 2: false

Evaluation Criteria 1: 5

Evaluation Criteria 2: 3

Evaluation Criteria 3: 4

Evaluation 2

Class X

Category 1: true

Category 2: false

Evaluation Criteria 1: 4

Evaluation Criteria 2: 4

Evaluation Criteria 3: 2

Evaluation 3

Class Y

Category 1: true

Category 2: true

Evaluation Criteria 1: 1

Evaluation Criteria 2: 2

Evaluation Criteria 3: 3

Evaluation 4

Class Y

Category 1: true

Category 2: true

Evaluation Criteria 1: 2

Evaluation Criteria 2: 3

Evaluation Criteria 3: 5

Given these four records, my report on class evaluations is:

Eval Crit 1 Eval Crit 2 Eval Crit 3

Class X

1 0 0 0

2 0 0 1

3 0 1 0

4 1 1 1

5 1 0 0

Ave 4.5 3.5 3

Class Y

1 1 0 0

2 1 1 0

3 0 1 1

4 0 0 0

5 0 0 1

Ave 1.5 2.5 4

I'm looking for how to generate this report on categories:

Eval Crit 1 Eval Crit 2 Eval Crit 3

Category 1

1 1 0 0

2 1 1 1

3 0 2 1

4 1 1 1

5 1 0 1

Ave 3 3 3.5

Category 2

1 1 0 0

2 1 1 0

3 0 1 1

4 0 0 0

5 0 0 1

Ave 1.5 2.5 4

Does that help?

Any ideas or pointers would be appreciated.

Thanks,

Chuck

[LiveOak]

Chuck, two ways come to mind. One is to create calculated fields for each category/criteria combination. Something like:

cCatOneforCriteriaOne =

If(Category 1, Criteria 1, 0)

This allows you to total a Criteria 1 value for each separate category. This would be repeated for each criteria and category. Unfortunately, if you have 50 criteria and 10 categories, you end up with 500 fields and 500 summary fields!

A second approach is to use a second file related to the first to create the report. In your existing file, create a text field with a value list of category numbers (instead of or calculated from your individual fields for each category selection). If entered as a checkbox field, the result, displayed unformatted, is a field containing all the category numbers checked. For instance, if categories 1,3,and 5 are checked, the actual field will look like:

1

3

5

with numbers separated by carriage returns.

Create a second report file with one record for each category with a Cat# field. Relate Cat# to the checkbox field in your originalfile. The relationship will cause each record/Category in this new file to isolate one categories worth of records. It doesn't matter that one record in the original file contains data to be used in several categories.

In the display file you can use the Sum() and Count() aggregate functions to total the criteria and average the values from the related records.

I hope the concept isn't too hard to follow. The basic idea is to use relationships to allow you the freedom of locating numbers where you want them on a report without being tied down by the "record list" nature of FileMaker. -bd

p.s. Thanks for all your contributions to the forums!

[This message has been edited by LiveOak (edited October 09, 2000).]

[Chuck]

It sounds like the second solution is going to be the one for me. There are about 30 different classes and about 10 different categories, so we're looking at 600 new fields for the first solution. And they might at any time add a new class, which means that every time a new class is added, they would have to come back to me to add new fields to the file.

I might need some clarification though with the second technique. I'm going to play around with it and see if I can get it to work.

Thanks,

Chuck

[Chuck]

Well, I began to implement this technique and I ran into a problem. So let me give some more information that seems to be pertinent.

Here is what I can think of as relevant from the file structure:

File: Trainings.fp5

Fields:

TrainingID

CategoryA (number, set to checkbox with possible value of 1)

CategoryB (number, same as above)

...

CategoryK (number, same as above)

File: Evaluations.fp5

Fields:

TrainingID (relationship to Trainings.fp5 based on this field)

Rating1 (number, set to radio button on layout, possible values: {1,2,3,4,5})

Rating2 (number, same as above)

...

Rating9 (number, same as above)

Categories (calc) = Case( Trainings::CategoryA, "A" & "

[Chuck]

Well, the relationship isn't working as I thought.

I have a Categories database with a single record for each category and a field called "Category"

In the Trainings file, I have a field called Categories which checks for each category being true, and if it is, adds it to the field, with paragraph marks between them.

In the Categories file, I have a relationship between its Category field and Trainings' Categories.

But the relationship only works if the Category field matches the first line of the Categories field. So if the Trainings file's Categories field has the following:

1

2

3

And the Category field of the Categories file has three records with 1, 2, and 3 in the Category field, the Trainings record will only be related to the record with the category set to 1, not to those with it set to 2 or 3.

Now I know I've seen this work when the multi-line or checkbox field is on the left side of the relationship (like when using a global field to go to all related records in the current found set), but I've never tried it with the multi-line field on the rights side of the relaitonship. Does this actually work?

Help!

Chuck

[This message has been edited by Chuck (edited October 10, 2000).]

[LiveOak]

The approach I proposed was based upon the source information being in a single file (your original file + added file to do the summary).

I'm afraid you've lost me! First, I think I need a little more background in English (not FM) about what you are doing. What is the big picture (recording evaluation data?). What form does the evaluation data take? What resulting summaries need to be created? The first level of description needs to avoid numbers and specifics (for my benefit!).

Second, why create the additional files? Breaking pieces out of the original file will create a problem with complex relationships and indexing. -bd

[This message has been edited by LiveOak (edited October 11, 2000).]