Get next object version number

Every developer has sometimes the issue: What is my next version number for the new object ? If there is a couple of developers working in the some database, you’ll need a kind of a version control.

I’ve developed a page, which calculates the next version number for a defined version prefix. The version sytax is: <PREFIX><Main No.>.<2-digit Sub No.>.

Let’s start with a list of pages with different version list, version ARCH1.00 to ARCH1.06. It does not matter, if the version list of an object contains more than one value.

pic2

After running the page set the version prefix, here ARCH, and push action “Get next version”. We get then the new version: ARCH1.07.

pic1

You can download the page here.

 

Advertisements

How to work with big item descriptions

Sometimes it’s needed to save very long descriptions, but in Nav text fields can have only 250 characters. Additional you may want to search in these long text values. You can add a couple of these text fields to save long texts or save the text in text files and add them to the item. But what about searching? Not that easy.
An other option to save long texts is the usage of blob fields. For that option i developed a solution.

First add a new field “Description 3” to table Item, type BLOB, subtype Memo.
Then edit page Item Card, add a global variable Desc3Txt of type text with no length. Add the variable as new field to the item card, Editable=False, MultiLine=Yes.
Add following code to trigger OnAfterGetRecord in item card page:

// InStr | InStream
CALCFIELDS("Description 3");
IF "Description 3".HASVALUE THEN BEGIN
  "Description 3".CREATEINSTREAM(InStr);
  InStr.READ(Desc3Txt);
END;

Add to trigger Desc3Txt – OnAssistEdit()

// OutStr | OutStream 
// EditCtrl | DotNet | Archer.TextEdit.'Archer.TextEdit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1465b259ee2284cb' 
CLEAR(EditCtrl);
EditCtrl := EditCtrl.TextEdit;
EditCtrl.Load(Desc3Txt);
EditCtrl.ShowDialog;
Desc3Txt := EditCtrl.Save;
EditCtrl.Close;
CLEAR(EditCtrl);

"Description 3".CREATEOUTSTREAM(OutStr);
OutStr.WRITE(Desc3Txt);
MODIFY;
CurrPage.UPDATE;

itse-1

Page item card with new multiline text field “Description 3” and Assist Button.

itse-2

Clicking on Assist Button starts the TextEdit Control and loads the current text. After changing the text and closing the text control, you are asked, if you want to change the text.

Now we need the opportunity to search within the new text/blob field. For that we need a new page Item Search. That new field cannot be searched using the standard search function.

Create a new page with objectid 50000, name Item Search. Add a group under the contentarea, add a global text variable SearchString, add a new field under the group with SearchString as SourceExpr. Add another group, add a new line with type part. Later we set the value for property PagePartID.

itse-3

To show the search result we need another page: type listpart, objectid 50001, name Item Search Result. As source of the new page we need a new table: objectid 50000, name Item Search Result.

itse-4

The new page 50001:

itse-5

Properties: Editable=False, SourceTable=50001, SourceTableTemporary=Yes.

Now add global function SetData(SearchFilter : Text) to the new page. Add following code to the new function:

// local variables
// Item, Record, Item 
// ItemSearchResultLine, Record, Item Search Result 
// InStr, InStream 
// Desc3Txt, Text 
// LineNo, Integer 

DELETEALL;

LineNo := 10;
Item.FINDSET;
REPEAT
  Item.CALCFIELDS("Description 3");
  IF Item."Description 3".HASVALUE THEN BEGIN
    Item."Description 3".CREATEINSTREAM(InStr);
    InStr.READTEXT(Desc3Txt);
    IF STRPOS(LOWERCASE(Desc3Txt),LOWERCASE(SearchFilter)) > 0 THEN BEGIN
      "Line No." := LineNo;
      "Item No." := Item."No.";
      Description := Item.Description;
      "Description 2" := Item."Description 2";
      "Description 3" := COPYSTR(Desc3Txt,1,250); // first 250 chars
      INSERT(FALSE);
      LineNo += 10;
    END;
  END;
