ZappySys Dynamics CRM Destination comes with support for special datatypes such as PartyList, PickList and Lookup.
In this post, we will cover some important details about these 3 data types. If you want to learn how to add / edit fields in Dynamics CRM then check this article.
Dynamics CRM Source (Find out DatType)
You can find out many important details about datatype from CRM Source Object Browser. For that make sure you select AccessMode=FetchXML > Goto Object Browser > Select Table > Highlight Column (See DataType in Yellow Panel).
Notice data preview. It has PartyList datatype see how it exports it as JSON. You can map this same information to CRM Destination too as long as target field is PartyList.
CRM Destination Mapping
Find out CRM Entity Field Type in Destination
Handling Special DataTypes (PickList, Multi-Select, Lookup, PartyList)
Dynamics CRM supports many datatypes which can be handled without any issue in ZappySys CRM Destination. However following three datatypes (i.e. PickList (Single or Multi-Select), Lookup, PartyList) require special handling when you insert/update or upsert.If you want to learn how to add / edit fields in Dynamics CRM then check this article.
1. PickList DataType
PickList datatype is simple drop-down style datatype which provides a selection of predefined options (Enum in Programming Language) where the user selects Text and behind the scene, it stores the Numeric value for selection. In CRM Destination you can set PickList field by Text or Numeric value. If you use Text as input then any non-matching entry will result into an error. If you wish to ignore errors and insert as null then set UseDefaultIfLookupFails optionYourOptionSetNumericValue
Syntax (For Write as Label):
[text=]YourOptionSetLabel
text=Green
Green
2. Multi-Select PickList DataType (OptionSet Collection)
Dynamics CRM introduced a new MultiSelectPickList data type recently to allow multiple selections for picklist type. ZappySys is pleased to announce support for the same in our CRM Destination.Sometimes when its desired to read Multi-Select OptionSet as Text List (i.e. read labels rather than values from Pick List options) then use associated Virtual Column returned by CRM. For each OptionSet / PickList column, you will see one Virtual Column as well. This Virtual Column returns Labels (Formatted value). Virtual Column name ends with "name".
NumericValue1;NumericValue2;NumericValue3....NumericValueN
10000000;10000001;10000002
Syntax (For Write as Label):
[text=]Label1;Label2;Label3
text=Green;Yellow;Red
Green;Yellow;Red
3. Lookup DataType
Lookup datatype allows referring record from the different table just like a foreign key in relational database. Here are some important points about this datatype.- This field is special type of foreign key which holds reference to related records but also holds referenced entity type (e.g. lead, account, contact). In typical foreign key you can only reference to one table.
- If you are not sure which fields are Lookup type field in CRM Destination then perform these steps. ([1]. Right click on CRM Destination field [2] Goto Input and Output Properties [3] Expand DynamicsCrmDestinationInput [4] Expand External columns and click on each column. You will see custom properties like AttributeType and LinkedEntity
- If you use Text lookup then any non-matching entry will result into error. If you wish to ignore errors for non-match and insert as null then set UseDefaultIfLookupFails option to true for CRM Destination.
- Text format - Id Lookup:
You can set Lookup field using following simple input format (key/value pair). Id is related record Id from different table. Type is related record type (i.e. account, lead, contact). Type is optional so if you dont specify it then first allowed type will be assumed in alphabetical order (e.g. account).Syntax:
RECORD_ID[=TYPE_NAME];
Examples:
0fe3ac7c-d63d-e811-a953-000d3a37468c --OR-- 0fe3ac7c-d63d-e811-a953-000d3a37468c=account
- JSON format - Id Lookup:
You can also use JSON Format as your input data to set Lookup type field. Where RECORD_ID is related record Id from different table. Type is related record type (i.e. account, lead, contact). Type is optional so if you dont specify it then first allowed type will be assumed in alphabetical order (e.g. account).Syntax:
[{"id":"RECORD_ID" ,"type":"RECORD_TYPE"}]Examples:
[{"id":"0fe3ac7c-d63d-e811-a953-000d3a37468c"}] --OR-- [{"id":"0fe3ac7c-d63d-e811-a953-000d3a37468c", "type":"account"}] - JSON format - Text Lookup (**SLOW** - Local Caching Needed):
You can also use JSON Format to lookup by Text rather than Id. However this approach can be very slow if you are doing lookup against very large entity so use Text Lookup only if necessary.text is related record's primary field value (if field is not supplied) --OR-- value of the field name you supplied.
field is name of column (e.g. firstname) on which you like to perform lookup. If you dont specify field in your json then PrimaryNameAttribute will be used to determine field name. Use Dynamics CRM Source FetchXML mode and on the Object Browser goto Table Info tab to find PrimaryNameAttribute to detrmine primary name field for selected entity.
Syntax:[{"text":"VALUE" ,"type":"LOOKUP_RECORD_TYPE","field":"LOOKUP_FIELD_NAME"}]Examples:
[{"text":"ZappySys"}] --OR-- [{"text": "ZappySys", "type": "account"}] --OR-- [{"text": "ZappySys", "type": "account", "field": "name"}]
4. PartyList DataType
Certain entities like Appointment support special type of fields for (e.g. requiredattendees) which can hold reference to multiple records from multiple tables. Here are some important point about this datatype.- This field is special type of foreign key which holds reference to many related records in many types of entities (e.g. lead, account, contact). In typical foreign key you can only reference to one table.
- If you are not sure which fields are Lookup type field in CRM Destination then perform these steps. ([1]. Right click on CRM Destination field [2] Goto Input and Output Properties [3] Expand DynamicsCrmDestinationInput [4] Expand External columns and click on each column. You will see custom properties like AttributeType and LinkedEntity
- You can also use CRM Source to preview PartyList field and see how it outputs data (CRM SOurce will output PartyList as JSON format).
- If you use Text lookup then any non-matching entry will result into error. If you wish to ignore errors for non-match and insert as null then set UseDefaultIfLookupFails option to true for CRM Destination.
- Text format - Id Lookup:
You can set PartyList field using following simple input format (key/value pairs). Id is related record Id from different table. Type is related record type (i.e. account, lead, contact). Type is optional so if you dont specify it then first allowed type will be assumed in alphabetical order (e.g. account).Syntax:
RECORD_ID_1[=TYPE_NAME];RECORD_ID_2[=TYPE_NAME];....RECORD_ID_N[=TYPE_NAME];
Examples:
0fe3ac7c-d63d-e811-a953-000d3a37468c --OR-- 0fe3ac7c-d63d-e811-a953-000d3a37468c;6329dbd0-4e1c-e511-80d3-3863bb347ba8=lead --OR-- 0fe3ac7c-d63d-e811-a953-000d3a37468c=account;6329dbd0-4e1c-e511-80d3-3863bb347ba8=lead
- JSON format - Id Lookup:
You can also use JSON Format as your input data to set PartyList field. Where RECORD_ID is related record Id from different table. Type is related record type (i.e. account, lead, contact). Type is optional so if you dont specify it then first allowed type will be assumed in alphabetical order (e.g. account).Syntax:
[{"id":"RECORD_ID_1" ,"type":"RECORD_TYPE"}, {"id":"RECORD_ID_2" ,"type":"RECORD_TYPE"} ... {"id":"RECORD_ID_N" ,"type":"RECORD_TYPE"}]Examples:
[{"id":"0fe3ac7c-d63d-e811-a953-000d3a37468c"}] --OR-- [{"id":"0fe3ac7c-d63d-e811-a953-000d3a37468c", "type":"account"}] --OR-- [{"id":"0fe3ac7c-d63d-e811-a953-000d3a37468c"}, {"type":"lead","id":"6329dbd0-4e1c-e511-80d3-3863bb347ba8"}] --OR-- [{"id":"0fe3ac7c-d63d-e811-a953-000d3a37468c", "type":"account"}, {"type":"lead","id":"6329dbd0-4e1c-e511-80d3-3863bb347ba8"}] - JSON format - Text Lookup (**SLOW** - Local Caching Needed):
You can also use JSON Format as to lookup by Text rather than Id. However this approach can be very slow if you are doing lookup against very large entity so use Text Lookup only if necessary.text is related record's primary field value (if field is not supplied) --OR-- value of the field name you supplied.
field is name of column (e.g. firstname) on which you like to perform lookup. If you dont specify field in your json then PrimaryNameAttribute will be used to determine field name. Use Dynamics CRM Source FetchXML mode and on the Object Browser goto Table Info tab to find PrimaryNameAttribute to detrmine primary name field for selected entity.
Syntax:[{"text":"VALUE_1" ,"type":"LOOKUP_RECORD_TYPE","field":"LOOKUP_FIELD_NAME"}, {"id":"VALUE_2" ,"type":"LOOKUP_RECORD_TYPE","field":"LOOKUP_FIELD_NAME"} ... {"text":"VALUE_N" ,"type":"RECORD_TYPE","field":"LOOKUP_FIELD_NAME"}]Examples:
[{"text":"ZappySys"}] --OR-- [{"text": "ZappySys", "type": "account"}] --OR-- [{"text": "ZappySys", "type": "account", "field": "name"}] --OR-- [{"text": "ZappySys", "type": "account", "field": "name"}, {"text": "Bob Smith", "type": "lead", "field": "fullname"}] --OR-- [{"text": "ZappySys", "type": "account"}, {"text": "Bob Smith", "type": "lead", "field": "fullname"}]
Known Errors
Here are some common errors you might face when you write to Lookup / PickList / PartyList fields
Error - Failed to generate lookup catch for [type_name]. Duplicate records found on field [name]
This error happens when you select lookup by text and there is more than one row found for the same lookup text (i.e. you lookup contacts by email id .. but let's say 2 rows are found for the same email). In such cases, you can use more unique fields to prevent this error.
-- Duplicate Lookup rows found scenario
[Update Contact Table [856]] Error: System.Exception: System.Exception: Failed to generate lookup catch for [account]. Duplicate records found on field [name].
Try to set this field by ID rather than text. Lookup Text: SOME_TEXT, ID for Record-1: 73ca5b48-3f58-ec11-xxxxxx-xxxxxxxx,ID
for Record-2: 07ca5b48-3f58-xxxxxx-xxxxxxxx, Error: An item with the same key has already been added., Query Invoked: <?xml version="1.0"?><fetch><entity name="account"><attribute name="accountid" /><attribute name="name" /></entity></fetch>
at ZappySys.Crm.DynamicsCrm.DynamicsCrmConnection.GetEntityDataCache(String entityName, String lookupBy, CrmDataRequest arg)
Error - Lookup by text failed
You may get below error if you lookup by some value which is not found. Error is expected behaviour. To
skip Update for such values you can set UseDefaultIfLookupFails but in old version it had no effect ... instead it was throwing Sequence contains no elements error. This bug is fixed in v5.0.2 or higher. So upgrade your version to higher version to use UseDefaultIfLookupFails option.
Lookup by text failed. Text [BAD_VALUE] not found in [typename->fieldname]. Set UseDefaultIfLookupFails=true if you wish to use default / null value if match not found. Error Message:
Error - You get error after Setting UseDefaultIfLookupFails - Sequence contains no elements
This bug is fixed in in v5.0.2 or higher.
Comments
0 comments
Please sign in to leave a comment.