NAV 2013 Universal XMLport

After introducing a dataport that can be used to quickly bring data into any NAV table without coding, this great little tool is almost immediately obsolete with the release of NAV 2013. Luckily, we have a solution for that: The Insight Works’ Universal XMLport.

The two biggest problems with the Rapid Start tools are that they’re quite slow, and there’s a bit of a learning curve to get them up and running. Once you have some packages set up for a master data set, they work great! Another new option for data import is simply pasting data from Excel into NAV, which works great for smaller data sets and has been done for years using NavXtender.

• Quickly import tab-delimited text files into any NAV 2013 table without any development or configuration.
• Much faster than pasting from Excel or using the data migration tools.
• Import Record Notes and Record Links.
• Import item tracking lines (serial/lot numbers) on the item journal and includes the ability to run triggers.
• Import serial/lot numbers, include the serial no. and/or lot no., expiration date, or warranty date in the import for table 83 (Item Journal Line).
• Run OnInsert and OnModify triggers.
• Works with 2013 and 2013R2.

A simpler solution

What do you do if you need to quickly bring a larger data set into NAV 2013 today? Simple, use the Insight Works Universal XMLport! NAV 2013 eliminates dataports, but enables users to run XMLports directly from the RTC. Much like the Universal Dataport, all users need for the XMLport is a tab-separated file (usually generated from Excel or SQL Server Management Studio), and a table number. The XMLport will import all the data from the text file, either inserting new records or updating existing ones; field validation and disabling the change log are also supported.

To use the XMLport, simply import the object and either add the XMLport to the menu as a Task, or run it directly from the Object Designer. If you add it as a menu item, you can also add it to the ribbon of the Role Center by right-clicking the menu item. Once running, the XLport user interface looks like this:

Description of fields

• Filename: The name of the tab delimited text file to import. This is optional, as NAV prompts users for a filename when hitting OK anyway. However, if this is filled in, users can click “open” when NAV prompts users again.
• Import table number: This is the NAV table ID to import data into. If users click the “assist edit” button beside the lookup, the XMLport will launch the table selected, which will open in another instance of NAV.
• Enable record inserts: If checked, new records will be created.
• Enable record updates: If checked, existing table records will be updated. This will only work if the user’s file includes the record’s primary key value.
• Validate fields: If “only validate fields prefixed with ‘*’” is selected, the NAV data validation logic will only be called for fields that begin with an asterisk on the first row of the import file. If the “validate all fields” option is chosen instead, the validation logic will be called for all fields.
• Disable change log: Unlike earlier versions of NAV, NAV 2013 logs programmatic record changes to the change log. If users enable the “disable change log” option, the XMLport will disable the change log during the import and re-enable it when done (assuming it was originally enabled). This can improve import performance significantly.

Sample import

A basic import into the NAV “location” table appears in Excel:

The first row tells the Insight Works Universal XMLport to import values into four NAV fields; “Code”, “Name”, “City” and “Phone No_”. The asterisk in front of the “code” fieldname would indicate to the XMLport to validate this field if the “Validate all fields” option was not enabled.

NOTE: The XMLport will accept field names in the NAV format (using punctuation as in “Phone No.”), or in the SQL format that replaces punctuation with underscores (like “Phone No_”).

Saving this spreadsheet as a tab delimited text file would yield the following file which can be imported into NAV:
*code name City Phone No_
Insight Works Edmonton 780-988-0215

Additional information:

• If users are importing Contacts, they can include the “No.” field in the header row but leave the values blank/empty and the XMLport will automatically use the Contact number series for you. This functionality could be extended to other tables fairly easily.
• NAV will attempt to validate fields in the order it encounters them (from left to right) so be sure to lay out the import file to avoid validation problems based on field ordering. For instance, if users are importing into the item journal and NAV encounters the quantity field before the item number field the validation will fail with an error.
• When importing into the Item or Resource table the base unit of measure will automatically be added to the item unit of measure or resource unit of measure table for users, so they can import into these tables in one step.
• Be careful with tools like Excel that might insert double quotes around columns containing certain text data when it creates a tab delimited text files.
• Watch out for duplicate data when running with the “allow record inserts” option.