FTP Connector - Customer Update

This document describes the process by which customer data, including addresses, contact persons, dashboards, notes, price list links, fixed item filters, and customer-specific item class discounts, are synchronized from a CSV file hosted on an FTP server into the App4Sales platform. The connector downloads a customers.csv file and processes its contents to create or update customer records.

Data Source Configuration

The connector downloads a customers.csv file from a configured FTP server. The connection details (host, username, password, port, SFTP/TLS/SSL usage) are specified in the connector's administration settings. The downloaded file is expected to be a CSV file, with the separator character defined in the connector settings (default is semicolon ';').

Data Mapping Table - Customer Core Fields

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
`CustomerGuid`	`CustomerCode` (from CSV)	If a customer with the `CustomerCode` already exists (even if inactive), its existing GUID is reused. Otherwise, a new GUID is generated.
`CustomerName`	`CustomerName` or `Name`	Direct mapping. Required field; records with empty names are skipped.
`CustomerCode`	`CustomerCode` or `Code`	Direct mapping. Required field; records with empty codes are skipped.
`LanguageCode`	`LanguageCode`	Mapped based on configured language mappings. If `LanguageCode` from the source is provided, it attempts to map it to an App4Sales language.
`VatLiable`	`VatLiable`	If source `VatLiable` is empty, defaults to `true` if the customer's `VisitCountry` is 'NL' (case-insensitive). Overridden to `false` if the administration is configured for USA.
`Sysmodified`	Current system date/time	Set to the current date and time. Truncated to seconds.
`Discount`	`Discount`	Defaults to 0 if the source field is empty.
`CustomerManager`	`CustomerManager`	Direct mapping.
`CustomerDashboard`	`CustomerDashboard`	If provided and not empty, the Base64-encoded string is decoded and stored as a dashboard. The customer's `CustomerDashboard` field is then cleared.
`PaymentConditionCode`	`PaymentConditionCode` or `PaymentCondition`	Value is sanitized for XML compatibility. If the resulting code is empty, it is set to `null`.
`ChamberOfCommerceCode`	`ChamberOfCommerceCode`	Direct mapping.
`VatCode`	`VATCode`	Direct mapping. Overridden to `null` if the administration is configured for USA.
`ItemFilter`	`ItemFilter`, `ItemFilter_<ItemClass>` (from extra data)	If `ItemFilter` is present, pipe characters (`\|`) are converted to semicolons (`;`). If the `CustomerItemFilter` setting is enabled, item filters can also be derived from extra customer data (columns prefixed with `ItemFilter_` followed by the item class name). When coming from an application (not a direct updater), existing item filters are preserved if the incoming item filter is empty.
`UsesPriceField`	`UsesPriceField`	Defaults to 1 if the source field is empty. If price deduplication is enabled, this value may be remapped to a different price list ID.
`ActionPriceList`	`ActionPriceList`	If empty and a `DefaultActionPriceListCode` is configured, the ID of the default price list is used. If price deduplication is enabled, this value may be remapped to a different price list ID.
`Phone`	`CustomerPhone`	Direct mapping.
`PasswordWebshop`	N/A	Set to `null` during updates.
`DynamicFreeFields`	`DynamicFreeFields` (XML string)	If the source contains XML data, it is deserialized, transformed based on custom extra field definitions, and re-serialized.
`FreeFieldList`	`FreeField_<Caption>` (from extra data)	Extra customer data columns prefixed with `FreeField_` become entries in this list. The part after the prefix becomes the caption.
`FreeFields`	Derived from `FreeFieldList`	If `FreeFieldList` is populated, its content is serialized to XML and stored in this field.
`InternalCode`	Existing database record	If not an updater and the incoming `InternalCode` is empty, the existing `InternalCode` from the database is retained.
`ExtraData`	Existing database record	If not an updater and the incoming `ExtraData` is empty, the existing `ExtraData` from the database is retained.

Data Mapping Table - Addresses

