Salesforce-NetSuite ID Mapping and Loading

Like many of Celigo’s Integration App, the Salesforce-NetSuite Integration App employs a mechanism to link records together, by using ID values as a cross-reference between the two systems. When an integration flow is triggered, it checks to see if the ID field is populated or blank on the source record.

• If the ID field is blank, this indicates to the Integration App that no corresponding record exists in the target system. If the flow is enabled to create, it proceeds to create a new record in the target system.
• On the other hand, if an ID is populated and the flow is enabled for Update, this means there is a linked record in the target system and the Integration App will update the record.
• As another example, when syncing a Salesforce opportunity to a NetSuite sales order, the opportunity includes one or more products. Those products must be linked to NetSuite items in order for the Integration App to know which items to add to the sales order.

Before going live on the Salesforce-NetSuite Integration App, it’s important that you link together all existing records in order to avoid problems like failed synchronizations or duplicate creation. Typically this pertains to products with items,  accounts with customers, and contacts with contacts. Other historical transactional records like sales orders, invoices, and payments can also be linked between systems. Most of Celigo’s customers don’t pursue this path due to the likelihood of high volumes of data, and the difficult decision of how far back to go. Once the Integration App is activated, it will automatically utilize these links or create a new link for each new record it creates from one system to the other.

Are there cases where you would not want to populate an ID onto all records of a particular type?  Yes. You might have Salesforce accounts that don’t exist in NetSuite and you don’t want to sync them because there are only leads or prospects right now. Or maybe you have customers, items, or products that have been discontinued.

The purpose of this article is to describe the general process of data loading into and between NetSuite and Salesforce production systems to link records.

Prerequisite: Make sure your Salesforce-NetSuite Integration App is installed.

Note 1: For NetSuite contacts, the Salesforce ID field you should use is called Salesforce ID. For NetSuite items and NetSuite customers, it’s called Salesforce ID (2). For Salesforce records, the NetSuite ID you should use is always NetSuite ID.

Note 2: Celigo’s Integration App uses the Salesforce 18-digit ID, never the 15-digit ID. If you need to convert from a 15-digit to an 18-digit ID, please consult your Salesforce knowledge articles or other internet resources.

In both scenarios below, we’ll use the example of Salesforce accounts and NetSuite customers.

Scenario 1: Salesforce and NetSuite records both exist but are not linked together.

1. From Salesforce, export a csv file of all accounts for linking to NetSuite.
• That export should include the account name (or some other identifier you can recognize) and the Salesforce 18 digit API ID.
• This is the ID we require to link Salesforce and NetSuite records. (This is different than the 15-digit ID visible in the Salesforce UI.)
2. Export from NetSuite to a csv file, all customers to link to Salesforce.
• The export should include the NetSuite Internal ID.
• The export should also include the customer name (or some other identifier you can recognize) and the NetSuite Internal ID.
3. Sort each file alphabetically by the customer/account name (or other visual identifier you selected for export to your file).
4. Import or copy and paste the 2 columns from each file into one spreadsheet such that two column pairs are side by side.
5. Using the customer/account names or other identifier, manipulate the file such that each NetSuite customer-ID pair is lined up on the same row with its matching Salesforce account-ID pair.
6. If you end up with pairs on either side that have no match, you have found one or more cases where an account exists in Salesforce but has no corresponding customer in NetSuite or vice versa.
• Keep each of these pairs on their own line with no corresponding match, i.e. you’d see two blank cells and then a pair. Or you’d see a pair and two blank cells on another row.
• If you are sure they really don’t match, cut and paste them to another sheet for later review.
7. When the above process is complete, back up the file and create a copy.
8. In the copied version, delete the column with the visual identifiers, probably the customer name and account name.
• Your file should now only include two columns, one with the NetSuite ID and the other with the Salesforce ID.
• You now have a file you can load into both NetSuite and also Salesforce to establish the mappings between accounts and customers.
9. Make sure the Integration App is paused.
10. Load the file into Salesforce with Celigo’s Data Loader or the Salesforce Apex Data Loader.
• Use the Salesforce ID as the account’s record key to load each NetSuite Internal ID.
• Map the NetSuite Internal ID column with the NetSuite ID field on the Salesforce account.
• This field will only exist on your account if Celigo’s Salesforce-NetSuite Integration App has been installed. Do not add it on your own.
11. Load the same file into NetSuite with Celigo’s Data Loader or NetSuite’s CSV load.
• Use the NetSuite Internal ID as the record key to load the customers.
• Map the Salesforce 18-digit ID to the Salesforce ID (2) field.
• This field will only exist on your customer if Celigo’s Salesforce-NetSuite Integration App has been installed. Do not add it on your own.
12. For those remaining lines that had no matches, determine if you might want to load them from NetSuite to Salesforce (for those only in NetSuite) or Salesforce to NetSuite (for those only in Salesforce). If so, follow the instructions in the next section.

    Note: If you have any Integration App-mapped fields that contain inconsistent values (e.g. Customer name in NetSuite and Account name in Salesforce are slightly different - for instance: Acme, Inc. vs Acme Incorporated) you will want to reconcile these values through the following steps below. This process allows you to control which system holds the master value on a field by field basis. If you do not follow these steps, the Integration App will update fields at the time an individual record is edited and saved, always updating mapped field values (including blanks) from source to target. This is a potential problem of not only inconsistency but also overriding the correct target value with an incorrect source value.

