Xlsx Connector - Item Update
The Item Update function for the Xlsx connector processes item-related data from a single multi-sheet Excel file. It reads items, prices, stock levels, classifications, and matrix (parent/child) relationships, then transforms and saves this data into the App4Sales database. The process is orchestrated to handle complex data structures, including tiered pricing, future stock availability, and custom attributes. Image updates are handled separately by processing a zip file containing image files named after item codes.
Data Source Configuration
The connector reads data from a single XLSX file specified in the connector settings. The process expects the following sheets to be present, each with a specific set of columns:
Items: The main sheet containing core item information like item code, description, and stock levels. Unrecognized columns in this sheet are treated as custom attributes (free fields).
Prices: Defines the base price for items within different price lists.
TierPrices: Used for quantity-based tiered pricing for items.
PriceLists: Contains the definitions of the price lists used in the 'Prices' sheet.
ItemClasses: Defines item classifications (or item groups) in a flat list format, which are then structured hierarchically.
DeliveryDates: Provides information about future incoming stock for items.
MatrixParents: Defines parent products for matrix/variant items. (Used when 'SyncMatrixLogicStandard' setting is disabled).
MatrixRows: Defines the child/variant items in a matrix. (Used when 'SyncMatrixLogicStandard' setting is disabled).
MatrixColumns: Defines the matrix headers (e.g., sizes, colors). (Used when 'SyncMatrixLogicStandard' setting is disabled).
Additionally, a separate zip file containing images can be provided to update item images.
Data Mapping
Items
App4Sales Field | Source Field (Excel Sheet: Items) | Logic/Notes |
ItemCode | ItemCode | Unique identifier for the item. |
Description | Description | The main name or description of the item. |
Stock | LastAvailableStock | Represents the current physical stock level. |
Unit | Unit | The unit of measure for the item (e.g., PCS, BOX). |
VatCode | VatCode | The tax code associated with the item. |
IsActive | IsActive | A boolean (TRUE/FALSE) indicating if the item is active. |
BarCode | BarCode | The item's barcode. |
Brand | Brand | The brand of the item. This is also used to create an 'ItemClass' named 'Brand'. |
ItemGroupCode | ItemGroupCode | Used to link the item to a classification group from the 'ItemClasses' sheet. |
MatrixParent | MatrixParent | Links a child item to its parent in a matrix structure. |
MatrixRowCode | MatrixRowCode | Identifier for the item's row position in a matrix. |
MatrixColumnCode | MatrixColumnCode | Identifier for the item's column position in a matrix. |
Free Fields | (Any unrecognized column) | Any column in the 'Items' sheet that is not one of the standard fields is dynamically added as a custom attribute (free field) to the item. |
Item Prices
App4Sales Field | Source Field (Excel Sheet: Prices) | Logic/Notes |
ItemCode | ItemCode | Links the price to an item. |
PriceList | PriceList | The pricelist this price belongs to. Must match a 'PriceList' from the 'PriceLists' sheet. |
Price | Price | The sale price of the item for the given pricelist. |
Currency | Currency | The currency of the price. |
Item Tier Prices
App4Sales Field | Source Field (Excel Sheet: TierPrices) | Logic/Notes |
ItemCode | ItemCode | Links the tier price to an item. |
PriceList | PriceList | The pricelist this tier price belongs to. |
MinimumQuantity | MinimumQuantity | The minimum quantity required for this price tier to apply. |
Price | Price | The price for this quantity tier. |
Future Stock (Delivery Dates)
App4Sales Field | Source Field (Excel Sheet: DeliveryDates) | Logic/Notes |
ItemCode | ItemCode | Links the delivery to an item. |
DeliveryDate | DeliveryDate | The expected date of the future delivery. |
Quantity | Quantity | The quantity of the item in the future delivery. |
Domain Specifics
Price Logic
The connector constructs item pricing from multiple sources within the Excel file:
Base Prices: The 'Prices' sheet is used to define the standard price for an item in one or more price lists. Each row links an `ItemCode` to a `Price` within a named `PriceList`.
Tiered Pricing: The 'TierPrices' sheet allows for volume-based pricing. It defines a `Price` for an `ItemCode` that becomes active when a `MinimumQuantity` is reached.
Price Lists: The 'PriceLists' sheet defines the available price lists and their descriptions, which are then referenced by the 'Prices' and 'TierPrices' sheets.
Image Handling
Item images are not read from the Excel file. Instead, the connector processes a separate zip archive. The image files (JPG, JPEG, PNG) inside the zip file must be named in a way that links them to an item. The part of the filename before the first underscore `_` is assumed to be the `ItemCode`. For example, an image file named SHIRT01_front.jpg will be linked to the item with `ItemCode` 'SHIRT01'.
Stock & Availability
Stock information is sourced from two places:
Current Stock: The `LastAvailableStock` column in the main 'Items' sheet is used for the current, immediately available stock quantity.
Future Stock: The 'DeliveryDates' sheet provides information on upcoming deliveries. Each row links an `ItemCode` to a `Quantity` that will be available on a future `DeliveryDate`. This is used to display future availability in App4Sales.
Matrix & Attributes
The connector supports two ways of handling classifications and matrix items:
Item Classifications: The 'ItemClasses' sheet contains a flat list of item groups (`ItemGroupCode` and `Description`). The connector processes this list to build a hierarchical structure of classifications that can be assigned to items. Additionally, the 'Brand' column from the 'Items' sheet is automatically used to create a 'Brand' item class.
Matrix Items: When the `SyncMatrixLogicStandard` setting is disabled, the connector uses three dedicated sheets (`MatrixParents`, `MatrixRows`, `MatrixColumns`) to construct a full matrix/variant structure. Items from the main 'Items' sheet are then linked to this structure using the `MatrixParent`, `MatrixRowCode`, and `MatrixColumnCode` fields.
Custom Attributes: Any column in the 'Items' sheet that is not a recognized, predefined column is automatically processed as a custom attribute (free field). The column header becomes the attribute label, and the cell value becomes the attribute value for the item.
Related Settings & Prerequisites
SyncMatrixLogicStandard: This setting determines how matrix items are processed. If disabled, the connector uses the dedicated 'MatrixParents', 'MatrixRows', and 'MatrixColumns' sheets to build the matrix relationships.
File Paths: The paths to the main XLSX file and the optional image zip file must be correctly configured in the connector settings.
Sheet Naming: The Excel sheets must be named exactly as listed in the 'Data Source Configuration' section for the connector to find them.