QuickBooksOnline - Customer Update

This function synchronizes customer data from QuickBooks Online to the App4Sales platform. It processes core customer information, associated addresses, contact persons, item class discounts, and customer-specific dashboards. The update runs in batches and incorporates various data transformations, validations, and conditional logic to ensure data integrity and consistency within App4Sales.

Data Source Configuration

Customer data is primarily received as a list of Customer objects from the QuickBooks Online connector. This data is typically pre-processed by the connector before being passed to this update handler.

Additionally, supplementary customer data (ExtraCustomerData) can be loaded, potentially from CSV files or a similar flat-file source, which provides extra fields that can override or extend the main customer properties.

Data Mapping Table: Customer

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
CustomerCode	Customer.CustomerCode	Mandatory. Used as a unique identifier.
CustomerName	Customer.CustomerName	Mandatory. Display name for the customer.
CustomerGuid	Customer.CustomerGuid	Unique identifier for the customer within App4Sales. If `UpdaterHelper.IsUpdater` is true, attempts to retrieve from a local cache (`_customerCodeLinks` or `_customerCodeGuidReactivationLinks`). Otherwise, attempts to retrieve from Portal API using `CustomerCode` and `CustomerName`. If still empty, a new GUID is generated. Reactivates previously deleted/inactive customers by reusing their GUID based on `~` prefixed codes.
Created	Customer.Created	Timestamp of customer creation. If the value is `DateTime.MinValue`, it defaults to `DateTime.Now`. Truncated to seconds (milliseconds removed) to prevent SQL execution timeout.
Sysmodified	Customer.Sysmodified	Timestamp of last modification. Truncated to seconds (milliseconds removed).
PasswordWebshop	Customer.PasswordWebshop	Set to `null` if `UpdaterHelper.IsUpdater` is true.
CustomerDashboard	Customer.CustomerDashboard	Base64 encoded dashboard layout. If not empty or whitespace, it's decoded and stored separately via `_customerDataManager.SaveDashboards`. The `CustomerDashboard` field on the customer object is then set to `null`.
LanguageCode	Customer.LanguageCode	Mapped language code. If not empty, the value is mapped using `SetCustomerLanguageCode` based on `MappedLanguages`. The `LanguageCodeErp` from the source is mapped to `LanguageMapped` in App4Sales.
ActionPriceList	Customer.ActionPriceList	ID of the action price list to apply. If empty and `ConnectorBaseSettings.DefaultActionPriceListCode` is configured, it defaults to the price list ID associated with that code. If `EnablePriceDeduplication` is active, the value might be migrated to a deduplicated price list ID. If the migrated ID does not exist, it defaults to 1.
PaymentConditionCode	Customer.PaymentConditionCode	Set to `null` if the source value is an empty string to prevent foreign key problems.
Email	Customer.Email	Trimmed for whitespace.
DynamicFreeFields	Customer.DynamicFreeFields	XML payload containing extra ERP data. Deserialized from XML string (`<extraFields />` structure) into `DynamicFreeFields` object. Converted to App4Sales `ExtraFields` using configured custom extra fields.
VatCode	Customer.VatCode	Set to `null` if `Administration.IsUSA` is true.
VatLiable	Customer.VatLiable	Set to `false` if `Administration.IsUSA` is true.
FreeFields	Derived / CsvCustomer.UnknownElements	XML representation of custom free fields. If `FreeFields` is empty and `FreeFieldList` (a list of `CustomerFreeField` objects) is not empty, then `FreeFieldList` is serialized to XML and stored in `FreeFields`. Can also be populated from `ExtraCustomerData.UnknownElements` where keys start with 'FreeField_'.
ItemFilter	Derived / CsvCustomer.UnknownElements	Defines a fixed item filter for the customer. If `SyncSettings.CustomerItemFilter` is enabled: Reads existing `ItemFilter` from the database to prevent loss during updates from apps. Can be processed from `ExtraCustomerData.UnknownElements` where keys start with 'ItemFilter_'. Values are split by `~` and mapped against `ItemClassMap`.
CustomerNote	Customer.CustomerNote	Customer-specific note. If not empty, existing notes from 'BackOffice' are deleted from `CustomerNotes` table. A new note is inserted into the `CustomerNotes` table with `salesrep` as 'BackOffice', `subject` as 'Backoffice note', and the provided `message`.
InternalCode	Customer.InternalCode	ERP-specific internal code. If `UpdaterHelper.IsUpdater` is false (i.e., not a sync from Updater), and the App4Sales app provides an empty `InternalCode`, the existing `InternalCode` from the database is retained.
ExtraData	Customer.ExtraData	Additional extra data for the customer. If `UpdaterHelper.IsUpdater` is false and the App4Sales app provides empty `ExtraData`, the existing `ExtraData` from the database is retained.

