I'm new to FM, and I'm stuck creating a summary percentage field. Here
is my situaltion, with actual data. I've been stuck for days, so any
help will be greatly appreciated!

I have a database of machines. For these units, there are hundreds of
repair codes. All the repair codes will be summarized by grouping
together common repairs. There are 11 main groups, each representing
several combined repair codes. For example, several repair codes will
belong to Group 1 (G1),
which will be all the repair codes that fall into the Electrical
repairs category. Here are the actual groupings. (Not of great
importance to my problem, but just so you get a picture of what I'm
working with!)

GROUP - REPAIR CODES - GROUP DESC
G1 211, 212, 3** [i.e., all codes starting with 3] Misc
Electrical
G2 210 Battery Failure
G3 4** Clamp
G4 52* Sensor PCB:
G5 53*, 54* Main PCB
G6 85* Motor/Cam
G7 811,815,817 Misc Mechanical/Rocker
G8 821, 822, 823, 825 Load Cell
G9 110, 111, 120, 310, 413, 422, 451, 6**, 7**, 812, 813, 816,
824 User Damage
G10 999 No Problem Found
G11 90*, 91*, 92*, 93*, 94*, 95*, 96*, 97*, 98* User Setting/
Usage


There is a form where these repair codes get entered. There are 3
fields. There are codes entered in RepairCode1, RepairCode2, and
RepairCode3.


First I created boolean fields (in sets of 3) for each of the 11
group's
corresponding 3 repair fields (E.g.: G1boolean1 [returns a 1 if
RepairCode1 meets the criteria for Group 1], G1boolean2 [returns a 1
if RepairCode2 meets the criteria for Group 1], G1boolean3,

and the same for Group 2: G2boolean1, G2boolean2, G2boolean3, etc.).

Worked great - I have a 1 returned if the appropriate repair code
meets that group's
criteria.

Then I created a calculation field, a sum of the set of 3 boolean
fields for each group (E.g.: G1booleansum [Adds "boolean points" from
G1boolean1, G1boolean2, G1boolean3], and then the same for
G2booleansum, etc.).

Next, I created a summary field (G1count - which is a summary count of
G1booleansum) to give a total count of each group's booean sum.

So far so good! I can say how many repairs there were for each group
in the whole table. For example, I have 47 occurances of repair codes
that fall into the Group 1 Electrical category. But what precentage is
47 of the total units (records in the table)? And what if I want to
know what percentage 47 is of the total repairs?

The end goal is to have a report of the number of repairs made in
each
of the 11 groups, by month, and what proportion that count is of the
total number of cases in the table.

And it is here I am stuck...oof.

I don't see "fraction of a total of" as an option for my summary
variable...
and I can't figure out the "get summary" code....

Should I even be trying to make this field from the"Define Database"
dialog?

Been stuck on this one for days...(the boss is saying, it's a simple
percentage!!) any help will be greatly appreciated!

Thanks so much for reading.

EnJoy,
-cat

In article <1173991452.577683.104230 (AT) e65g2000hsc (DOT) googlegroups.com>,
"cat" <cathytyner (AT) gmail (DOT) com> wrote:

OUCH! As you can see, FileMaker is not really designed for data
analysis, although using three fields per record may not be the best
way to create this database, but we'll stay with that since you've
already got it working that way.

You didn't really need all the Boolean fields. You could have added the
error codes group fields themselves using Boolean logic.

Anyway so you've now already got 11 Summary fields that totals each of
the groupings.
eg.
s_G1Total Summary Total of G1booleansum
s_G2Total Summary Total of G2booleansum
...
s_G11Total Summary Total of G11booleansum

To work out the percentages you will of course also need a total of all
the error codes, which you may or may not already have. This means two
new fields - a Calculation and a Summary.
eg.
CodeSum Calculation, Number Result, Unstored
= G1booleansum + G2booleansum + ... +G11booleansum

s_CodeTotal Summary Total of ErrorCodeSum


Now comes the tricky part. You can't use a Summary field to summarise
the separate s_GXTotal fields against the s_CodeTotal, so we have to
create some more new fields which do this for us. These fields use the
Get Summary function to retrieve the result from the existing Summary
fields for use in further calculations.
eg.
G1_Percent Calculation, Number Result, Unstored
= Get Summary(s_G1Total, SortField)
/ Get Summary(s_CodeTotal, SortField)

G2_Percent Calculation, Number Result, Unstored
= Get Summary(s_G2Total, SortField)
/ Get Summary(s_CodeTotal, SortField)

...

G11_Percent Calculation, Number Result, Unstored
= Get Summary(s_G11Total, SortField)
/ Get Summary(s_CodeTotal, SortField)

where SortField is the name of the field you using to collate /
breakdown the records in the report. For example you might be making a
report with the records collated / broken down for each month, in which
case you need to sort the records by the Month.

If you're not sorting / collating the records and are simply creating a
report for the whole Found Set of records, then you'll need to create
another new field which is given the same value for every record.
eg.
SortField Text
Auto-enter Calculation = "Error Record"

