Skip to main content

DbFact - Customer sync

DbFact Connector - CustomerUpdate The CustomerUpdate pipeline for the DbFact connector synchronizes customer-related data from the e...

Updated over a week ago

DbFact Connector - CustomerUpdate

The CustomerUpdate pipeline for the DbFact connector synchronizes customer-related data from the external DbFact ERP system into the App4Sales platform. This process involves retrieving core customer information, associated addresses, contact persons, item class discounts, and customer dashboards. The data is retrieved via various DbFact API endpoints and transformed before being stored in the App4Sales internal database.

When does this sync occur?

This synchronization occurs when the DbFact connector's CustomerUpdate function is executed.

What entities does it touch?

  • Customers (core account records)

  • Addresses (Visit, Delivery, Invoice, or custom types)

  • ContactPersons (individuals linked to the customer)

  • Customer Dashboards (Base64-encoded dashboard layouts)

  • ItemClassValueDiscounts (per-item-class percentage discounts)

  • Customer Notes

  • Mapped Languages

Data Mapping Table (Customer Core Fields)

App4Sales Field

Source Field (API/Excel/DB)

Logic/Notes

CustomerCode

Relation.Code (from DbFact RelExportDossier)

Directly mapped from DbFact. Used for identifying customers.

CustomerGuid

Relation.Guid (from DbFact RelExportDossier) or Internal App4Sales lookup

Attempted to be retrieved from an existing inactive customer (if UpdaterHelper.IsUpdater and _customerCodeGuidReactivationLinks contains it). If not found in reactivation links, it's looked up in a local cache (_customerCodeLinks) by CustomerCode. If still not found, it's retrieved from PortalServerProvider.GetCustomerGuid using CustomerCode and CustomerName. If still empty, a new Guid.NewGuid() is generated. New GUIDs are added to _customerCodeLinks if UpdaterHelper.IsUpdater.

CustomerName

Relation.Name (from DbFact RelExportDossier)

Converted to uppercase (SafeToUpper()). Invalid customers (empty name or code) are logged and skipped.

Created

DateTime.Now (default) or source value

If the source Created date is DateTime.MinValue (default value), it is set to the current date and time (DateTime.Now). Truncated to seconds to avoid SQL timeout issues.

Sysmodified

Source value

Truncated to seconds to avoid SQL timeout issues.

PasswordWebshop

null

Always set to null if UpdaterHelper.IsUpdater is true.

CustomerDashboard

Client.DownloadCustomerDashboard(customer.CustomerCode) (Base64 string)

If Settings.EnableCustomerDashboards is true, the dashboard data is downloaded as a Base64 string. If the string is empty or whitespace, it's set to null. If not empty, it's converted from Base64 to bytes and stored separately; the CustomerDashboard field itself is then set to null to save memory after processing.

LanguageCode

Relation.LanguageCode or Mapped Languages

Mapped from the source LanguageCode using configured MappedLanguages. If no mapping is found or the mapped language is empty, the original LanguageCode is retained. If empty in the source and the customer already exists, the existing customer's LanguageCode is used.

ActionPriceList

DefaultActionPriceListCode setting or source value

If DefaultActionPriceListCode is set in connector settings and no ActionPriceList is provided by the source, the price list ID corresponding to DefaultActionPriceListCode is used. If price deduplication is enabled, the ActionPriceList is migrated to a deduplicated version if a mapping exists. If the migrated price list does not exist, it is set to null.

UsesPriceField

DbFactPriceListXml.PriceListId or UsesPrice function

If price lists are downloaded, DbFactPriceListXml.PriceListId is used based on customer.CustomerCode. Otherwise, resolved using the UsesPrice function based on Enum.GetName(typeof(ArticlePriceEnum), relation.PriceListId). If price deduplication is enabled, the UsesPriceField is migrated to a deduplicated version if a mapping exists. If the migrated price list does not exist, it defaults to 1.

PaymentConditionCode

Relation.PaymentConditionCode

If the source PaymentConditionCode is an empty string, it is set to null to prevent foreign key problems.

Email

Source email

Whitespace is trimmed from the email address.

DynamicFreeFields

Source XML payload for dynamic free fields

If the source DynamicFreeFields is not empty, does not contain </extraFields>, and is not <extraFields />, it is deserialized into DynamicFreeFields object. Then, it's converted to a standardized extra fields format using ConvertToExtraFields based on customerExtraFields.