Data Mapping Table: Address

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
CustomerGuid	Customer.CustomerGuid	Inherited from the parent Customer.
AddressId	Address.AddressId	If the source `AddressId` is empty, a default ID is generated using `SetIdAsDefault()`.
Iso2	Address.Iso2 / Address.Country	Two-letter ISO country code. If a mapping exists in `MappedCountries` for the trimmed and lowercased source `Iso2`, the mapped country code is used. Otherwise, the source `Iso2` is trimmed. If `Iso2` is still empty, it attempts to derive it from the `Country` field using `CultureHelper.GetIso2FromCountry`.
Country	Address.Country / Address.Iso2	Country name. If `Country` is empty, it defaults to the value of `Iso2`.
Email	Address.Email / Customer.Email / ContactPerson.Email	Email address for the address. Unicode unit separators (`\u001F`) are removed. If empty and `IsMainAddress` is true, it attempts to inherit: first from the parent `Customer.Email`, then from other addresses associated with the customer, and finally from any contact person's email.
AddressType	Address.AddressType	Type of address (e.g., Visit, Delivery, Invoice). Defaults to `Visit` if the source `AddressType` is empty. If only a `Visit` or `Delivery` address is provided, the missing type is created as a copy. If other address types are present but `Visit` or `Delivery` are missing, they are created from an existing address.
AddressLine1	Address.AddressLine1 / AddressLine.Street, AddressLine.Number, AddressLine.Addition	Full address line. If `AddressLine` object is null, `AddressLine1` is parsed to extract street, house number, and addition. Otherwise, `AddressLine1` is formatted from `AddressLine.Street`, `AddressLine.Number`, and `AddressLine.Addition`.
Street	AddressLine.Street (derived from Address.AddressLine1)	Extracted from the `AddressLine` object or parsed from `AddressLine1`.
HouseNumber	AddressLine.Number (derived from Address.AddressLine1)	Extracted from the `AddressLine` object or parsed from `AddressLine1`.
Addition	AddressLine.Addition (derived from Address.AddressLine1)	Extracted from the `AddressLine` object or parsed from `AddressLine1`.
IsMainAddress	Address.IsMainAddress	For `Visit` and `Delivery` address types, if no main address is specified, the first address of that type is designated as the main address.

