TechKnowledge Content
Issue
How can multiple Address ID's for a customer or vendor be integrated in one integration?
Resolution
There are 3 different types of source files/situations that are discussed in this techknowledge. Please read through the entire document before deciding which situation might be best for you.
Last Address Code for Each Customer is Primary/Default
If the source file contains all the addresses for each customer/vendor and the LAST address is the primary or default address, these are the steps to follow. Below is an example source file for customers (additional address information that is not included here may be included).
CUSTOMER ID NAME ADDRESS CODE ADDRESS1 CITY STATE ZIP
MICKEY Mickey Mouse FIRST 123 Main Street Fargo ND 58103
MICKEY Mickey Mouse SECOND 456 Third Avenue Moorhead MN 56560
MICKEY Mickey Mouse THIRD 233 University Drive Fargo ND 58103
DONALD Donald Duck FOURTH 3943 Main Avenue West Fargo ND 58204
GOOFY Goofy FIFTH 9384 First Street Moorhead MN 56561
Steps to perform the integration:
1. Create an integration by clicking on the New icon. Name the integration 'Customers and Addresses'. Click OK.
2. Create a query to the source file by clicking on the Add Query icon. The type of Query will be a Text Query. Name it 'Customers and Addresses'. Select the appropriate delimiter that pertains to the source file (the above example would be 'tab'). If the source file includes a header row, mark the Checkbox for 'First Row Contains Column Names'. Click OK.
3. Create the destination by clicking on the Add Dest icon. Select the destination of Dynamics\Receivables Management\Customers (or Dynamics\Payables Management\Vendors). Click Open.
4. Open the Integration Properties window by selecting the Integration name('Customers and Addresses') and clicking Integration -> 'Customers and Addresses' Properties. Change the Destination Edit Mode to Insert/Update.
5. Create the Destination Mappings by double-clicking on the Mapping icon. Path out the following information.
Customer Collection:
Customer ID-Use Source Field CUSTOMER
Name-Use Source Field NAME
Address ID-Use Source Field ADDRESS CODE
Addresses Collection:
Address ID-Use Source Field ADDRESS CODE
Address 1-Use Source Field ADDRESS1
City-Use Source Field CITY
State-Use Source Field STATE
Zip Code-Use Source Field ZIP
6. Save the integration.
7. Run the integration.
Notes: With this way of integrating multiple addresses, the LAST Address Code for the customer/vendor will be integrated as the Primary (Default) Address Code listed in the customer/vendor card. For example, Mickey Mouse's Primary Address ID will be THIRD, Donald Duck's will be FOURTH, etc. This is because the Destination Edit Mode is set to Insert/Update and THIRD is the last address code in the source file for Mickey Mouse.
Source File Contains a Column that Denotes the Primary/Default Address Code
If the source file includes a column that designates which address code should be considered the Primary, these are the steps that to follow. Below is an example source file - the 'PRIMARY' column for this example, will contain a 'P' for each primary address:
CUSTOMER ID NAME ADDRESS CODE ADDRESS1 CITY STATE ZIP PRIMARY
MICKEY Mickey Mouse FIRST 123 Main Street Fargo ND 58103 P
MICKEY Mickey Mouse SECOND 456 Third Avenue Moorhead MN 56560
MICKEY Mickey Mouse THIRD 233 University Drive Fargo ND 58103
DONALD Donald Duck FOURTH 3943 Main Avenue West Fargo ND 58204 P
GOOFY Goofy FIFTH 9384 First Street Moorhead MN 56561 P
Steps to perform the integration:
1. Create an integration by clicking on the New icon. Name the integration 'Customers and Addresses'. Click OK.
2. Create a the first query by clicking on the Add Query icon. The type of Query will be a Text Query. Name it 'Customers'. Select the appropriate delimiter that pertains to the source file (the above example would be 'tab'). If the source file includes a header row, mark the Checkbox for 'First Row Contains Column Names'.
3. Create a restriction on the 'Customers' query by going to the 'Rows' tab. Click on the drop-down list for Column and select the field which denotes the primary (using the above source file, this would be the column 'PRIMARY'). Click on the drop-down list for Operator and select the '='. Click on the 'List Possible Values' button, then click the drop-down list for Value. Select the value that will determine if it is primary (using the above source file, the value would be 'P'). Click the 'And Into Criteria' button, then click OK.
4. Create a second query by clicking the Add Query icon. The type of the Query will be a Text Query. Name it 'Addresses'. Choose the appropriate delimiter. If the source file includes a header row, mark the Checkbox for 'First Row Contains Column Names'. Click OK.
5. Create a relationship between the two queries. Click the 'Relationships' button on the tool bar. Drag the CUSTOMER field from the 'Customers' query to the CUSTOMER field from the 'Addresses' query. Click Close.
6. Create the destination by clicking on the Add Dest icon. Select the destination of Dynamics\Receivables Management\Customers (or Dynamics\Payables Management\Vendors). Click Open.
7. Open the Integration Properties window by selecting the Integration name('Customers and Addresses') and clicking Integration -> 'Customers and Addresses' Properties. Change the Destination Edit Mode to Insert/Update.
8. Create the Destination Mappings by double-clicking on the Mapping icon. Path out the following information. Make sure when you are mapping that you check the options tab to ensure your mappings are pulling from the correct query. The options tab for the Addresses collection should be pointed at your Addresses Query.
Customer Collection (map from the 'Customer' query)
Customer ID-Use Source Field CUSTOMER
Name-Use Source Field NAME
Address ID-Use Source Field ADDRESS CODE
Addresses Collection (map from the 'Addresses' query)
Address ID-Use Source Field Addresses.ADDRESS CODE
Address 1-Use Source Field Addresses.ADDRESS1
City-Use Source Field Addresses.CITY
State-Use Source Field Addresses.STATE
Zip Code-Use Source Field Addresses.ZIP
9. Save the integration.
10. Run the integration.
Ship To or Bill To address code(s) are different from Primary/Default
This situation is very similar to the last one mentioned, but here the 'TYPE' column determines whether the address is primary, bill to or ship to. Below is an example:
CUSTOMER ID NAME ADDRESS CODE ADDRESS1 CITY STATE ZIP TYPE
MICKEY Mickey Mouse FIRST 123 Main Street Fargo ND 58103 P
MICKEY Mickey Mouse SECOND 456 Third Avenue Moorhead MN 56560 S
MICKEY Mickey Mouse THIRD 233 University Drive Fargo ND 58103 B
DONALD Donald Duck FOURTH 3943 Main Avenue West Fargo ND 58204 P
GOOFY Goofy FIFTH 9384 First Street Moorhead MN 56561 P
You will NOT be able to bring this information into Dynamics with only one integration. The integration will need to be re-run for each additional address TYPE.
For the Primary Address, follow steps 1-10 in the previous example.
For the Ship To address, make the following changes to your integration:
1. Open the properties window on the 'Customers' query. Go to the 'Rows' tab. Select the expression in the criteria area and click on the red 'X' at the bottom of the window. This will remove the restriction for the 'P's. Now you want to add a restriction for the 'S' values. Follow step 3 in the previous example.
2. Open the Mapping window. Change the following fields.
Customer Collection (map from the 'Customer' query)
Address ID-Use Default
Ship To Address-Use Source Field ADDRESS CODE
3. Save the Integration.
4. Run the Integration.
For the Bill To address, make the following changes to your integration:
1. Open the properties window on the 'Customers' query. Go to the 'Rows' tab. Select the expression in the criteria area and click on the red 'X' at the bottom of the window. This will remove the restriction for the 'S's. Now you want to add a restriction for the 'B' values. Follow step 3 in the previous example.
2. Open the Mapping window. Change the following fields:
Customer Collection (map from the 'Customer' query)
Address ID-Use Default
Ship To Address-Use Default
Bill To Address-Use Source Field ADDRESS CODE
3. Save the Integration.
4. Run the Integration. This article was TechKnowledge Document ID: 9956
