Building Business Processes in Dynamics CRM 2011: Installment 4

Querying CRM Data

Now that we’ve discussed prompts and responses in more detail, consider again the following figure:

Remember from above, this is the Response Details section from the Prompt and Response dialog. In the figure, the Query CRM data option is selected for the Provide Values setting, instead of the alternative Define Values option. These options are only available for the Option Set (picklist) and Option Set (radio buttons) response types.

With the Define Values option, you enter the options manually; use this option for situations when the values are not stored as records in a Dynamics CRM entity.

Use Query CRM data when the values are stored as CRM records. There are several advantages to using Query CRM data, including a couple of obvious ones:

• You won’t have to type as much.
• Your dialogs will always reflect up to date data, without you having to manually update the values.

Others are less obvious but perhaps more important. For example, you can create dynamic, or linked queries. These are where a user’s selection in query 1 is passed as input to query 2, making the second one dynamically dependent on the first.

Here are a few examples of how you might take advantage of the ability to query CRM data in a dialog process:

• Suppose a process needs to prompt a user to select a contact associated with an account. A query can select the contacts associated with the account a dialog is running against, and present them to the user in a prompt. The user selects one, and a case record is associated with the selected contact.
• A process might allow a manager to assign cases based on criteria such as time availability. A query might select users with no scheduled activities and present the list of users in a dialog prompt; the dialog could then assign a case to the selected user.
• A process might allow a user to select from a list of e-mail templates, and use the selected template to send an appropriate follow-up e-mail to a contact. The list of e-mail templates could be created with a query in a dialog, with different criteria used to present templates appropriate for the business issue requiring follow-up.

Designing CRM Queries for Use in Dialog Processes

The key to successfully using Query CRM data is designing your queries appropriately. Remember that the response types that can take advantage of this feature are the picklist and radio buttons options. These are not suitable for queries that return too much data, as you will see in the following simple example.

Suppose you have a dialog process a user can run from a Dynamics CRM Phone Call form. A scenario might be a receptionist receives a call, records the details with a phone call activity, and runs a dialog from the form to assist in creating a case, an opportunity, or some other record type. Let’s assume that you want to present the receptionist with a list of active accounts, and allow the selection of one, for which the record will be created.

We will present a more detailed step-by-step exercise shortly, but for now let’s take a high-level walk-through of how you’d build this and what it would look like.

1. Create a new dialog process, name it “Phone Intake, Create Opportunity”, and base it on the Phone Call entity, and you will give you a process design form that looks like the following:



2. Now, click Add Step and select Query CRM data. The Step Editor will look like this:

   

3. Click Set Properties, and the Define Query dialog will appear. This is the specialized version of Advanced Find you use to define the query that will provide the values to prompts presented later in the dialog. For this example, we will type “Active Accounts” in the Statement Label field, and select “Status Equals Active” in the query design section of the dialog:

   

4. You can click Find to preview the query results, and then, as necessary, Back to Query to refine the query criteria.
5. Click Save and Close, and the Step Editor will look like this:

   

6. For this example, all we will do is add a page with a single prompt and response pair. Click the Add Step drop-down and select Page. Then, click the indented line within the Page section, click the Add Step drop-down again, and this time select Prompt and Response:

   

7. Click the Set Properties button to the right of Prompt and Response, and specify the following in the Define Prompt and Response dialog:
   1. Type Selected Account in the Statement Label field.
   2. Type appropriate text in the Prompt Text field.
   3. In the Response Type drop-down, select Option Set (picklist).
   4. In the Provide Values option, select Query CRM data.
   5. The Query Variables drop-down will then become active, and you can select the previously defined query, Active Accounts.
   6. In the Columns section, select the Account Name column.

       

      After doing all this, you should see something along these lines:

      

8. Finally, click Save and Close, then Save and Close again. Activate the dialog process, and from a phone call form (make sure you save it first) you should be able to click Start Dialog in the Process section of the ribbon, and select the dialog process just created.

