Sage100 Connector - ItemUpdate

The ItemUpdate function synchronizes item-related data from Sage100 to the App4Sales platform. It retrieves items, prices, VAT percentages, warehouses, stock information, and item classifications from Sage100 APIs and SQL services, then transforms and maps this data to the internal App4Sales item structure. This process ensures that App4Sales has up-to-date information on products for sales representatives and for internal use.

Data Source Configuration

The connector retrieves item data from various Sage100 API endpoints and a SQL service.

Item Units: Fetched from the API endpoint ArticleService/rest/GetUniteVenteList.
Item Prices: Fetched from the API endpoint TarifService/rest/GetListTarifArticles. These include item-specific pricing and category-based tariffs.
VAT Percentages: Retrieved from the API endpoint TaxeService/rest/GetList.
Warehouses: Obtained from the API endpoint StockService/rest/GetListDepot.
Available Stock: Queried from the SQL Service endpoint SqlService/rest/GetData using the SQL view view_webservice_availablestock.
Item Classes: Sourced from the API endpoint ArticleService/rest/GetCatalogueList. These are used to build item classifications in App4Sales.
Items: The core item data is retrieved from the API endpoint ArticleService/rest/GetList.
Item Pictures: Fetched from the API endpoint FileService/rest/ArticleImage/{itemCode}, where {itemCode} is dynamically replaced with the item's reference.

Data Mapping Table - Items

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
ItemCode	`Item.Reference`	Direct mapping from Sage100 item reference.
EanCode	`Item.CodeBarres`	Direct mapping from Sage100 barcode.
Description	`Item.Intitule`	Direct mapping from Sage100 item title/description.
Unit	`Item.IdUniteVente`, `ItemUnit.Nom`	Looks up the unit's `Name` (`Nom`) from the fetched `ItemUnit` list using `Item.IdUniteVente` as the identifier.
InternalItemCode	`Item.Reference`	Direct mapping from Sage100 item reference.
PurchasePackageSize	Derived	Hardcoded to `1`.
SalesPrice	`Item.PrixVente`	Direct mapping from Sage100 sales price.
VatPercentage	`VatCodePercentage.Taux`	Defaults to `0%`. Overridden if a matching `VatCode` (from `VatCodePercentage.CodeTaxe`) is found based on the `UseVatCodeForVatLiability` setting.
VatIncluded	`Item.TypePrix`	Set to 'I' (Included) if Sage100 `Item.TypePrix` is 'AllIn'; otherwise, set to 'E' (Excluded).
NextDelivery	`Item.DelaiLivraison`	If Sage100 `Item.DelaiLivraison` is `0`, it is set to null. Otherwise, it is calculated as the current date plus the number of days specified in `Item.DelaiLivraison`.
AcceptsDefaultDiscount	`Item.TypePrix`	Set to `true` if Sage100 `Item.TypePrix` is not 'AllIn'; otherwise, `false`.
ItemType	`Item.TypeArticle`	Mapped by converting the Sage100 article type enumeration to its string representation.
Description2	`Item.Langue1`	Direct mapping from Sage100 additional language description 1.
Description3	`Item.Langue2`	Direct mapping from Sage100 additional language description 2.
LastAvailableStock	`SqlServiceAvailableStock.QteAvail`	Aggregated from all available stock records for the item and calculated using the stock formula (default: `+ShelfStock-ToBeDelivered+ToBeReceived`).
ItemClasses (Assortment)	`Item.IdCatalogue1`, `ItemClass.Nom`	Uses `Item.IdCatalogue1` to look up the `Description` (`Nom`) from the fetched `ItemClass` list. If no description is found, `Item.IdCatalogue1` itself is used as the value.
ItemClasses (Assortment2)	`Item.IdCatalogue2`, `ItemClass.Nom`	Uses `Item.IdCatalogue2` to look up the `Description` (`Nom`) from the fetched `ItemClass` list. If no description is found, `Item.IdCatalogue2` itself is used as the value.
ItemClasses (Assortment3)	`Item.IdCatalogue3`, `ItemClass.Nom`	Uses `Item.IdCatalogue3` to look up the `Description` (`Nom`) from the fetched `ItemClass` list. If no description is found, `Item.IdCatalogue3` itself is used as the value.
ItemClasses (Assortment4)	`Item.IdCatalogue4`, `ItemClass.Nom`	Uses `Item.IdCatalogue4` to look up the `Description` (`Nom`) from the fetched `ItemClass` list. If no description is found, `Item.IdCatalogue4` itself is used as the value.
FreeItemFields	`Item.InfosLibres` (list of `FreeField`)	Mapped from Sage100 free fields. Converted into an XML string containing key-value pairs. Free fields with a value of "0" are skipped if the `Allow zero values in free fields` setting is `false`.
FreeSortField	`Item.InfosLibres` (specific FreeField)	If an `ItemCategory` is marked as a `FreeSortField`, its corresponding value from `Item.InfosLibres` is parsed as an integer and mapped.