13. For any mapped fields whose master values live in Salesforce, create an export from Salesforce that includes those fields as well as the NetSuite ID.
14. Import the file into NetSuite as an update, using the NetSuite ID as the key and mapping the other field(s) exported to the corresponding NetSuite field(s).
15. For any mapped fields whose master values live in NetSuite, create an export from NetSuite that includes those fields as well as the Salesforce 18-digit ID.
16. Import the file into Salesforce as an update, using the Salesforce ID as the key and mapping the other field(s) exported to the corresponding Salesforce field(s).

Scenario 2: A record exists in one system and is considered the master. It does not exist in the other system. You would like to load all such records into the other system and link them together.

The general process is to export each NetSuite customer over to a Salesforce account and then bring back the Salesforce 18-digit ID for each record into NetSuite.

1. Export a csv file with all fields you plan to populate into Salesforce accounts.
• You must also be sure to include the NetSuite internal ID field.
• If you are not exporting all customer records, you can use a NetSuite saved-search to restrict which customers are exported.
2. Make sure all fields in the export have corresponding target fields in Salesforce. This includes the NetSuite Internal ID field which will only exist on your account if Celigo’s Salesforce-NetSuite Integration App has been installed.
3. Make sure the Salesforce Account to NetSuite Customer integration flow is turned off.
• Taking into consideration which columns map to which fields, load the file into the Salesforce account records, with Celigo’s Data Loader or the Salesforce Apex Data Loader.
• After the load, you now have the NetSuite customers loaded as Salesforce accounts. But there’s one more step: to link them together.
4. Export from Salesforce each account into a CSV file, but with only two fields: the Salesforce 18 digit API  ID and the NetSuite Internal ID.
5. Make sure the Celigo Customer to Account integration flow is turned off.
6. Load the exported file back into the NetSuite customer record (as an update).
• You may do this with Celigo’s Data Loader or NetSuite’s csv load.
• The key field is the NetSuite Internal ID. You will want to map the Salesforce ID column to the Salesforce ID (2) field.

General Testing

Once the above steps are completed, you will want to validate one or more ID links, by triggering an update on one of the records. The particular integration flow must be enabled (e.g. if you linked Accounts and Customers, that flow must be On). You would just edit the customer or account and make a minor change to a mapped field (e.g. description) and save the record. After a matter of seconds, you should see that update in the opposite and linked record.