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

[Dan_McDermott]

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

[Pch]

Hi @Dan_McDermott !

Maybe this post could help you here

Extract checked checkboxes from a column

P.S. Here you go. Note that I’m not doing anything cool with indenting. E.g. if you have nested checklist items, it will not smartly resolve incomplete parent items or such. It will simply filter for lines that are checked/unchecked and combine those together:

[Dan_McDermott]

That is awesome! Thank you so much!

[Dan_McDermott]

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.

[Pch]

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

PS: Happy Community’versary by the way !

[Dan_McDermott]

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

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

[Pch]

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

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

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 … )

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 .

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

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 (see the very quickly created sample below)
This will change your display but might help to gather statistics and such

[Dan_McDermott]

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