CSV Connector - Customer Update

This document describes the process of updating customers, including their addresses, contact persons, and other related data, using the CSV connector. The process reads customer data from a CSV file, transforms it, and saves it into the App4Sales database. The update is handled by the CustomerUpdateHandler, which contains the core logic for processing the data.

Data Source Configuration

The connector retrieves customer data from .csv files. The source can be configured in two ways:

FTP/SFTP/FTPS: The connector downloads all *.csv files from a specific directory on a remote server. The server address, path, and credentials are set in the connector's administration settings. For the customer sync, it looks in the folder configured at the 'Address' field, in a subfolder named 'customers'.
File Upload: A CSV file can be uploaded directly during a manual sync.

The CSV parsing is configured with the following settings:

Separator Character: Defined by the SeparatorCharacter setting in the connector configuration. It defaults to a semicolon (;) if not specified.
Headers: The connector expects the first line of the CSV file to be a header row.

Data Mapping: Customer

The following table shows the mapping from the source CSV file fields to the App4Sales customer fields.

App4Sales Field	Source Field (CSV)	Logic/Notes
CustomerCode	CustomerCode / Code	Mapped from `CustomerCode`. If `CustomerCode` is empty, it falls back to `Code`. This field is mandatory.
CustomerName	CustomerName / Name	Mapped from `CustomerName`. If `CustomerName` is empty, it falls back to `Name`. This field is mandatory.
CustomerGuid	-	An internal identifier. The system tries to find an existing GUID based on the `CustomerCode`. If a customer was previously inactive (code starting with '~'), its old GUID is reused. If no existing GUID is found, a new one is generated.
VatCode	VATCode	Directly mapped from `VATCode`. Set to `null` for US-based administrations.
VatLiable	VatLiable	Directly mapped from `VatLiable`. Set to `false` for US-based administrations.
ChamberOfCommerceCode	ChamberOfCommerceCode	Directly mapped from `ChamberOfCommerceCode`.
LanguageCode	LanguageCode / Language	Mapped from `LanguageCode`, with a fallback to `Language`. The ERP language code is then mapped to an App4Sales language code via the 'Mapped Languages' table.
Discount	Discount	Directly mapped from `Discount`.
PaymentConditionCode	PaymentConditionCode / PaymentCondition	Mapped from `PaymentConditionCode`, with a fallback to `PaymentCondition`. If the value is an empty string, it is converted to `null`. The payment condition is also added to the global payment conditions table.
CustomerManager	CustomerManager / Manager	Mapped from `CustomerManager`, with a fallback to `Manager`.
Sysmodified	Sysmodified	Directly mapped from `Sysmodified`. The time is truncated to seconds.
UsesPriceField	UsesPriceField	Mapped from `UsesPriceField`. Defaults to `1` if not provided. If Price Deduplication is enabled, the ID might be migrated to a new pricelist ID.
ActionPriceList	ActionPriceList	Mapped from `ActionPriceList`. If Price Deduplication is enabled, the ID might be migrated to a new pricelist ID. Can be overridden by the 'Default Action PriceList Code' setting.
Website	Website	Directly mapped from `Website`.
Phone	CustomerPhone / Phone	Mapped from `CustomerPhone`, with a fallback to `Phone`. The value is trimmed.
Email	Email	Directly mapped from `Email`. The value is trimmed.
Fax	Fax	Directly mapped from `Fax`.
CustomerEnabled	-	Hardcoded to `true`.
PasswordWebshop	-	Set to `null` during an automated sync (Updater).

Domain Specifics

Addresses

The connector processes delivery and visit addresses from the CSV. It ensures that if one address type (Visit or Delivery) is provided, the other is created as a copy. If no addresses are provided, a dummy address might be created to store the country code.

