Skip to main content

Sales Documents

Business Value

When you have multiple systems to generate sales in addition to Business Central (for example, a webshop), you need to update Business Central with the transactions. If you don't integrate with these systems, you need to record the transactions manually. Usually, processing general journals or sales journals is not sufficient because you also need to record the inventory movements. The sales document import feature allows you to import all sales document types (Quotes, Orders, Invoices, Credit Memos, Return Orders and Blanket Orders) from an Excel file.

Possible Scenarios

Here are some possible scenarios where you might use this feature to import sales documents:

  • Sales orders from a webshop where there is no integration.
  • Sales orders or invoices to record sales through Amazon, AliExpress or another platform when the volumes do not justify the costs of integrating the platform to Business Central. You can even use it as a temporary measure while building an integration.
  • Sales invoices that get processed in a third-party system like Chargify or Stripe.
  • Sales invoices or orders that get raised in a CRM system.
  • Initial loading of sales orders for a new company in Business Central.
  • Sales Credit Memos to process rebates that you have calculated outside of Business Central.
  • An order form that you have configured to allow your customers to place orders with you.

Feature Details

The Import from Excel action can be found in the lists:

List
Sales Quotes
Sales Orders
Sales Invoices
Sales Credit Memos
Sales Return Orders

Settings

Sales Document Settings

Document No. as Posting No.

This setting allows you to use the imported Document No. (if present) as the Posted Document No.

ValueDescription
NoBusiness Central will assign a number while posting the document. This is the default option.
YesUse the imported document as the posted document number. You use this setting if you want to keep the document number on the posted entries that will get created when you post the imported documents.

Replace with Document No. from Series

ValueDescription
YesThe imported document number gets replaced using the number series specified in the setup.
No (Default)The imported document number gets used for the imported documents.

Negative Document Totals

This setting allows you to specify how to handle documents when the total value is negative and will enable you to Ignore, Skip, Cancel and Change Document Type. You can use this setting when importing sales documents from another system when the file includes orders and returns. It allows you to import the file with a single process - rather than split the file and process two separate imports.

ValueDescription
Do not ValidateThis is the default setting where the app imports the file without checking the totals. Use this setting when you are sure that the file only contains records for the same document type.
Skip during ImportUse this setting if you want the app to check the total and skip documents that have a negative total.
Cancel ImportUse this setting if you want the app to check the total and raise an error if the document total is negative.
Order/Invoice to CreditUse this setting if you want the app to change the document type of negative documents. For example, if you import a file containing orders and returns into either sales orders or invoices, the app will create credit memos for the documents with negative totals.
Order/Invoice to Return/CreditUse this setting if you want the app to change the document type of negative documents. For example, if you import a file containing orders and returns into sales orders, the app will create return orders for documents with negative totals. The app will create credit memos for negative documents when importing into sales invoices.

Custom Values

You can use Custom Values to:

  • Create multiple sales lines from one imported line. For example, the file may contain a charge for freight that you want to show as a separate line. You can read more about this here.
  • Create comment lines above or below the imported line using a column from the file. You can read more about this here.
  • Handle other custom logic, which you can add by extending Excel Importer.

Work Description

Excel Importer supports mapping to Excel columns to blob fields. You can map multiple columns from the file to the work description. Here's an example of how to do this mapping.

Document Grouping

When you import a file, the app groups lines into documents differently depending on the following scenarios.

  • If one field you mapped is the document number, the app will group lines by Document No., Currency Code, Sell-to Customer No. and Bill-to Customer No..
  • If no document number is mapped, the app groups lines by External Document No., Your Reference, Currency Code, Sell-to Customer No. and Bill-to Customer No..

Line Amount/Unit Price/Quantity

Some files contain only a quantity and a Line Amount. The app checks for this while importing a line, and if it finds that a line has a non-zero Line Amount and a zero Unit Price, it will calculate the Unit Price for you. The following applies when there is a non-zero Line Amount and a zero Unit Price:

  • If Quantity = zero, then the app assumes 1; otherwise, it uses the Quantity on the line.
  • The app then calculates the Unit Price as:
    • Unit Price = Line Amount / Quantity (if no Multiplier specified)
    • Unit Price = (Line Amount * Multiplier) / Quantity (if you specified a Multiplier)
  • The app then rounds the calculated Unit Price using the Rounding Precision specified for the Line Amount. Therefore, in this instance, we recommend specifying the Unit Amount Rounding Precision for the Line Amount on your mapping template (usually 0.00001).

