Documentation Experience Platform Source Connectors Guide

Microsoft Dynamics field mappings

Last update: Fri May 26 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

Topics:
Sources

CREATED FOR:

Developer

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

Contacts 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 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 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 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 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 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 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 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 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

male

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

recommendation-more-help

337b99bb-92fb-42ae-b6b7-c7042161d089