Filter control on a checkbox for any value

[Lewis_Levin]

I have a filter control on a checkbox column. The value of checkbox can be true or false.

However, as a filter on the canvas, I can’t find a way to show ALL of the records. That is, it does NOT seem possible to enable the filter to select for true (checked) OR false (unchecked). Note this does NOT mean I want a checkbox that can have both states–that is impossible and not the point of this question.

This is about a FILTER that can allow ALL rows to be matched, or only rows that are checked, or only rows that are unchecked.

This is what filters on select or multi-select columns allow. A checkbox is really a select column with only 2 permitted values of which only one can be chosen.

If I changed the column to select with values of yes or no then this would be easy. It should also work with checkbox or toggle.

Any idea of how to do this?

[Quentin_Morel]

Hi @Lewis_Levin
Indeed, once you’ve got a checkbox interactive control, it’s white or black, but there is multiple way to avoid this by creating your own tools

First proposal : Create a master toggle to activate or not the filtering of your column. If your toggle is activated, the filter will take into account your interactive checkbox filter, if not, you will see BOTH check or unchecked value of your table as this will behave like there is not filter

Second proposal : Rather than using the automatic filtering, create your own filter, with for example a dropdown select list with three options : all, checked, uncheked

• type /select in the canvas and create this control

• Then configure the filter in your table like this and use SwitchIf() function that will compare the value of the select and apply the filtering according to this.

• Here you go
  

Please find this very brief example here

Please let me know if you have any further question !

Quentin

[Lewis_Levin]

Thanks for your helpful reply.

I built my own filter, but it is a very ugly approach:

SwitchIf(And([multiple select 1].Contains("Unchecked"),thisRow.Wishlist==False()), True(),
         And([multiple select 1].Contains("Checked"),thisRow.Wishlist==True()), True(),
         And([multiple select 1].contains("Dimmed"),thisRow.Wishlist.IsBlank()),True(),
         [multiple select 1].IsBlank(), True()
)

The control values are set as so:

It is unfortunate that when using the “built-in” way–“uses interactive filter”–to generate a filter control for a column, Coda does NOT generate a valid control. I’d like to make this a feature request.

Here is what’s wrong:

1. Even though the checkbox column type only allows a single value in each row, the filter can NOT be a checkbox. Coda creates a checkbox control on the canvas, which can’t work.
2. Even though a checkbox cell can only contain a single value, the filter must allow filtering on any allowed input. The filter is applied row-by-row: any row that matches the filter criteria will appear in the result set.
3. There are seven potential value sets for the criteria:
   i) only checked;
   ii) only unchecked;
   iii) only dimmed (this results when the previous cell value was deleted rather than reversed)
   iv) checked OR unchecked;
   v) checked OR unchecked OR dimmed;
   vi) checked OR dimmed;
   vii) unchecked OR dimmed.
4. The good news is that we don’t need 7 distinct tests to capture all the possibilities. You can see from the SwitchIf above that only 3 tests are needed:
   a test of contains(“Checked”) picks up sets i, iv, v, and vi: namely four of the possible sets
   a test of contains(“Unchecked”) picks up sets ii and vii: two of the possible set
   a test of contains(“Dimmed”) picks up set iii

(Note that there is no way to eliminate “Blank” from a multi-choice list for a filter control: we have to test for it to make sure we return all rows if a person chooses it. It does NOT mean that an empty cell will be matched for a row–that is handled by “Dimmed” and testing for the column.isBlank().)

My formulation works but is admittedly very bad:
a) There is no way to put the checked, unchecked and dimmed states in the list of choices for the filter control so I use text values. They’re obvious enough, but it’s annoying.
b) An And is necessary to test both the filter control value and the value in the cell of the row in the filter loop.
c) It’s tricky code even if you understand SwitchIf and filtering.

It has only one slight advantage: only one control is needed, rather than 4 (a toggle to turn on the checkbox controls and the 3 checkbox controls).

This would all go away with the result of being far less complicated if generating an on-canvas control using the built-in way worked as well for checkbox type columns as for select and multi-select columns.

[Quentin_Morel]

Hi @Lewis_Levin
thanks for this deep analysis. I assume I don’t have such a high level of exceptation on this subject, and the solution I suggested are OK for me.

Otherwise, rather than a checkbox you may use a classic “status” with a select dropdown list, and use this time interactive filter which is great because you can select multiple status.

As you seem very picky (in a good way, looking for improvement) I’m trying to find something that’s better for your use case
Quentin

[Lewis_Levin]

Yes, that is also a good suggestion.

I have a “rights” field that offers choices of “owned” or “streamed”. I could easily add “buy it” or “wish” and then get rid of the checkbox for wishlist.

This would work just as you suggest and I already have a suitable field.

I kinda like the checkbox: I want to go buy this album…

Probably for other reasons Coda developers should make the create a canvas filter control for a checkbox field work correctly. They probably assumed, not unreasonably, that people want to see what’s checked rather than what’s not checked. Though reasonable, the problem is that there is no state for “all” in the current implementation. And “all” is also a pretty obvious need.

[Quentin_Morel]

@Lewis_Levin

Yes, that is also a good suggestion.

Hi @Lewis_Levin, glad this solution was acceptable for you ! Please do not hesitate to reach out if you have any other question
Quentin