Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.
All utilities and downloads are available at www.dmsiworks.com
Data is the currency of the information age, so it should come as no surprise one of the more frequent tasks integrators, users, and system administrators encounter when implementing or maintaining a Microsoft Dynamic NAV installation is data migration. The four common approaches to getting the data from where it resides into NAV are: manual entry in the NAV client, using SQL server’s database tools, using NAV's Excel mapping, or via a NAV Dataport or XMLport.
- Manual entry is the low-tech approach, but can be beneficial in cases where the information exists in a non-electronic format or if a low volume of unusually formatted data needs to be entered. A major downside to manual data entry is the opportunity for random data entry errors, which studies have shown occur roughly once for every 300 keystrokes.
- SQL Server’s tools provide simple and robust data manipulation and formatting capabilities, but using it to insert or update directly into NAV bypasses the business logic and can result in any number of issues if the user doesn’t know and replicate what NAV would normally do behind the scenes.
- NAV's Excel Mapping tools are fairly easy to use from an end-user point of view, but require a separate setup for each table that can be tedious to configure, and require a very specific Excel sheet layout. Once they're set up, they can be very slow to run - significantly slower than a Dataport or SQL. They do have the advantage of providing validation on the incoming data and letting the user review any errors that have occurred before committing the data to the database.
- NAV’s Dataport and XMLport can be tedious when having to select all the import fields, but have the benefit of having the imported data validated by the business logic. However, this validation can also create additional work, such as an initial import into the Item table. You can’t provide a base unit of measure or the validation on that field will fail, so you’re forced to import the item without it, create the item unit of measure, and then import the item again and supply the base unit of measure.
At its core the DMS Universal Dataport is a NAV dataport that automatically sets the fields to import based on the values that are present in the first row of the tab delimited import file. Users have the ability to select which fields the NAV business logic should validate, can specify if new records should be inserted or if existing records should be updated.
It's true that NAV 2013 will do away with dataports completely, but the Universal DataPort is great for any pre-2013 databases, and all the code in it is easily ported to an XMLPort (stay tuned - we'll be releasing that shortly).
DMS Universal Dataport
Description of fields:
Filename: The name of the tab delimited text file to import
Import table number: The NAV table ID to import data into
Allow record inserts: If checked new records will be created.
Allow record updates: If checked existing table records will be updated.
Use change log if set up: If checked the actions of the DMS Universal Dataport will be logged in the change log (if it is configured to log the table and fields being imported or updated).
Validate fields starting with “*”: If checked the NAV data validation routine will only be called for fields that begin with an asterisk on the first row of the import file.
Validate all fields: If checked all fields that exist in the import file will have their imported values validated.
Sample import:
A basic import into the NAV “Location” table appears in Excel below:
The first row tells the DMS Universal Dataport 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 Dataport to validate this field, assuming the “Validate all fields” option was not enabled. Note that the Universal Dataport 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_
DMS Dynamic Mfg. Solutions Edmonton 780-988-0215
Additional information:
- If you're importing Contacts, you can include the "No." field in the header row but leave the values blank/empty and the Dataport 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 you 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 you, so you can import into these tables in one step.
- Make sure to include fields that match one of the NAV table keys for the table you are updating.
- 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.
Extending the Dataport:
Though the Dataport functions very well as-is, there are a lot of possibilities for extending what it can do, including:- adding more default subtable insertions (like what the DataPort does for units of measure on the Item table)
- launching user-specified Codeunits for pre- or post-processing imported records
- creating a reconciliation screen that users can review before committing changes to existing records
Dynamic Manufacturing Solutions
No comments:
Post a Comment
Your comment will be posted once approved.