Integrating Multiple Address ID's from One Source File


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