How to tally ye/no responses.

[nuimage]

Hello All,

I have  a simple form asking users to check the "Yes" checkbox if they prefer a vegetarian meal option.  The field datatype is "Yes/No".   I have a report that i would like to count only those who checked "Yes".  I've created an aggregate field in the report called "Veg Count" and I have selected the field "veg" to do a count function on.  However, i'm unable to count only those who have checked the checkbox.   It gives me a tally of all entries "yes" and "no".

How can i count only those boxes that have been checked and has a value of "Yes".   I cannot figure out how to create such a report.  Any help would be greatly appreciated!

 

[Franchiser]

Hello NUIMAGE,

 

You will still need to use the total and aggregation, but instead of function, you need to choose formula and use a simple SQL statement. Please see sample screenshot below with the SQL code. Please change YesNo with your own field and test_table with your table name in where you want to COUNT.

 

yours truly,

Franchiser

[nuimage]

Franchiser,

Thank you! Thank you! for such a quick response.   Initially,  i tried a select statement but instead of a value of "1",  i indicated a value of "yes".   I completely forgot that Yes/No fields have numerical  values of  "1" or "2".    Your solution worked like a charm.

Thanks again, you are a life saver.

 

nuimage

 

 

[MayMusic]

In Caspio Yes is 1 and No is 0 in formula and calculated fields

[nuimage]

Thank you MayMusic for responding!

[DavidKennett]

Following on from the above, is it possible to some how do a tally on which fields have been answered so that i can do a % completion for each of my forms. I use a mixture of submission forms and reports. 

[nuimage]

Hello David,

did you receive a response to your question?  if not you may want to create a new thread.

regards

[NailDyanC]

On 11/23/2017 at 8:14 AM, DavidKennett said:

Following on from the above, is it possible to some how do a tally on which fields have been answered so that i can do a % completion for each of my forms. I use a mixture of submission forms and reports. 

Hello, if this is still unanswered, you may try using triggered actions with conditions to count only those records from a particular field, so you will know what fields are being updated or answered. You may check this link for reference about triggered actions: https://howto.caspio.com/triggered-actions/triggered-actions-2/

[cheonsa]

Hi All,

Just wanted to share with you this solution when using the Yes/No field in a formula.

There are times that 1 or 0, or 'Yes' or 'No'  is not working for me, so what I usually do is convert it to a Bit.

CAST([@field:YESNO_FIELD] AS BIT).

With this, I can now compare it to 1 or 0.

 

Hope this helps.