NetSuite Connector - Item Update

This function synchronizes item data from NetSuite to App4Sales. It processes various item types, including inventory items, and handles pricing, stock, images, and item categorization. The synchronization runs periodically as configured within the App4Sales platform.

Data Source Configuration

The connector retrieves item data directly from the NetSuite ERP system using its SuiteTalk API.

Items: Fetched via the NetSuite SuiteTalk API using ItemSearch. Items can be filtered by record type and/or a custom "Yes/No" field.
Price Levels & Currencies: Retrieved via PriceLevelSearch and GetAll(GetAllRecordType.currency) calls to the NetSuite API.
Stock Data: Can be retrieved in two ways:
- Directly from NetSuite API: Using service.GetItemsAvailability for item availability records, optionally filtered by specified locations.
- From NetSuite Servlets: If configured, stock data is fetched from custom NetSuite servlets (scripted endpoints) via HTTP GET requests using OAuth authentication. The servlet URL and authentication details are configured in the connector settings. The JSON response from the servlet is parsed to extract quantity, on-hand quantity, and delivery date.
Customer Pricing Information: Customer-specific pricing data (e.g., pricing groups, item-specific price overrides) is retrieved from an internal ERP response data store, which is populated during the customer synchronization process.

Data Mapping Table: App4Sales Item (Optimizers.App4Sales.Models.ObjectsDb.Item)

