Microsoft Dynamics NAV, since its 2013 release, is completely configured and tuned. A range of brand new functionalities have been developed and everything is ready for you to go live. There's only one thing missing in the database - the data!
In this chapter, we will see which tools can be used in Dynamics NAV to migrate data into the system and how to convert the 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. You 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 the Dynamics NAV requirements.
We'll go through the different tools available in Dynamics NAV to migrate the data. We'll also explain how to write our own tools if the ones provided out of the box do not meet our requirements or expectations.
RapidStart Services is a new feature of Microsoft Dynamics NAV that was released in Dynamics NAV 2013. It allows you to configure your company data using out-of-the-box features that are built in. Microsoft Dynamics NAV also allows streamlined importing of opening balances into journals, and also allows you to open documents with dimensions.
With RapidStart Services, you can set up the tables involved in the configuration process of new companies. You can create a questionnaire to guide you and your customers through the collection of setup information. Your customers have the option of using the questionnaire to set up application areas on their own, 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 Department | Departments | Administration | Application Setup | RapidStart Services for Microsoft Dynamics NAV.
The following components can be used to set up a new company:
Let's work through these components by following a step-by-step example. Before starting with the example, you need to create a new company, and you may have an easier time if you change your role to that of RapidStart Services Implementer.
In this example, we will do the following:
The following steps will guide you through to creating your new company within PowerShell:
New-NAVCompany
.My New Company
.DynamicsNAV90
, otherwise, run the Microsoft Dynamics NAV 2016 Administration and check the services that are running.You have now entered in the My New Company section.
Follow the ensuing steps for changing User Personalization:
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 also load your company logo by right clicking on the Picture box.
You can go through all the tabs of the page, entering the required information. The Select Package tab is mentioned in the Applying a configuration package section, next.
There are more than 200 tables that can be considered as configuration tables. If you intend to utilize all the features in Dynamics NAV, you need to fill them in when you create a new company.
You will find almost 50 tables with the word setup as their description, but there are many other tables that can also be considered as setup tables. Here you can see a list of some setup tables:
Having to edit all these tables manually on each implementation can take a long time. Fortunately, this is where RapidStart package can help by speeding up this process.
The best approach is 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 functionalities and one package for the finance functionalities. Another approach can be to create one package for each type of data. For example, you can create one package with the data related to all the posting groups found in the application and one package for all the master data.
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 the 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:
RAPIDSTART
file that you can save. Go ahead and save the file somewhere on your computer so we can import it into My New Company.In the previous section, we created a new configuration package. In this section, we are going to apply this package to the company My New Company; the new company we created earlier in this chapter.
Follow these steps to apply the configuration package:
The data contained in the configuration package has now been imported to the new company. You can also import packages from the packages page we saw while creating the configuration package.
In the previous section, we created a configuration package and imported all the tables and fields that were within that package. But what if we want to migrate specific tables but use the same field setups in the package? For the purpose of eliminating the need for creating a different configuration package for each table that we want to convert, this is where the configuration worksheet comes in.
The configuration worksheet allows you to migrate specific tables using a specific configuration package that we setup in the previous step. You can plan, track, and perform your own data imports instead of asking developers to create XMLports to import data into NAV.
For those of you who have used the previous versions of Dynamics NAV, the configuration worksheet is the old migration tool with some new features.
The configuration worksheet is used 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. This is also very handy if you're going to be importing data from existing Excel sheets that the users are working with.
Basically, the configuration worksheet allows you to select and choose specific tables to import/export using the configuration package.
Let's explore how the configuration worksheet works by importing the Customer
table and all the related tables into our new company.
Make sure you're currently working in My New Company
. As described previously, we must first open the configuration package and setup a new package.
CUSTOMER
.18
for the Table ID field.Perform the following steps to define the tables in a configuration worksheet:
BOOM! We're done! You're now ready to use this configuration to copy the customer data from CRONUS
to My New Company
. All you need to do is click on the Copy Data from Company button to copy the customer data over.
We proceed by performing the following steps:
Dynamics NAV is built based on a relational database. In the case of the Customer
table, there are a lot of tables that are related; for example, Payment Terms Code
. Just copying over the Customer
table will not allow us to function property on our new company.
Prior to RapidStart, you would've had to find out all the tables that the Customer
table is related to by going into the Development Environment. Fortunately, there's a function called Get Related Tables in the Config. Worksheet and the configuration package that takes care of this for us.
Select the Customer record from the Config. Worksheet and click on Get Related Tables. This will populate all the related tables onto our worksheet.
One thing you'll notice after you get the related tables is that Package Code is all blank. To assign a package to all the records, do the following:
Assigning the package to the tables on the worksheet will also populate the fields that are related to the tables.
We used the Copy Data from Company function to copy the data from a company that's setup within Dynamics NAV. Suppose we want to migrate the data from an external source. We wouldn't want to use the copy data functionality because that data is not even in Dynamics NAV yet!
We can utilize Dynamics NAV to export the structure that we've defined on the configuration package and configuration worksheet into Excel. Then you can either copy/paste the data, or manually enter the data into Excel and import it back in to NAV. To do so, perform the following steps:
My New Company
.Payment Terms
package data:My New Company
.Templates are used to default data on some fields when we import data into Dynamics NAV. There are some mandatory fields in Dynamics NAV which do not exist on the dataset that you're importing the data from, using the configuration templates; we will be able to default these mandatory fields.
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. Following is an example of a template setup for DOMESTIC
customers in CRONUS USA, Inc.
:
Let's create a new template on our own and apply it to an existing configuration package. While you're in My New Company
, do the following:
CUSTTERM
. In the Description field, enter a description.N30
.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, as can be seen in the following screenshot:
The configuration questionnaire is used to collect data from the 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.
The idea behind the configuration questionnaire is to allow the NAV partner to bypass speaking to customers directly and automate the setup. Why on earth would any partner do this to a customer? Simple, if your NAV solution is so vertical that you can practically set it up blindfolded, you can probably write down all of your knowledge and have the system set itself up.
Follow the steps described in the following section to create and complete a configuration questionnaire.
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 the questions that you do not want to include in the questionnaire.In the following screenshot taken from CRONUS USA, Inc.
, you can see an example of a questionnaire for the inventory setup area:
Once you have the configuration questionnaire thought out, you can export it to Excel and send it off to the customers to fill in. Change to the CRONUS USA, Inc.
company to see a list of the premade questions.
CRONUS USA, Inc.
company.XLSX
file that you have saved.We have already covered 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, as shown in the following screenshot:
By using XMLport Designer, we specify all the XML tag names and their types (element or attribute). We also map these 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 in 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 should use in our migrations.
We will be importing the following data into the Customers
table:
Name
Address
City
Salesperson code
Payment terms code
To understand the XMLport structure, we will create a new XMLport as an example, using the following steps:
50001
and a name, Import Customer Data
.The additional elements are indented using the icon and not by using the Spacebar. There are many details on programming Dynamics NAV in the Development Environment. I highly recommend Programming Microsoft Dynamics NAV 2015 published by Packt Publishing.
The following are the elements of our XMLport:
For both the 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 is selected, a list of Dynamics NAV tables is shown. When a field source type is selected, a lookup field appears for us to select a field in any of the tables selected as a table source type on the XMLport. When using a text source type, the information imported from the XML document is 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 the 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 in which 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. The 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. Let's leave this property alone for now.
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 /> <PaymentTermsCode>N30</PaymentTermsCode> </Customer> <Customer> <Name>Sellafrio S.L.</Name> <Address>Rambla de Teruel 153</Address> <City>Sabadell</City> <SalespersonCode /> <PaymentTermsCode>COD</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 the master data.
In our example, you can write code to create new payment terms if the payment terms code filled for one customer does not exist on the database.
XMLports 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, that 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 the 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 the Sales Header table, has a node type of attribute. We have designed it that way because, while analyzing the XML document, we can see the Date tag as an attribute of the preceding tag, SalesHeader.
The following screenshot shows the properties page of SalesLine. 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 the Sales Header table, 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 in the linked table (Sales Line).
The data provided to you may not be enough. Different software have different fields that they consider mandatory and Microsoft Dynamics NAV is no different. Therefore, we will need to write some code to fill-in those 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 also have to fill in the Type field in the Sales Line table. In addition, we will 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:
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 the global variable LineNo
as the Line No
field. Then the increment variable LineNo
should 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:
Note that this is a very fast example of how to write code in the XMLport. As previously mentioned, to get a complete guide on programming for Dynamics NAV, refer to Programming Microsoft Dynamics NAV 2015 published by Packt Publishing.
Everything seems to be fine except the dates, which were set to 01/18/12
in the XML document but have the value 2/16/2016
in the Sales Order
table.
The reason is because although the order and document dates were first set to 01/18/2012
, but when the OnInsert
trigger for the Sales Header
table was run, they get defaulted to Work Date
, which was 02/26/2016
. How do we know this? You can turn on the debugger (which will be covered in Chapter 11, Debugging, of this book) and follow through the code, or just ask a NAV developer.
Either way, 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 assign it to the variable named OrderDate
.
When the sales line record is being initialized (it means the OnInsert
trigger for the 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 the 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 you have the data in a completely different format? In that case, you 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.