Microsoft Dynamics NAV 2013 is now completely configured and tuned. A range of brand new functionalities have been programmed and everything is ready for us to go live. But, data is needed for it to start working!
Companies may now start working with Dynamics NAV, but they are not new companies; they have been working for a while, and they already have all kinds of data: their customers, vendors, items, accounting information, and so on.
In this chapter we will see which tools can be used in Dynamics NAV to migrate data into the system and how to convert data to meet NAV requirements. We will look at tools such as:
We will also see what kind of data is commonly migrated to Dynamics NAV and which strategies can be used to migrate it. The kind of data and strategies are listed as follows:
There are several ways to migrate data into Microsoft Dynamics NAV 2013. You will choose the method depending on what is to be migrated and whether any additional processes need to be carried out on the provided data to meet Dynamics NAV requirements.
We'll go through the different tools available in Dynamics NAV to migrate data. We'll also explain how to write our own tools if the ones provided out of the box do not meet our requirement or expectation.
RapidStart Services is a new feature of Microsoft Dynamics NAV 2013. It allows you to configure your company using out of the box configurations. Microsoft Dynamics NAV 2013 also allows streamlined importing of opening balances into journals and active documents with dimensions.
Typically, a Dynamics NAV implementation project can go on for a great length of time. With RapidStart Services, Dynamics NAV 2013 delivers a new way for partners and customers to speed up implementations. RapidStart Services is a tool designed not only to shorten deployment time but also to improve quality, to introduce a repeatable approach to implementations, and to automate and simplify recurring tasks.
With RapidStart Services, you can set up the tables often involved in the configuration process of new companies. You can create a questionnaire to guide your customers through the collection of setup information. Your customers have the option of using the questionnaire to set up application areas, or they can open the setup page directly and complete the setup there. Most importantly, RapidStart Services helps you, as a customer, prepare the company with default setup data that you can fine-tune and customize. Lastly, when you use RapidStart Services, you can configure and migrate existing customer data, such as a list of customers or items, into the new company.
The RapidStart Services tools can be found under the Department menu, Departments/Administration/Application Setup/RapidStart Services for Microsoft Dynamics NAV.
The following components can be used to set up a new company:
We will explain how these components work by following a step-by-step example of each of them. Before starting with the example, you need to create a new company, and we also recommend that you change your role to that of the RapidStart Services implementer. Follow these steps to do both these things:
You have now entered in the New Company section. Follow the ensuing steps to change your role.
Now that we have a new company and we have selected the RapidStart Services implementer role, we are ready to use all the components of the RapidStart Services tool to set up our company.
The configuration wizard is used to quickly configure a new company. Click on the RapidStart Services Wizard option found on the ribbon bar.
A new page will open where you will be able to enter basic information about the new company.
You can go through all the tabs of the page entering the required information. The Select Package tab will be explained in the Configuration packages section, next.
There are more than 200 tables that could be considered configuration tables. You will need to fill them in when you create a new company.
First of all, you will find almost 50 tables with the word setup as their description, but there are many other tables that could also be considered as setup tables. Here you can see a list of some setup tables:
Having to edit all those tables manually on each implementation could take forever. Many companies can use Dynamics NAV with the same data or almost the same data on those configuration tables.
The best approach will be to create a configuration package for the data on the configuration tables and then apply it on each new implementation, like a template.
You can create one configuration package per functional area, for example, you can create one package for the manufacturing functionality. Another approach would be to create one package for each type of data, for example, you could create one package with data related to all the posting groups found in the application.
In this section we will see how to create a configuration package and also how to apply it to a new company.
In this section we are going to create a new configuration package with all posting groups tables found on the application. Since posting groups refer to general ledger accounts, we are also going to include the chart of accounts in our package.
Follow these steps to create the new configuration package:
CRONUS International Ltd
.Table ID
column.G/L Account
table and click on Table | Fields. On the Config. Package Fields page, uncheck the Include Field
column for the Global Dimension 1 Code
field and the Global Dimension 2 Code
field.RAPIDSTART
file that you can save.In the previous section we created a new configuration package. In this section we are going to apply this package to the company New Company, that we created earlier in this chapter.
Follow these steps to apply the configuration package:
New Company
.The data contained in the configuration data has been imported to the new company. You can also import packages from the packages page we saw while creating the configuration package.
The configuration worksheet is the central location in which you can plan, track, and perform your configuration work. For those of you that have used previous versions of Dynamics NAV, the configuration worksheet is the old migration tool with some new features.
You use the configuration worksheet to create the structure of tables that need to be imported with the company data. You will be able to export this structure to Microsoft Office Excel, fill in the data, and then import it back to Dynamics NAV. This makes it easy for companies to copy and paste information from another ERP system.
We'll explain how the configuration worksheet works by creating a migration structure for the sales area and then importing some demo data into the customer's table.
To create a migration structure, you need to use a company that is already configured. We will use the demo company CRONUS International Ltd
.
Follow the steps described in this section:
18
, Customer
. You only need to fill in the Line Type
field and the Table ID
field.When importing the data into the table, fields will be validated according to their processing order. You can use the Move Up and Move Down options to change the default validation order.
You can also uncheck the Validate Field column for a field if you don't want to run the OnValidate
trigger of the field. If you do so, you will have to validate the data consistency on your own.
No.
field to only include customers with a blank No.
field. Since this is the primary key of the table, all customers should have filled up this field. No records will match the filter, so no records will be included while exporting the template. Add the filter as shown in the following screenshot:Customer
table.The first related table is Payment Terms
. We will not be able to fill in the payment terms code for a customer unless it exists on the Payment Terms
table. Therefore, we need to include related tables in the configuration worksheet. Use the Get Related Tables option found on the Actions tab of the ribbon bar to add new tables to the worksheet.
Line Type
field. Write Sales Area
in the Name
field. Use the Move Up option from the Actions tab of the ribbon bar to move the new line to the first position.Master Data
, Secondary Data
, and General Settings
groups. Use the Move Up and Move Down options to rearrange the tables as shown in the following screenshot:Customer
. Then, click on the OK button.Now that you, as a partner, have defined the migration structure, it's time for the users to complete the migration by providing their data.
The partners are responsible for creating the migration structure according to company's needs. We have done that in the previous section. Then, the rest of the work can be done by the end users. Therefore, you will need to train the end users on the steps explained in this section.
To provide data for the new company, you can follow the ensuing steps:
New Company
that we created earlier in this chapter.3
, Payment Terms
. The Package Table FactBox shows us some information, as shown in the following screenshot:For instance, we can see that 6 records were included in the package.
Before applying it to the database, you can delete or insert lines or modify the existing ones.
Payment Method
table.Status
column for both tables and select the option Completed.Customer
table, open the Config. Worksheet page and select the line for the Customer
table. Click on the Export to Template option on the ribbon bar. The system will export the template, and will open the Excel file.Do not change the columns in the worksheets. If they are moved, changed, or deleted, the worksheet cannot be imported into Microsoft Dynamics NAV.
Customer
table.Templates are used to fill in preconfigured data. When you import data such as items into Dynamics NAV, you only enter general information, such as item number, description, and price, and then collect the rest of the mandatory field data from a template.
You usually create templates for the master data, such as customers, vendors, and items master data.
In this section, we are going to see how to create a configuration template and how to use it while importing data into Dynamics NAV.
Each template consists of a header and lines. On the header, you specify the table related to the template. On the lines, you specify which fields are included in the template and their default values.
Follow the ensuing steps to create the configuration template shown in the preceding screenshot:
Code
field, enter a unique ID for the template. On the Description
field, enter a description.Table ID
field, enter the table to which this template has been applied.Using configuration templates with RapidStart Services is as simple as selecting the template we want to use on a line of a configuration package. The following steps will demonstrate this:
And we are done! When importing new customers using the package, the template will be applied:
Configuration questionnaire is used to collect data from users to help configure a new company. You can create a list of questions and provide it to the users as an Excel or an XML file. When the user completes the questionnaire, you import the file into the new Microsoft Dynamics NAV company and then apply it to the database. Follow the steps described in the following section to create and complete configuration questionnaires.
Follow the steps listed in this section to create a configuration questionnaire:
What is the name of <data being collected>
. As needed, you can also delete questions that you do not want to include in the questionnaire.In the following screenshot, you can see an example of a questionnaire for the inventory setup area.
XLSX
file that you have saved.We have already covered the RapidStart Services. Before moving to another tool to migrate, there are a few things you should know about RapidStart Services:
An XMLport is a Microsoft Dynamics NAV object type used to import and export data encapsulated in XML format. Fixed text and variable text formats are also available on an XMLport to import and export data from a plain text file, just as we used to do with dataports (a Dynamics NAV object type that has been discontinued in the previous release of the application). XMLports have their own designer, XMLport Designer , which can be found in Object Designer .
Using XMLport Designer, we will specify all the XML tag names and their type (element or attribute). We will also map those tag names to data structures (tables, records, or fields) in the Dynamics NAV database.
We will create an XMLport to import customers, just as we did on the Configuration Worksheet section. By performing the same example with both the tools, we will be able to compare them and have some elements to decide which one we will use in our migrations.
We will be importing the following data into the Customers
table:
To understand the XMLport structure, we will create a new XMLport as an example, using the following steps:
The following are the elements of our XMLport:
For both table and field source types, we can click on the up arrow that appears in the column to select the appropriate Dynamics NAV data structure. When a table source type has been selected, a list of Dynamics NAV tables will be shown. When a field source type is selected, a lookup field will appear for us to select a field in any of the tables selected as table source type on the XMLport. When using a text source type, the information imported from the XML document will be put in a text variable with the name specified in the Data Source column. This variable can be used as a global C/AL variable.
Child nodes have to be indented under their parent elements using one indentation per level. To indent elements, use the left and right arrows that can be found in the lower right corner of the XMLport Designer window. Nodes have to be entered in the exact same order that they appear in the XML document.
If you check the XMLport properties by placing the cursor on the first empty line of the XMLport and clicking on View | Properties (or pressing the Shift + F4 key combination), you will see a property called format, which is set to xml
. Other options for this property are variable text and fixed text. By selecting either variable text or fixed text, you will be able to import/export data in a plain text format rather than in an XML format.
We will be importing a file called Customer.xml
that has the following structure and data:
<?xml version="1.0" encoding="UTF-16" standalone="no"?> <CustomerList> <Customer> <Name>GDE Distribución S.A.</Name> <Address>Plaza del mercado 192</Address> <City>Barcelona</City> <SalespersonCode /> <PaymentMethodCode /> <PaymentTermsCode>CM</PaymentTermsCode> </Customer> <Customer> <Name>Sellafrio S.L.</Name> <Address>Rambla de Teruel 153</Address> <City>Sabadell</City> <SalespersonCode /> <PaymentMethodCode>BANK</PaymentMethodCode> <PaymentTermsCode>CM</PaymentTermsCode> </Customer> </CustomerList>
To import the file, follow these steps:
Check the customer list to see the records that have been created by the XMLport. You will notice that the OnInsert
and OnValidate
triggers for each of the fields, have been run (each customer has a number, so the OnInsert trigger has been run, and the field Search Name has been filled in, which means that at least the OnValidate trigger for the Name field has been run as well).
With an XMLport, you can write your own code to handle multiple situations. You can either write data on multiple Dynamics NAV tables or create secondary records while importing master data.
In our example, you can write code to create new payment methods if the payment method code filled for one customer does not exist on the database.
XMLports do also offer the capability of importing data into different Dynamics NAV tables that have a link relation between them, such as in a Sales Order table. In a Sales Order table, data has to be imported into the Sales Header and Sales Line table, which have a header/line relation through the Document Type and Document No. fields.
Imagine we have an XML document, like the one shown in the following screenshot, which we want to import into Dynamics NAV.
We analyze the XML document tag structure and decide that we will have to import the data into the Sales Header and Sales Line tables, and we design an XMLport with the following structure:
In this XMLport structure, we have used all XML tags detected on the XML document and we have mapped them to Dynamics NAV tables (the SalesHeader element is mapped to the Sales Header table and the SalesLine element is mapped to the Sales Line table) and Dynamics NAV fields in the corresponding tables.
Note that the Date tag, which has been mapped to the Order Date field of table Sales Header has a node type of attribute. We have designed it that way because, while analyzing the XML document, we have seen the Date tag as an attribute of the preceding tag, SalesHeader.
In the properties of the SalesLine tag, which is mapped to the Sales Line
table, we have indicated that this tag has a link relation with table Sales Header, we have specified which fields offer the link in the LinkFields property, and we have set the LinkTableForceInsert property to Yes
. This means that we force the record on the link table (Sales Header
) to be inserted before we start writing anything into the linked table (Sales Line
).
All of this won't be enough. We will need to write some code to fill in some fields that do not appear in the XML document but are needed in Microsoft Dynamics NAV to create a Sales Order
table.
For example, we will have to fill-in the Document Type field in both the Sales Header and Sales Line tables. We will have to fill-in the Type field in the Sales Line table. We will also need to find the customer number as only the name of the customer appears in the XML document, but in Dynamics NAV we will have to inform the Sell-to Customer No. field as well. Now, declare the global variables as shown in the following screenshot:
Write the code as stated in the following screenshot:
When initializing the Sales Header
record, assign Order
as the Document Type field and assign an initial value of 10000
to the global variable LineNo
.
Assign the Document Date field the same value as the Order Date field.
Find the customer number by setting a filter on its Name field and assign it to the Sell-to Customer No. field, as shown in the following screenshot:
When initializing the Sales Line
record, assign Order
as the Document Type field, Item
as the Type field, and the value of global variable LineNo as the Line No field. Then increment variable LineNo
to be used in the next line.
Save and compile the XMLport with the number 50003
and the name Import Sales Order
.
Run the XMLport and take a look at the Sales
Order
that has been created:
Everything seems to be fine, except the order and document dates, which were set to 18/01/12
in the XML document but have the value 26/01/2012
in the Sales Order table.
To find out what happened, you can run the import codeunit for XMLport 50003 Import Sales Order again, with the debugger option turned on. If you follow the code in the debug mode, you will see that the order and document dates are first set to 18/01/2012
, but, when the OnInsert
trigger for the Sales Header table is run, they are set to the date, Workdate
, which in the example is 26/01/2012.
We will have to change something in our XMLport to prevent this behavior. What we will do is save the Order Date field in a global variable and validate it against the table field after the OnInsert
trigger is run.
Create a global variable named OrderDate as the Date field. Modify the code in the XMLport to insert the highlighted code lines in the Date – Import::OnAfterAssignField()
trigger.
Also add the highlighted code line in the SalesLine – Import::OnAfterInitRecord()
trigger.
When the Date tag is assigned to the Order Date field, we can also do assign it to a variable called OrderDate
.
When the sales line record is being initialized (it means the OnInsert trigger for table Sales Header has already been run), we once again assign the saved date to the Order Date, Document Date, and Posting Date fields and we modify the Sales Header record.
Back in Microsoft Dynamics NAV, if we take a look at the Sales Order table that has been created, we will see that, finally, all data is correct.
The tools provided by Microsoft Dynamics NAV to import data only allow you to import data in a very specific Microsoft Office Excel format, in an XML format, or in plain text.
What if we have the data in a completely different format? In that case, you probably have two options:
You can use a codeunit
, a report, or even a page to write your own code. You will find several examples in the Dynamics NAV code on how to read from files or how to use the Excel Buffer
table to read from an Excel file. Use variables of type record
for as many tables as you have to import data to.
We will not be giving any examples on how to develop a tool to import data as it is not within the scope of this book. We just wanted to point out that this is always an option, although if possible, it is better to use the tools provided by Dynamics NAV. That will probably save you a lot of time.