App4Sales Field	Source Field (NetSuite)	Logic/Notes
`ItemCode`	`item.itemId`	Directly mapped.
`InternalItemCode`	`item.internalId`	NetSuite internal ID of the item.
`ItemType`	`item.RecordType.ToString()`	The NetSuite record type of the item (e.g., "InventoryItem").
`CreatedDate`	`item.createdDate`	Directly mapped.
`Sysmodified`	`item.lastModifiedDate`	Directly mapped.
`EanCode`	`item.upcCode`	Mapped from UPC Code.
`Unit`	`item.saleUnit?.name`	Sales unit name.
`VatIncluded`	`item.pricesIncludeTax`	'I' if prices include tax, 'E' if excluded.
`SearchDescription`	`item.searchKeywords`	Directly mapped.
`ExtraData`	`item.pricingGroup?.internalId`	Internal ID of the pricing group.
`IsActionItem`	`item.pricingGroup?.internalId`	True if a pricing group is assigned, false otherwise.
`NextDelivery`	Custom Field (`ConnectorSettings.FirstPossibleShipdateCustomFieldName`)	Date from the NetSuite custom field configured as 'First Possible Ship Date'.
`PurchasePackageSize`	`item.minimumQuantity` or Custom Field (`ConnectorSettings.CustomFieldForPurchasePackageSize`)	If `ConnectorSettings.GetMinimumQuantityAsPPS` is true, uses `item.minimumQuantity`. Otherwise, uses the value from the configured custom field.
`VatPercentage`	`item.taxSchedule?.name` or `ConnectorSettings.ItemVatPercentage`	Mapped based on the NetSuite tax schedule name (if configured in VAT percentage mappings) or falls back to the default item VAT percentage setting.
`SalesPrice`	`item.pricingMatrix?.pricing`	Determined from the item's pricing matrix for the configured base price level and default sales price currency. Defaults to the price for quantity 0/1.
`LastAvailableStock`	Stock data (`stock.Available`)	The available stock quantity retrieved from NetSuite API or servlets.
`ItemStatus`	Custom Field (`ConnectorSettings.ItemStatusCustomFieldName`)	Determined by a custom field in NetSuite, which can be a string, boolean, or select field. Mapped using `ConnectorSettings.ItemStatusCustomValuesMapping` if provided.
`MatrixXValue`	`item.matrixOptionList` (script ID matching `ConnectorSettings.MatrixXField`)	Value of the matrix option identified by the Matrix X Field regex.
`MatrixXDescription`	`item.matrixOptionList` (script ID matching `ConnectorSettings.MatrixXField`)	Description of the matrix option identified by the Matrix X Field regex.
`MatrixYValue`	`item.matrixOptionList` (script ID matching `ConnectorSettings.MatrixYField`)	Value of the matrix option identified by the Matrix Y Field regex. (Often empty if MatrixYDescription is present)
`MatrixYDescription`	`item.matrixOptionList` (script ID matching `ConnectorSettings.MatrixYField`)	Description of the matrix option identified by the Matrix Y Field regex.
`MatrixParent`	`item.parent?.name`	Name of the parent item for matrix children, if both Matrix X and Y values are identified.
`MatrixParentId`	`item.parent?.internalId`	Internal ID of the parent item for matrix children, if both Matrix X and Y values are identified.
`Description`	`item.storeDisplayName` or `item.salesDescription`	If `ConnectorSettings.UseStoreDisplayNameAsDescription` is true, uses `storeDisplayName`. Otherwise, uses `salesDescription`. For matrix children, can inherit from parent. Whitespace is trimmed.
`Description2`	Configured via `ConnectorSettings.ItemsDescriptionsMapping`	Mapped from a NetSuite item field or custom field as specified in the JSON mapping.
`Description3`	Configured via `ConnectorSettings.ItemsDescriptionsMapping`	Mapped from a NetSuite item field or custom field as specified in the JSON mapping.
`Description4`	Configured via `ConnectorSettings.ItemsDescriptionsMapping`	Mapped from a NetSuite item field or custom field as specified in the JSON mapping.
`Description5`	Configured via `ConnectorSettings.ItemsDescriptionsMapping`	Mapped from a NetSuite item field or custom field as specified in the JSON mapping.
`ItemCategories` (Product group)	`[email protected]`	Mapped from the item's class name.
`ItemCategories` (Department)	`item.department?.name`	Mapped from the item's department name if `ConnectorSettings.UseDepartmentAsItemCategory` is true.
`ItemCategories` (Price group)	`item.pricingGroup?.name`	Mapped from the item's pricing group name.
`ItemCategories` ([CustomField] {scriptId})	`item.customFieldList`	All custom fields are added as item categories, using their script ID as part of the category name.
`ItemCategories` ([MatrixOption] {scriptId})	`item.matrixOptionList`	All matrix options are added as item categories, using their script ID as part of the category name.
`ItemCategories` ([MatrixParent])	`item.parent?.name`	Name of the parent item if applicable.
`ItemCategories` (StoreDetailedDescription)	`item.storeDetailedDescription` (or parent's if matrix child)	Mapped from the item's (or parent's) detailed store description.
`ItemCategories` (Weight)	`item.weight` and `item.weightUnit`	Combines weight value and unit (e.g., "10.50 kg") as an item category.
`FreeItemFields` (XML string)	Various, based on settings	Contains additional fields as key-value pairs (OptKvp). See special logic for details.
`PicturesBytes`	`item.storeDisplayImage` or custom image field	Images are downloaded from NetSuite based on URLs obtained from `item.storeDisplayImage` or a custom field specified by `ConnectorSettings.CustomFieldForItemPicture`.

Data Mapping Table: App4Sales PriceList (Optimizers.App4Sales.Models.PriceList)

App4Sales Field	Source Field (NetSuite)	Logic/Notes
`Id`	Derived	Generated hash code from NetSuite Currency Internal ID and Price Level Internal ID. For customer-specific price lists, a hash code of the customer code is used.
`ExternalId`	`priceLevel.internalId`	NetSuite internal ID of the price level.
`Currency`	`currency.displaySymbol`	Display symbol of the currency (e.g., "€", "$").
`ExternalCurrency`	`currency.internalId`	NetSuite internal ID of the currency.
`Code`	Derived	Combination of currency symbol and price level internal ID (e.g., "€_123" or "Level:123_Currency:€_Customer:CUST001").
`Description`	`priceLevel.name` or customer code	Name of the NetSuite price level (e.g., "[€] Base Price") or "Customer {customerCode}" for customer-specific price lists.
`Selectable`	Derived	True for standard price lists, False for customer-specific price lists.
`DuplicateOfPriceListId`	Derived	Internal ID of an existing price list if the current price list's data (prices, tier prices) is identical to avoid duplication.

Data Mapping Table: App4Sales PriceListPrice (Optimizers.App4Sales.Models.PriceListPrice)

App4Sales Field	Source Field (NetSuite)	Logic/Notes
`PriceListId`	Derived	References the generated ID of the associated App4Sales PriceList.
`ItemCode`	`item.itemId`	The App4Sales item code.
`Price`	`item.pricingMatrix?.pricing?.priceList?.value`	The individual item price. For customer-specific prices, this can be an override or derived from a specific price level in NetSuite.

Data Mapping Table: App4Sales StafflePrice (Optimizers.App4Sales.Models.StafflePrice)

App4Sales Field	Source Field (NetSuite)	Logic/Notes
`PriceListId`	Derived	References the generated ID of the associated App4Sales PriceList.
`ItemCode`	`item.itemId`	The App4Sales item code.
`MinQuantity`	`item.pricingMatrix?.pricing?.priceList?.quantity` (where quantity > 1)	The minimum quantity required for this tier price.
`Price`	`item.pricingMatrix?.pricing?.priceList?.value`	The price for this tier.

Data Mapping Table: App4Sales Warehouse (Optimizers.App4Sales.Datalayer.Entities.Warehouse)

App4Sales Field	Source Field (NetSuite)	Logic/Notes
`LocationId`	`location.internalId`	NetSuite internal ID of the location.
`Name`	`location.name`	Name of the NetSuite location.
`Code`	`location.internalId`	The NetSuite internal ID of the location is used as the warehouse code.
`IsActive`	`location.isInactive`	True if the location is active, False otherwise.

Special Logic & Filters

Item Filtering: Only active items are synchronized (isInactive = false). Items can be further filtered by:
- Specific NetSuite item record types (e.g., InventoryItem, AssemblyItem) defined in ConnectorSettings.ItemTypesForSync.
- A custom "Yes/No" field in NetSuite, specified by ConnectorSettings.SyncItemsBasedOnCustomYesNoField. If this setting is provided, it overrides item type filtering, and only items with this custom field set to "Yes" are synchronized.
Matrix Item Handling: Parent matrix items are explicitly skipped during synchronization (item.matrixType == _parent). Only matrix child items are processed, and the connector constructs the matrix relationship in App4Sales based on these children. This means that App4Sales does not receive parent items as standalone products but rather groups variants under a conceptual parent.
Stock Data Retrieval:
- By default, stock data is retrieved from NetSuite's item availability records.
- If ConnectorSettings.RetrieveStockDataUsingServlets is enabled, stock data is retrieved from a custom NetSuite servlet, which allows for highly customized stock logic within NetSuite.
- Stock quantities are aggregated by item, and can be split by location/warehouse.
- The "To Be Received" stock quantity can be read from a custom field in NetSuite (ConnectorSettings.ToBeReceivedStockCustomFieldName).
Price Deduplication: The connector checks for identical price list data (base prices and tier prices) to avoid creating duplicate price lists in App4Sales. If a new price list's data matches an existing one, the new price list is marked as a duplicate of the existing one.
Item Description Mapping: The ConnectorSettings.ItemsDescriptionsMapping allows flexible mapping of NetSuite item properties or custom fields to App4Sales item description fields (Description2-5).
Item Field Mapping: The ConnectorSettings.ItemFieldsMapping allows flexible mapping of NetSuite item properties or custom fields to other App4Sales item fields or additional Item Categories.
Concurrency: Picture downloading is performed concurrently, with the degree of parallelism controlled by ConnectorSettings.DownloadPicturesConcurrencyLimit.

Domain Specifics (Expanded)

Price Logic

The connector retrieves and processes item pricing from NetSuite, including base prices, tier prices, and customer-specific price overrides.

Base Price: The primary sales price is determined by searching the item's pricing matrix for a price associated with the ConnectorSettings.BasePriceLevelName and ConnectorSettings.DefaultSalesPriceCurrency. If no exact match is found, it falls back to a price with no specific price level for the default currency.
Tier/Quantity Pricing: NetSuite's quantity-based pricing (where quantity > 1) is mapped to App4Sales StafflePrice entities.
Customer Pricing Groups: NetSuite customer pricing groups (defined in ItemPriceDataOnCustomer.GroupPricingList) can override the default price level for specific items for a given customer. This allows for customer-segment-specific pricing.
Customer-Specific Item Prices: Individual item prices for a specific customer can be explicitly defined in NetSuite (via ItemPriceDataOnCustomer.ItemPricingList). These prices take precedence over general price levels and pricing groups for that particular item and customer. These can be direct price overrides or point to a different price level for that item for the specific customer.
VAT Calculation: The item's VAT percentage is determined first by its NetSuite taxSchedule name, matched against an internal VAT percentage configuration. If not found, a default ConnectorSettings.ItemVatPercentage is used.

Image Handling

The connector retrieves item images from NetSuite and stores them in App4Sales.

Image Sources: Images can be retrieved from the item's standard storeDisplayImage field or from a custom field specified by ConnectorSettings.CustomFieldForItemPicture.
Image Retrieval: The connector fetches URLs for images from NetSuite File records and then downloads the image data (bytes).
Image Storage: Downloaded images are stored in App4Sales as byte arrays, with duplicate checks to ensure efficiency.

Stock & Availability

Stock information is crucial for App4Sales and is handled with various configurations.

Locations/Warehouses: Stock can be managed across multiple NetSuite locations. These locations are mapped to App4Sales warehouses.
Stock Quantities: The connector synchronizes various stock quantities: available, committed, on hand, on backorder, and on order.
"To Be Received" Stock: A dedicated custom field in NetSuite (specified by ConnectorSettings.ToBeReceivedStockCustomFieldName) can be used to capture and synchronize stock that is expected to be received.
Zero/Negative Stock: Items with zero or negative stock are generally included, but specific filters can be applied to exclude them if needed (not explicitly shown in current code but possible through custom field filtering).

Matrix & Attributes

The connector supports NetSuite matrix items and maps various item attributes to App4Sales Item Categories and Free Fields.

Matrix Items: Parent matrix items are skipped, and only child variants are synchronized. The connector builds its own representation of the matrix by setting MatrixXValue, MatrixXDescription, MatrixYValue, MatrixYDescription, MatrixParent, and MatrixParentId based on the item's matrixOptionList and parent information, as configured by ConnectorSettings.MatrixXField and ConnectorSettings.MatrixYField.
Item Categories: The following NetSuite fields are mapped to App4Sales Item Categories for filtering and categorization:
- Product group (from [email protected])
- Department (from item.department?.name, if ConnectorSettings.UseDepartmentAsItemCategory is enabled)
- Price group (from item.pricingGroup?.name)
- Custom fields (all custom fields are added as categories using "[CustomField] {scriptId}")
- Matrix options (all matrix options are added as categories using "[MatrixOption] {scriptId}")
- Matrix parent name (using "[MatrixParent]")
- Detailed store description (from item.storeDetailedDescription)
- Weight (combining item.weight and item.weightUnit, e.g., "10.50 kg")
Free Fields: App4Sales Free Fields provide a flexible way to store additional item attributes. The connector can map:
- Shift prices (tier prices with quantity > 1) if ConnectorSettings.UseShiftPricesAsFreeFields is enabled.
- Pick locations (from item.location?.name) if ConnectorSettings.UsePickLocationsAsFreeFields is enabled.
- Recommended retail price (from SalesPrice) if ConnectorSettings.UseBasePriceAsFreeField is enabled.

Related Settings & Prerequisites

ItemTypesForSync: (String, comma-separated list) Specifies which NetSuite item record types (e.g., "InventoryItem", "AssemblyItem") should be synchronized. If empty, defaults to "InventoryItem".
SyncItemsBasedOnCustomYesNoField: (String) The script ID of a NetSuite custom "Yes/No" field. If provided, only items where this field is set to "Yes" will be synchronized. This setting overrides ItemTypesForSync.
LocationsUsedForStock: (String, semicolon-separated list) Specifies the NetSuite location names (or internal IDs) to consider when retrieving stock data.
RetrieveStockDataUsingServlets: (Boolean) If true, stock data will be retrieved from a custom NetSuite servlet instead of directly from item availability records.
StockDataPropertiesMapping: (String, comma-separated) Required if RetrieveStockDataUsingServlets is true. Defines the JSON property names for quantity, on-hand quantity, and delivery date within the servlet's response (e.g., "quantity,onHand,deliveryDate").
RetrieveServletsStockDataScriptValue: (String) The script ID of the NetSuite servlet that provides stock data.
RetrieveServletsStockDataDeployValue: (String) The deployment ID of the NetSuite servlet that provides stock data.
BasePriceLevelName: (String) The name of the NetSuite price level to use as the base or default price level for items.
DefaultSalesPriceCurrency: (String) The currency name (e.g., "Euro", "US Dollar") used to determine the default sales price.
ToBeReceivedStockCustomFieldName: (String) The script ID of a NetSuite custom field that holds the "To Be Received" stock quantity.
CustomFieldForItemPicture: (String) The script ID of a NetSuite custom field that contains an alternative item picture.
UseStoreDisplayNameAsDescription: (Boolean) If true, the item's storeDisplayName is used for the main App4Sales description; otherwise, salesDescription is used.
UseDepartmentAsItemCategory: (Boolean) If true, the item's department in NetSuite is mapped as an App4Sales Item Category.
ItemVatPercentage: (Decimal) The default VAT percentage to use if no specific tax schedule mapping is found.
GetMinimumQuantityAsPPS: (Boolean) If true, the item's minimumQuantity from NetSuite is used for App4Sales PurchasePackageSize.
CustomFieldForPurchasePackageSize: (String) The script ID of a NetSuite custom field to use for PurchasePackageSize if GetMinimumQuantityAsPPS is false.
ItemStatusCustomFieldName: (String) The script ID of a NetSuite custom field that determines the item's status (e.g., "Pre-sale", "In Stock").
ItemStatusCustomValuesMapping: (JSON String) A JSON mapping to translate NetSuite custom field status values to App4Sales item status values. Example: [{"NetSuiteStatus":"Presale","App4SalesStatus":"Presale"}].
MatrixXField: (String, Regex) A regular expression pattern to identify the NetSuite custom field script ID that represents the X-axis of a matrix item.
MatrixYField: (String, Regex) A regular expression pattern to identify the NetSuite custom field script ID that represents the Y-axis of a matrix item.
ItemsDescriptionsMapping: (JSON String) A JSON array defining mappings from NetSuite item fields (NetSuiteItemFieldName) or custom fields (NetSuiteItemCustomFieldName) to App4Sales item description fields (A4SDescriptionFieldNumber, 2-5).
ItemFieldsMapping: (JSON String) A JSON array defining mappings from NetSuite item fields (NetSuiteItemFieldName) or custom fields (NetSuiteItemCustomFieldName) to other App4Sales item fields (A4SItemFieldName) or Item Categories (A4SItemCategoryName).
UseShiftPricesAsFreeFields: (Boolean) If true, NetSuite tier prices (shift prices) are added as free fields to the App4Sales item.
UsePickLocationsAsFreeFields: (Boolean) If true, the item's pick location from NetSuite is added as a free field.
UseBasePriceAsFreeField: (Boolean) If true, the item's calculated sales price is added as a free field named "Recommended retail price".
DownloadPicturesConcurrencyLimit: (Integer) The maximum number of concurrent connections to use when downloading item pictures.
PageSizeForBatchCalls: (Integer) The number of records to fetch in each batch when making paginated API calls to NetSuite.

Known Limitations

The connector explicitly skips NetSuite parent matrix items, processing only their child variants. This means that parent items are not available as standalone products in App4Sales.
The accuracy of stock data from servlets is dependent on the custom NetSuite script's implementation and adherence to the defined StockDataPropertiesMapping.

NetSuite - Item sync