PULSE eCommerce Mapping Dictionary

The sales order mapping is a destination mapping that writes information into the sales order header table, which for Macola is the oeordhdr_sql table.

Not all fields must be defined.  When the order is created, many will be populated based on the customer defined on the order.

NOTE: The following fields are the minimum that are required for the order to be created:

CustomerNumber

LineMapping

OrderNumber

OrderDate 

The AddExtraItem functionality in the Header Mapping can be used to add extra items like discount lines or surcharge items. 

AddExtraItem = True if you want to add the item, False or blank if not. 

AddExtraItem.ItemNumber = This is where you would define the item that would be brought in.  This could be a static value like “Discount” 

AddExtraItem.Key = this would help determine if eCommerce is finished adding additional items.  It might be used to see if the item has been used more than once.

AddExtraItem.QuantityOrdered = what quantity it would use

Note: To use the AddExtraItem options, you must create a script and put it into one of the fields that eCommerce evaluates. The script will pick the item number and write it to the AddExtraItem.ItemNumber field. Then the AddExtraItem.QuantityOrdered should be updated based on the difference between the original order quantity and the total of the items created. For binned or lotted items, use a temp field in the script to add the quantity from the lot/bin found to that field. Once that temp field is the same as the order quantity, that is what would stop the system from adding more items. 

AddressChangeOptions.SalesPerson

AddressChangeOptions.ShippingMethod

AddressChangeOptions.TaxCode(1-3)

ApplyToInvoiceNumber

ArReference

BigCommerce.accessToken

BigCommerce.createDateStart

BigCommerce.storeHash

BillingAddress.AddressID

BillingAddress.AddressLine(1-3) = there are three fields, one for each line of the billing address

BillingAddress.City

BillingAddress.Country

BillingAddress.Name

BillingAddress.PostalCode

BillingAddress.State

ECommerce uses the CheckEmail fields to connect to an IMAP email setting.  Using these fields eCommerce will check the email account, download the attached files, and process the order into the ERP system. This works for generic import/export and import sales orders.

CheckEmail.CheckForNewMessages = True/false.  Turns feature on/off

CheckEmail.HighestUID = IMAP protocol uses integer IDs for every email.  After eCommerce checks your email, it keeps track of the last message ID.  For instance, if the last message ID was 504983, eCommerce stores that ID here.  Then the next time it checks your email, it only fetches messages above that ID.

CheckEmail.IgnoreExtensions = define the extensions to ignore, like jpg, gif, etc.

CheckEmail.ImapServer

CheckEmail.Password

CheckEmail.UserName

CheckEmail.UseSSL

CheckEmail.LocalDownloadFolder = The local folder (generally a UNC) where eCommerce will drop the files it pulls from your email account.  On your mapping, you must still use the source folder and move-to folder settings as you normally would.  This is just where the files will be dropped (which will be the same as the source folder in your mapping)

CheckEmail.MoveToFolder = Refers to your email folders (the folders you see in Gmail or Outlook).   For example, the customer can make a folder in their email called “Processed orders”.  After eCommerce successfully processes an email with a file attached, it will move the email to the defined email folder.  This indicates to the user that the email has been processed.  An accumulation of emails in the root inbox indicates something must be wrong.

Comment(1–3) = There are three fields to represent the 3 comment lines in the Macola order header.

CreateAlternateAddress

Customer

CustomerNumber = This is what the software uses to look up the customer to use on the order.  Once a valid customer is found, the customer’s properties are used to set the corresponding properties on the sales order. 

CustomField(1-5) = These fields represent user-defined fields 1 – 5 (user_def_fld_1)

DeleteLineNumber = This is an integer setting that will delete a certain line number from an order before it is saved.

Department

Discount

DueDate

DuplicateOrder = Boolean value expected.  This allows one to override the standard logic for detecting duplicate orders.  An SQL query could be supplied to use custom logic for determining if an order is a duplicate or not.  For example, instead of using just an order number for duplicate determination, one could define logic to allow the same order number to be used more than once, provided that the order number is from a different customer. This can occur when multiple customers use the same range of PO numbers. 

EmailAddress

EnteredDate

Extra(1-15) – These fields represent the 15 extra fields in Macola ES (extra_1).  Extra1 through Extra9 are text fields.  Extra10 through Extra15 are numeric with 10 and 11 having up to 6 decimals, 12 and 13 having up to 2 decimals, and 14 and 15 with no decimals. 

Filler00001

FreightCharges

FreightPayCode

FTP.ConnectMode

FTP.DeleteFilesOnSucess = True or False – should the files be deleted on the FTP site once the file has been used. (Boolean value setting)

