My database has a field formatted with a checkbox set. There are 8 values in that set. I need to find out how many times (in a found set) each of these values has been selected. There can be more than one value selected for each record.

For Example:

Found Set = 8512 records

Values: CL, IR, CC, WM, DI, CI, VI, CODE

CL = 3794 (number of times this value has been checked)

IR = 517

CC = 76

WM = 122

DI = 1731

CI = 1187

VI = 301

CODE = 215

Not all records will have values selected at the time the report is run.

I have done this calculation with a calculator and it comes out fine. I just can't get it to work in FileMaker.

This is what I am doing:

The total of all values selected is 7943.

I am setting up the calculation to read:

(total_cl / ( total_cc + total_ci + total_cl + total_code + total_di + total_ir + total_vi + total_wm)* 100)

That should give me about 48 percent.

Obviously I am doing this wrong since I keep getting 1 as the answer.

All help and comments greatly appreciated.

See this thread for a solution to a very similar problem.

I think I have all of that done. I have all of the counting done I just need to translate the numbers into percentages.

I am thinking like this -

There are 100 records in the found set.

Each record has 8 values that can be checked.

It is possible that all 100 records will have all 8 values checked resulting in a total checkbox count of 800.

Each of those value totals(CC=100, CI=100, etc.) should translate to 12.5%.

I can't get it work in FileMaker.

(total_cl / ( total_cc + total_ci + total_cl + total_code + total_di + total_ir + total_vi + total_wm))*100

That didn't work either - Everything I try gives me 1 as a result.

Could it have to do with the total fields being set as summary fields?

What I have set up are count fields for each value. They are set up as calculations that read: [color:red]=PatternCount (rfi_category; "CC")

To total that field I have the total fields set up as summary fields that read: [color:red]=Total of count_cc (running)

I have trouble following you, since I don't know how you derived those totals, and by now I am not sure what is it that are you trying to calculate. I would have thought that if ALL records have a certain value checked, then the result for that value should be 100%.

See if one of the calcs in the attached file meets yor expectations.

sumByCategorySimple.fp7.zip

Ah! The cFractionCategoriesR2 is what I am after.

Thank you so much. I have never made a calc into a repeating field

Wow - you made my week! Thank you SOO much!

I have never made a calc into a repeating field

Understood. And you will repeatedly hear to flush repeating fields. Flush them for data storage but DON'T ignore their power for calculations. I did just that and now kick myself. Ouch!

L

What does "not not Position" in cSplitCategoriesR do? I understand the Position Function but not the "not not" part.

It makes Position() return a boolean result.

>>not not Position ( ¶ & Extend ( Category ) & ¶ ; ¶ & cAllCategoriesR & ¶ ; 1 ; 1 )

I see that the "not not" makes the result boolean (didn't know you could do this), but could you describe the rest of the calculation? I see that you are testing for the presence of individual categories within all the categories but am not quite sure I follow the mechanics.

Thanks!

James

I am not sure where the difficulty is. The calc is itself a repeating field. Each repetition tests the presence of a single category (found in the corresponding repetition of cAllCategoriesR) within the checkfield. The result is an array of 1's and 0's, which is easy to summarize.

>>The result is an array of 1's and 0's, which is easy to summarize.

Yes, I put that field on the layout and see what you mean. Thanks!