3 Replies Latest reply on Nov 11, 2013 11:53 AM by philmodjunk

    Execute SQL Select Count with values from checkbox



      Execute SQL Select Count with values from checkbox


           I am trying to get a count for the number of members that I have in each different category. Under the member table, there is a field with a checkbox set that allows them to be members of ITA, Affiliates, ITA R.E.A.D., R.E.A.D., R.E.A.D. Affiliates, and Inquiry. They can be members of multiple categories, and most of them are. I have created a table for statistics, that is full of calculation fields to get the number of volunteers in each field. I have been using Execute SQL (Select Count) to get results. So far, it has been very effective, except when dealing with people who have more than one choice selected in the checkbox set. The equation I am using is

           ExecuteSQL ( "Select Count ( * ) from NewContact where Members  =  ?";"";""; GlobFields::MemberType)

           GlobFields::MemberType is a global field that selects one of the values (ITA, Affiliates, ITA R.E.A.D., R.E.A.D., R.E.A.D. Affiliates, or Inquiry) to be counted.

           This calculation works fine if the member only has one checkbox selected, but if two checkboxes are selected, the member is not counted in either. So if I have 5 members, two are only members of ITA, one is a member of ITA, ITA R.E.A.D., and R.E.A.D., and two are members of just R.E.A.D., my calculations show ____ when they should show (_______)

      •           ITA- 2 (3)
      •           ITA R.E.A.D.- 0 (1)
      •           R.E.A.D.- 2 (3)

           What can I do to query to have it count the member if it is a member of the group and something else. I think that the issue might be the equal sign, because it does not equal just the one value, but I'm not sure what I can put in its place to have it still work.

        • 1. Re: Execute SQL Select Count with values from checkbox

               Just to confirm, members is the text field formatted with a group of check box values?

               Ever put a copy of that field next to the original, but with field formatting changed back to Edit box to see what data is actually entered into the field?

               That's a very useful way to become familiar with how data is actually stored in the field when two or more check boxes are clicked by the user.

               The individual check box values are stored in the field in the order that the boxes were clicked by the user, separated by returns. So if a use clicks ITA, then clicks Affiliates, the edit box copy will show:


               but if the user clicks Affiliates, then clicks ITA you'll get:


               That makes counting specific values pretty complicated. With different values in your value list, you might try using the LIKE operator which, with the % wildcard operator can function much like the patterncount() function, but the different values in your value list are not values that will work with that approach.

               I suggest replacing your single text field with a table of related records. The user then selects member values by adding new records in the related table instead of adding values to a single check box. It then becomes much easier to count how many records have a particular value selected. And it's possible to set up a portal with buttons, scripts and conditional formatting that looks and functions exactly like your check box formatted field.

          • 2. Re: Execute SQL Select Count with values from checkbox

                 How exactly would I go about setting up the related table? Would I only have to set up one related table, or would I have to set up a related table for each of my values?

            • 3. Re: Execute SQL Select Count with values from checkbox

                   You would use two related tables. One has one record for each of the values in your value list. The other records the actual values selected.


                   CurrentTable::anyfield X ValuesTable::anyfield

                   ValuesTable::gCurrentTableID = SelectedValues::_fkCurrentTableID AND
                   ValuesTable::Value = SelectedValues::Value

                   Values table has one record for each value with the values from your value list entered into a text field (I've named it "value")  in that table. gCuirrentTableID is a field with global storage. The OnRecordLoad script trigger updates it the the primary key of your current table and "allow creation..." is enabled for SelectedValues in the ValuesTable to SelectedValues relationship.

                   I mentioned a button an conditional formatting earlier and that can be used, but I am remembering a simpler method for the check boxes:

                   Add a number field to SelectedValues. Add this field to the portal row and format it with a single value check box that enters a 1 if selected. Re-size the field so that only the check box, not the 1 is visible. When you click this check box the first time for a given value listed in the portal, it will automatically create a new record in Selected values, a 1 will be entered into this field and the text value will be entered into the Value field. If you click it again, this number field will be cleared. To count selected values, you'll count records with 1 in the selection field and specific text in the value field.