IT50 – Importing/Exporting data in NAV 201 - Advanced

IT50 – Importing/Exporting data in NAV 201 - Advanced Jason Chance, Seventh Generation Lewis Rosenberg, Mars Fishcare

IT50 – Importing/Exporting data in NAV 201 During this NAVUG “How To” session, we will demonstrate some advanced techniques for importing and exporting data from Dynamics NAV.

IT50 – Importing/Exporting data in NAV 201 We’ll add logic to a dataport to perform some advanced functionality and data validation. We’ll show you how to modify a simple NAV Object Designer Report to Export data to Excel. Finally we’ll demonstrate a custom flatfile integration tool developed at Seventh Generation Inc.

Demonstration: NAV 4.0 Report Designer : Add Print to Excel Lewis Rosenberg

Demo: Add Print to Excel to Rpt • Add Three Global VARs • PrintToExcel (Boolean) • FirstExcel (Boolean) • ExcelBuf (Temp Record)

Demo: Add Print to Excel to Rpt • Add Four (or more) Functions • MakeExcelInfo • MakeExcelDataHeader • MakeExcelDataBody • CreateExcelBook

Demo: Add Print to Excel to Rpt • Add a Check Box on the Request Form • Set the Caption • Set the SourceExpression

Demo: Add Print to Excel to Rpt • Add SECTIONS logic: • Header (1)/Presection: IF PrintToExcel AND NOT FirstExcel THENBEGINMakeExcelInfo;FirstExcel := TRUE;END;

Demo: Add Print to Excel to Rpt • Add SECTIONS logic: • Report – OnPostReport()IF PrintToExcel THENCreateExcelbook; • Report – Body(1) -OnPreSection() IF PrintToExcel THENMakeExcelDataBody;

Demo: Add Print to Excel to Rpt • Add SECTIONS logic: MakeExcelInfo() ExcelBuf.SetUseInfoSheed; ExcelBuf.AddInfoColumn(FORMAT('Company Name'),FALSE,'',TRUE,FALSE,FALSE,''); ExcelBuf.AddInfoColumn(COMPANYNAME,FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.NewRow; ExcelBuf.AddInfoColumn(FORMAT('Report Name'),FALSE,'',TRUE,FALSE,FALSE,''); ExcelBuf.AddInfoColumn(FORMAT('Put Report Name Here'),FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.NewRow; ExcelBuf.AddInfoColumn(FORMAT('Report No.'),FALSE,'',TRUE,FALSE,FALSE,''); ExcelBuf.AddInfoColumn(REPORT::"Put Report Name Here",FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.NewRow;

Demo: Add Print to Excel to Rpt • Add SECTIONS logic: MakeExcelInfo() ExcelBuf.AddInfoColumn(FORMAT('User ID'),FALSE,'',TRUE,FALSE,FALSE,''); ExcelBuf.AddInfoColumn(USERID,FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.NewRow; ExcelBuf.AddInfoColumn(FORMAT('Date/Time'),FALSE,'',TRUE,FALSE,FALSE,''); ExcelBuf.AddInfoColumn(TODAY,FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddInfoColumn(TIME,FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.NewRow; ExcelBuf.NewRow; ExcelBuf.ClearNewRow; MakeExcelDataHeader;

Demo: Add Print to Excel to Rpt • Add SECTIONS logic: MakeExcelDataHeader() ExcelBuf.NewRow; // AddColumn Parameters 1=data/label, 3=Cell Comment, 4=Bold, 5=Italicize, // 6=underline, 7= @ means no carriage return ExcelBuf.AddColumn('Item No.',FALSE,'',TRUE,FALSE,TRUE,'@'); ExcelBuf.AddColumn('Item Desc',FALSE,'',TRUE,FALSE,TRUE,''); ExcelBuf.AddColumn('Comm',FALSE,'',TRUE,FALSE,TRUE,''); ExcelBuf.AddColumn('Cost Method',FALSE,'',TRUE,FALSE,TRUE,''); ExcelBuf.AddColumn('Shelf No',FALSE,'',TRUE,FALSE,TRUE,''); ExcelBuf.AddColumn('BOM',FALSE,'',TRUE,FALSE,TRUE,''); ExcelBuf.AddColumn('Blocked',FALSE,'',TRUE,FALSE,TRUE,''); ExcelBuf.AddColumn('Qty On Hand',FALSE,'',TRUE,FALSE,TRUE,''); ExcelBuf.AddColumn('Base UOM',FALSE,'',TRUE,FALSE,TRUE,''); ExcelBuf.AddColumn('Inventory Value($)',FALSE,'',TRUE,FALSE,TRUE,'');

Demo: Add Print to Excel to Rpt • Add SECTIONS logic: MakeExcelDataBody() ExcelBuf.NewRow; ExcelBuf.AddColumn(Item."No.",FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(Item.Description,FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(Item.Comment,FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(Item."Costing Method",FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(Item."Shelf No.",FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(Item."Bill of Materials",FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(Item.Blocked,FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(Item.Inventory,FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(Item."Base Unit of Measure",FALSE,'',FALSE,FALSE,FALSE,''); ExcelBuf.AddColumn(TotalValue,FALSE,'',FALSE,FALSE,FALSE,'');

Demo: Add Print to Excel to Rpt • Add SECTIONS logic: MakeExcelBook() ExcelBuf.CreateBook; ExcelBuf.CreateSheet('Data','Put Report Name Here',COMPANYNAME,USERID); ExcelBuf.GiveUserControl; ERROR('');

Demonstration:Add Custom Logic to a Dataport Jason Chance

Dataport – Flow (single transaction) Init Trigger Request Form PreDataport Trigger Open File Get Next DataItem DataItem Imp/Exp Last Record PostDataport Trigger Close File

SVG Flat File Import Tool Why did we bother? • Dataports can’t be scheduled in Job queue. • Wanted an extra layer of data validation • Allows for easy code reuse.

SVG Flat File Import Tool Structure • Interface record • One per flat file layout. • Staging table • Import data before loading into NAV table • Mirrors flat file layout (columns.) Plus: unique key, status, error text • Single code unit that does all the text file processing

SVG Flat File Import Tool Process Flow (Executed in a NAV Report) • Files arrive via FTP, sFTP, AS2 • Files opened and read into staging table • Archive or delete file • Validate data in staging table, load into desired NAV table • Status and error text fields used here

Demonstration: SVG Flat File Import Tool Vendor Invoice Import Jason Chance

IT50 – Importing/Exporting data in NAV 201 Q&A

Thank you for attendingIT50 – Importing/Exporting data in NAV 201 - Advanced Please remember to fill out Forum Session Evaluations!

Jason Chance, Seventh Generation jac@seventhgeneration.com Lewis Rosenberg, Mars Fishcare lrosenberg@aquariumpharm.com

IT50 – Importing/Exporting data in NAV 201 - Advanced