Query REST Entities

The Query REST Entities activity supports querying list items across different Microsoft Dynamics CRM lists and websites in a specific site collection. This activity supports two modes: FetchXML and OData to query for entities.

General

On the General tab, you can establish a connection to the Microsoft Dynamics CRM server, and specify the type of entity records you want to retrieve.

The following table lists the configurations on the General tab of the Query REST Entities activity:

Field Module Property? Description
Name No Specify the name of the activity in the process definition.
Dynamics CRM REST Connection Yes Specify a shared connection resource.

Click the icon to select a Dynamics CRM REST connection. If no matching Dynamics CRM REST connections are found, click Create Shared Resource in the Select DynamicscrmRestResource Resource Template dialog to create one. See Creating a Dynamics CRM REST Connection for more details.

Mode No Specify the retrieve mode. Select OData or FetchXML.
  • In OData mode, you can specify Filter and Orderby OData query strings on the Query or Input tab based on which entity records are returned.
  • In FetchXML mode, you can specify the XML query based on which entity records are returned.

    Note: If you select FetchXML mode, the fields on the Query tab are not shown.

Dynamics CRM Entity No Specify the type of the entity record(s) you want to retrieve.

Click Fetch Entity to open the Entity Selection Dialog that contains all the available entities. Select an entity from the list and click OK. The selected entity is automatically specified in this field.

Note: This field is visible when:

  • OData mode is selected.

    or

  • FetchXML mode and Use DynamicFetchXML checkbox are selected.
Select No Specify the attributes of the entity that you want to be returned.

After selecting the entity, click Select Attributes to open the Attributes Selection Dialog that contains all the available attributes associated with the entity. Select the attributes that you want and click OK. The selected attributes are displayed on the Output tab.

Note: This field is shown only when you select OData mode.

Use DynamicFetchXML No

Specify the type of the entity record(s) you want to retrieve.

Note:

  • This checkbox is shown only when you select FetchXML mode.

  • If you select this checkbox, FetchXML field is hidden.

  • Click Fetch Entity to open the Entity Selection dialog that contains all the available entities. Select an entity from the list and click OK.

  • Click Build Schema to open the Attributes Selection dialog that contains all the available attributes associated with the entity.

    • To select all the entities in the list, click Select All.

    • To remove all the selected entities, click Deselect All.

    • To see the updated entities list, click Refresh.

  • Click OK. The selected attributes are displayed on the Output tab.

     
FetchXML No

Specify the FetchXML query.

Note: This field is shown only when the mode is FetchXML and Use DynamicFetchXML checkbox is not selected.

You can use different FetchXML Query types such as:

  • Inner and Outer Joins: In Outer Join, the link-type attribute is specified as outer in the FetchXML query. An outer join in the FetchXML query result is equivalent to the result of the left outer join in an SQL query. If the link-type is not mentioned in the FetchXML query, the join is considered as inner join.
  • Multiple Join: The Multiple Join joins different entities to a base entity. All the link entities with their attributes are shown in sorted order under the LinkedEntities node in the output schema.
  • Nested Join: The Nested Join is similar to sub joins in an SQL query. All the link entities with attributes are shown in sorted order under the LinkedEntities node in the output schema.
  • Support for Aliases in Base Entity and Link Entities: The Base Entity with alias name has no effect on the output schema. If the alias name is specified for base entity attributes, link entity or to its attributes, the output schema is generated with the alias names in the query.
  • All Attributes in Base Entity and Link Entities: The <all-attributes> tag is specified as a child element of the entity node in the FetchXML query to retrieve all attributes for an entity. The output schema is generated with all the attributes in the entity metadata.
  • Aggregate Functions: Aggregate functions have aggregate attribute set to true in a FetchXML query. Aggregate functions can be applied on base entity or link entity attributes. Multiple aggregate functions can also be specified in the query on the same attribute.
  • Aggregate Group by: Group by can be used either on base entity attributes or on link entity attributes with aggregate functions. Multiple groups by clauses can be used in the query on the same attribute.

After you provide the FetchXML query, the Build Schema button is enabled.

Click the Build Schema button to open the Build Schema dialog that contains a list of base and linked entities retrieved from the FetchXML input.

Select the entities from the list and click Refresh to refresh the schema.

Click OK to build the output schema.

When the mode is FetchXML, you can pass values for the “value” attribute of the FetchXML query's <filter> conditions dynamically to return a specific set of records based on these conditions. '?' is the placeholder for the value attribute of the FetchXML query's <filter> conditions.

You can specify a non-nested filter or a nested filter in a FetchXML query.

Example: nonnested filter 

<filter>
<condition attribute ='lastname' operator='eq'value='?'/>
</filter>

Example: nested filter

For a nested filter, if you have parameters for values within the filter conditions, an input schema is generated that allows you to specify or map values for these parameters in the input of the Query REST activity. 

<filter type='and'>
<condition attribute='name' operator='eq' value='?'/>
<filter type='or'>
<condition attribute='emailaddress1' operator='eq' value='?' />
<condition attribute='emailaddress2' operator='eq' value='?' />
</filter>
</filter>

You can filter based on the attributes of a base entity and a linked entity.

Example: filter of a base entity and a linked entity

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' >
<entity name='account' >
<attribute name="name" />
<attribute name="accountid" />
<filter>
<condition attribute='name' operator='eq' value='?' />
</filter>
<link-entity name='contact' from='contactid' to='primarycontactid' link-type='inner'>
<attribute name='fullname' />
<filter>
<condition attribute='lastname' operator='eq' value='?'/>
</filter>
</link-entity>
</entity>
</fetch>

Description

On the Description tab, enter a short description for the Query REST Entities activity.

Query

You can specify the criteria on this tab to query the entities or expand on the associated entities.

