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.
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?
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.