FAQ: Understanding "Not In"/"Not Equal To"
- When attempting to exclude contacts from a report if any of their records/transactions meet a certain criteria presents a unique challenge because the report logic evaluates each record individually against the stated criteria.
- For example, excluding donors from a report if they ever donated to a particular campaign.
- The report logic is looking at every single individual donation record individually and deciding if it meets the criteria. If donation A was to the campaign, it gets excluded. But if donation B was not to the campaign, it gets included. If those donations were made by the same person, then the person gets pulled into the report even if they have an excluded donation. The system is not looking and cannot look at a donor's donation history as a collective/whole and is not evaluating the DONOR, it's evaluating each donation (the same rules/logic applies to memberships, event registrations, etc. - this is why membership reporting becomes very difficult if a member has two membership chains/records; the system is going to evaluate each membership record individually, not collectively, so that if one record matches the criteria but another doesn't, then the member still gets pulled in to the report).
- This reporting logic exists in all relationship databases/regardless of system and is not specific to Fundly CRM/Exago.
- Fundly CRM's advanced search function is specifically designed to search a contact's history as a whole/collectively, rather than evaluating individual records. Using advanced search for the example given above (exclude donors who ever gave to a particular campaign), if any of the contact's records meet the criteria, then the contact is excluded from the results.
- When attempting to write these types of reports, you should create the criteria in Advanced Search and then use the saved search in the reports module to customize the report output (see our tutorial on using a saved search in a report).
- If you select "apply to household" in advanced search when creating this type of search, then everyone in the household will be excluded from the search results if anyone meets the criteria. For example: Terri Smith and Jim Smith are in a household together, and Terri has a donation on her record that is linked to the Annual Appeal 2018 campaign. If we write an advanced search with the criteria "donation campaign not equal to Annual Appeal 2018" and check the apply to household box, both Terri and Jim will be excluded from the search results/will not be pulled into the search results.