Data Mapping Table: Contact Person

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
CustomerGuid	Customer.CustomerGuid	Inherited from the parent Customer.
ContactId	ContactPerson.ContactId	If `UpdaterHelper.IsUpdater` is true and `ContactId` is empty, a hash code of the contact person object is used as a unique identifier.
PasswordWebshop	ContactPerson.PasswordWebshop	Set to `null` if `UpdaterHelper.IsUpdater` is true.
Email	ContactPerson.Email	Trimmed for whitespace.
DynamicFreeFields	ContactPerson.DynamicFreeFields	XML payload containing extra ERP data. Deserialized from XML string into `DynamicFreeFields` object. Converted to App4Sales `ExtraFields` using configured custom extra fields for contact persons. Invalid XML characters are removed.
FullName	ContactPerson.FullName / ContactPerson.FirstName, ContactPerson.MiddleName, ContactPerson.LastName	Full name of the contact person. If `FirstName`, `LastName`, and `MiddleName` are null, and `FullName` is provided, `FullName` is split to populate these fields. If `FullName` is null, it is constructed by concatenating `FirstName`, `MiddleName`, and `LastName`.
FirstName	ContactPerson.FirstName	Populated from `FullName` if not explicitly provided.
MiddleName	ContactPerson.MiddleName	Populated from `FullName` if not explicitly provided.
LastName	ContactPerson.LastName	Populated from `FullName` if not explicitly provided.
IsMainContactPerson	ContactPerson.IsMainContactPerson / Customer.MainContactPerson	Indicates if this is the main contact for the customer. If `Customer.MainContactPerson` is provided, that contact is marked as main. If not present in `Customer.ContactPersons`, it is added. If no contact is explicitly marked as main, the first contact in the list is designated as the main contact.
Initials	Derived from ContactPerson.FirstName	Derived from the first character of the `FirstName`.

Data Mapping Table: Item Class Value Discount

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
CustomerGuid	Customer.CustomerGuid	Inherited from the parent Customer.
discount	ItemClassValueDiscount.discount	Only discounts with a value greater than 0 are processed and stored.

Data Mapping Table: Dashboard

App4Sales Field	Source Field (API/Excel/DB)	Logic/Notes
CustomerCode	Customer.CustomerCode	Identifies the customer to whom the dashboard belongs.
DashboardData	Customer.CustomerDashboard	Base64 decoded content of the customer dashboard.

Special Logic & Filters

Input Validation & Filtering:
- Customers with empty or whitespace CustomerName or empty CustomerCode are considered invalid, logged, and skipped from the update process.
Batch Processing:
- Customer records are processed in batches of 100 to optimize database operations.
Customer GUID & Reactivation:
- For updater instances (UpdaterHelper.IsUpdater is true), the system attempts to retrieve existing CustomerGuid values from an in-memory cache (_customerCodeLinks and _customerCodeGuidReactivationLinks).
- This includes logic to reactivate previously inactive customers by reusing their GUID, identified by customer codes starting with ~.
- If no GUID is found in the cache, it queries the Portal API. If still not found, a new GUID is generated.
Script Hooks:
- BeforeUpdateCustomers: A script is executed before processing customer batches, allowing for pre-processing of customer data.
- UpdateCustomers: A script is executed after processing customer batches, allowing for post-processing or additional updates.
Price Deduplication:
- If EnablePriceDeduplication is active (either in ConnectorBaseSettings or connector-specific settings), customer UsesPriceField and ActionPriceList values are migrated to deduplicated price list IDs based on KeySettings.GenericPriceListMigrations.
- If a migrated price list ID does not exist, UsesPriceField defaults to 1 and ActionPriceList is set to null.
Address Processing:
- Invalid addresses (empty and no ExternalId) are filtered out.
- If a customer has no valid addresses but an original ISO2 country code was provided, a dummy 'Visit' address is created to store the ISO2.
- Logic ensures that both 'Visit' and 'Delivery' address types exist for a customer, creating a copy of an existing address if one is missing.
- Addresses are normalized (e.g., email cleansing, ISO2 mapping, default address types).
Customer Note Handling:
- If CustomerNote is provided, any existing 'BackOffice' notes for that customer are deleted from the CustomerNotes table. A new note is then inserted with 'BackOffice' as the sales representative.
USA VAT Logic:
- For administrations configured as USA (Administration.IsUSA is true), VatCode is set to null and VatLiable is set to false.
Extra Data & Free Fields Processing:
- If ExtraCustomerData is available, properties are mapped to the Customer object based on matching property names.
- Unknown elements from ExtraCustomerData with keys starting with 'FreeField_' are converted into CustomerFreeField objects and added to the FreeFieldList.
- Fixed item filters (keys starting with 'ItemFilter_' in ExtraCustomerData.UnknownElements) are processed and converted into the ItemFilter string if SyncSettings.CustomerItemFilter is enabled.
Invalid XML Characters:
- Invalid XML characters are removed from Customer and ContactPerson dynamic free fields before processing.