Each customer can have multiple addresses of different types (e.g., Visit, Delivery, Invoice). The connector specifically processes Visit and Delivery addresses from the CSV. At least one Visit and one Delivery address will always be ensured. If only one type is provided, the other is created as a copy. Addresses are added to the customer's Addresses collection.

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
`AddressType`	"DEL" (for Delivery), "VIS" (for Visit)	Hardcoded based on the source columns (`DeliveryAddress1` vs. `VisitAddress1`). Defaults to "VIS" if empty.
`CustomerGuid`	Parent Customer's `CustomerGuid`	Automatically assigned.
`AddressId`	N/A	If empty, a default ID is set. Reset to `null` during creation of dummy addresses to avoid duplication.
`ExternalId`	N/A	Reset to `null` during creation of dummy addresses to avoid duplication.
`AddressLine1`	`DeliveryAddress1` / `VisitAddress1`	Direct mapping. This field is also parsed into `Street`, `HouseNumber`, `Addition` components.
`AddressLine2`	`DeliveryAddress2` / `VisitAddress2`	Direct mapping.
`AddressLine3`	`DeliveryAddress3` / `VisitAddress3`	Direct mapping.
`PostCode`	`DeliveryPostCode` / `VisitPostCode`	Direct mapping.
`City`	`DeliveryCity` / `VisitCity`	Direct mapping.
`State`	`DeliveryState` / `VisitState`	Direct mapping.
`Country`	`DeliveryCountry` / `VisitCountry`	If empty, defaults to "NL". If `Iso2` is empty, derived from `Iso2`.
`Iso2`	`DeliveryCountryCode` / `VisitCountryCode`	Mapped based on configured country mappings (if `Iso2` is provided). If `Iso2` is empty, it is derived from `Country`.
`Email`	`ContactEmail` (from customer), customer's `Email`, other addresses' `Email`, contact persons' `Email`	Unicode unit separators are removed. If the address email is empty and it's a main address, it attempts to inherit the email from the customer's main email, other addresses, or associated contact persons.
`Phone`	`ContactPhone` (from customer)	Direct mapping.
`Fax`	`ContactFax` (from customer)	Direct mapping.
`IsMainAddress`	`true`	Hardcoded as `true` for addresses derived from `DeliveryAddress` and `VisitAddress` fields. The system ensures at least one main Visit and one main Delivery address.

Data Mapping Table - Contact Persons

The connector primarily processes a single main contact person from the customer CSV data. Additional contact persons can be handled if present in the ContactPersons collection of the Customer object.

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
`FullName`	`ContactFullName`	If `FirstName`, `LastName`, `MiddleName` are empty but `FullName` exists, `FullName` is parsed. If `FullName` is empty but individual name parts exist, `FullName` is constructed.
`FirstName`	Derived from `FullName`	Derived from the first part of `FullName` if individual name fields are empty.
`MiddleName`	Derived from `FullName`	Derived from the middle part(s) of `FullName` if individual name fields are empty.
`LastName`	Derived from `FullName`	Derived from the last part of `FullName` if individual name fields are empty.
`Initials`	Derived from `FirstName`	First letter of `FirstName`.
`Phonenumber`	`ContactPhone`	Direct mapping.
`Email`	`ContactEmail`	Value is trimmed.
`IsMainContactPerson`	`true` (for main contact)	The contact derived from `ContactFullName` is marked as main. If multiple contacts exist, one is explicitly marked as main.
`CustomerGuid`	Parent Customer's `CustomerGuid`	Automatically assigned.
`ContactId`	N/A	If empty during an update, a hash-based ID is generated.
`DynamicFreeFields`	`DynamicFreeFields` (XML string)	If the source contains XML data, it is deserialized, transformed based on custom extra field definitions for contact persons, and re-serialized.
`PasswordWebshop`	N/A	Set to `null` during updates.

Special Logic & Filters

Customer Reactivation

If a customer was previously marked as inactive (e.g., its code was prefixed with ~), the system attempts to reuse its existing CustomerGuid when a new customer record with the same original code arrives. This ensures that historical data linked to that GUID (such as customer notes or order history) is preserved.

Invalid Customer Records

Customer records lacking a CustomerName or CustomerCode are considered invalid and are skipped during processing. Warnings are logged for these records.

Batch Processing

Customer updates are processed in batches of 100 records. This optimizes database performance and resource usage.

Script Hooks

The connector provides several script hooks (via the Jint scripting service) that allow for custom logic to be injected into the customer update pipeline:

BeforeUpdateCustomers: Executed once before any customer batches are processed.
UpdateCustomers (before database update): Executed after each batch of customers has been prepared but before being committed to the database.
UpdateCustomers (after database update): Executed after all customers and their related entities have been updated in the database.

Price List Deduplication

If the connector setting EnablePriceDeduplication is enabled, the UsesPriceField and ActionPriceList values for customers are checked against a predefined list of price list migrations. If a migration is found, the customer's price list ID is remapped to the new, deduplicated ID. If an original price list no longer exists, UsesPriceField defaults to 1 and ActionPriceList is cleared.

Address Creation and Normalization

