Knowledge BaseField Mapping between SC and NAV

Field Mapping between SC and NAV

Sana Commerce 8.3
Your provider

Order Export

The data which is retrieved from the SQL database and stored in the MS Dynamics NAV database for each entity of the order export process is listed in the tables below.
During the order export the product inventory for the order lines is not checked and calculations for the discounts and prices are not performed.

For more information about the order export read this chapter.

IOrder

Sana Commerce SQL datamodel Sana Commerce property NAV field
'Order'.'UserId' AccountId 'Sales Header'.'Sell-to Customer No.'
'Order'. 'BillToName' BillToName Retrieves from the 'BillingAddress'.'Name'
 - Contact 'Sales Header'.'Sell-to Contact'
 - ContactId 'Sales Header'.'Sell-to Contact No.'
XML field CurrencyId 'Sales Header'.'Currency Code'
If this field is empty the currency will be retrieved from the 'General Ledger Setup' table.
XML field DiscountAmount Not used
 - DocumentDate 'Sales Header'.'Document Date'
 - DocumentType 'Sales Header'.'Document Type'
 - DueDate 'Sales Header'.'Due Date'
XML field InvoiceDiscount Calculated field
 - LocationCode 'Sales Header'.'Location Code'
'Order'. 'DateCreated' OrderDate 'Sales Header'.'Order Date'
'Order'.'Id' OrderId During the order export the original order id is stored in 'Sales Header'.' Sana Order No.'
The new order number is generated and stored in 'Sales Header'.'No.'
The 'OrderLine' table OrderLines Retrieves from the 'Sales Line' table
 - OutstandingAmount Calculated field
XML field PaymentDiscount 'Sales Header'.'Payment Discount %'
 - PaymentDiscountDate 'Sales Header'.'Pmt. Discount Date'
XML field PaymentMethod 'Sales Header'.'Payment Method Name'
'Order'. 'PaymentStatus' PaymentStatus 'Sales Header'.'Payment Method Status'
 - PaymentTermsCode 'Sales Header'.'Payment Terms Code'
 - PostingDate 'Sales Header'.'Posting Date'
XML field PricesInclVat 'Sales Header'.'Prices Including VAT'
 - PromisedDeliveryDate 'Sales Header'.'Promised Delivery Date'
 - RequestedDeliveryDate 'Sales Header'.'Requested Delivery Date'
 - SalesPerson Retrieves from the 'Salesperson/Purchaser' table
 - SalesPersonId Retrieves from the 'Salesperson/Purchaser' table
 - ShipmentDate 'Sales Header'.'Shipment Date'
XML field ShipmentMethodCode 'Sales Header'.'Shipment Method Code'
XML field ShipmentMethodName 'Sales Header'.'Sana Shipment Method Name'
'Order'. 'ShipToName' ShipToName 'Sales Header'.'Ship-to Name'
 - ShopAccountId 'Sales Header'.'Sell-to Shopaccount Code'
 - Status 'Sales Header'.'Status'
XML field TotalPrice Calculated field
XML field TotalPriceExcludingDiscount Calculated field
XML field TotalPriceExcludingVat Calculated field
 - TotalQuantity Calculated field
XML field VatAmount Calculated field
XML field VatPercentage Calculated field
BillingAddress (saved as XML field in the SQL datamodel)
During the order export at first the customer information is saved in the 'Customer' table in NAV and after that the 'BillingAddress' fields are automatically filled with the values from the customer information.
  Address 'Sales Header'.'Bill-to Address'
  Address2 'Sales Header'.'Bill-to Address 2'
  City 'Sales Header'.'Bill-to City'
  Contact 'Sales Header'.'Bill-to Contact'
  ContactId 'Sales Header'.'Bill-to Contact No.'
  CountryRegionCode 'Sales Header'.'Bill-to Country/Region Code'
  Country Retrieves from the 'Country/Region' table according to the 'Sales Header'.'Bill-to Country/Region Code' field
  CustomerId 'Sales Header'.'Bill-to Customer No.'
  FirstName Not used for the NAV Connector.
