WMF Search Discovery

added comp:Search git:civicrm-core core~88 triaged labels

Sounds great.

@eileen could you make the analysis linked above public?

These are examples from @JonGold's client

A search of "All members of group 'Alumni' along with their total donations, if any, to the 'Alumni Fund' Financial Type". This isn't possible with Contribution Summary report because you can't show contacts who don't have a contribution.
Nestable AND/OR blocks, easily reordable on the search screen.
An option to display all fields, not just "exportable" fields. E.g. "Address ID" isn't currently exportable, but is necessary if you're sending your address list off for cleaning to a third party.
The ability to choose at search time what fields you'd like returned and in what order, not unlike CiviReport/Extended Reports.
When displaying search results that join entities on a one-to-many basis (e.g. contacts with contributions), the ability to return search results with some fields grouped - E.g. sum of contributions, or denormalizing multiple email addresses into separate columns.
First-class support for multi-record custom field groups.
The ability to join to the same table multiple times. "Show me all contacts in the 'Major Donor' group with total lifetime donations in one column, total donations in the past 3 years in the next column, and total donations in the last year to the Capital Improvement financial type in the final column."
The ability to search by a HAVING clause. E.g. "Show me all contacts who have attended 3 or more Events of type Meeting".
There should be a mechanism for putting search results on dashlets.
A toggleable "statistics" area. E.g. "Show me all contributions this year, with totals at the bottom".
Pseudofields, like "Display Address" in Extended Report, ideally can be added via a hook (which most likely will provide a way to modify the SELECT clause of the query).
Search on non-standard custom fields. I think this is implied by the "use APIv4 Explorer for Search Builder", but if someone extends custom fields to a new entity, the custom fields should be available for search.

Some notes from call:

Scratch-sheets

Expressiveness in APIv4

Things that you can't yet express as part of query:

Select and Where expressions:
- With functions
- With subqueries
Groupings
- Aggregate functions
- GROUP BY
- HAVING
Joins
- Cartesian joins
- Multiple joins (same table)
Temporary tables / Scripting / Multiple passes through data

Near term goal

Mockup of a UI/pageflow/workflow which:

Addresses the usability problem of needing to choose the right search UI upfront (advanced search vs search builder vs reports)
Uses more mixable fragments

Is there a canonical place to put examples of searches that should be doable comparable to the list I gave @eileen that she posted above? In discussion with my client today, two more came up:

Searching for non-primary details should be...less problematic.
Wildcards don't work consistently across fields, which is a UX problem. I won't detail this one too much because I think Search Builder is largely immune to this by letting you select a variety of operators.

One particular feature of Raiser's Edge that I wanted to draw out is that it asks the user, before showing the search screen, what kind of query it is - contacts, contributions, etc. This lets RE choose a base table from which to build the query. Having a user-defined base table solves a lot of the problems associated with multiple joins, non-primary contacts - essentially, any problem that can be expressed as, "I get back multiple rows for the same record". It also lets us build more performant queries than using DISTINCT because we can eliminate duplicates with an indexed primary key.

@JonGold I think just add any new thoughts on here / on chat/ @colemanw is actively working on this this week

Attached is a zip file of Raiser's Edge screenshots. What follows is some context to explain what you're looking at.

'Screenshot from 2020-01-29 11-30-12.-crop.png'
'Screenshot from 2020-01-29 11-30-21.-crop.png'

Starting a new query. You can choose the type of query, which sets a base table. You can also choose a static or dynamic query. This is roughly equivalent to static/smart groups - but the actual values returned by the query are saved, not just the contact IDs. That will make more sense below.

'Screenshot from 2020-01-29 11-31-04.-crop.png'
'Screenshot from 2020-01-29 11-31-08.-crop.png'
'Screenshot from 2020-01-29 11-31-26.-crop.png'
'Screenshot from 2020-01-29 11-31-32.-crop.png'

The basic query screens. "Criteria", "Output", and "Sort" are all separated - which maps to the CiviReport concept of a "Filters", "Columns", and "Sorting" tab. From a UX perspective, I think asking for the search criteria before the output makes more sense than the CiviReport order. Note also that when doing a constituent ("contact") search, sections are available for spouse's details, first/last/largest gift, etc. IMO these sorts of opinionated options on what "joins" to display make a lot of sense.

I mentioned above that static queries save their data. Every static query saves its output in its own table in the db with a table name of STATIC_RECS_X, where X is a query ID.

'Screenshot from 2020-01-29 11-31-54.-crop.png'
'Screenshot from 2020-01-29 11-32-02.-crop.png'

When opening the "Addresses" group, you can select "preferred" (primary) address, or select by address types. You can have criteria or output that work across multiple address types - "Home address is in CT, Work address is in NY, display both addresses".

Also, when selecting fields' criteria, the options for what filter to add is more sophisticated than "HTML Type" - see the second screenshot, where address-specific options are displayed. This is likely out of scope for now but worth noting.

'Screenshot from 2020-01-29 11-32-25.-crop.png'
'Screenshot from 2020-01-29 11-32-29.-crop.png'

On the other hand, most fields' criteria are very similar to Search Builder/API4, except the "ask" option at the bottom.

"ask" allows you to save a (dynamic) query but pop up a dialog box for the user to enter a particular value at run-time. This is similar to what we do with custom searches; build arbitrary custom SQL and allow the end user to supply a variable or two. However, custom searches require a dev; in this case, an administrator can build a complex query to give to an end user. The Data Processor extension - or Drupal Views' exposed filters - are the analog in Civi world.