FTP.DownloadFolderLocal = define where to put the files downloaded from the FTP site.  This should match the Input File Path in your header mapping.  This is done on the local machine.

FTP.DownloadFolderRemote = Define the location of the FTP folder that will hold the files used by eCommerce.  These would be the files that contain the orders.

FTP.EnableDownloadFiles = This is true or False and lets eCommerce know it can download the files. (Boolean value setting)

FTP.HostName = the server where the FTP folders are stored; this is provided by the company that provides access to the data.

FTP.UserName = the username needed to access the FTP site.

FTP.Password = the password for accessing the FTP site

FTP.UsesSFTP = True or False and will allow eCommerce to send the correct connection information when secure FTP is used. 

GrandTotal

IgnoreThis = This is a Boolean setting that will completely ignore this order if set to True.  This order will not even appear on the eCommerce screen.  A SQL script can be used to help define the orders that should be ignored. 

InventoryLocation.Code

InvoiceBatchID

InvoiceDate

InvoiceNumber

JobNumber

LineMapping = The line mapping associated with this header mapping.  This is required if the automated service is running to import orders.  The automated service needs to know which line mapping to use. 

In Software architecture, there is a thing called a “Message Queue”.  It provides a temporary holding area for any message format (including text, XML, JSON, CSV, or whatever).  Leahy Consulting can provide a Message Queue service that can be installed on the server.  This provides an endpoint for the 3rd party to send something to.  The message queue does not validate anything.  It just stores whatever is sent.  Then you set up a mapping inside eCommerce to poll the message queue (just like it can with an email account).  eCommerce can poll the queue from a database or a URL.  

Please be aware: Implementation of this feature requires billable hours as we must work with the customer to establish a webserver within a DMZ, and some other tasks.

MessageQueue.DownloadFromDB = True/False to use the database

MessageQueue.DownloadFromService = True/False to use the service

MessageQue.DownloadPath = a temporary place to put files if applicable

MessageQue.HighId = Uses integer IDs for every message.  After eCommerce receives a message, it keeps track of the last message ID.  For instance, if the last message ID was 504983, eCommerce stores that ID here.  The next time it receives a message, it only processes messages with an ID above that one.

MessageQue.Url = the URL of the MessageQueue to poll

MiscAmount

OrderComments

OrderDate

OrderNumber = This must have a unique value per order.  Sometimes a source might not even have an order number, but a value must be defined.  An SQL query can be used to make a unique value if needed. 

OrderStatus

OrderType = O is assumed by default if no value is defined.  I and C are supported options.  Keep in mind that if this is set to a static value, all orders being imported will have the same order type.  

Payment.CostCenter

Payment.CostUnit

Payment.CreateNewPayment = If this is set to True, a pre-payment for this order will be created in the ERP system. (Boolean value)

Payment.GLAccountNumber

PaymentTerm.Code

PhoneNumber

PostSQLQuery = This SQL Query will be executed for each order after it is pushed into the ERP system.  Using @SourceObject.PropertyName in the query, you can pass any parameters to the ERP fields. 

PrestaShop.ApiKey

PrestaShop.HighId = this field can be used to help filter the orders that will be reviewed and selected for import.  After setting it the first time, it will be updated automatically with the highest value that was pushed to Macola. 

PrestaShop.PageSize

PrestaShop.Url = your store’s Presta Shop web address.

ProfitCenter

The Promotion feature puts a promo item on the sales order with a negative amount.  It uses the item number you specify and uses the GL accounts associated with the item’s material cost type/location when defined. 

Promotion.Amount = On the promo line item, this amount serves as the unit price and will be multiplied by the quantity which is hard-coded to be negative one (-1). 

Promotion.Code = This is the promo code used by the trading partner on the order.

Promotion.ItemNumber = The item number that will be used for the “promo” line item that eCommerce will add to the order. 

PurchaseOrderNumber

SalesOrderLineItem.QuantityOrdered

SalesOrderLineItems

SalesPerson.Code

SalesTaxAmount = Leave blank to use the sales tax that is provided on your inbound order.

SalesTaxLineItem.Amount

SalesTaxLineItem.ItemNumber = This will attach an extra “Sales Tax Item” onto the order.  This is the item number in the ERP system to be used. 

SalesTaxOverride = This setting bypasses the ERP tax calculation logic. Map to your sales tax amount field to ensure that eCommerce uses the imported sales tax amount instead of allowing the ERP system to calculate it. 

SaveAsRma

The SendEmail fields are IMAP email settings used by eCommerce to send emails when errors occur.

