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

In 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 in 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 in 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 in 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: If you select FetchXML mode, this field is hidden.
Select No Specify the attributes of the entity which you want to be returned.

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

Note: This field is shown only when you select OData mode.
FetchXML No Specify the FetchXML query.

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 has 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 same attribute.
  • Aggregate Group by: Group by can be used either on base entity attributes or on link entity attributes with aggregate functions. Multiple group 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 box 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.

Note: This field is shown only when the mode is FetchXML.
Note: When the mode is FetchXML, you can pass values for the "value" attribute of the FetchXML query <filter> expression dynamically to return a specific set of records. '?' is the placeholder for the value attribute of the FetchXML query <filter> expression.
For example, 
<filter>    
  <condition attribute ='lastname' operator='eq'value='?'/>      
</filter>

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

For example, 
<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

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

Query

You can specify the criteria in 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 in 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? check box is enabled, and you can select or clear the check box. If you select the check box, the Choose Output Attributes column is hidden.
    Note: When you select this check box, 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? check box 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 in 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 in this tab are given higher preference over the values that you provide in the Query tab.

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

Input Item Data Type Description
The following field is displayed in FetchXML mode.
Filter<n> Complex Specify values dynamically for the "value" attribute of the FetchXML query <filter> expression to return a specific set of records. '?' is the placeholder for the value attribute of the FetchXML query <filter> expression.

We get a complex element filter <n> which has value <n> elements under it. For every filter condition, based upon the number of values specified, the input schema is built.

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 which the activity uses to get next bunch of data while querying data.
Note: This parameter is displayed in 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 in the Input tab when you select Top from the Restriction Option drop-down.

Output

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

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

The following table lists the possible output elements in 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? check box 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? check box 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 enity.
Note: This field is displayed when you select Use Ref to expand? check box.
odataid String The OData ID of the associated entity is displayed.
Note: This field is displayed when you select Use Ref to expand? check box. This parameter will not be displayed if schema name is collection valued.
nextPageLink String If you select PageSize from the Restriction Option drop-down, the URL is dispalyed in this filed for pagination.

Fault

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

The following table lists error schema elements in 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.