Sage200 Customer/Supplier & Contact Upload To Salesforce

These two uploads creates/updates in Salesforce customer & supplier Account records with a default contact record wherethe contact record is created/updated and associated to the Account.

As with all the standard Salesforce integrations this serves as a base.

The customer/Supplier upload has the following integration logic:

  1. Reads Customers/Suppliers from the DemoData database.
  2. Transforms the contact data in preparation for import into Salesforce.
  3. Creates/updates the Customer/Supplier record with the default contact in Salesforce.
  4. Filter erroneous records; flattens the Id from the Salesforce Contact record into the header so that it can be written to Sage; write both the Customer/Supplier Id and default contact Id to Sage.

Sage200 Requirements

The integration requires two Analysis Codes on the Customer/Supplier record. The Analysis Codes are used to store the Salesforce Ids for the record in Sage and passed to Salesforce to specify the record to update.

  • Salesforce ID - Used to store the Salesforce ID for the Customer/Supplier Record.
  • Salesforce Contact ID - Used to store the Salesforce ID for the default contact from the Customer/Supplier.

Salesforce Requirements

We recommend creating a new custom field on the Account object to store the Sage Customer/Supplier Id.

This field could be setup as an External ID Field, thereby eliminating the need to store Salesforce Id in an optional field, however you would either still need to store the Contact Id or create another field on the Contact object which is also marked as an external key.

Read Transform

Reads customers/Suppliers from the Sage200 database.

select A.CustomerAccountNumber, A.CustomerAccountName, A.AnalysisCode10 as "SalesforceID", A.AnalysisCode11 as "SalesforceContactID", ICur.Code, L.*, C.Code as "Currency", C.Code3, C.Name as "CountryName", Ctac.ContactName, V.ContactValue
from SLCustomerAccount A
inner join SLCustomerLocation L on A.SLCustomerAccountID = L.SLCustomerAccountID and SYSTraderLocationTypeID = 0
inner join SYSCountryCode C on C.SYSCountryCodeID = L.SYSCountryCodeID
inner join SYSCurrency Cur on Cur.SYSCurrencyID = A.SYSCurrencyID
inner join SYSCurrencyISOCode ICur on ICur.SYSCurrencyISOCodeID = Cur.SYSCurrencyISOCodeID
inner join SLCustomerContact Ctac on A.SLCustomerAccountID = Ctac.SLCustomerAccountID
inner join SLCustomerContactValue V on Ctac.SLCustomerContactID = V.SLCustomerContactID and V.SYSContactTypeID = 2 and V.IsPreferredValue = 1

Map & Hierarchy

The Map transform performs the necessary field level transformation needed to populate the bill-to and ship-to address fields, and also adding mandatory fields such as Indurstry & Account Type.

To import Customers/Suppliers with a default contact the data needs to be in a header/detail hierarchical form, where the header contains the fields which will map to the customer/Supplier and the detail contains the fields which map to the contact record.

The Hierarchy transform transforms the data into this format.

Salesforce Connector

Options Tab

The Salesforce Connector is set to create/update Accounts.

Field Mapping Tab

Customers -> Account

Contacts -> Contact

Account in Salesforce

Filter, Map & Sage200 Connector

These next three transforms write back successfully created/update records back to Sage.

  • Filter Transform
    • Filters unsuccessful records.
  • Map Transform
    • Pulls the Id from the Contact record into the header record so that it can be written back to the Customer/Supplier record's optional fields.
  • Sage200 Connector
    • Writes the ids back to the Customer/Supplier record.