VatCode

Source VAT Code

Set to null if AdministrationSession.CurrentSession.Administration.IsUSA is true.

VatLiable

Source VAT Liable status

Set to false if AdministrationSession.CurrentSession.Administration.IsUSA is true.

InternalCode

Existing Customer's InternalCode

If the source InternalCode is empty and the customer already exists, the existing customer's InternalCode is retained.

ExtraData

Existing Customer's ExtraData

If the source ExtraData is empty and the customer already exists, the existing customer's ExtraData is retained.

FreeFieldList

Derived from extraCustomerData.UnknownElements

Populated if extraCustomerData.UnknownElements contains keys prefixed with FreeField_. The prefix is removed to form the Caption, and the value becomes the Content.

FreeFields

FreeFieldList

If FreeFieldList is not empty, it is serialized to XML and stored in FreeFields.

ItemFilter

Derived from extraCustomerData.UnknownElements or existing customer's ItemFilter

If SyncSettings.CustomerItemFilter is enabled, ItemFilter can be processed from extraCustomerData.UnknownElements where keys are prefixed with ItemFilter_. These are then mapped to ItemClass and ItemClassValues to form a query string. If the update comes from the app (not an updater), the existing customer's ItemFilter is retained to prevent loss.

Data Mapping Table (Addresses)

App4Sales Field

Source Field (API/Excel/DB)

Logic/Notes

CustomerGuid

Parent Customer's CustomerGuid

Addresses are linked to the parent customer using its GUID.

ExternalId

Address.ExternalId

Addresses are only considered valid if they have an ExternalId or are not empty.

AddressType

Address.AddressType or Default

If no valid addresses exist but an iso2Backup is present, a dummy Visit address is created. If only one of Visit or Delivery address types is present, the other is created as a shallow copy. If neither Visit nor Delivery exists but other address types are present, both Visit and Delivery addresses are created from a shallow copy of an existing address (preferring IsMainAddress). Defaults to Visit if AddressType is empty after normalization.

Iso2

Address.Iso2, MappedCountryCodes, or Derived from Country

If Iso2 is present, it's trimmed. If a mapping exists in MappedCountryCodes (from _mappedCountriesContext.All()), it uses the mapped country code. If Iso2 is empty, it attempts to derive it from the Country using CultureHelper.GetIso2FromCountry.

Email

Address.Email, Parent Customer Email, or Contact Person Email

Unicode unit separators (\u001F) are removed from the email address. If Address.Email is empty and IsMainAddress is true:

  • It first attempts to use the parent customer.Email.

  • If still empty, it searches for a non-empty email among other customer addresses.

  • If still empty, it searches for a non-empty email among the customer's contact persons.

Whitespace is trimmed from the final email address.

AddressId

Address.AddressId or Generated

If AddressId is empty, a default ID is generated using SetIdAsDefault().

Country

Address.Country or Derived from Iso2

If Country is empty, it uses the Iso2 value.

Street

Address.AddressLine.Street or Parsed from AddressLine1

If AddressLine is null, the street is parsed from AddressLine1 using AddressUtils.ParseAddress.

HouseNumber

Address.AddressLine.Number or Parsed from AddressLine1

If AddressLine is null, the house number is parsed from AddressLine1 using AddressUtils.ParseAddress.

Addition

Address.AddressLine.Addition or Parsed from AddressLine1

If AddressLine is null, the addition is parsed from AddressLine1 using AddressUtils.ParseAddress.

AddressLine1

Formatted from AddressLine.Street, Number, Addition

If AddressLine is not null, AddressLine1 is formatted using AddressUtils.FormatAddressLine, considering if the administration is for the USA.

IsMainAddress

Derived or Defaulted

If no Visit addresses have IsMainAddress set, the first Visit address will have it set to true. If no Delivery addresses have IsMainAddress set, the first Delivery address will have it set to true.

Data Mapping Table (Contact Persons)

App4Sales Field

Source Field (API/Excel/DB)

Logic/Notes

CustomerGuid

Parent Customer's CustomerGuid

Contact persons are linked to the parent customer using its GUID.

Email

Source email

Whitespace is trimmed from the email address if not empty.

DynamicFreeFields

Source XML payload for dynamic free fields