Domain Specifics / Extension Section

Customer Core Fields

The customer core fields, such as CustomerCode and CustomerName, are fundamental for identifying and displaying customer records in App4Sales. The system enforces their presence, skipping any customer records lacking these essential identifiers. Customer GUIDs are managed to ensure continuity, even for previously inactive customers, enabling the retention of historical data like customer notes and order links. Price list assignments, both standard (UsesPriceField) and action-specific (ActionPriceList), are handled with consideration for deduplication settings and default configurations. Payment conditions are sanitized to prevent database inconsistencies, and VAT settings are automatically adjusted for USA-based administrations.

Addresses

Address processing is robust, ensuring data quality and completeness. The system filters out incomplete addresses unless an external identifier is present. It proactively creates dummy addresses for ISO2 codes if no valid addresses exist and ensures that every customer has both a 'Visit' and 'Delivery' address type, even if it means duplicating an existing address. Address data undergoes normalization, including email cleansing and country code mapping, to maintain consistency across the platform. The primary 'Visit' and 'Delivery' addresses are automatically identified if not explicitly marked.

Contacts

Contact persons associated with customers are managed with attention to detail. Contact identifiers are generated for updater instances if not provided. Email addresses are trimmed, and full names are intelligently parsed or constructed from individual name components. The system ensures a main contact person is designated for each customer, either by explicit source indication or by selecting the first available contact. Dynamic free fields for contacts are processed and invalid XML characters removed, allowing for flexible extension of contact information.

Extra Data & Free Fields

The connector supports extensive customization through extra data and free fields. Supplementary customer data, often from flat files, can directly override or extend standard customer properties. Unstructured 'UnknownElements' from this extra data are intelligently converted into App4Sales free fields based on specific naming conventions (e.g., 'FreeField_'). Similarly, fixed item filters for customers can be defined within this extra data, enabling granular control over product visibility based on item classes, provided the 'CustomerItemFilter' setting is enabled.

Discounts & Dashboards

Customer-specific item class value discounts are processed, with only positive discount values being stored. This allows for precise control over pricing based on product classifications. Customer dashboards, provided as Base64 encoded strings, are extracted, decoded, and stored separately to allow for personalized user interfaces within the App4Sales platform. Any existing customer notes from the 'BackOffice' are first cleared and then recreated based on the incoming data, ensuring the most up-to-date information is presented.

Related Settings & Prerequisites

Default Action Price List Code: Defines a default action price list to be applied to customers if they do not have one specified. Configurable via ConnectorBaseSettings.
Enable Price Deduplication: When enabled, customer price list assignments (standard and action price lists) are migrated to deduplicated versions based on system-wide price list migration settings. Configurable via ConnectorBaseSettings or connector-specific settings.
Customer Item Filter: A setting that enables or disables the processing of fixed item filters from customer extra data. Configurable via SyncSettings.
Administration is USA: A system-wide setting that influences VAT handling for customers. If true, customer VatCode and VatLiable fields are set to null and false respectively. Configurable via AdministrationSession.CurrentSession.Administration.
Mapped Languages: Requires configuration of language code mappings between the ERP system and App4Sales.
Mapped Countries: Requires configuration of country code mappings, influencing address normalization.
Custom Extra Fields: Configuration of custom extra fields for both customer and contact person entities, allowing for mapping of dynamic ERP data.
Scripting Service: The system utilizes a scripting service to execute custom logic via BeforeUpdateCustomers and UpdateCustomers scripts, enabling flexible pre- and post-processing of customer data.

Known Limitations

No explicit limitations were observed in the CustomerUpdateHandler.cs code that would directly impact the functional scope for Application Administrators and Implementation Consultants.

QuickBooksOnline - Customer sync