UNTIL Item.NEXT = 0;

CurrPage.UPDATE(FALSE);

Now you can set the property PagePartID in the part line in page 50000 to 50001.

For calling the search function we need a Search button in page 50000.
Add following code to trigger Search – OnAction()
CurrPage.ItemSearchResultLines.PAGE.SetData(SearchString);

itse-6

The new Item Search Page with a search result.

You can download the TextEdit Control here.

Followup:
You could simplify the solution by:
* Search page: Use only page 50001, add a second group at the top with field SearchString. So page 50000 is not needed.
* Page Item Card: remove the textedit control and the according code, set field Desc3Txt to editable, add the code to fill the blob field “description 3” using outstream to trigger Desc3Txt-OnValidate.

cheers

 

Dynamics NAV -Show table relations

In posting “The big picture” i published an upgraded version of Kamil Sacek’s tool to show all the table relations. This version needs a developer license. So it’s not useable for end users without developer license.

For that kind of end users i developed a simplified solution, which does not need a developer license. But … it does not show all table relations, but the main ones. To get the flowfield- and lookup relations the developer license version is needed.

aw10-nav2015-1

You can download the nav objects here.

cheers

 

Dynamics Nav – The big picture

Kamil Sacek developed 2008 a nice tool to view and analyse all Nav table relations. It’s a kind of a Where-used-tool, where all the relations are shown. It worked for Nav 5.0. You can download it from here.

I upgraded it to Nav 2009 and Nav 2015, reduced complexity, optimized the layout, added and reordered some fields in the table relations page/form and added needed buttons.

Installation and Usage:

  • Precondition:
    Export all tables from the database as text, save the export e.g. as tabs-60.txt. For that a developer license is needed.
  • How to use:
    1. Import the objects into your DB
    2. Run form/page 66001 Table Relations
    3. Click on button “Update table relations”
    4. Change parameters, if needed. “Fill Table” is preset (relation data is saved in table 66000).
    5. Click the “Process” button
    6. Close the Process form/page.
    7. Now you can browse the relations there. You will see all referred and all referring fields for the selected field.
  • When updating table relations you can optional export the table relation data into a text file for further analyzing or creating a diagram.

Nav 2015 Version:

bpn-2015-1

The “Update table relations” page…

bpn-2015-2

Nav 2009 Version:

bpn-2009-1

The “Update table relations” form …

bpn-2009-2

Downloads:
BPN2.0 for Nav 2015
BPN2.0 for Nav 2009

 

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):
– Sets 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);

// release memory
clear(XlsWorkbook);

You can download XlsLib here.

Hint: When downloading that file, it can be, that you have troubles using it because of security issues, e.g. dll is not visible in list of assemblies, you get an error message like “Cannot open xlslib, because one or more dependencies are not met”, methods are not visible or something else. then start windows explorer, goto folder were you copied the lib files, view file properties. if you see a notice at the bottom of the file properties window like “file is blocked because downloaded from internet, grant access?”, then click on “grant access”.

cheers

Where-Used-Tools for Dynamics Nav

Dynamics Nav does not ship with a Where-Used-Tool, although it is often needed. A Where-Used Tool is a tool, with which you can check, which nav objects use/reference a certain nav object or a certain table field.

Idyn has developed a widly used Toolset, the Object Manager Advanced (OMA). It is very helpful, contains that kind of tool, also a version/source control, rename tool, a.s.o. but it has it’s prize. 😦

Other Where-Used/Search tools:

  • Statical Prism“: free community version available, cheapest full version you can get for $37.
  • GDT Where Used“: This small free tool lists all Nav objects and has a “Where used” function. It supports Nav 5, Nav 2009, Nav 2013. . Looks very nice, it’s worth to test it.

02/2016, News about “GDT Where used”: There is a new version, which now supports also Nav 2015 and Nav 2016.