Note: The Query tab is empty if the mode is FetchXML.

The following table lists the configurations on the Query tab of the Query REST Entities activity:

Field Module Property? Description
Filter Yes Specify Search operator and attribute names to get the entity records.
Order By Yes

Specify the order in which you want the entity records to be returned.

Entity to Expand No Specify the entity or entities to be associated with the base entity.

Click Select Entity to open the Entity Selection Dialog that contains all the available entities. Select an entity or multiple entities from the list and click OK.

Click Clear Entity to clear the field.

Expand Query No Displays the query based on the output attributes selected for an expanded entity or entities. This is a read-only field.

To have the query displayed in this field, click Build Multiple Entities Schema to open the Attributes Selection Dialog.

  • Select an entity for which you want to configure schema name and output attributes.
  • In Schema Name, select the schema or relationship name for the associated entities.
    Note: If the chosen schema is a single valued navigation property, the Use Ref to expand? checkbox is enabled, and you can select or clear the checkbox. If you select the checkbox, the Choose Output Attributes column is hidden.
    Note: When you select this checkbox, the odataid is displayed in the Output schema.
  • In Choose Output Attributes, select the output attribute per schema for each associated entity.
    Note: This field is enabled only for collection valued navigation property and for single valued navigation property if the Use Ref to expand? checkbox is not selected.

Restriction Option No Select the option from the drop-down based on which you want to restrict the output.
Note: By default, None is selected.
Top Restriction Yes Specify the number of entity records to be returned on the Output tab.
Note: This field is enabled if you select Top from Restriction Option drop-down.
PageSize Yes Specify the number of entity records to be displayed in a page.
Note: This field is enabled if you select PageSize from Restriction Option drop-down.
Note: Using a single valued navigation property with Use Ref to expand? along with a collection valued navigation property on a Query REST Entities activity is not supported by Microsoft Dynamics CRM.

Input

The input of this activity varies depending on the selected entity when configuring the General tab.

Note: The values on this tab are given higher preference over the values that you provide on the Query tab.

The following table lists the possible input elements on the Input tab of the Query REST Entities activity:

Input Item Data Type Description
The following field is displayed in FetchXML mode when Use DynamicFetchXML is not selected and filter condition has to be passed as dynamic values of a FetchXML query.
Filter<n> Complex

Specify or map values for parameters within the filter conditions in a FetchXML query.

This input field is displayed only when the FetchXML query contains one or more filter expressions with parameterized "value" attributes, indicated by a '?' in the query entered on the General tab of the activity.

The structure of the Filter<n> complex element is very similar to the structure of the <filter> expressions in the FetchXML query. Each parameterized "value" attribute in the FetchXML query is represented by a value<n> input field where you must map the dynamic value for it. The value<n> input field is present under a Condition<n> field that may in-turn be present under a SubFilter<n> field, in case the parameterized "value" attribute occurs in a nested filter expression.

The 'n' in value<n>, Condition<n>, SubFilter<n> and Filter<n> represents the order in which parameterized "value" attributes occur in the FetchXML query rather than its actual position in the FetchXML query.

The following field is displayed in FetchXML mode when Use DynamicFetchXML is selected. The dynamic FetchXML query has to be passed to the FetchXML input node.
FetchXML String

Specify the retrieve criteria in XML format.

Note:

  • The format of the XML string must conform to the FetchXML syntax. See the Microsoft Dynamics CRM documentation for more details about the FetchXML query specification and usage.

  • Paging cookie is not supported in FetchXML mode.
The following fields are displayed in OData mode.
FilterQuery String Specify the attribute names and Search operators to get the entity records.
OrderbyQuery String Specify the order in which you want the entity records to be returned.
NextPageLink String Specify the URL that the activity uses to get next bunch of data while querying data.
Note: This parameter is displayed on the Input tab when you select PageSize or from the Restriction Option drop-down.
Top String Specify the number of entity records to be returned.
Note: This parameter is displayed on the Input tab when you select Top from the Restriction Option drop-down.

Output

If you select the Odata mode on the General tab, the output schema is built based on input provided on the General, Query, and Input tabs.

If you select FetchXml mode on the General tab, output schema is built based on the XML provided in the FetchXML field on the General tab or FetchXML query provided in Input tab.

The following table lists the possible output elements on the Output tab of the Query REST Entities activity:

Output Item Data Type Description
The following fields are displayed along with the entity attributes in FetchXML mode.
nextPageNumber String The next page number.
pagingcookie String The value of paging cookie.
The following fields are displayed along with the entity attributes in OData mode.

If the Query configuration contains expanding on a single-valued navigation property with Use Ref to expand? checkbox selected, only the following two items are displayed as child elements of an output element. The name of the output element is the attribute name of a single-valued navigation property association.

Note: If Use Ref to expand? checkbox is not selected, instead of the following two output items, all the output attributes that were selected in the Attributes Selection dialog for that association are displayed as child elements of an output element. The name of the output element is the attribute name of a single valued navigation property association.

entitylogicname String The attribute names of the associated entity.
Note: This field is displayed when you select Use Ref to expand? checkbox.
odataid String The OData ID of the associated entity is displayed.
Note: This field is displayed when you select Use Ref to expand? checkbox. This parameter is not displayed if the schema name is collection valued.
nextPageLink String If you select PageSize from the Restriction Option drop-down, the URL is displayed in this field for pagination.

Fault

The error code and error message of the Query REST Entities activity are displayed on the Fault tab. See Error Codes for a more detailed explanation of errors.

The following table lists error schema elements on the Fault tab of the Query REST Entities activity:

Error Schema Element Data Type Description
message String The error message returned by the plug-in.
messageCode String The error code returned by the plug-in.