If the source DynamicFreeFields is not empty and not an empty XML tag, it is deserialized into a DynamicFreeFields object. Then, it's converted to a standardized extra fields format using ConvertToExtraFields based on contactPersonExtraFields.

FullName

Derived from FirstName, MiddleName, LastName, or parsed from source FullName

If FirstName, LastName, and MiddleName are all null, but FullName is provided, it attempts to parse FirstName, MiddleName, and LastName from the FullName. If FullName is null, it constructs FullName from FirstName, MiddleName, and LastName.

FirstName

Derived from FullName or Source FirstName

Populated from the source FirstName or parsed from FullName if not provided.

LastName

Derived from FullName or Source LastName

Populated from the source LastName or parsed from FullName if not provided.

MiddleName

Derived from FullName or Source MiddleName

Populated from the source MiddleName or parsed from FullName if not provided.

Initials

Derived from FirstName

Set to the first character of the FirstName.

ContactId

Source ContactId or Generated Hash Code

If UpdaterHelper.IsUpdater is true and ContactId is empty, a ContactId is generated from the contact person's hash code (person.GetHashCode().ToString()).

IsMainContactPerson

Source value or Derived

If the customer has a MainContactPerson set, that contact person's IsMainContactPerson is set to true. If no MainContactPerson is explicitly set but contact persons exist, the first contact person in the list will have IsMainContactPerson set to true.

PasswordWebshop

null

Always set to null for all contact persons if UpdaterHelper.IsUpdater is true.

Data Mapping Table (ItemClassValueDiscount)

App4Sales Field

Source Field (API/Excel/DB)

Logic/Notes

CustomerGuid

Parent Customer's CustomerGuid

Item class value discounts are linked to the parent customer using its GUID.

discount

ItemClassValueDiscount.discount

Only discounts with a value greater than 0 and not null are processed.

Special Logic & Filters

  • Customer Validation & Filtering

    • Customers with empty or whitespace CustomerName or empty CustomerCode are considered invalid, logged as warnings, and skipped from the update process.

    • SyncCustomerOnType(relation.CustomerType): Customers can be filtered based on their CustomerType if the connector setting CustomerTypes is configured.

  • Identifiers & Reactivation

    • Customer GUID Retrieval: The system prioritizes retrieving an existing CustomerGuid.

      • It first checks for reactivation links (_customerCodeGuidReactivationLinks) for previously inactivated customers.

      • If not found, it checks a local cache (_customerCodeLinks) by CustomerCode.

      • If still not found, it queries the PortalServerProvider for an existing GUID using CustomerCode and CustomerName.

      • If no GUID is found, a new Guid.NewGuid() is generated. New GUIDs for updaters are added to the local cache.

    • Reactivation: Inactivated customers (those with a CustomerCode starting with '~') can be reactivated by reusing their original CustomerGuid.

  • Price Deduplication

    • If AdministrationSession.CurrentSession.ConnectorBaseSettings.EnablePriceDeduplication or the connector's own EnablePriceDeduplication setting is true, price lists are migrated.

    • Existing price list IDs (UsesPriceField and ActionPriceList) are checked against a priceMigrationLookup (deserialized from KeySettings.GenericPriceListMigrations).

    • If a migration entry is found, the price list ID is updated to the MigratedId.

    • If a price list ID is not found in existing price lists after migration, UsesPriceField defaults to 1, and ActionPriceList is set to null.

  • Address Processing (`ProcessAddresses` method)

    • Address Filtering: Only addresses that are not empty or have an ExternalId set are processed.

    • Dummy Address Creation: If no valid addresses exist but an iso2Backup is available, a dummy Visit address is created to store the ISO2 code.

    • Visit/Delivery Address Pair Assurance:

      • If only one of Visit or Delivery address types is present, the missing type is created as a shallow copy of the existing one. The AddressId and ExternalId are reset to avoid duplication.

      • If neither Visit nor Delivery exists but other address types are present, both Visit and Delivery addresses are created as shallow copies from an existing address (preferring the main address). The AddressId and ExternalId are reset.

    • Address Normalization (`NormalizeAddresses` method):

      • Removes Unicode unit separators from email addresses.

      • Normalizes Iso2 codes using MappedCountryCodes if available; otherwise, trims the existing Iso2.

      • Generates a default AddressId if empty.

      • Populates empty email addresses for main addresses from customer or contact person emails.

      • Derives Iso2 from Country, or sets Country to Iso2 if empty.

      • Defaults AddressType to Visit if empty.

      • Parses AddressLine1 into Street, HouseNumber, and Addition if AddressLine is null.

      • Sets the first Visit and Delivery addresses as IsMainAddress = true if no other main address is specified.

  • Contact Person Processing

    • Name Filling (`FillContactName` method): Handles parsing and constructing full names from various combinations of first, middle, and last names.

    • Contact ID Generation: If UpdaterHelper.IsUpdater is true and a contact has no ContactId, a hash code-based ID is generated.

  • Extra Data & Free Fields (`ProcessExtraDataForCustomer` method)

    • Property Overwriting: If extraCustomerData (from e.g., FTP/CSV) is present, it can overwrite standard customer fields if property names match.

    • FreeField Extraction: Elements from extraCustomerData.UnknownElements prefixed with FreeField_ are extracted and added to customer.FreeFieldList.

    • Item Filter Processing: If SyncSettings.CustomerItemFilter is enabled, elements from extraCustomerData.UnknownElements prefixed with ItemFilter_ are processed to create customer-specific item filters.

  • Dashboards & Notes

    • Dashboard Handling: Base64-encoded customer dashboard data is decoded and saved via _customerDataManager.SaveDashboards. After saving, the customer.CustomerDashboard field is cleared for memory optimization.

    • Customer Notes: If customer.CustomerNote is provided, any existing notes for the customer from 'BackOffice' are deleted, and a new note is inserted into the CustomerNotes table.

  • Post-processing Scripts

    • A Jint script named UpdateCustomers can be executed after a batch of customer data has been processed and committed to the database.

  • Payment Conditions

    • Empty string payment conditions are set to null to prevent foreign key errors in the database.

  • VAT Handling

    • If AdministrationSession.CurrentSession.Administration.IsUSA is true, VatCode is set to null and VatLiable is set to false.