Results will vary depending on how many accounts you have in your CRM, but you can tell from looking at the following figure that picklist or radio button controls will not be a very good way to present this list to your users!

The controls we have to work with in dialog processes are drop-down lists and radio buttons, not lookups, so you will need to design queries that don’t return hundreds of records. I’ll spare you the radio buttons version of this, but believe me, it’s even worse!

So…how many records are too many?

Well, you cannot display more than a single page of records in a dialog prompt, and since you cannot have more than two-hundred fifty records on a single page, a dialog prompt that breaks the 250 records barrier will not allow a user to select from all the available records. The minimum setting for Records Per Page is twenty-five, so if you go higher than that, there may be a few of your users who will need instructions on how to increase. On the other hand, the default setting is fifty, and a picklist with more than 25 options isn’t all that unmanageable, so I’d propose fifty or so as an upper bound for the number of records to return in a query designed for this purpose.

Fortunately, there are plenty of approaches you can use to optimize queries for use in Dynamics CRM 2011 dialog processes. I’ll review a couple of my favorite here, and then throughout the rest of the book we’ll examine others in context.

• Since the dialog is written with Account as its primary entity, we can use the current account as a filter for a query of contact records. For many organizations, the number of contacts per account is small enough that it won’t violate the “fifty or so” record count limit proposed earlier.
• The mechanics of constructing a query like this one take a little getting used to.
• We need a way of checking the number of contacts associated with the current account record; and in particular, if there are some, we need to prompt the user to select one and then update the case record. If there no associated contacts, we don’t want to do those things. Sounds like some conditional logic in the dialog, right?

The following figure shows what a dialog like this might look like in the Step Editor. Although it’s not required, this one is presented in stages to make it easier to understand:

Here’s an overview of each stage:

• Stage 1 is essentially the same as the previous example. The Page is collapsed to save space, but it does the same thing as before: Prompt/Response pairs for the basic information needed to create a case record.
• Stage 2 is where the query is constructed by passing the value of the current account record in as a filter to a query for contacts. This is new territory and is presented in detail next.
• Stage 3 performs a conditional check to see if the account has any associated contacts. Only if there are some will the dialog prompt the user to select one of them; and only in that case will the dialog update the previously created case record with the responsible contact.

The following figure shows what a query looks like when values are substituted into it at run time. This is the finished product of Stage 2 above; notice the Statement Label field is Contacts for Account, which you can also see as the label for the Query step in the previous figure.

If you haven’t done this before, you might not even realize there’s a Modify Query Variables tab you can use to substitute dynamic values into a query, but here it is. Notice the highlighted areas: the Account lookup field (schema name parentcustomerid) is passed, via the Variable1 variable, into the FetchXML statement. The tricky part is getting that yellow-highlighted Account(Account) dynamic value into Variable1, so here’s a step-by-step exercise to walk you through it.

Step by Step: Build a Dynamic Query

Assume for the moment that you’ve created the previous dialog up through Stage 1: that is, you’ve gathered the basic/required information in a series of prompts and responses in a single page, and have created the case record. Now it’s time to create the query for contacts at the current account, so position the cursor just outside of the Page block, and follow these steps:

1. Click Add Step, and select Query CRM Data.
2. Type Contacts for Account in the Statement Label field, and select Contacts in the Look for drop-down.
3. On the query designer, click Select, then scroll down to the Parent Customer field and select it.
4. Press the tab key, accept the default operator of Equals, and tab again into the lookup field.
5. Click the lookup button, and select any value you like. I know this seems odd at first, but you need to provide a placeholder here, otherwise you won’t be able to perform the all-important next steps. It should look something like this:

   

6. Now, click the Modify Query Variables tab and you will see this:

   

