SearchKit: Add ability to search and filter by active periods
Overview
Some entities (Relationships, Memberships, Cases, Events, Recurring Contributions) may be defined by a start date and end date that indirectly define a period in which the entity is active or not.
This is a proposal to enable the possibility to search for entities active during a specific period via SearchKit and also filter results through related SearchKit Afforms.
Example use-case
- Relationships: Search for active volunteers on a specific year
- Cases: Get a pivot report with active cases per week
- Events: Count number of active events on last year
- Memberships: List active members per month
- Recurring contributions: Get a pivot report with the active recurring contributions per month
Current behaviour
Currently, search for active relationships it’s only available through Advanced Search and Relationships Report, but it’s not possible to do this search via SearchKit.
@ayduns, points in this SE answer a way to search for a specific active period.
This it’s good, but not enough. It’s very tricky to build this clause intuitively. And if you create an Afform with this SearchKit you can’t expose start dates and end dates to reproduce this clause.
Proposed behavior
Create an “Active period” search field selector available for entities with start and end dates: Relationships, Events, Memberships, Recurring contributions and Cases.
This would allow for example:
- Click on Search -> Search Kit.
- Search for Relationships or Events
- Select Active Period in where clause
- Choose options for filter =/is Between and Pick Date/Date Range...
Active period selector, like in the image below:
Comments
Would answer questions like:
How to search active relationships (e.g) during past year?
Is it possible to get point-in-time membership data? duplicate
Create a report or graph of the number of campaigns active in any particular month
Will cover the use of this extension Historic Membership Data