Wednesday, June 3, 2009

The Poor Man's Advanced Find To Exclude Matching Related Records

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"


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.