[DONE] Need More Control over Filters on Checklist Fields

[CarsonRedCliffLabs]

This is closely related, yet still different from this previous post: [FIXED] Inconsistent Filter Results for Checklist Item Due Dates

The issue we face is that the Filters available for Checklist Fields are not sufficient to create certain views in our Tape App.

The client uses a Checklist Field called “Lead Tasks” to track open tasks for all their leads.
We have then created a Team View called “Actions Due Today” which is attempting to filter down to show all leads that have an incomplete task with a due date on or before the current date.

The filters used are:

Lead Tasks/Due Date → Any is on or before → Current Date
AND Lead Tasks/Status → Any is → Incomplete

The problem is that unwanted leads are showing up in the resulting view. Every lead that did have tasks with due dates in the past (even when that old task is marked as completed) AND that same lead has an Incomplete task listed (even when that incomplete task isn’t due until a future date)
These all show up in the view.

We need the ability to specifically use AND statements in the filter for individual checklist items, not the entire checklist as a whole.

Is there any other way I’m missing to run a filter that easily displays ONLY leads that have a task listed in the checklist which is Incomplete AND that same task is due on or before the current date?

Adding a short video example demonstrating the issue: Loom | Free Screen & Video Recording Software | Loom

[Tim]

HI @CarsonRedCliffLabs,

thanks for sharing your insights here. Let me figure this out and come back to you asap.

Cheers
Tim

[Tim]

Hi @CarsonRedCliffLabs,

went back and reconstructed your case on my end. I found a simple solution, that should cover your requirements in a most accurate fashion.

Recently, we introduced full access to checklist field values / entries inside the calculation field (thanks to @Felix!) - which enables many use cases, and yours should be among them.

We use a calculation field “Earliest incomplete due date” to retrieve the earliest due date of all incomplete checklist entries. We then format this field as a date, enabling Tape to support full date filters for it.

Here’s how things will look inside the record:

As you can see, the calculated date now reflects the earliest date of the incomplete subset. If I were to check subtask 3 in the screenshot, the field would turn to August 8 (as that is the next one, and so on).

All you need is a couple of lines of JavaScript:

You can now use this field in your view filters, to hopefully achieve your (and the client’s) goal. Hit me up if you run into issues or have open questions regarding this implementation.

I aim to also follow up on this by providing a little quick guide / showcase here inside the Community, on what can be achieved using the calculation field with checklist field tokens. I’m certain this enables many other useful scenarios!

Cheers & as usual, happy building
Tim

[CarsonRedCliffLabs]

@Tim Thank you so much. As always you come out swinging with a great solution. This simple fix does exactly what we need and works great for our situation. You are a master at what you do, much appreciated!

[Tim]

@CarsonRedCliffLabs very glad to hear - and thanks for your kind words.

[CarsonRedCliffLabs]

@Tim - jumping back on an old thread here sorry…but my team was trying to put in a calculation code for the inverse of the solution posted here.
Rather than seeing and reporting on a date of the incomplete tasks, they want to set a calc field that produces a date allowing a filter which answers “when was the most recent completed task” from that checklist field.
They are having trouble adjusting your calculation formula to satisfy that answer. Any tips?