![adventureworks2012 items table adventureworks2012 items table](https://www.essentialsql.com/wp-content/uploads/2014/08/Object-Explorer-Tables.png)
This extension will be discussed in a next post.This article is the second of a four part series: To achieve that, we create a custom activity SQL.READ, which has a connection string and a SQL query as parameters, and which returns the result set as an XML document. Of course it would be great if instead of using a temporary file, we could query directly from SQL Server and import into NAV.
#ADVENTUREWORKS2012 ITEMS TABLE GENERATOR#
For the CommonItemNo target element, map this on ProductNumberĬonfigure the Record Generator Activity to use the NAVItems internal document.For the ReorderPolicy target element, map this on a constant ‘ Maximum Qty.’.For the ReorderPoint target element, map this on ReorderPoint.For the Unit Price target element, map this on ListPrice.For the BaseUnitofMeasure target element, map this on a constant ‘ PCS’.For the Description target element, map this on Name.For the No target element, map this on ProductID.For the Item target element, map this on the Products/Product source element.
![adventureworks2012 items table adventureworks2012 items table](https://dataedo.com/asset/img/blog/dataedo_export_productmodel.png)
Set the target document type to Internal, and the Target Document No.Set the source document type to External, and the Source Document No.Select the appropriate line and click on the Edit button.Copy the sample file AWP.xml to the shared folder from step 5.b.Ĭonfigure the Mapper Activity between the external document and the internal document.Set the network share to a shared folder on your local disk (ensure the Microsoft Dynamics NAV Server user has sufficient permissions for the network share).Add the following elements to the connection:Ĭonfigure the File Reader Activity to use an XML file called AWP.xml.Add the created documents to the connection by clicking on the Edit button in the Documents Checkbox.AWProductImport and description Import Products from AdventureWorks Go to Departments/Business Integration Solutions/Connectivity Studio/Design/Connections.
![adventureworks2012 items table adventureworks2012 items table](https://robinphetsavongdata.files.wordpress.com/2019/06/adventure-works-dw.png)
Create manually the structure of the internal document structure, based on the screenshot below.ĭefine a connection with a File Reader Endpoint, a Mapper Activity and a Record Generator Activity.Create a new Internal document, with name NAVItems and description NAV Items.The application creates a complete external document definition based on this example.Go to Design and select Create Document from Sample file.Create a new External document, with name AWProducts and description Adventure Works Products.Open NAV 2013 and navigate to Departments/Business Integration Solutions/Documents.
![adventureworks2012 items table adventureworks2012 items table](https://benlarsonsite.files.wordpress.com/2016/04/sqlselect6.jpg)
Save the output to a file named AWP.xml.Select top 1 ‘AW’ + CAST(Product.ProductID as nvarchar) as ‘ProductID’, Product.Name, Product.ProductNumber, Product.ListPrice, Product.ReorderPointįor xml auto, elements, type, root(‘ProductEs’) Start SQL Management Studio and connect to the AdventureWorks database.Get a sample XML document from the first product in the AdventureWorks database The Microsoft SQL Server demo database AdventureWorks contains a few products (504), which I want to import into Dynamics NAV 2013. In NAV we will assign a specific unit of measure and reorder policy.
#ADVENTUREWORKS2012 ITEMS TABLE UPDATE#
We want to create or update NAV items, using the product ID, name, product number, reorder point and list price from the Products table. The scenario is to import products from AdventureWorks into Dynamics NAV.