Jump to content

Calculating/reporting value list checkboxes


Michael Sweet
 Share

This topic is 5755 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...
  • Newbies

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.

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

  • 3 weeks later...

This topic is 5755 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.