Domain Specifics / Extension Section

  • Customer Core Fields

    Refer to the Data Mapping Table (Customer Core Fields) for detailed mappings of identity, price list selection, VAT flags, and payment terms. Invalid customer records (missing name or code) are skipped, and existing inactive customer GUIDs are reused to maintain historical data. Language codes are mapped, and default action price lists can be applied if configured.

  • Addresses

    Refer to the Data Mapping Table (Addresses) for detailed mappings and normalization rules. The system ensures that both Visit and Delivery addresses exist for a customer, creating dummy addresses or copying existing ones if necessary. Email addresses within addresses are cleaned and populated from customer or contact emails if empty.

  • Contacts

    Refer to the Data Mapping Table (Contact Persons) for detailed mappings and logic. Contact names are parsed and constructed. If a ContactId is missing for an updater, a hash-based ID is generated. The main contact person is determined based on source data or by defaulting to the first available contact.

  • Extra Data & Free Fields

    The connector supports the processing of extra customer data, typically from FTP/CSV sources (CsvCustomer).

    • Property Overwriting: Properties in the extra data that match existing customer properties (by name) will overwrite the customer's corresponding field.

    • Free Fields (FreeField_ prefix): Any unknown elements in the extra data with keys starting with FreeField_ are converted into CustomerFreeField objects. The part of the key after the prefix becomes the Caption, and its value becomes the Content. These are then serialized into the customer's FreeFields XML payload.

    • Dynamic Free Fields: Existing XML payloads for dynamic free fields are deserialized, converted to a standardized format, and re-serialized, ensuring compatibility with App4Sales' custom extra fields definitions.

    • Item Filters (ItemFilter_ prefix): If the CustomerItemFilter setting is enabled, elements from extraCustomerData.UnknownElements with keys prefixed ItemFilter_ are used to construct customer-specific item filters. The filter values are split by '~' and matched against predefined ItemClassValues to create a query string.

  • Discounts & Dashboards

    • Item Class Discounts: Discounts per item class value are processed if their discount value is greater than 0. These are linked to the customer's GUID and stored.

    • Customer Dashboards: Base64-encoded dashboard data from DbFact is downloaded, decoded, and saved via the _customerDataManager. The raw dashboard string on the customer object is then cleared.

    • Customer Notes: Notes provided in customer.CustomerNote are handled by deleting any existing 'BackOffice' notes for the customer and then inserting the new note into the CustomerNotes table.

