3 Replies Latest reply on Jan 6, 2011 9:48 AM by ZhizhongNeo

    Count of Checkbox Field that has 84 Values

    ZhizhongNeo

      Title

      Count of Checkbox Field that has 84 Values

      Post

      Hi, i am facing this problem whereby i have to count the frequency of each checkbox value of a TimeSlot Field.

      My Student Schedule::Available_TimeSlot Field has 84 checkboxes: TS001 to TS0084 and the field is based on a valuelist of another table TimeSlots::TimeSlot ID.

      What i want to accomplish is to find out which are  the top 3 time slots that has the most students.

      Hence, I need to count the frequency of each timeslot that occurs in my students records and then present the top 3 timeslots in a graph/field/etc.

      I'm thinking of creating 84 fields that uses Summary of Count of each value but there are 2 problems

      1. It is very tedious

      2. Any new time slots will not be updated dynamically.

      Is there a better alternative to it?

      student_scheduler.png

        • 1. Re: Count of Checkbox Field that has 84 Values
          Rajkumar

          Creating 84 fields is really tedious as well as time and space consuming.

          Here what u need to do is, create a field(say A), and add a value list consisting of all those 84 values.

          Now u need to write a script:-

          set variable(abc; value : A"its the field consisting of check box set values ");

          It'll return the values which are marked in check box, now count total values using function "value count", this way u can count no. of values marked on in the check box of that field. and then u can compare what so ever u want to compare and perform next operation.

          Try it, it might be helpful to u, and reply in case of further problem.

          • 2. Re: Count of Checkbox Field that has 84 Values
            philmodjunk

            Create a related table so that you can list your 84 check box values as 84 related records in a portal. (You might add portal filtering driven from a value list formatted field so that your users can work with subsets of the total).

            Add a single value checkbox to select any given value.

            Since these values are now all in separate records, you can perform a find for all records where the check box field was selected, sort by the value and display the results with a summary report that uses a sub summary layout part and a count type summary field to report the total records in each group. (Use just the sub summary part with no body part if you want one line for each value.)

            This approach makes it fairly easy to add/remove/change the values listed and still get a report showing how many selected any given value.

            The main draw back to this result is that you have to create a script that generates the 84 related records each time a new student record is created.

            • 3. Re: Count of Checkbox Field that has 84 Values
              ZhizhongNeo

              Thank you all for the contributions, in the end, i did this instead:

              Any way to make the coding more elegant is greatly appreciated Cheers!

              Set Variable [ $tsid; Value:ValueListItems ( "Beautyful Minds Solutions"; "timeslots" ) ]

              Clear [ Student Scheduler::best timeslot_1 ]

              [ Select ]

              Clear [ Student Scheduler::best timeslot_2 ]

              [ Select ]

              Clear [ Student Scheduler::best timeslot_3 ]

              [ Select ]

              Clear [ Student Scheduler::freq_best_timeslot_1 ]

              [ Select ]

              Clear [ Student Scheduler::freq_best_timeslot_2 ]

              [ Select ]

              Clear [ Student Scheduler::freq_best_timeslot_3 ]

              [ Select ]

              Loop

              Enter Find Mode [ ]

              Set Field [ Student Scheduler::timeslots_available; LeftValues($tsid;1) ]

              Set Error Capture [ On ]

              Perform Find [ ]

              //this line is to check whether is frequency of time slot > than the lowest timeslot as to skip 2 if statements as we move along, as most of the time slots will only have 1 or 2 hence i will save more processing time not checking the other 2 if statements than checking this line (i have some really slow computers using my system and there can be many records

              If [ Get(FoundCount) > Student Scheduler::freq_best_timeslot_3 ]

              If [ Get(FoundCount) >= Student Scheduler::freq_best_timeslot_1 ]

              Set Field [ Student Scheduler::freq_best_timeslot_3; Student Scheduler::freq_best_timeslot_2 ]

              Set Field [ Student Scheduler::freq_best_timeslot_2; Student Scheduler::freq_best_timeslot_1 ]

              Set Field [ Student Scheduler::best timeslot_3; Student Scheduler::best timeslot_2 ]

              Set Field [ Student Scheduler::best timeslot_2; Student Scheduler::best timeslot_1 ]

              Set Field [ Student Scheduler::best timeslot_1; LeftValues($tsid;1) ]

              Set Field [ Student Scheduler::freq_best_timeslot_1; Get(FoundCount) ]

              Else

              If [ Get(FoundCount) > Student Scheduler::freq_best_timeslot_2 ]

              Set Field [ Student Scheduler::freq_best_timeslot_3; Student Scheduler::freq_best_timeslot_2 ]

              Set Field [ Student Scheduler::best timeslot_3; Student Scheduler::best timeslot_2 ]

              Set Field [ Student Scheduler::best timeslot_2; LeftValues($tsid;1) ]

              Set Field [ Student Scheduler::freq_best_timeslot_2; Get(FoundCount) ]

              Else

              If [ Get(FoundCount) > Student Scheduler::freq_best_timeslot_3 ]

              Set Field [ Student Scheduler::best timeslot_3; LeftValues($tsid;1) ]

              Set Field [ Student Scheduler::freq_best_timeslot_3; Get(FoundCount) ]

              End If

              End If

              End If

              End If

              Set Variable [ $tsid; Value:RightValues($tsid;ValueCount($tsid)-1) ]

              Show All Records

              Exit Loop If [ IsEmpty($tsid) ]

              End Loop