Emails can be sent during Import when:

• Something goes wrong as the data is being imported into memory
• When all imported orders are in memory but fail the validation checks. If any order passes the validation checks, no email will be sent. 
• When an error occurs while pushing orders into Macola. 

SendEmail.CCEmail

SendEmail.MailGunApiKey = c25536f5e1c4ec9ceaa4f1f978597b5a-f3238714-6682d8d7

SendEmail.MailGunDomain = pulsedashboard.com

SendEmail.Password

SendEmail.SendErrorMessage = True if you want to send them

SendEmail.FromEmail = the email address of the “person” sending the message.

SendEmail.SmtpPort

SendEmail.SmtpServer

SendEmail.ToEmail

SendEmail.UserName

ServiceDomain = Dynamics Nav API setting

ServicePassword = Dynamics Nav API setting

ServiceUrl = Dynamics Nav API setting

ServiceUser = Dynamics Nav API setting

ShippingAddress

ShippingAddress.AddressCode = The address code of an alternate shipping address.  If left blank, the default ship to address will be used.  An expression can be supplied.  For example, in an SQL query, one could use address, city, state, and postal code to look up which address code to use. 

ShippingAddress.AddressID

ShippingAddress.AddressLine(1-3) = there are three fields, one for each line of the ship to address

ShippingAddress.City

ShippingAddress.Country

ShippingAddress.Name

ShippingAddress.PostalCode

ShippingAddress.State

ShippingDate

ShippingInstructions(1-2)

ShippingOption.Code

Shippo.apiKey

Shippo.createDateStart = Date/Time the transaction was brought into the VAN (as defined in the field name).  Format the static value as YYYY-MM-DD.  Once set the system will update this field with the date and time of the last import.  This improves processing speed by only looking for orders added since the last import. 

ShipStation.apiKey

ShipStation.apiSecret

ShipStation.createDateStart = Date/Time the transaction was brought into the VAN (as defined in the field name).  Format the static value as YYYY-MM-DD.  Once set the system will update this field with the date and time of the last import.  This improves processing speed by only looking for orders added since the last import. 

ShipStation.orderNumber

ShipStation.orderStatus = This allows for the filtering of incoming orders based on the order status.  Current options are: awaiting_payment, awaiting_shipment, pending_fulfillment, shipped, on_hold, cancelled, or rejected_fulfillment. 

Shopfiy.AccessToken

Shopify.APIKey

Shopify.ApiVersion

Shopify.CreatedAtMin = Date/Time the transaction was brought into the VAN (as defined in the field name).  Format the static value as YYYY-MM-DD.  Once set the system will update this field with the date and time of the last import.  This improves processing speed by only looking for orders added since the last import. 

Shopify.FulfillmentStatus

Shopify.Password

Shopify.PopulateOrderMetaFields

Shopify.SharedSecret

Shopify.Status = This is a filer.  Current options include: open, closed, cancelled, or any. 

Shopify.StoreName

ShopifyID

SqlCommandTimeout

SubTotal

Taxable

TaxingBody.Code = When using SalesTaxOverride, a value must be entered here.  This is required even if you are using tax schedules in Macola.  We recommend creating a tax code for 0% that is used as a placeholder while importing the orders. 

TaxingBody(2-3).Code

TaxingBodyGroup.Code = This will write to the tax schedule field on the order header.  To use the sales tax from your inbound order set this to SQL Query: select null and set the What if a null or DbNull value from the source is encountered? to Set the destination field value to null.  This will ensure that any tax schedule that may be defined in the customer maintenance table will be removed.  For Macola, this is important to ensure that the correct sales tax amount remains on the sales order.  By design, Macola will recalculate the sales tax amount during select for billing when tax schedules are used. 

TempDictionary = These fields are used to hold temporary information that can be used when creating a new sales order in the ERP or to write information to custom tables.  For example this could be used to write the UPC Code or buyer part number for use when sending an acknowledgement, ASN, or invoice. A SQL script is generally used to populate these values. 

UserName = the user creating the record in the ERP, normally set to a static value of “eCommerce”

Walmart.clientId

Walmart.clientSecret

Walmart.createDateStart = Date/Time the transaction was brought into the VAN (as defined in the field name).  Format the static value as YYYY-MM-DD.  Once set the system will update this field with the date and time of the last import.  This improves processing speed by only looking for orders added since the last import. 

Walmart.Sandbox

Wayfair.clientID

Wayfair.clientSecret

Wayfair.fromDate

Wayfair.pageSize

Wayfair.poNumbers

Wayfair.url