Counting Rows that are checked and not checked inside a Canvas Field

Hello, I have a table with each employee in one row and inside the canvas field I have 79 rows starting with checkboxes. I’m trying to create some analysis fields i.e. % checked, % unchecked for each employee so I can also do some roll up stats for our entire employee base but I’m not able to figure out how to count the details inside the canvas field. Do I have to have it in a table inside the canvas field or is there a way to count it as is? Right now I just have it as shown

Hi @Dan_McDermott :blush: !

Maybe this post could help you here :blush:

1 Like

That is awesome! Thank you so much!

1 Like

If you happen to have a nifty formula to count the checked and unchecked rows that would be greatly appreciated too. Trying to get some counts then %'s for analytics.

1 Like

Could you share a sample doc/mock-up doc of your actual setup ? :innocent:
(So we could take a look and help you out here :blush: )

PS: Happy :coda: Community’versary by the way :cake: !

1 Like

Oh nice! I didn’t know it was Community’versary! :smile:

Sure here is link to analytics page. Thanks for the help

Ok :sweat_smile: … So, I really didn’t see that the trick from @Paul_Danyliuk relied onto some Black Magic as he likes to call it :blush: … So I won’t be able to explain what happens here (:innocent: … sorry :sweat_smile: )

But, I still added 3 columns to your doc.

One for the count of checked items (just after the Split into lines called Split & Checked ) and 2 for the unchecked one (Split & Unchecked 1 and Split & Unchecked 2) .

The formula in the Split & Checked column is almost the same Paul used in the post I referenced earlier :blush:

thisRow.[Split into lines].Filter(
  ParseJson(
    CurrentValue._Merge() + "",
    "$..children[?(@.style == 'CheckboxList' && @.isChecked)]"
  ).IsNotBlank()
).ListCombine().Count()

(and maybe it could be written differently as you don’t have subitems in your checklist but it seems to work as it is so … :sweat_smile: )

What it kind of does is takes the values of in the Split into lines column, access the hidden JSON storing effectively the data (on Coda’s side) and look for CheckboxList where the checkboxes are checked. This creates a list which is then filtered by .IsNotBlank()
To be able to count those items, the list is then “flatten” (by .ListCombine()) and then counted :blush: .

I’ve used the same principle to count the unchecked checkboxes in Split & Unchecked 1 except that this time, the Filter() needs to return the .IsBlank() items from the Split into lines :blush:

The column Split & Unchecked 2 will get you the same result but by doing a simple subtraction between the total of items in the Split into lines and the Split & Checked

Now that I see you already have a table storing your Checklist, if you don’t want to rely on hidden black magic, you could also use a lookup instead :blush: (see the very quickly created sample below)
This will change your display but might help to gather statistics and such :blush:

3 Likes

Thank you very much for helping out with this. Double thanks for providing a better way to go about it on the structure side!

3 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.