Microsoft Dynamics field mappings

The tables below contain the mappings between Microsoft Dynamics source fields and their corresponding Experience Data Model (XDM) fields.

Contacts

Source field Target XDM field Notes
address1_addressid workAddress._id
address1_city workAddress.city
address1_country workAddress.country
address1_county workAddress.stateProvince
address1_latitude workAddress._schema.latitude
address1_line1 workAddress.street1
address1_line2 workAddress.street2
address1_line3 workAddress.street3
address1_longitude workAddress._schema.longitude
address1_postalcode workAddress.postalCode
address1_postofficebox workAddress.postOfficeBox
address1_stateorprovince workAddress.state
assistantname extendedWorkDetails.assistantDetails.name.fullName
assistantphone extendedWorkDetails.assistantDetails.phone.number
birthdate person.birthDate
"Dynamics" b2b.personKey.sourceType
"${CRM_ORG_ID}" b2b.personKey.sourceInstanceID The value for "${CRM_ORG_ID}" will be automatically replaced.
contactid b2b.personKey.sourceID
concat(contactid,"@${CRM_ORG_ID}.Dynamics") b2b.personKey.sourceKey Primary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
iif(contactid != null && contactid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}","sourceID", contactid, "sourceKey", concat(contactid,"@${CRM_ORG_ID}.Dynamics")), null) personComponents.sourcePersonKey
department extendedWorkDetails.departments
fullname person.name.fullName
suffix person.name.suffix
iif(parentcustomerid != null && parentcustomerid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}", "sourceID", parentcustomerid, "sourceKey", concat(parentcustomerid,"@${CRM_ORG_ID}.Dynamics")), null) personComponents.sourceAccountKey
iif(parentcustomerid != null && parentcustomerid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}", "sourceID", parentcustomerid, "sourceKey", concat(parentcustomerid, "@${CRM_ORG_ID}.Dynamics")), null) b2b.accountKey
createdon extSourceSystemAudit.createdDate
emailaddress1 workEmail.address Secondary identifier.
emailaddress2 personalEmail.address
emailaddress1 personComponents.workEmail.address
firstname person.name.firstName
fullname person.name.fullName
lastname person.name.lastName
jobtitle extendedWorkDetails.jobTitle
middlename person.name.middleName
mobilephone mobilePhone.number
modifiedon extSourceSystemAudit.lastUpdatedDate
salutation person.name.courtesyTitle
telephone1 workPhone.number

Leads

Source field Target XDM field Notes
address1_addressid workAddress._id
address1_city workAddress.city
address1_country workAddress.country
address1_county workAddress.stateProvince
address1_latitude workAddress._schema.latitude
address1_line1 workAddress.street1
address1_line2 workAddress.street2
address1_line3 workAddress.street3
address1_longitude workAddress._schema.longitude
address1_postalcode workAddress.postalCode
address1_postofficebox workAddress.postOfficeBox
address1_stateorprovince workAddress.state
telephone1 workPhone.number
mobilephone mobilePhone.number
createdon extSourceSystemAudit.createdDate
emailaddress1 workEmail.address Secondary identifier
emailaddress2 personalEmail.address
emailaddress1 personComponents.workEmail.address
fax faxPhone.number
firstname person.name.firstName
fullname person.name.fullName
jobtitle extendedWorkDetails.jobTitle
lastname person.name.lastName
"Dynamics" b2b.personKey.sourceType
"${CRM_ORG_ID}" b2b.personKey.sourceInstanceID The value for "${CRM_ORG_ID}" will be automatically replaced.
leadid b2b.personKey.sourceID
concat(leadid,"@${CRM_ORG_ID}.Dynamics") b2b.personKey.sourceKey Primary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
iif(leadid != null && leadid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}","sourceID", leadid, "sourceKey", concat(leadid,"@${CRM_ORG_ID}.Dynamics")), null) personComponents.sourcePersonKey
middlename person.name.middleName
mobilephone mobilePhone.number
modifiedon extSourceSystemAudit.lastUpdatedDate
salutation person.name.courtesyTitle

