Calculating/reporting value list checkboxes

[Michael Sweet]

Hi Everyone,

I am new here, so I searched a ways back in the archive but did not find a post explicitly about my question. If this has been covered in previous posts, I apologize and humbly ask for direction to that post.

Anyway, my problem is that I have a checkbox set populated by a value list, and what I am wanting to do is tally up--across all my records--how many times each checkbox was checked.

Specifically, I consult with clients about many topics in a given conversation, and I have a checkbox for each of those topics. I am trying to generate a report that shows how many times in a given year I discussed topic 1, how many times I discussed topic 2 and so on.

I have found references to 'bucket reports' but they are for much earlier versions of FMP (2 and 3) and I am still new enough to FMP in general--having come from the world of Access--that I can't see how to make them work in FMP7.

Can someone help me out? Any advice MUCH appreciated!

Thanks so much,

Michael

[fmsavey]

Check boxes allow multiple values to be entered into a field. The values are entered in the order they were checked. So one record could have multiple values that could be counted. For clarity I am going to call your table "primary" and the field in which you store these values "topic".

Here is one way to accomplish this. Create a new table call it "topic count" in the same file. In this new table add a text field and call it "topic". Join that field to the field in your other table that holds the topics that you check off. primary::topic = topic count::topic. Create a calculation field in the "topic count" table called "topic count" and define it as Count (primary::topic).

Now to make this work... for every topic in your value list add a record in the topic count table and enter into the topic field the topic name. The moment you add the name it will tell you the total number of times that name appears in the "primary" table.

How you a view this information from the primary table could be a topic in itself. However, one possible solution is to define another relationship with a new table occurance called "topic count 2" that looks like this.

primary::topic x topic count 2::topic

The x is a cross join so it would find every record in the topic count table so if you added a portal showing "topic count 2" you could display the count of all topics in each record in the "primary" table.

[Søren Dyhr]

There is a drawback with relations and Count( as method ...it scales badly, while summaries are a more correct way to deal with say massive surveys.

I do here exceptionally to my own standards use repeating fields because they only deal with a utility purpose!!!

--sd

CheckSurvey.zip

[John Mark Osborne]

Nobody seems to have mentioned that the design of this solution is flawed. Reporting on checkbox fields is cumbersome at best. The ideal would be to use a many-to-many relationship. There would be a table for Clients, another for Topics and then a third to join the two tables. This would allow entry of the topics as records into the join table so that a report could be created with standard subsummary parts.

[Søren Dyhr]

Yes checkboxes were made with out much thought about 1NF, and I pledge guilty for not making enough proverbial noises here - because I was carried away with what an old template of Comment's did - I know that I have been chasing others for such behaviour, shame on me!

--sd

[comment]

A many-to-many without a join table has limited reporting capabilities, but I wouldn't go so far as calling the design flawed. In some cases, it can be a reasonable compromise.

[fmsavey]

What data goes into the third table to join the two?

[databasic]

I use a technique that might meet your needs

You need a clientID, a DiscussionID and a topicID

Then for each discussion record you enter the date, the clientID and check box against each topic ( which is one field - formated for checkbox from a value list of topics). That represents one record. Then you use a loop script to create a new record for each topic discussed - using varaibles and the value count function. So if you discuss 5 topics you create 5 records from the initial record ( you then delete the initial record ). Then design a summary report based on Client - Topic - Date. Add a count summary field if desired.My only concern is I'm not sure when the countvalue function was added to FM.

[Michael Sweet]

Thanks, folks!

I used the technique posted by fmsavey (thanks!) and got my count. Now I need to add various logics to it and get the reports I want.

Thanks also for pointing out that this is suboptimal architecture. I am in an environment that is really tiny, and using a system designed by someone else, so I kind of have to "play the cards I am dealt."

But, since there are only a few of us, the "by hand" nature of adjusting this solution won't be onorous or frequent (if at all).

Thanks again, all!

-M

[PaddyO]

I have a similar problem but I am trying to count how many records have a value from a drop down list. Is there a calc to define that since there is no chance of a 'multiple choice' in my case?