Sometimes the thing you want to filter on isn't on the record, it's in the records connected to it. "Donors who have given a large gift," "clients seen recently," "contacts with an unmet request." CoolFocus lets you filter a list by its related records, and it's still all point-and-click. If you haven't yet, read about finding records with Filters first.
When you filter Donors, for example, you can add a condition about their Contributions, even though a contribution is a separate record. CoolFocus checks each donor's related records and keeps the ones that match.
• Do they have any? Match records by whether related records exist. For example, "clients who have any request" or "donors with no gift this year."
• How many? Match on the number of related records, greater than, less than, exactly. For example, "donors with more than 5 gifts."
• What's the total? Add up a number across the related records, the sum (and other totals like largest or smallest), and compare it. For example, "donors whose total giving is over $1,000." When you pick which field to total, start typing in the field box to search for it by name, handy when the related record has many number fields to choose from.
• Who's at the top? Rank related records by a total or a count and keep only the highest matches, "the top 10 donors by total giving." See Ranking with "top N" below.
• Filter by their fields. Jump straight to a condition on one of the related record's own fields, without first choosing has-any, count, or total. Open the condition menu for a related-record field and choose "Filter by [name] fields...", for example "Filter by roles fields..." on a Roles field. Pick the specific field (a choice field, a date, a checkbox, and so on) and set its condition directly.
Once you've built a "total" or "count" style condition (a filter pill like "in the top 10 by total Amount"), you can change any part of it by clicking directly on that part of the pill, the calculation (sum, count, average, and so on), the field being added up, the comparison, or the number. Each part opens its own small menu, so you can, for example, switch from "sum" to "average" or raise the count from 10 to 25 without deleting the condition and rebuilding it from scratch.
If you change the calculation to one that needs a field (like switching from "count" to "sum") and haven't picked a field yet, or if a number you entered isn't valid for the comparison you chose, CoolFocus marks that part of the pill so you know it still needs your attention before the filter can run.
Inside any related-record condition you can add sub-conditions on the related record's own fields. This is how you ask precise questions:
• "Donors who have any gift over $250."
• "Clients with a visit in the last quarter."
• "Contacts with any request where the status is not Met."
The same sub-fields are available whether you reach them through a "has any" style condition or through the quicker "Filter by [name] fields..." shortcut described above. A few notes on how those sub-fields behave:
• Choice fields inside a related record (like a role type or a status) offer Is, Is not, and similar choice conditions, using that field's own list of values.
• Yes/No fields inside a related record use Yes / Checked and No / Unchecked, the same is true / is false conditions used elsewhere in CoolFocus.
• Date fields inside a related record support relative day-count conditions like more than, exactly, or less than a number of days ago. Type the number of days instead of picking an exact date, so a condition like "role started more than 30 days ago" stays accurate as time passes.
A regular total condition compares against a number: "total giving over $1,000." A ranking condition instead keeps a fixed number of the highest matches: "the top 10 donors by total giving."
To build one by hand:
Open a related-record total or count condition, for example Contributions → sum.
Choose is in the top instead of a number comparison.
Enter how many records to keep, up to 1,000.
The fastest way in is the guided shortcut: open the filter menu on a list like Donors and look for Top donors by Contributions... under Quick filters, or the suggested Top donors by Contributions chip next to the filter bar. It walks you through picking what to rank by and how many to keep.
A ranking pill reads back as, for example, in the top 10 by total Amount or in the top 5 by number of contributions.
Ranking with no other conditions inside that same related-record filter ranks across every record ever recorded: every fund, all time. To answer "the top 10 donors to the Baby Bottles fund this year," add Fund is Baby Bottles and Date Received this year as sub-conditions inside the Contributions filter, not alongside it. CoolFocus keeps a ranking's sub-filter expanded and shows a warning when it has no sub-conditions yet, so you don't miss this.
Some fields hold a list of entries directly on the record instead of pointing to a separate module, for example the Roles field on a person. These are called collection fields. For a collection field, the condition menu still offers Has any and Has none to check whether it contains anything at all, plus a Filter by [collection] fields... option.
Choose Filter by [collection] fields... to pick one of the collection's own fields, for example Role Type, Start Date, or Active on a Role entry. Once you pick that field, you get the same conditions that field type normally offers elsewhere in CoolFocus:
• A choice field inside the collection offers is, is not, and the other usual choice conditions, not just Has any / Has none.
• A date field inside the collection offers on, before, after, between, and relative options such as more than a number of days ago, entered as a day count.
• A Yes/No field inside the collection offers is true and is false.
A collection can also have a count or total condition of its own, for example totaling an "Hours" field across a Time Sheet Entries collection. When you pick which field to total, start typing in the field box to search for it by name instead of scrolling through the list.
Example: to find clients with a Singer role, on Clients add a condition on Roles, choose Filter by roles fields..., pick Role Type, then choose is and select Singer.
You can also filter clients, visits, and cases by their Custom Chart, Assessment, and Form entries, the same way you filter by Contributions or Visits. Pick the specific chart, assessment, or form template from the list, then choose "has any," "count," or "total" and add sub-conditions on that template's own fields, just like any other related record.
Because each chart, assessment, and form is tied to a specific template, a condition like "has any Ultrasound chart entry" only counts entries from the Ultrasound template, it does not include entries from other chart templates the client may also have. This keeps counts and sub-condition matches (such as a field inside that chart) scoped to the template you actually selected.
The picker lists the templates under headers such as Charts, Client Forms, and Assessments so that you can find the right template quickly, especially once you have many templates in use.
When you add sub-conditions on an assessment template, you are not limited to the raw answers a client gave. Every assessment template with scoring configured also exposes its computed results as filter fields:
• Overall score, the numeric total for the assessment.
• Overall result, the named risk band the score falls into (for example "High Risk" or "Low Risk"). Pick from the template's own list of bands.
• A score and result field for each domain or category the template scores separately (for example "Housing Need Score" and "Housing Need Result"). The result field offers that domain's own named outcomes (for example "Homeless" or "Stable").
This lets you build questions like "clients with an Intake Risk Assessment where Overall Result is High Risk" or "clients where Housing Need Result is Homeless," directly on the scored outcome instead of reconstructing it from individual answers.
If a template is edited later and a domain is removed or renamed, a saved filter that used the old domain's score or result field simply stops matching that condition. CoolFocus flags any condition it had to skip so you can review and update it, instead of silently returning the wrong results.
• Lapsed donors, on Donors: has no contribution this year.
• Major givers, on Donors: total contribution amount greater than 1000.
• Top donors this year, on Donors: Contributions total Amount is in the top 10, scoped to Date Received this year.
• Recently active clients, on Clients: has any visit where Date is this month.
• Busy cases, on Clients: more than 3 visits.
• Clients with a specific chart, on Clients: has any Ultrasound chart entry where Weeks Pregnant is greater than 10.
• High-risk clients, on Clients: has any Intake Risk Assessment where Overall Result is High Risk.
• Recently started roles, on Clients: Filter by roles fields..., Start Date is less than 7 days ago.
• Clients with a Singer role, on Clients: Roles → Filter by roles fields... → Role Type is Singer.
To count clients who booked more than one ultrasound, and how many of them actually came in, build the filter on the Clients list against their Appointments. This uses the appointment's own Appointment Type and Status fields as sub-conditions, the same way as any other related record.
An appointment's Status tracks where it stands, for example Scheduled, Confirmed, Checked in, Completed, Cancelled, No-show, or Show. Show means the client was present, so it is the status to filter on for attendance. See Appointment Icons on the Scheduler Calendar for the full status list.
Clients who booked repeat ultrasounds
Open Clients and start a new filter.
Add a condition on Appointments, choose count.
Add a sub-condition: Appointment Type is Ultrasound.
Set the count condition to greater than 1.
Save the filter as a segment, for example "Repeat Ultrasound Bookings." The number of rows in the segment is the number of clients who booked more than one ultrasound.
Of those, how many attended
Add one more condition to the same filter before saving it as a second segment:
Add another condition on Appointments, choose count.
Add two sub-conditions on that condition: Appointment Type is Ultrasound, and Status is Show.
Set the count condition to greater than 0.
Save this version as its own segment, for example "Repeat Ultrasound Bookings, Attended."
Compare the row counts of the two segments: the first is everyone who booked repeat ultrasounds, the second is how many of them showed up to at least one.
• Saving & Sharing Segments, save any of these so you never rebuild them.
• Advanced: Writing Queries with CQL, the same questions written as text, for power users.