Both these fields are stored in the 'Bill-to Name' column during the order export.
If the value is too long then 'FirstName' will be stored in the 'Bill-to Name' column and 'LastName' in 'Bill-to Name 2'.
  LastName

 

Street Not used for the NAV Connector.
Both these fields are stored in the 'Bill-to Address' column during the order export.
If the value is too long then 'Street' will be stored in the 'Bill-to Address' column and 'HouseNumber' in 'Bill-to Address 2'.
  HouseNumber
  Name 'Sales Header'.'Bill-to Name'
  PostalCode 'Sales Header'.'Bill-to Post Code'
SellToAddress (saved as XML field in the SQL datamodel)
During the order export at first the customer information is saved in the 'Customer' table in NAV and after that the 'SellToAddress' fields are automatically filled with the values from the customer information.
  Address 'Sales Header'.'Sell-to Address'
  Address2 'Sales Header'.'Sell-to Address 2'
  City 'Sales Header'.'Sell-to City'
  Contact 'Sales Header'.'Sell-to Contact'
  ContactId 'Sales Header'.'Sell-to Contact No.'
  CountryRegionCode 'Sales Header'.'Sell-to Country/Region Code'
  Country Retrieves from the 'Country/Region' table according to the 'Sales Header'.'Sell-to Country/Region Code' field
  CustomerId 'Sales Header'.'Sell-to Customer No.'
  FirstName Not used for the NAV Connector.
Both these fields are stored in the 'Sell-to Customer Name' column.
If the value is too long then 'FirstName' will be stored in the 'Sell-to Customer Name' column and 'LastName' in 'Sell-to Customer Name 2'.
  LastName
  Street Not used for the NAV Connector.
Both these fields are stored in the 'Sell-to Address' column.
If the value is too long then 'Street' will be stored in the 'Sell-to Address' column and 'HouseNumber' in 'Sell-to Address 2'.
  HouseNumber
  Name 'Sales Header'.'Sell-to Customer Name'
  PostalCode 'Sales Header'.'Sell-to Post Code'
ShippingAddress (saved as XML field in the SQL datamodel)
  Address 'Sales Header'.'Ship-to Address'
  Address2 'Sales Header'.'Ship-to Address 2'
  City 'Sales Header'.'Ship-to City'
  Code 'Sales Header'.'Ship-to Code'
  Contact 'Sales Header'.'Ship-to Contact'
  CountryRegionCode 'Sales Header'.'Ship-to Country/Region Code'
  Country Retrieves from the 'Country/Region' table according to the 'Sales Header'.'Ship-to Country/Region Code' field
  FirstName Not used for the NAV Connector.
Both these fields are stored in the 'Ship-to Name' column.
If the value is too long then 'FirstName' will be stored in the 'Ship-to Name' column and 'LastName' in 'Ship-to Name 2'.
  LastName
  Street Not used for the NAV Connector.
Both these fields are stored in the 'Ship-to Address' column.
If the value is too long then 'Street' will be stored in the 'Ship-to Address' column and 'HouseNumber' in 'Ship-to Address 2'.
  HouseNumber
  Name 'Sales Header'.'Ship-to Name'
  PostalCode 'Sales Header'.'Ship-to Post Code'

ICustomerProfile

Sana Commerce SQL datamodel Sana Commerce property NAV field
'UserProfile'.'Id' Id 'Customer'.'No.'
XML field Name 'Customer'.'Name'
XML field First Name Not used for the NAV Connector.
Both these fields are stored in the 'Name' column.
If the value is too long then 'FirstName' will be stored in the 'Name' column and 'LastName' in 'Name 2'.
XML field LastName
 - Balance 'Customer'. 'Balance'
 - CreditLimit 'Customer'.'Credit Limit (LCY)'
XML field PricesInclVat Retrieves from the 'Customer'.'Prices Including VAT' field.
If the 'Bill-to Customer No.' field is set the 'PricesInclVat' property will be retrieved from 'Bill-to Customer No.'
 - B2cCustomer 'Customer'.'B2C Customer'
 - Blocked 'Customer'.'Blocked'
