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 action can be found in the lists:
| List |
|---|
| Sales Quotes |
| Sales Orders |
| Sales Invoices |
| Sales Credit Memos |
| Sales Return Orders |
Settings
Document No. as Posting No.
This setting allows you to use the imported Document No. (if present) as the Posted Document No.
| Value | Description |
|---|---|
| No | Business Central will assign a number while posting the document. This is the default option. |
| Yes | Use 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
| Value | Description |
|---|---|
| Yes | The 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. |
Release Documents
This setting allows you to specify that the imported documents should be released or sent for approval after they have been imported.
| Value | Description |
|---|---|
| Yes | Release the documents or send them for approval after importing. |
| No (Default) | Do not release or send for approval. |
Update Header Dimensions
| Value | Description |
|---|---|
| Use First Line | The app will update the sales header using the dimension set specified on the first sales line. |
| Use values from all lines | The app will update the sales header using the dimensions from the first line, and then, for each successive line, it will add a dimension value if not already specified on the header. For example, if the first line specifies only the department and then the second line specifies the department and the project, the header will be updated with the department of the first line and the project of the second line. |
| Use Default and values from all lines | Similar to the previous setting except that the dimension set on the header does not get replaced by the first line - the first line adds to the values. This gives priority to the dimensions that are defaulted onto the sales header. |
| No | Use only the default dimensions for the header. |
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.
| Value | Description |
|---|---|
| Do not Validate | This 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 Import | Use this setting if you want the app to check the total and skip documents that have a negative total. |
| Cancel Import | Use this setting if you want the app to check the total and raise an error if the document total is negative. |
| Order/Invoice to Credit | Use 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/Credit | Use 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. |
Common Settings
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).
Auto Reserve
The app supports the Auto Reserve feature available in standard Business Central.
If insufficient inventory is available, you will not be prompted to reserve it manually, as the app cannot display the reservation page while importing.
Examples
Example 1: Basic Example
This example comes from our sample templates you can download using the Excel Importer Setup Wizard.
Excel file
Excel Mapping Template
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.
Imported Orders - 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.
Excel Mapping Template and Column Definition
Field Mapping
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.
Imported Order
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
Excel Mapping Template
Imported Document
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
Excel Mapping Template
Regarding the above image:
- Columns K and L are mapped to the Work Description. We selected a custom separator (Info) for this field.
- Columns J and Q are mapped to the Description. For this field, we are using Space as the separator.
- 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:
- The processing action specifies that the app must create an additional line.
- The Account/Dimension Mapping assigns the account, etc.
You can get more information on custom values here.
Imported Document
- The Work Description holds the values of K and L from the file and is separated with the text (Info).
- The Description holds the values of J and Q and is separated with a space.
- The app created an additional line to record the freight charges.