7. Here’s where the dynamic values part comes in: position the cursor in the Variable1 field in the XML Values section, and then make sure Account is selected in both drop-down lists underneath Look for in the Dynamic Values section. It looks odd, but the first Account refers to the entity, the second to the Account lookup field. Click Add, and then click OK, and you will see this:

   

8. Click Save and Close and you’ve created the dynamic query.

Another thing about that takes some getting used to: once you’ve used this Modify Query Variables tab to substitute a variable into the FetchXML, you cannot go back to the Design New tab. (If you click it, you’ll get a warning that your work will be lost. Which it will, so don’t do it unless you need more practice.)

That gets you through Stage 2 of this dialog; on to Stage 3. Refer back to the dialog overview figure above and focus on the most important thing in Stage 3, the If condition. This is an example of a topic I covered previously, in the section Specifying Conditions in Dialogs. And while the example is specific, the concept is a general one: when you construct a dynamic query, how do you know how many records will be returned? If too many are returned, you run the risk of violating the “fifty or so records max” rule of thumb discussed earlier. If too few records are returned, it gets worse, especially if there are no records returned.

The Create Case for Account example shown above already has logic built into it to manage this problem, but suppose it did not. For example, suppose you were just learning how to build Dynamics CRM 2011 dialog processes and didn’t know about these things yet. You might build a dialog like the following one:

If you compare the two versions of the dialog, you’ll notice that this one does not include any conditional logic in Stage 3. You might be able to guess what happens if you save and close it, activate it, and run it. If the account it runs against has associated contacts, it works fine, but if not, you see the following:

Notice that the Next button is grayed out, so you can’t even complete it! In this example, the problem is that a dialog prompt cannot be displayed without any options, and since the options here are provided by a query that returns no data, the dialog cannot proceed. And that’s why the special Records variable was created: so we can test how many records exist, and take appropriate action.

So how do you go about fixing a problem like this? Let’s continue our example by fixing the current broken version of the Create Case for Account dialog, starting here, in the Step Editor, with the cursor positioned on the single Page in Stage 3:

This is a real-world example, where you’re fixing something that’s broke, so to speak, so while you might not always take an approach like the one I do here…it’s worth seeing it, just in case.

Step by Step: Build a Condition to Check Record Count

1. Click the Insert drop-down, and select Before Step.
2. Click Add Step, and select Check Condition.

   

   Notice that the If condition went before the Page, which in this case is what we want.

    

3. Click <condition> (click to configure), and the Specify Condition dialog will open.
4. Click Select to access the drop-down list in the first column, and select the query, which will be in the Local Values section:
   

   Notice that in this version of the dialog, I’ve prefaced the query with the text “Query: “, to make it easier to find. This is part of a naming convention I will propose a little later in the book.

5. After selecting the query, tab to the second column in the dialog, and select the only available option, Records.
6. Then tab to the third column and notice the available operators are the standard ones you see in Advanced Find when filtering on integer fields. This makes sense, because that’s the data type of the special Records field you just selected. Select Equals and tab to the value field in the next column.

   

7. Enter 0 in the value field, and click Save and Close to return to the Step Editor:

   

8. Click the Select this row and click Add Step line and click Add Step. Select the Stop Dialog action. That’s all you really need to do, but consider adding some descriptive text, after which your Stage 3 might look like this:

   

   If you save and close, activate, and then run this dialog process, it will work fine regardless of whether the selected account has associated contacts or not:

• If there are no contact records, the condition evaluates to true, and the Stop dialog action is executed.
• If there are contact records, the condition evaluates to false, the Stop dialog action is skipped, and the dialog continues to the Page step, which displays the prompt and then updates the Case record with the selected contact.

Again: while this works, it’s not necessarily the best way to write a process like this! It depends on what else you need to do, but in general I’d say that the approach presented at the beginning of this section (with the If condition flipped (record count >= 1), and the Page and Update Case actions inside the If clause) is a little easier to read, and sometimes has other advantages as well.