Sage300 Customer/VEndor & Contact Upload to Salesforce

These two uploads sync customer & vendor Account records from Sage300 to Salesforce with a default contact record; the contact record is created/updated and associated to the Account.

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

The customer/vendor upload has the following integration logic:

  1. Reads Customers/Vendors from the SAMLTD database.
  2. Transforms the contact data in preparation for import into Salesforce.
  3. Creates/updates the Customer/Vendor 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/Vendor Id and default contact Id to Sage.

Sage300 Requirements

The integration requires two optional fields on the Customer/Vendor record. These optional fields are used to store the Salesforce Ids for the record in Sage and passed to Salesforce to specify the record to update.

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

Salesforce Requirements

We recommend creating a new custom field on the Account object to store the Sage Customer/Vendor 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/vendors from the Sage300 database.

select A.CODESLSP1, A.PRICLIST, A.CODECURN ,A.IDCUST, NAMECUST, TEXTSTRE1, TEXTSTRE2, TEXTSTRE3, TEXTSTRE4, NAMECITY, CODESTTE, CODEPSTL, CODECTRY, NAMECTAC, 
TEXTPHON1, TEXTPHON2, O.VALUE as "SFID", O1.VALUE as "SFCTACID", A.IDNATACCT, CTACPHONE, CTACFAX, EMAIL1, EMAIL2, AMTBALDUET
from ARCUS A
left outer join ARCUSO O on A.IDCUST = O.IDCUST and O.OPTFIELD = 'SFID'
left outer join ARCUSO O1 on A.IDCUST = O1.IDCUST and O1.OPTFIELD = 'SFCTACID'
where A.IDCUST <> A.IDNATACCT
and EMAIL1 <> ''

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/Vendors 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/vendor 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 & Sage300 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/Vendor record's optional fields.
  • Sage300 Connector
    • Writes the ids back to the Customer/Vendor record.