Accounts

Source field Target XDM field Notes
"Dynamics" accountKey.sourceType
"${CRM_ORG_ID}" accountKey.sourceInstanceID The value for "${CRM_ORG_ID}" will be automatically replaced.
accountid accountKey.sourceID Primary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
accountnumber accountNumber
accountratingcode accountOrganization.rating
address1_addressid accountPhysicalAddress._id
address1_city accountPhysicalAddress.city
address1_country accountPhysicalAddress.country
address1_county accountPhysicalAddress.region
address1_latitude accountPhysicalAddress._schema.latitude
address1_line1 accountPhysicalAddress.street1
address1_line2 accountPhysicalAddress.street2
address1_line3 accountPhysicalAddress.street3
address1_longitude accountPhysicalAddress._schema.longitude
address1_name accountPhysicalAddress.label
address1_postalcode accountPhysicalAddress.postalCode
address1_postofficebox accountPhysicalAddress.postOfficeBox
address1_stateorprovince accountPhysicalAddress.state
createdon extSourceSystemAudit.createdDate
description accountDescription
fax accountFax.number
modifiedon extSourceSystemAudit.lastUpdatedDate
name accountName
numberofemployees accountOrganization.numberOfEmployees
revenue accountOrganization.annualRevenue.amount
sic accountOrganization.SICCode
telephone1 accountPhone.number
tickersymbol accountOrganization.tickerSymbol
websiteurl accountOrganization.website
concat(accountid,"@${CRM_ORG_ID}.Dynamics") accountKey.sourceKey

Opportunities

Source field Target XDM field Notes
name opportunityName
"Dynamics" opportunityKey.sourceType
"${CRM_ORG_ID}" opportunityKey.sourceInstanceID The value for "${CRM_ORG_ID}" will be automatically replaced.
iif(parentaccountid != null && parentaccountid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}","sourceID", parentaccountid, "sourceKey", concat(parentaccountid, "@${CRM_ORG_ID}.Dynamics")), null) accountKey
actualclosedate actualCloseDate
actualvalue opportunityAmount.amount
iif(campaignid != null && campaignid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}", "sourceID", campaignid, "sourceKey", concat(campaignid,"@${CRM_ORG_ID}.Dynamics")), null) campaignKey
closeprobability probabilityPercentage
createdon extSourceSystemAudit.createdDate
description opportunityDescription
estimatedclosedate expectedCloseDate
estimatedvalue expectedRevenue.amount
modifiedon extSourceSystemAudit.lastUpdatedDate
opportunityid opportunityKey.sourceID
concat(opportunityid,"@${CRM_ORG_ID}.Dynamics") opportunityKey.sourceKey Primary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
salesstage opportunityStage
stepname nextStep

Opportunity contact roles

Source field Target XDM field Notes
"Dynamics" opportunityPersonKey.sourceType
"${CRM_ORG_ID}" opportunityPersonKey.sourceInstanceID The value for "${CRM_ORG_ID}" will be automatically replaced.
connectionid opportunityPersonKey.sourceID
concat(connectionid,"@${CRM_ORG_ID}.Dynamics") opportunityPersonKey.sourceKey Primary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
createdon extSourceSystemAudit.createdDate
modifiedon extSourceSystemAudit.lastUpdatedDate
iif(record1id != null && record1id != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}", "sourceID", record1id, "sourceKey", concat(record1id,"@${CRM_ORG_ID}.Dynamics")), null) opportunityKey
iif(record2id != null && record2id != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}", "sourceID", record2id, "sourceKey", concat(record2id,"@${CRM_ORG_ID}.Dynamics")), null) personKey
connectionrole1.name personRole
record1objecttypecode A custom field group must be defined as a target schema. See the appendix section for steps on how to map a picklist type source field to a target XDM schema for more information. For a list of possible and values and labels for the record1objecttypecode source field, see this Microsoft Dynamics connection entity reference document.
record2objecttypecode A custom field group must be defined as a target schema. See the appendix section for steps on how to map a picklist type source field to a target XDM schema for more information. For a list of possible and values and labels for the record2objecttypecode source field, see this Microsoft Dynamics connection entity reference document.

