So, I had a novel approach to a problem regarding the limitations of the Advanced Find function in CRM. When you are searching for a record that does not have a relationship to a some other record, there is hardly a workable set of query parameters you can use to accomplish the search.
For example:
Record Type A is the parent of Record Type B. Data: Record Type A instance Alpha is related to Record Type B instances Beta and Gamma. Record Beta has the name Q. Record Gamma has the name Y.
Exercise: Find all records of Type A that do not have a child Type B with the name Q.
The best that Advanced Find can do is find all records of Type A that have children of Type B without the name Q. This will always return Record Alpha, since Alpha does have another Type B record without the name Q.
To solve this limitation of the Advanced Find, I used a Workflow to augment it. Here's my solution to the problem above:
Workflow: "Find Type B with Q"
If (Related Entity)Type B's name field equals Q, then:
Stop Workflow with status of "Succeeded"
Otherwise:
Stop Workflow with status of "Canceled"
Then, run the Workflow "Find Type B with Q" on every Type A record you care to test against. This is probably the most cumbersome part--especially with a significant amount of Type A records to work with.
Advanced Find: Look for: Type A
(Related) System Job conditions:
System Job Name: "Find Type B with Q"
Status Reason: Canceled
Voila! I now have an Advanced Find that will exclude any Type A record with a related Type B record that has the name Q. It isn't very pretty, and if I had to perform this kind of search on a regular basis, I would have to limit the Advanced Find to making sure the System Job had only been recently run--making sure to run the Workflow on every Type A record first. I'd love to see a more native solution to this problem, but what I like about this solution is how fast it can be put together, and that I do not have to perform the query outside of CRM. It was this mechanism that allowed me to run yet another Workflow on all the Type A records to establish the missing Type B record with the name Q.
