Query Relational Operator “Gotcha”

As a robust relational-database platform, Microsoft Dynamics CRM Online offers numerous tools to sift through data. The Advanced Find tool is arguably the most robust as it allows users to create ad-hoc queries that can be saved and shared with other users. At xRM (www.xrm.com), Advanced Find is often one of the first things we train our customers how to use, because its concepts can be applied to so many areas in CRM Online.

This is not a post about how to use Advanced Find. Instead, this is a warning about one of the key query relational operators in Advanced Find. Knowing this tip can prevent you from overlooking crucial data or designing a faulty Workflow by mistake.

First, you may be asking, “What is a query relational operator?” In layman’s terms, it is the middle portion of a criterion in a query in CRM Online.

Query Relational Operator Gotcha

In a query, such as the one in the screenshot above, users specify the field on which they’d like to filter, the query relational operator, and then specify the parameters of the filter. Using the screenshot above, the query reads,” Show me all Accounts whose Account Name fields have a value that Begins With the letter ‘A‘ or ‘a’.” The available query relational operators change based on the field type. For example, a Date and Time field will present options such as “Before” or “Last X Months”. The query relational operator we’d like to focus on is the “Contains” operator.

CRM Online users learn early on that when searching for records using the quick find bar, the asterisk, commonly referred to as the “wildcard” character, can be their best friend. If when searching for an Account called “Some Sample Company “, a user enters the phrase “sample”, they will not get the Account record they want. If they use the phrase “*sample” (note the asterisk) they will get Some Sample Company in the results.

CRM Online searches based on character order. Since the first search in this example didn’t begin with the word “some” CRM Online didn’t see it as a match. The wildcard tells CRM Online to search the whole string to find a match, much like one would assume a Contains operator clause would behave.

Now we’ve arrived at our “gotcha”. If a user were to write an Advanced Find query like the one seen below, they would not get Some Sample Company in their search results. But why?

Query Relational Operator Gotcha

Because the Contains operator is already acting like a wildcard. The Advanced Find we just wrote above is literally telling CRM Online to search for an Account record that has an asterisk followed by the word “sample” in its name. Users unaware of this often get frustrated when they don’t see the results they are expecting. More dangerously, it can lead to users overlooking key data and making ill-informed decisions.

Workflows, which are automated background processes, commonly use these kinds of queries to trigger automated actions. Perhaps it’s necessary to automatically assign all Account records that contain the word “sample” in their name to a specific user. If the person who designed the workflow used an asterisk, CRM Online would fail to recognize Accounts it should be automatically assigning.

CRM Online is a powerful solution that is capable of meeting the needs of complex organizations and their data. Little oversights such as this one can have potentially harmful ramifications. Don’t be afraid to seek the assistance of CRM Online professionals at xRM to learn how to avoid pitfalls like this one.

If you would like to learn more tips, tricks, and tutorials for Microsoft Dynamics CRM, please check out our xRM blog and our Success Portal. If you would like to receive training from our team of experts, please inquire about our QuickStart Training.

Read More

Measuring Repeat Business in CRM Online

As CRM Online consultants at xRM (www.xrm.com), we listen to the needs of our clients and then make their vision a reality. This goes beyond implementing requests verbatim. It is important to take in the requirements, understand the driving factors behind them, and deliver a solution that meets the objectives beyond the explicit request of the client.

Recently a client expressed a need to differentiate revenue between first-time and repeat customers. Technically, a simple option set would accomplish this. The requested label of the field was “Client Type,” and the options were simply “New” and “Existing.” However, this solution would lead to less-than-reliable data as it would require user input to set the value. The client was concerned that in adopting a new CRM platform, users would often forget or not have time to verify whether the opportunity was new or repeat business. Fortunately, we settled on a simple way to systematically track repeat business by using a custom rollup field and a workflow.

For those of you uninitiated, a rollup field calculates an aggregate value computed over the child records related to a parent record. In this example, we use a rollup field to count the number of won opportunities related to an account. The process is quite simple.

From the entity in question, Account in this scenario, create a new field. (See below.) In the Type section, select “Whole Number” as the Data Type and “Rollup” as the Field Type. Next, click the Edit button.

Note that once you click the Edit button, CRM Online creates the field and the Data Type field can no longer be modified.

Measuring Repeat Business in CRM Online

The Rollup Field window opens. (See below.) In the RELATED ENTITY section, we choose the entity on which we want to perform the calculation, “Opportunities (Account)” in this example, which means any Opportunity records related to the Account record that has the rollup field. Next, we can optionally specify any filters. In this situation, we define an existing customer as any Account with one or more won opportunities. Therefore, we filter the Opportunities with “If Status equals ‘Won’”. Lastly, we set the aggregation, which will count the number of Opportunities that pass the filter, and then we click Save and Close.

Measuring Repeat Business in CRM Online

It’s important to understand that rollup fields operate on an asynchronous process, and the roll-up is performed every hour. A rollup calculation can be manually triggered by clicking the refresh icon in the actual rollup field on the form.

For testing purposes, it can be good to place the newly created field on the form somewhere to verify that it is calculating correctly. However, the field does not need to be present on any form for the Workflow that we will design shortly to function properly.

Before we can create the workflow, we need the “Client Type” field to exist. The customer in this example wanted the field to reside on the Opportunity entity. This is a simple option set where the values are “New” and “Existing”.

Once the necessary fields are in place, we can design the Workflow. This workflow is triggered whenever a new Opportunity record is created.

The first step is a Check Condition that checks if the Parent Account of the Opportunity has a Won Opportunities value greater than zero.

Measuring Repeat Business in CRM Online

If the Won Opportunities is greater than zero, then the workflow updates the Client Type to “Existing”. Otherwise, the Client Type is set to “New”.

Measuring Repeat Business in CRM Online

This workflow takes the human error component out of properly categorizing repeat business per these specifications. There is not only one way to design this workflow. The requirements of the scenario should dictate how the workflow should be written. What defines a repeat customer? Is there more than one way to establish a customer in CRM Online than with a won Opportunity? What other kinds of automation can be implemented from this logic? These are all important questions that need to be answered. As expert CRM Online consultants, xRM can help you achieve the optimal solutions.

You can learn more tips, tricks, and tutorials for Microsoft Dynamics CRM, please check out our xRM blog and our Success Portal. If you would like to receive training from our team of experts, please inquire about our QuickStart Training.

Read More