The system ensures that each customer has at least one Visit address and one Delivery address. If only one type is provided in the source data, the missing address type is generated by copying the details of the available address.

All address fields undergo normalization:

Email addresses are cleaned of unicode unit separators and trimmed.
Country ISO2 codes are mapped using configured country mappings.
Missing ISO2 codes are derived from the country name, and vice versa.
Address type defaults to "VIS" if not specified.
Address lines are parsed to extract street, house number, and addition components, and then reformatted.
If an address's email is empty and it is a main address, it attempts to inherit an email from the customer's main email, other addresses, or contact persons.

Customer Notes Handling

If a CustomerNote is provided in the source data, any existing "BackOffice" notes for that customer are deleted from the database, and a new note is inserted with the provided content.

Extra Data Processing

The connector can process additional customer data from a CsvCustomer object (likely from extraCustomerData that may be available to the connector) to override standard customer fields or populate free fields and item filters.

Properties in the extra data that match existing customer properties are used to overwrite the customer's data.
Columns in the extra data that start with FreeField_ are parsed as generic free fields (caption: part after prefix, content: value).
Columns in the extra data that start with ItemFilter_ (e.g., ItemFilter_Category) are processed as customer-specific item filters, linking them to existing item classes and their values.

Password Clearing

During a customer update, webshop passwords for both the customer and their contact persons are explicitly set to null to prevent unintended overwrites or security issues.

Domain Specifics

Customer Core Fields

The connector focuses on maintaining a robust customer identity within App4Sales. This includes ensuring unique CustomerGuid values, handling customer reactivation, and mapping essential business data such as language preferences, VAT liability, and default discount rates. Price list selection (UsesPriceField) and action price lists (ActionPriceList) are supported, with logic for deduplication and default assignment.

Addresses

Addresses are critical for logistics and invoicing. The system ensures data quality by normalizing country codes, parsing address lines, and maintaining consistency between Visit and Delivery addresses. Email inheritance across customer, contact person, and address records helps ensure contactability. Every customer will have at least one Visit and one Delivery address record, even if dummy records need to be created.

Contacts

Contact persons are linked to the main customer via CustomerGuid. The connector supports parsing full names into individual components and ensures that one contact person is designated as the main contact. Dynamic free fields for contact persons allow for flexible storage of ERP-specific contact information.

Extra Data & Free Fields

The system provides mechanisms for extending customer and contact person data beyond standard fields. Through the use of DynamicFreeFields (XML payloads) and FreeField_ prefixed columns in source CSVs, administrators can import and manage custom attributes without code changes. Customer-specific item filters, defined through ItemFilter_ prefixed columns, allow for fine-grained control over what items a customer can view or order.

Discounts & Dashboards

Customer-specific item class discounts (DiscountsPerItemCLassValue) are supported, allowing for tiered pricing strategies. Dashboards, often containing customized layouts or analytics for a specific customer, are handled by storing their Base64-encoded content separately and clearing the original customer field.

Related Settings & Prerequisites

FTP Connection Settings: Hostname, Username, Password, Use SFTP, Use TLS, Use SSL (configured in connector administration).
Separator Character: Defines the delimiter used in the customers.csv file (default is ';').
Clean Data: If enabled, allows the removal of customers not updated in the current sync.
Enable Price Deduplication: If enabled, customer price list assignments are adjusted based on predefined migration rules to ensure consistency across price lists.
Default Action Price List Code: A code that specifies the default action price list to be assigned to customers if not explicitly provided.
Customer Item Filter: If enabled, the connector will process customer-specific item filters defined in the source CSV's extra data.
Mapped Languages: Pre-configured mappings between ERP language codes and App4Sales language codes.
Mapped Countries: Pre-configured mappings for country codes.
Custom Extra Fields: Definitions for custom fields for customers and contact persons, which dictate how DynamicFreeFields XML is structured.
Script Hooks (BeforeUpdateCustomers, UpdateCustomers): Custom Jint scripts can be used to extend or modify the customer update logic at various stages.
Administration Is USA: Flag to handle specific VAT rules for USA-based administrations.

Known Limitations

Customer records with empty CustomerName or CustomerCode are silently skipped.
The parsing of FullName into FirstName, MiddleName, and LastName in FillContactName assumes a simple space-separated structure, which might not be robust for all naming conventions.
The current implementation of ItemFilter processing in extra data assumes that ItemFilter_ is followed directly by the item class name.
The exact behavior of "ExternalId" for addresses is not fully detailed in the provided code snippet, but it seems to be used as an indicator of a valid address from an external system.

FTP - Customer sync