Doing a 'null' search in CRM

One of the shortcomings of Advanced Find in Dynamics CRM is the inability to do a 'null' search. What I mean by this is the ability to pose a query like 'show me all accounts which have never had a phone call created for them'. If we were asking 'show me all accounts with  a phone call due this week' we have no problems but asking CRM to return records where related records don't exist simply can't be done.

Here are a few simple workarounds to this issue.

Using Workflow

You can get around this, relatively simply, using workflow. Let us take the example above. First of all create a bit field on the account form to store whether there has been a phone call created or not. Now all we have to do is create a workflow that triggers on the creation of a phone call activity and updates the account field we've created.

We now have a field we can search on to tell us whether a phone has ever been created and therefore if the account has ever had a phone call created against it.

In the case where we want to know whether or not there are phone calls in progress, we would need to resort to a counter to keep track of all the phone calls added/removed/completed. For details on this see the following post:

http://leontribe.blogspot.com/2009/02/leons-quick-and-dirty-workflow-counter.html

As with the counter, you will need to consider all events that could meet your criteria and whether you can capture them through workflow.

Using Excel (method 1)

It is easy to export all the accounts from CRM into Excel. It is also easy to export all accounts linked to an activity via the activity's regarding field where a compulsory field, such as when it was created contains data and where the activity is a phone call.

We now have a list of all accounts and all accounts with a phone call against them. Using a vlookup we can easily compare one list to the other and find the gaps. If you're being really fancy, export the two lists out as dynamic worksheets, combine them into one workbook, add in your vlookup filter and then all you have to do is open up the Excel file to get the results.

Using Excel (method 2)

When you export to Excel as a Dynamic Worksheet you can modify the query Excel uses. See here for details

http://leontribe.blogspot.com/2009/01/reporting-in-crm-with-excel.html

In this case you'd modify the SQL query to do what Advanced Find cannot, that is return accounts with no phone calls attached. Again this can be saved and opened at will.

Using SRS Reports

Although a bit of overkill in my opinion, you could create a report to bring back the information you need. Personally I'd use one of the Excel methods above and add this in as a report if I was thinking of going down this route but the option of an SRS report is open to you.