Sage 300 Item & Pricing Upload To Salesforce

The item & pricing syncs products from Sage300 to Salesforce.

The upload also creates/updates price book entries for the product. The pricing update is restricted to specific pricelists, where each Pricelist in Sage requiring mapping to the Price Book in Salesforce. Each new pricelist may require additional integration configuration.

As with all the standard Salesforce integrations this serves as a base, in particular the Pricing strategy.

The item/pricing upload has the following integration logic:

  1. Reads products and prices from the SAMLTD database.
  2. Queries Salesforce to obtain the Price Book Ids needed to create/update pricebooks in Salesforce.
  3. Hierarchises the data into a Product/Pricing type structure, ready for pushing to Salesforce.
  4. Creates/updates the Product records with the pricebook entries in Salesforce.
  5. Filter erroneous records; flattens the Id from the Salesforce Pricebook records into the header so that it can be written to Sage; write both the Product and Pricebook ids to Sage.

Sage300 Requirements

The integration requires several optional fields on the Item 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.

Since there are no optional field facility on the Item Pricing record in Sage300 the pricebook entry ids are records against the product.

This strategy does not scale for large numbers of pricelists since each pricelist syncing to Salesforce requires a separate optional field.
  • SFID - Salesforce ID - Used to store the Salesforce ID for the Item Record.
  • SFPRCCAD - Salesforce Pricebook Entry ID CAD - Used to store the Salesforce ID for the CAD pricelist.
  • SFPRCUSD - Salesforce Pricebook Entry ID USD - Used to store the Salesforce ID for the USD pricelist.

Salesforce Requirements

In order to create/update Price Book entries the price book must be known and/or created.

It is important to note that if creating new products through the integration a Standard Price Book Price entry must also be created for the product where the Price Book Entry requires at least one price populated (even if it is not used, or the price is zero).

Lookup Table

The integration uses a lookup table for storing the Salesforce Pricelist Name which the Sage prices will be synced.

Read Transform

Reads items and prices from the Sage300 database.

The query restricts prices to just the WHS pricelist (highlighted in red).

The optional field values used to store the price book entry ids in the Sage database are highlighted in blue. If additional pricelists were to be synced to Salesforce, the query would need to be extended accordingly.

The part of the query highlighted in Magenta is simply to restrict the query, and should be changed to fit your specific requirements.

select P.PRICELIST AS "PRICELIST", P.CURRENCY, I.ITEMNO, FMTITEMNO, I.[DESC] as "DESC", C.[DESC] as "CATEGORY", I.KITTING, L.LOCATION, 
ISNULL(L.QTYONHAND -L.QTYSHNOCST + L.QTYRENOCST + l.QTYADNOCST, 0) as "QTYONHAND", O.VALUE as "SFID", ISNULL(UNITPRICE, 0) as "UNITPRICE", I.INACTIVE, O1.VALUE as "SFPRCCAD", O2.VALUE as "SFPRCUSD"
from ICITEM I
inner join ICCATG C on C.CATEGORY = I.CATEGORY
left outer join ICILOC L on L.ITEMNO = I.ITEMNO and L.LOCATION in ('1')
left outer join ICITEMO O on I.ITEMNO = O.ITEMNO and O.OPTFIELD = 'SFID'
left outer join ICITEMO O1 on I.ITEMNO = O1.ITEMNO and O1.OPTFIELD = 'SFPRCCAD'
left outer join ICITEMO O2 on I.ITEMNO = O2.ITEMNO and O2.OPTFIELD = 'SFPRCUSD'
left outer join ICPRICP P on P.ITEMNO = I.ITEMNO and P.DPRICETYPE = 1 and P.PRICELIST in ('WHS')
where I.ITEMNO like 'A1%'

Map

The first map transform queries Salesforce to translate the Price Book Name to the Id of the Salesforce Price Book. Each price list has it's own field to perform the translation.

The expression shown below obtains the price list name from the Lookup table (using this Using Lookups for Settings & Defaults). The Pricebook name is then translated to its Id using Salesforce Lookups.

Hierarchy & Map

To import Products with price lists the data needs to be in a header/detail hierarchical form, where the header contains the fields which will map to the products and the detail contains the prices which map to the Price Book record.

The Map transform adds a couple of fields necessary to create the products and prices.

Salesforce Connector

Options Tab

The Salesforce Connector is set to create/update Products.

Field Mapping Tab

Items -> Product

Pricelist -> Pricebook Entry

Product 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 Ids from the price book records into the header record so that it can be written back to the Items' optional fields.
  • Sage300 Connector
    • Writes the ids back to the Item record.