Campaigns

Source field Target XDM field Notes
campaignid campaignKey.sourceID
"${CRM_ORG_ID}" campaignKey.sourceInstanceID The value for "${CRM_ORG_ID}" will be automatically replaced.
concat(campaignid,"@${CRM_ORG_ID}.Dynamics") campaignKey.sourceKey Primary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
"Dynamics" campaignKey.sourceType
iif(campaignid != null && campaignid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}","sourceID", campaignid, "sourceKey", concat(campaignid,"@${CRM_ORG_ID}.Dynamics")), null) extSourceSystemAudit.externalKey The extSourceSystemAudit.externalKey is the secondary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
createdon extSourceSystemAudit.createdDate
modifiedby extSourceSystemAudit.lastUpdatedBy
modifiedon extSourceSystemAudit.lastUpdatedDate
description campaignDescription
name campaignName
totalactualcost actualCost.amount
budgetedcost budgetedCost.amount
expectedrevenue expectedRevenue.amount
actualend campaignEndDate
actualstart campaignStartDate
expectedresponse expectedResponse
utcconversiontimezonecode timeZone
utcconversiontimezonecode timezoneName

Marketing list

Source field Target XDM field Notes
"Dynamics" marketingListKey.sourceType
"${CRM_ORG_ID}" marketingListKey.sourceInstanceID The value for "${CRM_ORG_ID}" will be automatically replaced.
description marketingListDescription
listname marketingListName
listid marketingListKey.sourceID
concat(listid,"@${CRM_ORG_ID}.Dynamics") marketingListKey.sourceKey Primary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
modifiedon extSourceSystemAudit.lastUpdatedDate
createdon extSourceSystemAudit.createdDate

Marketing list members

Source field Target XDM field Notes
"Dynamics" marketingListMemberKey.sourceType
"${CRM_ORG_ID}" marketingListMemberKey.sourceInstanceID The value for "${CRM_ORG_ID}" will be automatically replaced.
iif(entityid != null && entityid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}", "sourceID", entityid, "sourceKey", concat(entityid,"@${CRM_ORG_ID}.Dynamics")), null) personKey
listmemberid marketingListMemberKey.sourceID
concat(listmemberid,"@${CRM_ORG_ID}.Dynamics") marketingListMemberKey.sourceKey Primary identity. The value for "${CRM_ORG_ID}" will be automatically replaced.
iif(listid != null && listid != "", to_object("sourceType", "Dynamics", "sourceInstanceID", "${CRM_ORG_ID}", "sourceID", listid, "sourceKey", concat(listid,"@${CRM_ORG_ID}.Dynamics")), null) marketingListKey
createdon extSourceSystemAudit.createdDate

Appendix

The sections below provide additional information that you can use when configuring B2B mappings for your Microsoft Dynamics source.

Picklist type fields

You can use calculated fields to map a picklist type source field from Microsoft Dynamics to a target XDM field.

For example, the genderCode field includes two options:

Value Label
1 male
2 female

You can use the following options to map the genderCode source field to person.gender target field:

Use a logical operator

Source field Target XDM field
decode(genderCode, "1", "male", "2", "female", "default") person.gender

In this scenario, the value corresponds to the key, if the key is found in options, or default, if default is present and the key is not found. The value corresponds to null if options is null or there is no default and the key is not found.

Use a calculated field

Source field Target XDM field
iif(gendercode.equals("1"),"male",iif(gendercode.equals("2"),"female",null)) person.gender
TIP

A nested iteration of the above operation would be similar to: iif(condition, iif(cond1, tv1, fv1), iif(cond2, tv2, fv2)).

For more information is the document on logical operators in Data Prep

On this page