Premium Service Lookup for Dynamics 365 CDS/CRM

The Microsoft Dynamics 365 toolkit offers a Premium Service Lookup component which provides lookup capabilities by searching record in Dynamics 365 CE/CRM or CDS instance. The Premium Service Lookup (also short for "PSL" below) component was first introduced in our v21.1 release, any prior versions will not have the component. The Premium Service Lookup component is a shared component that is shipped by a few other Integration Toolkits including SSIS Productivity Pack, Dynamics 365, HubSpot, NetSuite toolkit since the v21.1 release.

In order to use the Premium Service Lookup component to look data from a Dynamics 365 CE/CRM or CDS instance, you will need to have the Dynamics 365 Toolkit installed. Once installed, you may continue with the configuration of the component using the following four different pages.

General

In the General page, you would mainly need to specify some general settings of the component including the following.

General page

The General page of the CRM Source Component allows you to specify the general settings of the component. 

CRM Source Editor

Connection Manager

The Connection Manager option shows all PSL compatible connection managers. When working with Dynamics 365 CRM/CDS, you would choose a DynamicCRM or CDS connection manager here in order to perform such lookup. 

Target Object

The Target Object option allows you to choose a CRM/CDS entity that you would read data from, for the lookup purpose.

Cache Mode

There are 3 Cache Mode options available when looking up records from a Dynamics 365 CRM/CDS instance.

  • Full Cache
  • Partial Cache
  • No Cache
Output Timezone

The Connection Manager option shows all PSL compatible connection managers. When working with Dynamics 365 CRM/CDS, you would first choose a DynamicCRM or CDS connection manager here in order to perform such lookup. 

Batch Size

The Batch Size option allows you to specify how many records you want to retrieve each time during the lookup. This option is mainly designed for the Full Cache option discussed above. The default value is 2000. The maximum allowed value is 5000. For performance reasons, this parameter should not be too small.

Lookup Conditions page

The Lookup Conditions page allows you to create filtering conditions for the lookup purpose. The page comes with a condition builder on the top which you may use to create matching conditions in order to return the needed records in the system.

The condition builder support various operators, including "equal to", "like", "between", among many others. It is important to note that only "equal to" operator is supported by the Full Cache mode discussed above. If you have chosen an operator that is not supported by the Full Cache mode, the Cache Mode option will be automatically updated to the Partial Cache option.

Once you have built such matching conditions, the component should display the query that will be used during runtime. It is important to note that the query displayed may not be the exact query sent to Microsoft Dynamics 365 CRM/CDS. In particular, the input value is generally represented by a special token during the design time in order to support input column, SSIS variables, etc. Those tokens will be replaced automatically during runtime.

CRM Source Editor

When building such matching conditions, it is worth noting that the Output Timezone option discussed above will have an impact on how date time input values are used for matching purpose. When the Output Timezone is set to UTC, all datetime values will be converted to UTC based on the connection user's time zone setting in the system before matching.

Output Columns page

The Output Columns page allows you to add columns from the lookup entity to be returned as output columns for the lookup component.

CRM Source Editor

In this page, you can easily add any columns that should be returned. Note that at the bottom of the page, you can include two special lookup result fields.
  • _MatchFound - this will indicate whether the lookup has succeeded. It should return a true when there is at least a match found in the target system. Otherwise it should return a false.
  • _HasMoreThanOneMatch - this will be set to true when there are more than one matching records in the target system. 

Error Handling page

The Error Handling page allows you to specify how errors should be handled when they happen.

CRM Source Editor

There are three options available that you can choose for the component's error handling.

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error