XLSX Connector - Customer Update

This document describes the customer synchronization process for the XLSX connector. The connector reads customer data from an Excel file (.xlsx), which can be provided directly or fetched from an FTP location. It processes customers, addresses, contact persons, payment conditions, and related data, and updates the App4Sales database.

Data Source Configuration

The connector retrieves an Excel file (*.xlsx) as the data source. The file can be provided in two ways:

Direct Upload: The Excel file is uploaded directly through the CMS.
FTP Location: The connector downloads all .xlsx files from a specific folder on an FTP server. The FTP server details (address, username, password) are configured in the administration settings. The folder for customer data is typically named /CUSTOMERS/.

The Excel file should contain the following sheets:

Customers: Main customer data.
Addresses: Customer address information.
ContactPersons: Customer contact persons.
PaymentConditions: Payment condition details. If this sheet is not present or empty, payment conditions are inferred from the PaymentConditionCode column in the Customers sheet.

Data Mapping Tables

Customers

This table details the mapping of fields from the Customers sheet to the App4Sales customer entity.

App4Sales Field	Source Field (Excel)	Logic/Notes
CustomerCode	`CustomerCode`	Mandatory. Rows with an empty `CustomerCode` are skipped. A special reactivation logic applies if the code starts with `~`, which is used to reactivate previously deleted customers.
CustomerName	`CustomerName`	Mandatory. Rows with an empty `CustomerName` are skipped.
CustomerEnabled	`CustomerEnabled`	Boolean (true/false, 1/0). Defaults to `true`. Determines if the customer is active.
UsesPriceField (Price List ID)	`PriceListCode`	The code is matched against the App4Sales price lists to find the corresponding internal ID. If `EnablePriceDeduplication` setting is active, the code may be migrated to a new pricelist ID. If no match is found, it may fall back to the default pricelist (ID 1).
Discount	`Discount`	The default discount percentage for the customer.
VatLiable	`VatLiable`	Boolean (true/false, 1/0). Determines if the customer is liable for VAT. For USA administrations, this is always set to `false`.
LanguageCode	`LanguageCode`	The customer's language code (e.g., "EN", "NL"). It is mapped to an internal App4Sales language code via the MappedLanguages table.
PaymentConditionCode	`PaymentConditionCode`	The code for the customer's payment condition. If empty, it's set to null to prevent database errors.
CustomerManager	`CustomerManager`	The sales representative or manager for this customer.
CustomerClassification	`CustomerClassification`	A code used for classification and filtering in the CMS.
ChamberOfCommerceCode	`ChamberOfCommerceCode`	The customer's Chamber of Commerce number.
ActionPriceList	`ActionPriceList`	The ID of a secondary price list for promotional items. Can be set globally via the `DefaultActionPriceListCode` setting.
CustomerNote	`CustomerNote`	A general note for the customer. When provided, it overwrites any existing backoffice notes for that customer.
VatCode	`VatCode`	The customer's VAT identification number. For USA administrations, this is always cleared.
Website	`Website`	The customer's official website.
Email	`Email`	The customer's general email address. This email is assigned to the main visiting address (VIS).
PhoneNumber	`PhoneNumber`	The customer's main phone number.
FreeFields	(All other columns)	Any columns in the `Customers` sheet that do not match the fields above are stored as XML in the `FreeFields` property.

Addresses

This table details the mapping of fields from the Addresses sheet.

App4Sales Field	Source Field (Excel)	Logic/Notes
(Customer Link)	`CustomerCode`	Links the address to a customer.
AddressType	`AddressType`	Type of address (e.g., "VIS" for Visiting, "DEL" for Delivery). If empty, defaults to "VIS". The connector ensures that both a VIS and DEL address exist, creating a copy if one is missing.
IsMainAddress	`IsMainAddress`	Boolean (true/false, 1/0). Marks the main address. If no main address is specified for VIS or DEL types, the first one found becomes the main one.
AddressLine1, AddressLine2, AddressLine3	`AddressLine1`, `AddressLine2`, `AddressLine3`	Address lines. `AddressLine1` is parsed to extract street name, house number, and addition if not already structured.
PostCode	`PostalCode`	The postal code for the address.
City	`City`	The city for the address.
Iso2 (Country Code)	`IsoCode`	The 2-letter ISO country code. It can be mapped to a different code via the MappedCountries table. If empty, it's derived from the `Country` field.
State	`State`	The state or province.
Country	`Country`	The full country name. If empty, it's derived from the `IsoCode` field.

Contact Persons

This table details the mapping of fields from the ContactPersons sheet.

App4Sales Field	Source Field (Excel)	Logic/Notes
(Customer Link)	`CustomerCode`	Links the contact to a customer.
IsMainContactPerson	`IsMainContactPerson`	Boolean (true/false, 1/0). If no main contact is specified, the first contact person in the list is automatically marked as the main one.
FirstName, MiddleName, LastName, FullName	`FirstName`, `MiddleName`, `LastName`, `FullName`	Name fields. If `FullName` is provided but others are not, it is parsed to populate them. If the individual name fields are provided, `FullName` is composed from them.
Function	`BusinessFunction`	The contact person's job title or function.
Email	`Email`	The contact's email address. The value is trimmed.
Phonenumber	`PhoneNumber`	The contact's direct phone number.
MobileNumber	`MobileNumber`	The contact's mobile phone number.
Language_iso2	`LanguageIsoCode`	The contact's language code.
Initials	`Initials`	The contact's initials.
Gender	`Gender`	The contact's gender.
Pred	`Pred`	The contact's title (e.g. Dhr.).

Special Logic & Filters

Batch Processing: Customers are processed in batches of 100 to manage memory and performance.
Data Validation: The process skips any customer records where CustomerCode or CustomerName is missing.
Address Normalization: The connector ensures that every customer has both a "Visit" (VIS) and a "Delivery" (DEL) address. If one is missing, it creates a copy of the other. If no addresses are provided at all, it may create a dummy address to store the country ISO code.
Main Contact Person: If a contact person is marked as the main contact, it is linked to the customer. If no contact is explicitly marked as main, the first contact person in the list for that customer is automatically designated as the main contact.
Scripts: The process triggers two Jint scripts if they are configured: BeforeUpdateCustomers before the batch processing starts, and UpdateCustomers after each batch is processed.

Related Settings & Prerequisites

EnablePriceDeduplication: If enabled, migrates customers to use consolidated price lists based on a predefined mapping.
DefaultActionPriceListCode: If set, assigns this action pricelist to all customers who do not have one specified.
CustomerItemFilter: When enabled, allows for creating fixed item filters for a customer based on data from an extra data source.

Known Limitations

The connector does not process customer-specific item class discounts from the base Excel file; this requires a separate data source.
Dashboards and customer notes are processed, but this is less common with the XLSX connector.

Xlsx - Customer sync