Data Mapping Table - Item Prices

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
PriceList.Id	`ItemPrice.IdCategorieTarifaire`	Mapped from Sage100 price category ID. Uses a default value if null.
PriceList.Code	`ItemPrice.IdCategorieTarifaire`	String representation of Sage100 price category ID.
PriceList.Description	`ItemPrice.Description`	Mapped from Sage100 price category description.
PriceList.Selectable	Derived	Hardcoded to `true`.
PriceListPrice.ItemCode	`ItemPrice.RefArticle`	Mapped from Sage100 item reference for pricing.
PriceListPrice.PriceListId	`ItemPrice.IdCategorieTarifaire`	Mapped from Sage100 price category ID. Uses a default value if null.
PriceListPrice.Price	`ItemPrice.PrixVente`	Mapped from Sage100 sales price. Note: The connector currently only uses prices exclusive of VAT.

Data Mapping Table - VAT Percentages

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
VatCodePercentage.Country	Derived	Hardcoded to `"SAGE"`.
VatCodePercentage.VatCode	`VatCodePercentage.CodeTaxe`	Mapped from Sage100 tax code.
VatCodePercentage.VatPercentage	`VatCodePercentage.Taux`	Mapped from Sage100 tax rate.

Data Mapping Table - Warehouses

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
Warehouse.MagCode	`Warehouse.Id`	Mapped from Sage100 warehouse ID.
Warehouse.MagDescription	`Warehouse.Code`, `Warehouse.Intitule`	Concatenation of Sage100 warehouse code and title/description (format: `{Code}_{Intitule}`).

Special Logic & Filters

Item Status Filtering: Only items with a Sage100 Status of 'Active' are processed. Items with other statuses are skipped.
Webshop Item Filtering: If the connector setting Pass Web Shop Item is enabled, only items with a Sage100 Webshop status of 'Active' are processed.
Stock Calculation: The LastAvailableStock for an item is calculated based on a configurable stock formula. The default formula is +ShelfStock-ToBeDelivered+ToBeReceived. Stock values from Sage100 SqlServiceAvailableStock.QteAvail contribute to this calculation.
Image Handling Conditional Logic: Item pictures are fetched from Sage100 if the UseImagesOnlyFromPIM setting is false. If this setting is true, the connector assumes images are managed by a separate PIM system and does not attempt to fetch them from Sage100.
Free Fields Zero Value Handling: When mapping free fields, any free field with a value of "0" will be skipped if the connector setting Allow zero values in free fields is false.

Domain Specifics (Expanded)

Price Logic

The connector retrieves item prices from Sage100 using the TarifService/rest/GetListTarifArticles API. Prices are categorized by IdCategorieTarifaire, which maps to App4Sales PriceList.Id. The SalesPrice (PrixVente) is directly mapped to PriceListPrice.Price. Note: The connector currently only supports prices exclusive of VAT. Inclusive VAT prices are not considered in the current implementation. VAT percentages are applied based on the UseVatCodeForVatLiability setting, which looks for a matching VAT code (CodeTaxe) from Sage100.

Image Handling

Item pictures are retrieved from Sage100 via the FileService/rest/ArticleImage/{itemCode} API endpoint. The system attempts to fetch images for each item unless the UseImagesOnlyFromPIM setting is enabled. If an image is not found or an error occurs during retrieval, it is logged, but the item update process continues without the image.

Stock & Availability

Stock information is obtained from the Sage100 SQL service by querying the view_webservice_availablestock view. The QteAvail field from this view is used to calculate the LastAvailableStock in App4Sales. The calculation is dynamic and based on a configurable stock formula, which defaults to +ShelfStock-ToBeDelivered+ToBeReceived if no specific formula is defined.

Matrix & Attributes

Item classifications in App4Sales are derived from Sage100's catalogue system and free fields. The IdCatalogue1, IdCatalogue2, IdCatalogue3, and IdCatalogue4 fields from Sage100 Item entities are mapped to generic 'Assortment' item classes in App4Sales. The Description (Nom) from the Sage100 ItemClass is used for the item class value; if not available, the catalogue ID itself is used. Additionally, Sage100's free fields (InfosLibres) contribute to both App4Sales free item fields and additional item classes or a free sort field, depending on how their corresponding ItemCategory is configured in App4Sales (i.e., whether it's marked as a free field, filter, or free sort field).

Related Settings & Prerequisites

The following connector settings influence the behavior of the ItemUpdate function:

Use vatcode for vatliability: (UseVatCodeForVatLiability) Specifies the VAT code to use for determining the default VAT percentage for items. If a matching VAT code is found in Sage100, its percentage is applied.
Pass Web Shop Item: (IsWebshopItem) If enabled, only items marked as 'Active' in Sage100's webshop status will be processed.
Allow zero values in free fields: (AllowZeroValuesInFreeFields) If false, free fields with a value of "0" from Sage100 will be ignored and not mapped to App4Sales.

Additionally, the global PIM setting UseImagesOnlyFromPIM, found in PimSettings, controls whether item pictures are sourced from Sage100 or an external PIM system. If true, Sage100 item pictures are skipped.

Known Limitations

The connector currently only handles prices exclusive of VAT when updating price lists. Inclusive VAT prices are not supported.
Item pictures are fetched individually per item, which may impact performance for a very large number of items.
The stock formula has a default value (+ShelfStock-ToBeDelivered+ToBeReceived); if Sage100 stock fields do not align with these, custom configuration of the stock formula is required.

Sage100 - Item sync