FtpXml Connector - Customer Update
Overview
This document describes the Customer Update pipeline for the FtpXml connector. This process synchronizes customer data, including addresses, contact persons, discounts, dashboards, and free fields, from an external XML file via FTP into the App4Sales internal data structure. The synchronization runs in batches of 100 customers.
Data Source Configuration
The connector downloads a file named `customers.xml` from a configured FTP location (`FolderName`) to a local temporary directory. The FTP connection details (host, port, username, password, connection type, and encryption method) are configured in the connector settings. The data is pulled from the external system.
Data Mapping Table - Customer Core Fields
App4Sales Field | Source Field (XML Node/Attribute) | Logic/Notes |
CustomerGuid | Customer/customerGuid | Parsed as a GUID. If not provided and in updater mode, attempts to reuse GUID from inactive customers or an existing customer. If still empty, a new GUID is generated. |
CustomerCode | Customer/customerCode | Mandatory. Used as a unique identifier. Leading tilde (~) in customer code is handled for reactivation. |
CustomerName | Customer/customerName | Mandatory. |
ChamberOfCommerceCode | Customer/chamberOfCommerceCode | |
VatCode | Customer/vatCode | Set to null if the administration is configured for USA. |
VatLiable | Customer/isVatLiable | Boolean value. Set to false if the administration is configured for USA. |
Discount | Customer/discount | Parsed as a decimal value. |
UsesPriceField | Customer/usesPrice | Integer value. Represents the default pricelist to use. Migrated if price deduplication is enabled and a mapping exists; otherwise, defaults to 1 if no existing pricelist. |
ActionPriceList | Customer/actionPriceList | Integer value. Represents an action pricelist. If not provided and `DefaultActionPriceListCode` setting is configured, it will be set from that setting. Migrated if price deduplication is enabled and a mapping exists; otherwise, set to null if no existing pricelist. |
HidePrices | Customer/hidePrices | Boolean value. |
CustomerManager | Customer/customerManager | |
Sysmodified | Customer/lastmodified (or customer.lastmodified) | Timestamp truncated to seconds. |
Created | Defaults to current timestamp if not provided. Truncated to seconds. | |
CustomerClassification | Customer/customerClassification | |
ItemFilter | Customer/fixedItemFilter (or ItemFilter_ prefixed fields in extra data) | Can be sourced directly or derived from `ItemFilter_` prefixed fields in extra customer data (see 'Extra Data & Free Fields' section). If the sync is not from the updater and an existing customer has an `ItemFilter`, it will retain the existing filter if the new one is empty. |
CustomerNote | Customer/customerNote | If present, existing notes for the customer are deleted and a new note is inserted with "Backoffice note" as subject. |
PaymentConditionCode | Customer/paymentCondition | If empty, set to null to avoid foreign key issues. Distinct payment conditions are inserted/updated in the system. |
DeliveryMethod | Customer/termsOfDelivery (or customers.deliverymethod) | |
Filters | Customer/filters | |
LanguageCode | Customer/languageCode | Mapped via `MappedLanguages` configuration. If syncing from the App4Sales app and the existing customer has a language code, it retains the existing one if the new one is empty. |
Trimmed for whitespace. | ||
PasswordWebshop | Derived (from main contact person) | Set to null if in updater mode. Inherited from the main contact person if customer's `PasswordWebshop` is empty. |
InternalCode | If syncing from App4Sales app and existing customer has an `InternalCode`, it retains the existing one if the new one is empty. | |
ExtraData | If syncing from App4Sales app and existing customer has `ExtraData`, it retains the existing one if the new one is empty. |
Data Mapping Table - Addresses
App4Sales Field | Source Field (XML Node/Attribute) | Logic/Notes |
AddressType | Derived | Explicitly created for 'inv' (invoice), 'del' (delivery), 'vis' (visit). If not specified, defaults to 'Visit'. If only one of 'Visit' or 'Delivery' is present, the other is created as a copy. |
AddressLine1 | customers.invoiceAddress1, customers.visitAddress1, customers.deliveryAddress1 | Parsed into Street, HouseNumber, and Addition using `AddressUtils.ParseAddress`. |
AddressLine2 | customers.invoiceAddress2, customers.visitAddress2, customers.deliveryAddress2 | |
PostCode | customers.invoicePostcode, customers.visitPostcode, customers.deliveryPostcode | |
City | customers.invoiceCity, customers.visitCity, customers.deliveryCity | |
customers.contactEmail | Applied to all address types. Trimmed for whitespace. Cleans Unicode unit separator. If an address email is empty and it's a main address, it inherits from customer email or another address/contact person. | |
Phone | customers.contactPhone | Applied to all address types. |
Fax | customers.contactFax | Applied to all address types. |
Country | customers.languageCode, customers.countryCode | Inherited from `customer.LanguageCode` or `customer.CountryCode`. If `Iso2` is empty, derived from `Country`. |
Iso2 | Derived from Country | Mapped via `MappedCountries` configuration. Trimmed for whitespace. If empty, derived from `Country`. |
CustomerGuid | Derived (from Customer) | Inherited from the parent customer. |
IsMainAddress | Derived | Ensured that at least one `Visit` and one `Delivery` address are marked as main if they exist. |
Data Mapping Table - Contact Persons
App4Sales Field | Source Field (XML Node/Attribute) | Logic/Notes |
CustomerGuid | Derived (from Customer) | Inherited from the parent customer. |
FullName | Customer/contactFullName (or derived from FirstName, MiddleName, LastName) | Populated from `contactFullName` or composed from individual name fields. |
FirstName | contactpersons/contactperson/FirstName | Derived from `FullName` if individual name fields are empty. |
MiddleName | contactpersons/contactperson/MiddleName | Derived from `FullName` if individual name fields are empty. |
LastName | contactpersons/contactperson/LastName | Derived from `FullName` if individual name fields are empty. |
customers.contactEmail (or contactpersons/contactperson/Email) | Applied from `customers.contactEmail` to `MainContactPerson`. Trimmed for whitespace. | |
Phonenumber | customers.contactPhone (or contactpersons/contactperson/Phonenumber) | Applied from `customers.contactPhone` to `MainContactPerson`. |
IsMainContactPerson | Derived | `MainContactPerson` from XML is marked as main. If multiple contact persons exist and no main contact is explicitly defined, the first one is marked as main. |
ContactId | Derived | Generated as `GetHashCode().ToString()` if empty and in updater mode. |
PasswordWebshop | contactpersons/contactperson/PasswordWebshop | Set to null if in updater mode. |
Data Mapping Table - Item Class Value Discounts
App4Sales Field | Source Field (XML Node/Attribute) | Logic/Notes |
CustomerGuid | Derived (from Customer) | Inherited from the parent customer. |
discount | DiscountsPerItemCLassValue (nested XML) | Only discounts with a value greater than 0 are processed. |
Special Logic & Filters
Invalid Customer Handling: Customers with empty `CustomerName` or `CustomerCode` are filtered out and logged as warnings.
Batch Processing: Customers are processed in batches of 100 to optimize performance and resource usage.
Customer Reactivation: If `UpdaterHelper.IsUpdater` is true, the system attempts to reactivate previously soft-deleted customers (those with a `~` prefix in their code) by reusing their existing GUIDs.
Customer GUID Resolution:
Attempts to find an existing `CustomerGuid` in a local cache (`_customerCodeLinks`).
If not found, queries the `PortalServerProvider` for the `CustomerGuid` based on `CustomerCode` and `CustomerName`.
If still no `CustomerGuid` is found, a new `Guid` is generated for the customer.
Timestamp Truncation: `Created` and `Sysmodified` timestamps are truncated to the second to avoid SQL execution timeouts.
Webshop Password Clearing: If `UpdaterHelper.IsUpdater` is true, `PasswordWebshop` fields for both the customer and contact persons are set to null.
Customer Dashboard Processing: Customer dashboards (Base64 encoded) are extracted, converted to byte arrays, and stored separately via `_customerDataManager`. The `CustomerDashboard` field on the customer object is then cleared.
Payment Condition Nulling: Empty `PaymentConditionCode` values are converted to null to prevent foreign key errors in the database.
Email Trimming and Cleaning: All customer and contact person email addresses are trimmed for whitespace. Unicode unit separators (char 0x001F) are removed from email addresses during address normalization.
Dynamic Free Fields Conversion: `DynamicFreeFields` for both customers and contact persons are deserialized and then converted using `ConvertToExtraFields` against predefined custom extra fields. This allows for dynamic and flexible storage of additional data.
Contact Person Name Standardization: The `FillContactName` method ensures that `FullName`, `FirstName`, `MiddleName`, `LastName`, and `Initials` are consistently populated based on available name components.
Contact Person ID Generation (Updater Mode): If `UpdaterHelper.IsUpdater` is true and a contact person's `ContactId` is empty, a new `ContactId` is generated using the hash code of the contact person object.
Customer Note Handling: Existing customer notes (from "BackOffice" salesrep) are deleted and recreated if a `CustomerNote` is provided in the incoming XML.
App4Sales App Data Preservation: If the update originates from the App4Sales application (not the updater), and the incoming `InternalCode`, `ExtraData`, or `LanguageCode` are empty, these fields will retain their existing values from the database.
USA VAT Configuration: For administrations configured for the USA, `VatCode` is set to null and `VatLiable` is set to false.
Invalid XML Character Removal: Invalid XML characters are removed from customer and contact person data before saving.
Script Hooks:
`BeforeUpdateCustomers`: Executed before customer processing begins.
`UpdateCustomers`: Executed after each batch of customers is processed, and again after all customers are processed for finalization.
Domain Specifics
Customer Core Fields
The core customer record is updated with essential details such as `CustomerCode`, `CustomerName`, and identifiers like `CustomerGuid`. Pricing-related fields like `UsesPriceField` (default price list) and `ActionPriceList` are integrated, with special handling for price deduplication migrations. VAT information is handled based on the administration's country setting (e.g., cleared for USA). Payment terms and delivery methods are also updated. The `LanguageCode` is mapped to internal App4Sales language codes.
Addresses
The connector ensures a comprehensive set of addresses (Invoice, Delivery, Visit) are maintained for each customer. It performs significant normalization:
Empty addresses are filtered unless they have an `ExternalId`.
If no valid addresses are found, a dummy `Visit` address is created to store the `Iso2` code.
If only a `Visit` or `Delivery` address is present, the missing type is automatically generated as a copy to ensure both exist.
Address lines (`AddressLine1`) are parsed to extract street, house number, and addition.
Email addresses are cleaned and inherited across addresses or from the customer/contact persons if not explicitly provided.
Country codes (`Iso2`) are mapped and derived from country names, and vice-versa, ensuring consistency.
At least one `Visit` and one `Delivery` address are designated as `IsMainAddress`.
Contacts
Contact persons are linked to customers. The system determines a main contact person based on the `contactFullName` or existing `IsMainContactPerson` flag. If no main contact is explicitly set, the first contact in the list becomes the main one. Contact IDs can be generated in updater mode if missing. Dynamic free fields are supported for contact persons, allowing for flexible additional data storage.
Extra Data & Free Fields
The system handles extra data for customers, potentially coming from CSV sources (`CsvCustomer`). This extra data can overwrite standard customer fields. Additionally, it supports `FreeField_` prefixed fields within the extra data to create `CustomerFreeField` objects, which are then serialized to XML. `ItemFilter_` prefixed fields are used to build dynamic item filters based on item classes and their values, utilizing the `itemClassMap` for validation and lookup.
FTP/CSV Overrides: Properties in `CsvCustomer` can directly overwrite corresponding properties in the `Customer` object if they have a value.
`FreeField_` Prefix: Any `UnknownElements` in the extra data starting with `FreeField_` (case-insensitive) are treated as free fields. The suffix after `FreeField_` becomes the `Caption`, and the value becomes the `Content`.
`ItemFilter_` Prefix: Any `UnknownElements` in the extra data starting with `ItemFilter_` (case-insensitive) are processed as fixed item filters. The suffix after `ItemFilter_` is matched against `ItemClass` names. The value (split by `~`) is matched against `ItemClassValue`s. This constructs a query string for `customer.ItemFilter`.
DynamicFreeFields: These are XML payloads within the customer or contact person object that are deserialized and converted into a standard format based on custom extra field definitions.
Discounts & Dashboards
Item Class Value Discounts: Discounts per item class value are processed, linked to the customer, and only applied if the discount value is greater than zero. These are updated in batches.
Customer Dashboards: Base64-encoded customer dashboard layouts are extracted from the customer data, decoded, and stored separately via the `CustomerDataManager`. This allows for custom dashboard configurations per customer.
Customer Notes: Existing customer notes (specifically those created by the "BackOffice" salesrep) are replaced with any new `CustomerNote` provided in the update.
Related Settings & Prerequisites
FtpHostName, FtpPort, FtpUsername, FtpPassword, FtpActiveConnection, FtpDataEncryptionMethod, UseSftp, FtpRootDirectory: FTP connection settings.
DefaultActionPriceListCode: Defines a default action price list to be applied to customers if they do not have one specified.
EnablePriceDeduplication: (ConnectorBaseSettings) If enabled, allows for the migration of price list IDs to deduplicated versions. Requires `KeySettings.GenericPriceListMigrations` to be configured.
CustomerItemFilter: (SyncSettings) If enabled, allows processing of `ItemFilter_` prefixed extra data for fixed item filters and ensures existing item filters are not overwritten by empty incoming data.
Updater Mode: (`UpdaterHelper.IsUpdater`) Influences GUID resolution, password clearing, and contact ID generation.
MappedLanguages: Configuration for mapping external language codes to internal App4Sales language codes.
MappedCountries: Configuration for mapping external country codes to internal App4Sales country codes (ISO2).
Administration.IsUSA: A flag that, if true, clears VAT information (VatCode and VatLiable) for customers.
Script Hooks: JavaScript files (`BeforeUpdateCustomers.js`, `UpdateCustomers.js`) can be used to inject custom logic before and after customer processing.