App4Sales Field	Source Field (CSV)	Logic/Notes
AddressLine1	VisitAddress1 / DeliveryAddress1	The full address line. The system attempts to parse this into separate Street, Number, and Addition components.
PostCode	VisitPostCode / DeliveryPostCode	Directly mapped.
City	VisitCity / DeliveryCity	Directly mapped.
State	VisitState / DeliveryState	Directly mapped.
Country	VisitCountry / DeliveryCountry	Directly mapped. If empty, it's set to the value of `Iso2`.
Iso2	VisitCountryCode / DeliveryCountryCode	The country code is mapped from the source. If a mapping exists in the 'Mapped Countries' table, the mapped value is used. Otherwise, the source value is trimmed. If the source is empty, the system attempts to derive it from the `Country` field.
Email	-	The email for the main address is inherited from the customer's main email if it's not provided on the address itself.
IsMainAddress	-	The first visit address and the first delivery address are marked as the main address for their respective types.

Contact Persons

Contact persons are linked to customers. If a main contact person is provided, it's flagged accordingly. If no main contact is specified, the first contact person in the list is automatically designated as the main contact.

App4Sales Field	Source Field (CSV)	Logic/Notes
FullName	ContactFullName	Mapped from `ContactFullName`. If only FullName is provided, the system attempts to split it into FirstName, MiddleName, and LastName. If those are provided, FullName is constructed from them.
Email	ContactEmail	Mapped from `ContactEmail`. The value is trimmed.
Phone	ContactPhone	Directly mapped.
Fax	ContactFax	Directly mapped.
IsMainContactPerson	-	Set to `true` if the contact is identified as the main contact. If no main contact is specified, the first contact in the list is marked as the main contact.
PasswordWebshop	-	Set to `null` during an automated sync (Updater).

Extra Data & Free Fields

The connector can enrich customer data with information from a secondary 'extra data' CSV file. This file is typically located in an FTP folder. Properties from this extra file overwrite any existing values on the customer record if the property names match.

Fields from the extra data file with a name starting with FreeField_ (e.g., FreeField_CustomInfo) are added to the customer's free fields. The caption will be 'CustomInfo'.
If the CustomerItemFilter setting is enabled, fields from the extra data file starting with ItemFilter_ are used to create fixed item filters for the customer. For example, a column named ItemFilter_Brand with a value of BrandA~BrandB would restrict the customer to seeing only items from BrandA and BrandB.

Discounts & Dashboards

Discounts: Item class discounts can be provided. Any discount with a value greater than 0 is stored.
Dashboards: A Base64-encoded dashboard layout can be provided in the CustomerDashboard field. This is decoded and stored separately. The field on the customer object is then cleared.
Notes: A customer note can be provided in the CustomerNote field. When a note is supplied, all existing notes from the 'BackOffice' salesrep for that customer are deleted and the new note is inserted.

Special Logic & Filters

Customer Validation: Rows in the CSV are skipped if both CustomerCode (or Code) and CustomerName (or Name) are empty. A warning is logged for invalid records.
Batch Processing: Customers are processed in batches of 100.
Reactivation: The system is designed to reactivate customers whose codes were previously prefixed with a tilde (~), preserving their history by reusing the existing GUID.
Address Normalization: The system ensures that every customer has both a visit and a delivery address. If one is missing, it's created by copying the other. Country codes (ISO2) are normalized using a mapping table or derived from the country name.
Scripting Hooks: The connector executes two Jint scripts if they are configured: BeforeUpdateCustomers (before processing) and UpdateCustomers (after processing each batch).

Related Settings & Prerequisites

SeparatorCharacter: Defines the delimiter for CSV files. Defaults to ';'.
CleanData: If enabled, all existing customers are wiped before the sync.
EnablePriceDeduplication: If enabled, pricelist IDs are migrated according to a predefined mapping.
DefaultActionPriceListCode: If set, all customers without a specific action pricelist will be assigned the one specified here.
CustomerItemFilter: Enables the creation of fixed item filters for customers from the extra data file.

Csv - Customer sync