Sage 200 Item & Pricing Upload

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.

The item/pricing upload has the following integration logic:

  1. Reads products and prices from the DemoData 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.

Sage200 Requirements

The integration requires several Analysis Codes on the Stock Item record. These 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 Sage200 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 Analysis Code.
  • SalesforceID - Used to store the Salesforce ID for the Item Record.
  • Salesforce Pricelist Entry ID GBP - Used to store the Salesforce ID for the GBP pricelist.
  • Salesforce Pricelist Entry ID EUR - Used to store the Salesforce ID for the EUR 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 Sage200 database.

The query restricts prices to just the Standard and Euro pricelists (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.

select I.Code, I.Name, ISO.Code as "Currency", P.Price, (WI.ConfirmedQtyInStock - WI.QuantityAllocatedSOP) as "QtyAvailable", 
I.AnalysisCode10 as "SalesforceID", I.AnalysisCode11 as "SFPriceIDGBP", I.AnalysisCode12 as "SFPriceIDEUR", I.StockItemStatusID
from StockItem I
inner join StockItemPrice P on I.ItemID = P.ItemID 
inner join PriceBand PB on PB.PriceBandID = P.PriceBandID
inner join SYSCurrency C on C.SYSCurrencyID = PB.CurrencyID
inner join SYSCurrencyISOCode ISO on ISO.SYSCurrencyISOCodeID = C.SYSCurrencyISOCodeID
inner join WarehouseItem WI on WI.ItemID = I.ItemID
inner join Warehouse W on WI.WarehouseID = W.WarehouseID and W.UseForSalesTrading = 1 
where PB.Name in ('Standard', 'Euro') and W.Name = 'WAREHOUSE'

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 & Sage200 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.
  • Sage200 Connector
    • Writes the ids back to the Item record.