Even though these GX_Percentage fields are normal record-level fields
and not Summary fields, they are being given the same value for every
record in the SortField grouping - which means you can simply put these
fields into a Summary part of the Layout (Sub-summary and / or Grand
Summary), formatting them as percantages to however many decimal places
you want.

For example, a report broken down by Month might use a Layout something
like:

G1 G2 ... G11 Total
Header
-----------

[Month]
[s_G1Total] [s_G2Total] ... [s_G11Total] [s_CodeTotal]

[G1_Percent] [G2_Percent]... [G11_Percent]

Sub-smmary by Month (Leading)
-----------

[s_G1Total] [s_G2Total] ... [s_G11Total] [s_CodeTotal]

[G1_Percent] [G2_Percent]... [G11_Percent]

Grand Trailing Summary
-----------


Sort the Found Set by Month and then Preview / print the report layout.

ALL DONE!!

Note: Because of rounding issues, manually totalling of the Percentage
fields may not actually give a result of 100.






Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Mar 15, 10:39 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Ouch is right! Thanks so much though - I will try this. If you have
time and can share an idea about how I could have set this up
differently, I'm willing to redesign - better to simplify now rather
than later. Would the entries for the repair codes all be stored in
one field? Each machine may have multiple repair codes assigned to it.
You've touched on an important design issue that I'm concerned about.
What if a machine eventually has 4 or more repairs assigned to it?

Could you elaborate a bit about how I could have added the error codes
group fields themselves using Boolean logic?

I don't know how to thank you - and I really want to set this up the
best way.
-cat

On Mar 16, 2:07 pm, "cat" <cathyty... (AT) gmail (DOT) com> wrote:
Perhaps by added the error codes group fields themselves using Boolean
logic, you mean that instead of returning a "1" for every group, I
would have "Group 1" returned, and so forth?

Now that I understand a little bit more about how reports and
summaries work, this makes sense. But I don't see how it could work
with the 3 fields.

In article <1174079221.461228.42880 (AT) o5g2000hsb (DOT) googlegroups.com>, "cat"
<cathytyner (AT) gmail (DOT) com> wrote:

Technically you could probably use Repeating fields here instead of
separate fields, but they do have limitations and problems, so it's
often best to not use them when you want to perform Calculations and
Summaries.


Instead you might be better off using a second Table to store the error
codes and use a Relationship and Portal to enter them on the main
table's layout.

That way instead of having three fields, three lots of Calculation
fields, etc. to worry about:
eg.
ErrorCode_1
ErrorCode_2
ErrorCode_3

ErrorGroup_1 Calculation
ErrorGroup_2 Calculation
ErrorGroup_3 Calculation

you would only need one set:
eg.
In the ErrorCode table you have:
ErrorCode
ErrorGroup Calculation

Then, depending on the requirements, you can define extra Relationships
that allow you to collect the appropraite ErrorGroups and use the
Sum(Relationship::Field) to obtain how many there are, etc.

This can get a little complicated though.




I did type it up, but then took it out so as not to add more complexity
/ confusion than you might need. )


You may already know that instead of your separate Boolean fields and
then a Calculation to sum those, you can do it all in one Calculation
and reduce the number of fields you need.
eg.
G1booleanSum Calculation, Number Result, Unstored
= If (FirstErrorCodeGroup = "G1", 1, 0)
+ If (SecondErrorCodeGroup = "G1", 1, 0)
+ If (ThirdErrorCodeGroup = "G1", 1, 0)

This is easy to read, but by using Boolean logic you can do away with
the "If"s as well.
eg.
G1booleanSum Calculation, Number Result, Unstored
= (FirstErrorCodeGroup = "G1")
+ (SecondErrorCodeGroup = "G1")
+ (ThirdErrorCodeGroup = "G1")

Using the = symbol inside the usual mathematical calculation causes
FileMaker (or Excel, etc.) to evaluate the bracketed parts just like an
If statement, returning either 1 for true or 0 for false. (Note in some
applications / programming environments these numbers can be around the
other way.)

For example, if the record was:

FirstErrorCode = 210 FirstErrorCodeGroup = G2
SecondErrorCode = 211 SecondErrorCodeGroup = G1
ThirdErrorCode = 304 ThirdErrorCodeGroup = G1

then the G1BooleanSum Calculation would become:

(G2 = G1) + (G1 = G1) + (G1 = G1)

which when evaluated becomes:

False + True + True
or 0 + 1 + 1

giving the total of 2 that you want.



It's not just the = symbol either. You can do the same with all the
comparison symbols.
eg.
(FirstNum > SecondNum) + (FirstNum > Third Num)

Will return 1 + 0 when
FirstNum = 5
SecondNum = 3
ThirdNum = 8

meaning FirstNum is greater than only 1 of the other numbers.

Anything you can put in the test part of an If statement will evaluate
in this True / False way.

I don't know that it evaluates any faster than using the first If + If
+ If approach, but either of these means you would have less fields to
worry about.





Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)