The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Dialog’s Query Step: Demystifying the Advanced Mode Capabilities

Dialog’s Query Step: Demystifying the Advanced Mode Capabilities

  • Comments 6

Microsoft CRM Dynamics 2011 beta is out and so is the brand new feature included in this release: Dialogs. As most of you would have already guessed, Dialogs share a part of the infrastructure with already shipped Workflows feature. In addition, Dialogs include some new authoring steps which provide a richer authoring experience along with systematic synchronous runtime capabilities. One such step is the Query step which allows author to actually query CRM records/database at runtime using fetchXml and use the results in later steps of the Dialog. Again those of you who have played with the feature would know Query step has two different modes:

1. Design New: In this mode, author can specify the CRM fetchXml based query using the familiar advanced find control

2. Modify Query Variables: The fetchXml created in the first mode can be parameterized in this mode to provide runtime handle over the conditions used in the fetchXml

Let us have a quick preview over scope and capabilities of the query step. We will start by formulating a simple scenario where the user wants to search the CRM records for an account with a particular name taken as a runtime input from the user. Later we will move on to scenarios with more complex and powerful conditions like search with partial names, search with wildcards etc. Before we begin let us create a small setup to better understand the guiding example.

Scenario Setup

1. A Dialog over account containing following 3 steps:

a. Prompt with simple text type response to get a keyword from the user

b. Query step which will consume the keyword and generate the fetchXml

c. Prompt to show the results to the user

clip_image002

2. Following accounts are present in the system

clip_image004

With this setup, let us explore various scenarios.

Scenarios

1. Accounts with names equal to the keyword

Click on ‘Set Properties’ button to launch the query dialog. Select ‘account’ entity in the advanced find control and set condition as:

clip_image006

Parameterize the condition in modify query variables tab by deleting the hardcoded value “Temp” from Variable1’s textbox and inserting slug corresponding to Keyword prompt step

clip_image008

Save the step without changing the tab. The corresponding fetchXml generated will have the dynamic value of keyword in place of “Temp”

2. Accounts with names containing the keyword

Add condition in advanced find:

clip_image010

Parameterize the condition in modify query variables tab by deleting the hardcoded value “Temp” from Variable1’s textbox and inserting slug corresponding to Keyword prompt step (the same way as it was done in first scenarios.

However since the variable values are direct text replacements within the fetchXml, there is a more powerful option available for ‘Like’ operator by using SQL wildcards.

For example, a parameterization like below is possible in query step which cannot be done through ‘advanced find’ control.

clip_image012

During runtime, this will return all the records containing the keyword followed by static word ‘Limited’. In our case, the results for keyword “ABC” would be:

clip_image014

Similarly, other wildcards:

Wildcard

Use

_

Single letter replacement

[characterlist]

Any single character in characterlist

[^characterlist]

Any single character not in characterlist

*Note that this would work only in case of ‘Like’ operator of fetchXml and not with equals.

**If these operators are to be used not as wild cards, these should be escaped properly by enclosing them within square brackets [%] & [_]

3. Using default account in parameterization

Suppose we want to select a single account from a parameter, but if that parameter is null, we want to provide a default account. This scenario is supported by the existing workflow infrastructure through “default value” section in form assistant which works on “select first not null” operation. For data types other than lookup, it works in the same way as in other places in the designer. However for lookups, there is a slightly different procedure that has to be followed.

Suppose we want ABC Corporation as the default lookup. Add condition in advanced find as:

clip_image016

Now go to parameterization tab, move the GUID in the variable1 value textbox to the default value textbox of the form assistant and insert the slug.

clip_image018

In this case, if the parent account of the account on which Dialog was executed is not set, the default account of ABC Corporation would be used.

4. Checking whether query returned any records

A typical case when before creating a record you first want to check whether a record with the same name already exists. Then you can use the record count slug of query step inside a condition step as follows:

clip_image020

Data Types and Operators supported for Parameterization

Currently the following data types along with the specified operators are supported for parameterization

Data Type

Operators Supported

Integer

All except Contains Data/Does not Contain Data

Double

All except Contains Data/Does not Contain Data

String

All except Contains Data/Does not Contain Data

DateTime

Operators which require integral right hand value like Last X Hours, Last X days etc

Lookup

Equals/Does not Equal

OptionSet

All except Contains Data/Does not Contain Data, will not work when multiple options are provided to Equals operator in advanced find which in turn translate to ‘in’ operator in fetchXml

Things you should be aware of before you start authoring

1. Structure of CRM fetchXml generated through advanced find cannot be modified. Thus any query which is not currently supported by advanced find cannot be used in Dialogs. For example as someone pointed out on forum, query on subject entities is not possible as subject entity is not advanced findable.

2. Parameterization of conditions is allowed only for conditions up to 1 level of depth. For example in the following case, the marked values will not be open for parameterization:

clip_image022

In such cases, alternative query designs should be considered.

3. If value of some specific column query result has to be used later in the Dialog, then that particular column has to be included in the fetchXml through add columns in advanced find.

4. If author switches back to Design New tab from parameterizations tab, all the current parameterizations will be lost. This is because the structure of the fetchXml itself can be changed through advanced find.

5. In parameterization tab, strong type validation of values inserted in textboxes is not enforced. A mismatched type will however throw an error at the runtime.

6. Parameterization of lookup type of variables is neither metadata driven nor hardcoded GUID value provided is verified in any way during authoring. Any discrepancies/permissions issue will lead to issues when you are running the script.

7. Query Records parameter should be avoided in case exact number of record count is required and large numbers of records are expected. This is because the value is upper capped by current records per page user setting.

Cheers,

Devansh Dikshit

  • Hi,

    is there a way how to start dialog automatically based on event in the system?

  • This message is very usefull. I was only wondering what I need to do to get a results page in my dialog.

    Thanks!

  • Hi,

    Dialogs are interactive processes. For any async jobs triggered on event in the system you can use workflows.

    Can you please explain the scenario a bit more, how/when you would like to automatically start the dialog?

    thanks

    devansh

  • Wonderful article Devansh!  The CRM 2011 Dialog is a powerful function.

    Question:  Can Dialog Query CRM Data supports responses from downsteam

    Prompt and Response tasks?

    Or allow Query CRM Data within a page like in between two Prompt and Responses?

  • Hi Frank,

    Unfortunately the answer to both the questions would be no. The reason downstream responses are not supported is beacuse of flow of logic, for example suppose the order is QueryStep1, PromptResponse1, PromptResponse2. Now it is possible that the result of QueryStep1 is being consumed by PromptResponse1, and QueryStep1 consumes response from PromptResponse2. This can easily lead to cyclic dependencies, hence is not supported.

    For the second question, we can not have a query step between 2 prompts in the same page because query would involve a db hit. While there is not round trip to server for prompts which are on the same page. So there is no way a query can be synchronously executed between 2 prompts on the same page.

    Thanks

    devansh

  • Hi Devansh good post thanks. Why is the DateTime data type not supported in Dialog Prompt and Response please?

Page 1 of 1 (6 items)
Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post