XML field Email 'Customer'.'E-Mail'
 - FaxNo 'Customer'.'Fax No.'
 - Homepage 'Customer'. 'Home Page'
 - SalesPersonCode 'Customer'.'Salesperson Code'
 - SearchName 'Customer'.'Search Name'
XML field CurrencyId 'Customer'.'Currency Code'
 - BillToCustomerId 'Customer'.'Bill-to Customer No.'
XML field PhoneNo 'Customer'.'Phone No.'
XML field Name 'Customer'.'Name'

Address (saved as XML field in the SQL datamodel)

  Name 'Customer'.'Name'
  Address 'Customer'.'Address'
  Address2 'Customer'.'Address 2'
  City 'Customer'.'City'
  Contact 'Customer'.'Contact'
  CountryRegionCode 'Customer'.'Country/Region Code'
  Country Retrieves from the 'Country/Region' table according to the 'Customer'.'Country/Region Code' field
  CustomerId 'Customer'.'No'
  FirstName Not used
  LastName Not used
  Street Not used for the NAV Connector.
Both these fields are stored in the 'Address' column.
If the value is too long then 'Street' will be stored in the 'Address' column and 'HouseNumber' in 'Address 2'.
  HouseNumber
  PostalCode 'Customer'.'Post Code'

IOrderLine

Sana Commerce SQL datamodel Sana Commerce property NAV field
XML field VariationId 'Sales Line'.'Variant Code'
'OrderLine'.'ProductId' ProductId 'Sales Line'.'No.'
XML field Title 'Sales Line'.'Description' (filled only for the shipping costs and payment costs)
XML field ProductCode 'Sales Line'.'No.'
XML field SortId 'Sales Line'.'PfsSorting' (PFS add-on)
 - VariationHorizontal Calculated field (PFS add-on)
 - VariationVertical Calculated field (PFS add-on)
 - UnitOfMeasure 'Sales Line'.'Unit of Measure Code'
'OrderLine'.'OrderId' OrderId 'Sales Line'.'OrderNo'
 - PrepackCode   'Sales Line'.'PfsPrepack Code' (PFS add-on)
 - VerticalCode 'Sales Line'.'PfsVertical Component' (PFS add-on)
 - OriginalVariationId Calculated field (PFS add-on)
XML field IsNonEditableItem Calculated field
 - ShipmentDate 'Sales Line'.'Shipment Date'
XML field DiscountPercentage 'Sales Line'.'Line Discount %'
 - Inventory Not used
XML field Quantity 'Sales Line'.'Quantity'
 - QuantityInvoiced 'Sales Line'.'Quantity Invoiced'
 - QuantityShipped 'Sales Line'.'Quantity Shipped'
 - QuantityOutstanding 'Sales Line'.'Outstanding Quantity'
XML field VatPercentage 'Sales Line'.'VAT %'
XML field DiscountAmount 'Sales Line'.'Line Discount Amount'
XML field Price 'Sales Line'.'Unit Price'
XML field SubTotal 'Sales Line'.'Line Amount'
 - PrepackQuantity 'Sales Line'.'PfsPrepack Quantity' (PFS add-on)
XML field LineType Calculated field

Promotions

When a promotion is used for the SQL Provider the promotion discount is saved in the order lines.
When a promotion is used for the NAV Connector the promotion discount is saved in the sales header table and accessible from the sales orders (sales quotes) forms in the 'E-Commerce' tab.
For more detailed information about promotions, please read this chapter.

Sana Commerce SQL datamodel Sana Commerce property NAV field
'OrderLine'.'ProductId' ProductId 'Sales Header'.'Sana Promotion Code'
XML field Title 'Sales Header'.'Sana Promotion Title'
'DiscountType' XML field   'Sales Header'.'Sana Promotion Discount Type'
'DiscountValue' XML field   'Sales Header'.'Sana Promotion Discount Value'
XML field Price 'Sales Header'.'Sana Promotion Discount Amount'
Knowledge BaseField Mapping between SC and NAV