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.

1 comment:

  1. Interesting and novel approach. An alternative is to create a workflow that marks the parent record with a hidden flag whenever a child record with name Q is linked to it, unlinked from it, deleted or deactivated. Then just run the query on whether the parent has that flag ticked. It really depends on the nature of what you're querying I suppose. If it's something you are doing on the fly, my way is not suitable, as you have to know up front that you're going to be running this query when you first start to use the system.


Unrelated comments to posts may be summarily disposed at the author's discretion.