In fact, I think it's notable just how much of the functionality and UI here mirrors Views, which we should probably also consider as UI inspiration.

'Screenshot from 2020-01-29 11-32-39.-crop.png'

The multi-select box. I think Select2 is far superior.

'Screenshot from 2020-01-29 11-34-49.-crop.png'

"VB" is Visual Basic. Essentially, the language an RE "extension" is written in to provide custom field types.

'Screenshot from 2020-01-29 11-35-28.-crop.png'

Attributes are the RE equivalent of custom fields, though the db structure is very different. They're all stored in a single "ConstituentAttributes" table. It would be like if we only had one custom field set, but it was a multi-record custom field set. The first column is "Category", which would be like a custom field set. Other fields in the set would chain-select based off each other.

What's notable here is the ability to produce output that is currently impossible with multi-record custom field sets in Civi. E.g. If you have an "Education" multi-record field set in Civi with fields "Degree Type" and "School", you can't search/report that says, "Show me PhD holders and the school they got the PhD from" without also displaying the schools their other degrees are from.

'Screenshot from 2020-01-29 11-36-06.-crop.png'

You can search on "summary" fields - but these are calculated on the fly, unlike Civi's "Summary Fields" extension (but similar to CiviReport). This allows queries like, "Show me people who have given > $50K total to the Refugee Relief financial type". While this is possible in CiviReport, you can't control output to say, "Show me those people as well as their total giving to the organization" or "show me their giving to this Financial Type both over a lifetime and in the last three years".

'Screenshot from 2020-01-29 11-37-39.-crop.png'
'Screenshot from 2020-01-29 11-38-01.-crop.png'
'Screenshot from 2020-01-29 11-38-09.-crop.png'
'Screenshot from 2020-01-29 11-38-15.-crop.png'

Besides queries, RE still has reports. My client doesn't use these much except for financial integration and couldn't give me great info here - but in general, it's much more common to filter reports by query results than it is to filter CiviReports by group.

'Screenshot from 2020-01-29 11-38-43.-crop.png'
'Screenshot from 2020-01-29 11-38-54.-crop.png'
'Screenshot from 2020-01-29 11-39-41.-crop.png'
'Screenshot from 2020-01-29 11-39-46.-crop.png'
'Screenshot from 2020-01-29 11-39-56.-crop.png'
'Screenshot from 2020-01-29 11-40-07.-crop.png'
'Screenshot from 2020-01-29 11-40-09.-crop.png'
'Screenshot from 2020-01-29 11-40-22.-crop.png'
'Screenshot from 2020-01-29 11-40-34.-crop.png'

Views of the export screen. The main thing to note is that much of the functionality described above is also available on export. I think the new Search UI will need a button that exports the exact results displayed on screen from a query, to account for the grouping etc. you can do.

'Screenshot from 2020-01-29 11-44-39.-crop.png'
'Screenshot from 2020-01-29 11-45-09.-crop.png'

Back to queries - you can choose which phones/emails/addresses to display by location type in any combination. You can do this on export in Civi, but not on search/reporting.

'Screenshot from 2020-01-29 11-45-34.-crop.png'

You can display related records' data in the query. Again, possible on export but not on search or (most) reporting. I also believe these accordions are recursive - opening the "Individuals" would allow you to drill down by relationship type, then to a complete set of fields comparable to the ones we can view on the original contact.

raisers-edge-screenshots.zip

The tl;dr of above:

We've discussed convergence of search and reporting - but currently Civi has functionality that's only available via export (e.g. display related contact's fields). We should consider that as well.
Separation of filters, columns and sorting like in CiviReport feels like a necessity.
Joining multiple times to the same table solves several of the current limitations of Civi search (displaying multiple phone numbers per contact, multi-record custom field set searches, etc.)
Grouping on the fly for customized "summary fields" is very powerful, and is arguably the original reason CiviReport needed to exist at all.
It's fine for a query to run for 5-10 minutes if it's sufficiently complex; users who need that sort of functionality are used to it. My client says that before RE optimized certain queries, he would run them overnight.

Thanks @JonGold this is super helpful!

Just to add to the great brainstorming...

+1 for some UX process that asks what kind of search is to be done, which at a developer level is asking for the base table. The current Advanced Search option for returning results as x, y, z is clunky. We want something that works both for a moderately skilled user as well as a power user.

It might be worth looking at drag and drop UX for configuring column order in search results / report layout builder. The profile editor is a great start in this direction. Stats could be dragged into a box at the bottom of the layout...

On the long wish list for reports should be support for cross-tabs. I've built them over the years for various clients, but MySQL does not provide good native support for something like pivot (SQL Server, Oracle), model (Oracle), or crosstab (postgres). They're especially useful for Activities and accounting stuff.

Moving this issue under https://lab.civicrm.org/dev/report/ and adding @artfulrobot @jamienovick1 @jtwyman
Created wiki page with results of discovery project and recommended overhaul of search and reported.
Added phase 1 meta tasks and tagged them to this milestone %Search Builder Overhaul - Phase 1
Added tasks associated with future work to this milestone %Search Builder Overhaul - Phase 2

moved from core#1543 (moved)

changed the description

closed

moved to core#3235 (closed)

WMF Search Discovery

Child items 0

Activity

Scratch-sheets

Expressiveness in APIv4

Near term goal