Customers are processed in batches of 25,000 at a time if the CustomerBatch setting is enabled.

Pre-processing Scripts

A Jint script named BeforeUpdateCustomers can be executed before the customer update process begins, allowing for custom pre-processing of customer data.

Data Source Configuration

The DbFact connector retrieves customer-related data through several API calls to the DbFact system. The data is pulled from DbFact and then processed before being updated in App4Sales.

  • Customer Relations: Core customer data is retrieved using the Client.RelExportDossier API call. This call can be batched, retrieving customer records based on numerical ranges (e.g., between(relatie.r_nummer,{begin},{end})).

  • Delivery Addresses: Delivery-specific addresses are fetched via the Client.LadresExportDossier API call, which returns a list of CrsLadres objects.

  • Contact Persons: Contact person information associated with a customer is retrieved using the Client.ContPersExportDossier API, providing details based on the customer code.

  • Customer Dashboards: If enabled, customer dashboard layouts are downloaded using Client.DownloadCustomerDashboard, which returns Base64-encoded dashboard data for a given customer code.

  • Price Lists: Available price lists are obtained through Client.DownloadPriceLists. These are used to map price list IDs from DbFact to App4Sales' internal representation.

Related Settings & Prerequisites

The following settings and prerequisites influence the behavior of the Customer Update pipeline:

  • CustomerBatch: (Connector Setting) A boolean setting that, if enabled, processes customers in batches of 25,000 to optimize performance during large synchronizations.

  • CustomerTypes: (Connector Setting) A semi-colon separated string that specifies which customer types from DbFact should be synchronized. If empty, all customer types are synchronized.

  • EnablePriceDeduplication: (Connector Base Setting or Connector Sync Setting) If true, enables the migration of price lists to deduplicated versions based on mappings in KeySettings.GenericPriceListMigrations.

  • DefaultActionPriceListCode: (Connector Base Setting) If set, this price list code will be used as the ActionPriceList for customers if no action price list is provided by the source.

  • EnableCustomerDashboards: (Connector Setting) If true, the connector will attempt to download and process customer dashboards from DbFact.

  • CustomerItemFilter: (Sync Setting) If true, enables the processing of customer-specific item filters from extra data (e.g., from CSV/FTP sources). If false, any item filter information in extra data will be ignored.

  • FtpFolderForCustomerNotes: (Connector Setting) If not empty, indicates that customer notes should be uploaded via FTP rather than using the default database insertion mechanism. This implies that the Client.UploadCustomerNote method is used.

  • Mapped Languages: (System Configuration) Language codes from DbFact are mapped to App4Sales internal language codes via the configured MappedLanguages in the system. If no mapping is found, the original code is used.

  • Mapped Countries: (System Configuration) ISO2 country codes from DbFact are mapped to App4Sales internal country codes via the configured MappedCountries in the system. If no mapping is found, the original code is used.

  • Administration.IsUSA: (System Configuration) If the administration is configured for the USA, VAT related fields (VatCode, VatLiable) are cleared/set to default values.

  • Scripts:

    • BeforeUpdateCustomers: A Jint script that can be executed before any customer data is processed, allowing for custom transformations or validations on the incoming customer list.

    • UpdateCustomers: A Jint script that can be executed after customer data batches have been processed and updated in the database.

Known Limitations

  • The parsing of full names into first, middle, and last names for contact persons assumes a space-separated format and might not accurately handle all naming conventions.

  • The connector does not store webshop passwords for customers or contact persons when running in Updater mode.

  • Extra data processing (ProcessExtraDataForCustomer) only overwrites existing properties if the property names match exactly (case-insensitive for comparison). Enumerable fields like UnknownElements and FreeCsvFields from the source extra data are not directly mapped as properties.

  • The TempCustomerCodePrefix and related logic for handling temporary customer codes are marked with a TODO in the code, indicating a potential future refactoring. Admins should be aware that temporary customer codes are managed via KeySettings.

  • The current implementation of UpdateCustomerToDatabase always uses new CustomerUpdateHandler().Update(customers); in a loop. While functional, it could potentially be optimized to prevent redundant object instantiations if the handler is stateless across batch calls.

Did this answer your question?