Copying data to-and-from between Excel & Navision

Great posting from Ashwini Tripathi about copying data between Nav and Excel with Nav Standard Processes:

One of my reader has requested to show him how to export data from Nav Journal to Excel, perform correction and import back to Navision. So let us see how can we perform this and what are the limit…

Source: Copying data to-and-fro between Excel & Navision

Project Madeira – August 2016 Update

Great news about Project Madeira (Dynamics 365 – Business Edition)

Maderia-1

There were added a couple of new features, which make the whole thing smoother and much more usable:

  • Smart Notifications
  • Sales and Inventory Forecast extension
  • Sales and purchasing improvements (No big, but important changes)
    • Cancel posted credit memos
    • Package Tracking No. and Shipping Agent Code on sales invoices
    • Cross reference item numbers
    • Item substitution

For details read the great posting from , Senior Program Manager at Microsoft: https://www.linkedin.com/pulse/project-madeira-august-update-fredrik-hietala

cheeers

 

 

How to display sum value in factbox on base of multiple line selection

Typical issue is to show a calculated value in a factbox on base of the currently selected line in a list page or the lines in a subpage. Is it possible to get a calculated value on base of more than one selected line? Let’s check out.

Let’s say we want to display the sum of field “Credit Limit” in the customer list when selecting multiple lines. So first we edit page 9082 “Customer Statistics FactBox”. There we add a global variable SumCredLimit (decimal) to the page, then we add a new line in page with sourceexpr = SumCredLimit.

ccl1.png-750x491

Now we add a new global function SetSumCredLimit.

ccl2.PNG-594x65

Save and close the page. Now we edit page 21 customer list. There we rename page part “Customer Statistics FactBox” to CustStatFbx. That’s needed, so that we can reach the custom functions in page “Customer Statistics FactBox” from page “customer list”.

ccl3.png-750x571

After that edit the page code (F9). Goto trigger OnAfterGetCurrRecord, there add local variable Cust (record|Customer). Add following code to trigger:

ccl4.png-750x291

Save and close the page. Now run the Nav windows client and goto customer list.
The result:

ccl5.png-750x485

You see, the new value in the factbox shows the sum of the Credit Limit values of the 3 selected lines.

cheers

Simple Read/Write Excel Data

In a Nav forum there was a question, how to read data from an excel document, change the data in Nav, write data back to the excel document without changing the existing cell formatting. With table ExcelBuffer and the helper assemblies delivered with Nav this cannot be done, because the Write functions e.g. SetCellValueText work with decorators.

So i developed assembly XlsLib. With that assembly read and write actions from an excel document are done very easy. The provided methods are very simple. You can read the cell values, the basic number formats and save it to table Excel Buffer. When writing only the value as is is written back to the cell. No further formatting is read or written.That can also be used, when you work with templates.

Main functions:

  • XlsWorksheet.GetValue(rowId, colId, value, type):
    • Get a value from cell rowId/colId,
    • given value is of type text,
    • type is of datatype integer, can be 0-3 according option field NumberFormat in table ExcelBuffer, Type=4 means Unknown
  • XlsWorksheet.SetValue(rowId, colId, value):
    • Set a value in cell rowId/colId, value can be of any type.

Here is a sample code:

TestXlsLib()
// variables
//  XlsWorkbook DotNet XlsLib.Workbook.’XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a’
// XlsWorksheet DotNet XlsLib.Worksheet.’XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a’
// value Text
// type Integer

// open excel workbook
XlsWorkbook := XlsWorkbook.Workbook(‘c:\temp\test.xlsx’);
// select sheet ‘Sheet2′
XlsWorksheet := XlsWorksheet.Worksheet(XlsWorkbook,’Sheet2’);

// read cell values from row=1, column=1-3
XlsWorksheet.GetValue(1, 1, value, type); // e.g. 1234
XlsWorksheet.GetValue(1, 2, value, type); // e.g. 10.10.1990
XlsWorksheet.GetValue(1, 3, value, type); // e.g. 99.12

// Write some values
XlsWorksheet.SetValue(1, 1, 5432);  // an integer value again
XlsWorksheet.SetValue(1, 2, 010175D); // again a date value
XlsWorksheet.SetValue(1, 3, 34.45); // a decimal value

// read values again to check changes
XlsWorksheet.GetValue(1, 1, value, type);
XlsWorksheet.GetValue(1, 2, value, type);
XlsWorksheet.GetValue(1, 3, value, type);

// Save: Save to loaded file filename
//XlsWorkbook.Save();
// SaveAs: create a copy, save result to new file
XlsWorkbook.SaveAs(‘c:\temp\test2.xlsx’);
// Close Excel App !!
XlsWorkbook.Close();

// if an error occurs …
IF XlsWorkbook.Error <> ” THEN
MESSAGE(XlsWorkbook.Error);

You can download XlsLib here.

cheers

 

Options in Report Request Page

This posting shows how to add option fields to a report’s request page.

I developed a simple report on base of table Sales Header.

sh-list-1

To filter by field Posting Date you can simply add that field to report data item “Sales Header”‘s property ReqFilterFields, but in this sample we check out how to handle complex filter processing.

Add 2 global variables of type Date: StartDate, EndDate.

sh-list-2

Now edit the request page (Menu View/Request Page), add a container line, a group line and 2 field lines to the request page, set the SourceExpr in line 1 to StartDate, line 2 to EndDate.

sh-list-3

To apply the filter fields add following code to trigger Sales Header – OnPreDataItem.

sh-list-4

If both filter fields are filled, the data is filtered: StartDate<=Posting Date<=End Date.
if only StartDate has a value: StartDate<=Posting Date
if only EndDate has a value: Posting Date<=EndDate

When running the report, we get:

sh-list-5

cheers

 

Check License state of Objects

In Nav forums it’s quite often asked, how to check, if a object is within the current loaded license. There are some solutions for Nav 2009. I did not find a satisfying solution for newer Nav versions. So i’ve developed a “License Permission” Page, which lists Objects and their Permissions. Object IDs, which are not in the current loaded license, means there is no read or execute permission for that Object ID, are displayed red. When running the page the list of all nav objects in the database is loaded.

licperm

The buttons in the page:

licperm2

Reload Base Objects: List all current database Object IDs
Swap Read Filter: Switch between 3 Read Permission Filter settings ” “, Yes|Inherited, No Filter
Swap Execute Filter: same as button above for Execute Permission
Custom Objects Filter: List objects between Object ID 50.000 and 99.999.

licperm3

The object range buttons:

View Table Range: List all table Object IDs according the filter you set.
View Page Range: List all page Object IDs according the filter you set.
View Report Range: List all report Object IDs according the filter you set.
View CU Range: List all codeunit Object IDs according the filter you set.

You can download the page source code here.

The license information is saved in table “License Permission”. With a page on base of that table the windows client freezes, because the data load is really huge, especially, when filtering. So i developed that kind of solution.

cheers