Examples

Example 1: Basic Example

This example comes from our sample templates you can download using the Excel Importer Setup Wizard.

Excel file

SalesDocument-Eg1-File

Excel Mapping Template

SalesDocument-Eg1-FieldMapping

note

Look at 1 and 2 in the image above. While we are importing the document number to group the lines into documents, we have configured the template to replace this document number to assign a new number from the number series configured in Business Central.

Imported Orders - List

The three lines in the Excel Worksheet created two separate orders. SalesDocument-Eg1-Result-List

Imported Orders - Document

SalesDocument-Eg1-Result-Document

Example 2: Importing Web Orders from a variable-length (CSV) file

This video shows how to import a variable-length text file as sales orders.

Example 3: Import a variable-length (CSV) file and use mapping

This is a more complex example where we show you how to:

  • Specify Constant Values to provide values that do not exist in the file
  • Use Account/Dimension Mapping and even handle scenarios where the same column could contain an Item or G/L Account Number.
  • Use the Multiplier to change the sign of the amount and also handle where the amount is provided in cents.

Text File

The source file is a text file. The image below shows the file if you open it in Excel and if you open it in a text editor.

SalesDocument-Eg3-File

Excel Mapping Template and Column Definition

SalesDocument-Eg3-MappingTemplate

Field Mapping

SalesDocument-Eg3-FieldMapping

Regarding the image above:

  • 1 and 2 show how we can add constant values to specify values that are not available in the file but are always the same. In our example, we have the customer number, a standard discount agreed upon with this customer, and the quantity.
  • 3 shows how we use Account/Dimension Mapping to map data in the file to values in Business Central. Our file will mostly contain items, but there will be some lines that contain G/L Accounts. Note the following:
    • Mapping Type is specified as Parent - this means we want to use this column to perform the mapping.
    • Mapping Field Caption - we have specified to use the No. value from the Account/Dimension Mapping.
    • Default Values is specified as Use Imported Value if No Mapping. We have selected this, so we only need to include some mappings in our mapping template. If the value imported from the file does not exist in the Account/Dimension Mapping, then the app imports the value without changing it.
  • 4 shows how we can use different values from the Account/Dimension Mapping. Note the following:
    • Mapping Type is specified as Parent. Even though we use the same value as 3, we cannot specify Child because we want to use a constant value if no mapping is found.
    • Mapping Field Caption - we have specified to use the Type value from the Account/Dimension Mapping.
    • Default Values is specified as Use Constant No Mapping. We have selected this so that we will use the constant value if the mapping does not exist.
    • Constant Value - we have specified Item because we expect most of the lines to be items.
  • 5 shows that we specified -0.01 as the multiplier. We did this because the unit price is provided as a negative value in cents.

Account Dimension Mapping

You will notice that we have only included mapping for the lines that are not items. SalesDocument-Eg3-AccountDimensionMapping

Imported Order

SalesDocument-Eg3-ImportedDocument

Example 4: Using Absolute References

In this example, we import an order form that one of our customers has filled out and sent to us. Some data must be on every line but is only in one place on the document. To handle this, we use absolute references.

Excel File

Sales Order Form

Excel Mapping Template

Sales Order Form Mapping Template

Imported Document

Sales Order Form Result

Example 5: Concatenating Several Columns and Custom Values

This example shows you how to concatenate multiple columns into one field. We also use the custom values feature to add a freight charge as an additional line on the order.

Excel File

Sales Order Form

Excel Mapping Template

Concatenate - Mapping Template

Regarding the above image:

  1. Columns K and L are mapped to the Work Description. We selected a custom separator (Info) for this field.
  2. Columns J and Q are mapped to the Description. For this field, we are using Space as the separator.
  3. Column F is mapped to a custom value we defined to handle a freight charge. This is an additional charge that we must post to a different account.

Custom Value

The image below shows the definition of the custom value:

  1. The processing action specifies that the app must create an additional line.
  2. The Account/Dimension Mapping assigns the account, etc. Custom Value

You can get more information on custom values here.

Imported Document

Concatenate - Result

  1. The Work Description holds the values of K and L from the file and is separated with the text (Info).
  2. The Description holds the values of J and Q and is separated with a space.
  3. The app created an additional line to record the freight charges.

See Also