Forum Discussion

MeaganMcOlin's avatar
Qrew Trainee
3 years ago

Counting inputs from multiple multiple-choice fields


We have a table with several multiple choice fields. Each multiple choice field is either left blank, is marked "complete", or "incomplete". Somehow me need to count up all the "complete" and "incompletes" in the entire table (not just a single field). I don't know where to start with this? 

Could someone give me some direction?



Meagan McOlin

3 Replies

  • Hi Meagan,

    In the past when someone is looking for a simple way to count the number of responses across many multiple choice fields is to create two formula numeric fields, one to count the number of fields with "complete" and the number of fields with "incomplete" and then build a formula that checks each field and adds one if it does. Something like:

    If([Multiple Choice Field 1]="Complete", 1,0),
    If([Multiple Choice Field 2]="Complete",1,0),
    -Keep adding for each option

    Then for each record you will have a field you can see the number of completes and then do the same for the incomplete field. Then you would be able to report off those fields and make a summary report or pass that data up in a summary field, etc.

    Evan Martinez
    • MeaganMcOlin's avatar
      Qrew Trainee


      This works! However, the totals are at the bottom and not in an actual row so I am not able to make charts with it. I want a pie chart that will show me how many incomplete vs completes I have. Is that possible? (I need the highlighted info in a chart).

      Meagan McOlin
      • MarkShnier__You's avatar
        Icon for Qrew Legend rankQrew Legend

        It is not going to be possible to have a pie chart based on your patients. When you have a pie chart each record can only appear once in the pie chart.  But the problem is any individual patient cannot be simultaneously on two different slices of the pie. 

        The only way I can think to do this would be to set up an automated process which ensures that each patient has two child records on it. The child records would have a Type field populated on them with a multiple choice value of Complete or Incomplete and each patient would have one of each Type of records added to it. 

        You would then look up the number of Completed an incomplete questions down to this Child Table and then by formula the child called Completed would show the number Completed and the Child called Incomplete would show the number Incomplete. Then you would run your pie chart on this Child Table.

        So that setup is easy, but then you would need to have an automated process to create these two child records for every patient.  You can do this by setting up an automation or a pipeline to create the tooth Child records whenever a patient record is created.  The table would initially be populated manually by say dumping a spreadsheet of patients into excel with the single field of the record ID and importing that into the child table with a fixed column of complete. Then repeat the process with a fixed column of incomplete.

        You would need to have to deal with the possibility that a patient record might get deleted. You would want to have an automation or pipeline set up to delete the children if the parent got deleted. 

        Do you like a simpler way is to create a field on the patient record call [Patient Exists?] with the formula of


        ... I looked it up down to the trail table. Then just make sure when you run any reports that you only count Patients which exist.

        Of course, you can also look up in the other field do you like down to the pie chart such as date ranges are some categorization of patients so you have full flexibility to create any kind of pie charts filtered by any fields which are on the patient record.

        Mark Shnier (YQC)