Count number of boxes checked

[DeborahW]

I've read a couple of threads that might relate to this question but the solutions seem rather complicated. I have a very simple (and flat) database listing some fabrics. I have a checkbox field where I check who has chosen a given fabric. At the end of the day, I'd like Filemaker to give me a total of how many accounts have chosen each fabric. Is there any way (without completely redesigning the database) to have Filemaker simply count the number of "checks" for each fabric and return the total in a field? When I tried a calculation using the Count function, it would only return "1" for each fabric regardless of how many boxes were checked for that fabric. Thank you.

Deborah

[mr_vodka]

You could try and use summary fields. After you do a find for the records of a certain date, the summary fields should give you your results.

[Genx]

ValueCount(YourCheckBoxField) should work (assuming that function exists in fm 7).

[Genx]

Why would value count not work -- they only want to count the number of boxes checked in a particular field -- Selections are return delimited...

Whatever if the function doesn't exist then, If( IsEmpty( YourCheckBoxField ) ; 0 ; PatternCount( YourCheckBoxField ; "¶" ) + 1)

Edited February 8, 2007 by Guest
I'm not crazy, LaRetta deleted her post

[LaRetta]

Yes I did. No sleep and I misread the question. But yes, you're crazy anyway. :smile2:

[Genx]

Lol, I know, gw!

[John Mark Osborne]

I think Deborah wants to display a summary across the found set for each fabric. ValueCount and PatternCount for return characters will only count the number of values checked. While she is better off storing the fabrics in a separate table so she can create a subsummary report with summary fields, it can be accomplished with calculations and summary field with the way she currently has it setup.

First, make calculations for each possible fabric in the check box:

Case(CheckBox = "Cotton"; 1)

Case(CheckBox = "Silk"; 1)

etc...

Then, create a summary field for each calculation. If Deborah adds a new fabric, she will need to add another calculation and summary field. With a separate table, new fabrics will require no additional work (and, it's really not that much work).

[mr_vodka]

That is how I read her post too, and possibly LaRetta. We need some clarification from Deborah, on how she has it exactly set up.

[Genx]

If Deborah adds a new fabric, she will need to add another calculation and summary field. With a separate table, new fabrics will require no additional work (and, it's really not that much work).

From what i understand, if she adds another fabric, she will also have to add another field at this stage. From what i understand, the OP want's to count the boxes checked in each field relating to each fabric.

I agree that a related structure would likely suit better here, but we'll wait for the OP.

[DeborahW]

OK, sorry if I wasn't clear enough before. The way it is set up is that I have a record for each fabric (they each have a name, number, price, etc.). Each fabric may be chosen to be used by one or more of several accounts which I have set up in a value list. Each appears as a checkbox in the record. Any account that choses the fabric gets a check in the box by their name. I'd like to add another field in the same record that would give me a total of the number of boxes checked in that record. Thank you for your willingness to be of help.

Deborah

[Genx]

Can you post a sample at all?

[comment]

I think Genx' first suggestion should work for you: define a calculation field (result is Number) =

ValueCount ( yourCheckboxField )

The way you have it set up now is rather limited in terms of reporting and summarizing. For example, you cannot get the same report from the accounts point of view (each account selected how many fabrics). You should consider a full relational approach, with separate tables for Fabrics, Accounts and Choices.