How to create a quick search in CRM that spans multiple entities with grouped conditions

Question

We are a housing association with a large CRM system (2016 & SP1). We have a new requirement that requires our users to be able to search for people who are current (ie not previous) occupants or residents or who are not residents (eg contractors)

For this purpose, we need to search the Person entity which has a related Tenancy entity. Person has TenancyType field with possible (option set) values Occupant, Resident, Contractor. Tenancy has TenancyStatus field with possible (text) values Current and Previous.

We tried using the following filter criteria in the quick view on the Person entity:

thinking that it would return all people who are not previous residents. However we noticed that it would filter out contractors because contractors do not have related tenancy records.

We needed to change the criteria to return all contractors OR all residents and occupants with no previous tenancy. So we changed it to the following:

at which point we got stuck because we noticed that it was not possible to AND together the second and the third conditions as the third one is a related entity.

We are wondering what the best way is to achieve the above bearing in mind that we do not want a separate view for each condition, eg one for residents, one for none residents, etc.

Any help or suggestion is greatly appreciated.

Answer 1

Thanks to everyone here who spent time answering my question. The following describes the correct answer: https://community.dynamics.com/crm/f/117/p/241352/666651#666651

Answer 2

It is not possible to do this with a single query.

Instead, you can use two queries. If you do not want to do that, then using reports (as suggested by Alex) or a BI-solution would be other possibilities.