Functional Testing in NAV 2009 SP1

Because SP1 for Microsoft Dynamics NAV 2009 was released, I tried to create short demo of the new functionality, which allows developers to create tests for their code. Official documentation for this part of NAV 2009 SP1 could be found here.

 

How it works

You have new property on codeunit –Subtype – which could be “Normal,Test,TestRunner”.

Normal – you know it… ;-)

  • Test – this codeunit have functions, which are testing the functionality, this is the main codeunit doing the testing
  • TestRunner – this is codeunit which runs the test codeunits and e.g. collecting the results. Because you can run all tests by this one codeunit, it is not problem to run the tests e.g. in NAS or through webservices.

 

If you use Test subtype, you can use new property on each function in the codeunit – FunctionType – which could be

  • Normal – again you know it…
  • Test – this is the function which tests the functionality
  • MessageHandler – handles cases, when you expect that some message will be displayed
  • ConfirmHandler – handler for Confirm dialog, in which you can simulate the answer by user (yes,no)
  • StrMnuHandler – same like ConfirmHandler but for String menu dialogs
  • FormHandler – you can handle displayed form which you expect
  • ModalFormHandler – same for modal form, you can simulate the result (Yes, No, Ok, Cancel, LookupOK, LookupCancel etc.)
  • ReportHandler – handling reports which are runned by the tested function

Using the handlers could be complex task, how to create them is described here.

Each test function could have assigned one or more handlers, and if the handler is not used during the testing, it leads to test Failed result (it means you are expecting something but it didn’t happened).

In testing function you can use new keyword ASSERTERROR before some command to say that you are expecting error in the command or block of commands. After that you can test if correct error was raised by testing GETLASTERRORTEXT. If there is wrong error text, you can call ERROR and the test function will have result Fail, else it will be Success.

TestRunner codeunit could have two functions – OnBeforeTestRun and OnAfterTestRun – which could e.g. store the date and time of start of some test function, result of the test etc. More info is here.

Short Demo

1st Step – something to test

Because creating the tests is not easy task, best is to test only the base functions you are using. I cannot imagine to create test for testing all aspects of posting codeunit or something like that.

We will start by creating some function which we will test. I have created one codeunit with one function with one parameter of type boolean and result of type boolean. Logic is this:

If parameter is True, Confirm is displayed and if user select Yes, function return true. If User select No, function display Error with text ‘Canceled’.

If parameter is False, function return False.

The function looks like this:

PROCEDURE TestedFunction@1000000001(Parameter@1000000000 : Boolean) : Boolean;
BEGIN
  IF Parameter THEN BEGIN
    IF CONFIRM('My Confirm',TRUE) THEN
      EXIT(TRUE)
    ELSE
      ERROR('Canceled');
  END ELSE
    EXIT(FALSE);
END;

You can see that I have used hardcoded texts. Please, take it as something, which will not happen in real development, I have used it to have as simple example as possible.

 

2nd Step – Testing codeunit

Ok, now we will create the test for this function. We want to test all three paths – Parameter = true, Answer = Yes; Parameter = true; Answer = No; Parameter = False. It means we will use three test functions:

  • TestMyFunctionTrueYes
  • TestMyFunctionTrueNo
  • TestMyFunctionFalse

All three functions are of type ”Test”, whole codeunit is of type “Test”. There are no parameters or return datatype for these functions.

There is the code for these functions:

[Test]
[HandlerFunctions(ConfirmHandlerYes)]
PROCEDURE TestMyFunctionTrueYes@1000000000();
VAR
  TestedFunction@1000000000 : Codeunit 50010;
BEGIN
  IF NOT TestedFunction.TestedFunction(TRUE) THEN
    ERROR('Wrong Answer');
END;

[Test]
[HandlerFunctions(ConfirmHandlerNo)]
PROCEDURE TestMyFunctionTrueNo@1000000001();
VAR
  TestedFunction@1000000000 : Codeunit 50010;
BEGIN
  ASSERTERROR TestedFunction.TestedFunction(TRUE);
  IF GETLASTERRORTEXT <> 'Canceled' THEN
    ERROR('Unexpected error: %1', GETLASTERRORTEXT);
END;

[Test]
PROCEDURE TestMyFunctionFalse@1000000006();
VAR
  TestedFunction@1000000003 : Codeunit 50010;
  ErrorText@1000000002 : TextConst 'ENU=The update has been interrupted to respect the warning.';
BEGIN
  IF TestedFunction.TestedFunction(FALSE) THEN
    ERROR('Wrong answer');
END;

 

Again, I have used hardcoded texts, do not use them in your real development!

You can see, that first two functions have assigned Handler Function. One is simulating the answer Yes, one answer No. In second function we are expecting error, thus ASSERTERROR was used. Than we test if the correct error was triggered.

Now how the handlers looks like:

[ConfirmHandler]
PROCEDURE ConfirmHandlerYes@1000000002(Question@1000000000 : Text[1024];VAR Answer@1000000001 : Boolean);
BEGIN
  IF Question <> 'My Confirm'  THEN
    ERROR('Unknown Confirm text: %1',Question);
  Answer := TRUE;
END;

[ConfirmHandler]
PROCEDURE ConfirmHandlerNo@1000000003(Question@1000000001 : Text[1024];VAR Answer@1000000000 : Boolean);
BEGIN
  IF Question <> 'My Confirm'  THEN
    ERROR('Unknown Confirm text: %1',Question);
  Answer := FALSE;
END;

 

Both are ConfirmHandlers, we are handling the confirm dialog. We are testing that correct confirm dialog was displayed.

Now we have the testing codeunit. Ok, what’s next?

3rd Step – Test runner codeunit

Now we just prepare codeunit which will run this test. This codeunit could be more complex than in our example, e.g. it could run tests based on some setup table, where you can select which tests to run etc. I will give you example of both functions which could be used in this codeunit.

Start with creating new codeunit, select the Test runner subtype. In the OnRun trigger add this line:

CODEUNIT.RUN(CODEUNIT::"Test codeunit");

That’s all if you want only to test the functionality. If you want to do something more, e.g. log the results, you can add these two functions:

 

VAR

  Before@1000000000 : DateTime;
  TestResult@1000000001 : Record 50000;

PROCEDURE OnBeforeTestRun@1000000002(CodeunitID@1000000000 : Integer;CodeunitName@1000000001 : Text[30];FunctionName@1000000002 : Text[30]) : Boolean;
BEGIN
  Before := CURRENTDATETIME;
  EXIT(TRUE);
END;

PROCEDURE OnAfterTestRun@1000000003(CodeunitID@1000000002 : Integer;CodeunitName@1000000001 : Text[30];FunctionName@1000000000 : Text[30];Success@1000000003 : Boolean);
BEGIN
  TestResult.INIT;
  TestResult."Entry No." := 0;
  TestResult."Codeunit ID" := CodeunitID;
  TestResult."Codeunit Name" := CodeunitName;
  TestResult."Function Name" := FunctionName;
  TestResult."Test Start" := Before;
  TestResult."Test End" := CURRENTDATETIME;
  IF  Success THEN
    TestResult.Status := TestResult.Status::Success
  ELSE BEGIN
    TestResult.Status := TestResult.Status::Failure;
    TestResult."Error Text" := GETLASTERRORTEXT;
  END;
  TestResult.INSERT(TRUE);
END;

TestResult global variable is using new table I have created. You can create it easily, just look at the code, it is clear which datatype is used and how to name the fields. The PK of the table is AutoIncrement=Yes.

4th Step – Run the test

Ok, what to say – Run the Test Runner… :-D

Now you can play with the tested function and e.g. modify the logic to return wrong error or wrong result. All will lead to Failed test.

 

Conclusion

Ok, now you have imagination how it works. But, are we able to use it to test some complex functionality? That’s the question. I do not know. I am a beginner in Test Driven Development, I can imagine using it for some basic functions, may be that when we will test each function, it will mean that complex functionality is tested too, but I cannot imagine that Customer will want to pay for the time we will spend by creating the test functions. Or, may be he will pay, if the result will be much less bugs which costs money too… What’s your opinion on this? Is it possible to create e.g.. Fully tested Addon? Biggest problem will be the GUI and functionality connected to the GUI, e.g. when I wanted to test the Availability warning, if it is correctly displayed, I was not able to do it, because the warning isconditioned by CurrFieldNo and I am not able to simulate it (the warning is not displayed when the functionality is called from code).

Posted by kine | 2 comment(s)

NAV 2009 NST Management app goes Public

I made decision and the source code for my application is now public. You can freely extend/update the code through GIT tools. The project is available on this address:

git://github.com/kine/NAV_NST_Management.git

I hope that this application will grow into superb tool for administering the NST services and may be not only the NSTs.

Posted by kine | with no comments

Future of NAV Development environment

During the MiBuSo conference 2009 in Mechelen we had one presentation, where all attendees could see the future of NAV development environment, which will replace the C/Side from classic client. For others, which are not there, I have some pictures I made with my mobile (sorry for the poor quality). We will see if the presentation will be for download, than you will have high-quality screenshots... :-) I hope that you will enjoy what you will see.

 

Table designer example:

Table designer

All is done through common page object, it means, you can FULLY CUSTOMIZE it!

 

 

 

 

 

 

 

Designer home center:

Designer home center

You can notice that there are info about results of test (yes, currently in NAV 2009 SP1 will be some possibility to create testing functions and create tested solutions!), defects, projects, modules etc.

 

 

 

 

 

 

 

 

Object list:

Object list

As you know it. Including data about where used and what uses.

 

 

 

 

 

 

 

Object list 2 

Objects are assigned into modules, you can filter them etc…

 

 

 

 

 

 

Code editor:

Code editor

No Visual Studio! Own simple editor, to not overload users by unneeded functionality and to keep things simple.

 

 

 

 

 

 

 

Code editor

Editor can highlight e.g. variable you are searching for. It means easier orientation and searching in the code.

 

 

 

 

 

 

 

Code editor

You know it from Visual Studio… ;-) No more Symbol menu!

 

 

 

 

 

 

 

Code editor

Easier entering of parameters. Once again, you know it from Visual Studio. Much better entering parameters, because you know which you are entering/modifying etc.

 

 

 

 

 

 

 

As you can see, there are good things prepared for us. But take all what you saw as example, how it could be, not how it will be. Nothing is final yet.

But I hope that you are looking for it!

Posted by kine | with no comments
Filed under: ,

NAV 2009 Management application

Hello all,

today I am glad that I can introduce you my new application, which is focused on management of NAV 2009 service tier and WebService. In last few days I learned much about C# and managing services, reading and modifying XML files etc. Result is the application named "NAV 2009 NST Management".

What you can do with this application?

  1. Start/Stop the NAV services
  2. Create new instances of the services
  3. Configure the services (change DB server, DB name, and enable/disable debugging on them)
  4. Remove the services (excluding the default one)
  5. Do it all on remote machine (remote registry access required)

I hope that this application will help all of you who needs more instances of the services (developers etc.). The new instances are created with .NET port sharing, it means running all on same default port (you need to run the service for port sharing manually, it is disabled by default). They differs only by instance name. All is based on the example from Freddy's blog (BIG thanks to Freddy). Instances are created as running under NETWORK Services account. If needed, may be setting the account will be possible in some next version ;-).

Update: 2.2.2009 - newer version attached, with appropriate error management in situations when NST is not installed etc.

Update: 3.2.2009 - again, newer version attached, I had some chaos in files. Sorry...

Enjoy the app, thanks for any feedback.

 

Posted by kine | with no comments
Filed under: ,

The Art of NAV –Dynamics NAV 2009 network Stats

Today I prepared some stats about network traffic in NAV 2009 RTC client. You can compare the results with C/Side client (classic client).

 

Measuring: for measuring I have used the Microsoft Network Monitor for catching the NAV communication between client and DB Server/Service tier. Than I create sum of Frame Length, TCP Payload length and count of frames for the communication for each process. The results are not exact, because there are many things I didn’t take into account (cold/warm buffers, transfers of objects into classic client, used another order for measuring etc.). It means take the stats as example, how it can look like, but not as precise bandwidth measuring. Sizes are in Bytes.

Results:

Process

Frame total size (C/Side) Frame total size (RTC) Payload total size (C/Side) Payload total size (RTC) Frames count (C/Side) Frames count (RTC)

WIN

Comment

Client start

475 944 301 859 435 237 282 113 753 364

RTC

Start of client (Role center with data on RTC, Navigation Pane only on Classic)

Order list

387 740 253 255 356 366 237 775 581 286

RTC

Opening list (different size of window, in Classic opening first card and F5 on card)

Open order card

318 292 160 012 291 508 149 500 496 194

RTC

Opening card

Nothing to post

265 980 22 346 243 510 20 075 416 41

RTC

Processing

Closing order card

17 387 28 724 5 777 26 840 215 34

C/Side

Closing form

Order confirmation preview

538 989 1 000 756 504 429 939 150 640 1 108

C/Side

Printing

Open departments page

N/A 6 167 N/A 5 669 N/A 9  

Only in RTC

Adj. cost - item entries

4 453 658 51 371 3 853 774 44 075 11 108 134

RTC

Processing only

Post cost to G/L

1 910 662 3 415 814 1 599 491 3 187 554 5 749 4 190

C/Side

Processing + printing

Add line on Sales Order (filled fields Item no., location, quantity, price)

1 742 217 402 336 1 605 192 375 788 2 536 487

RTC

Processing only

Conclusion:

Form the table you can see the effect of 3-tier system. When you are processing some data, RTC is much better, because no raw data are transfered. Classic client needs to read all data to process them. But problem for RTC are Reports. When you want to print something in RTC, the client need to transfer the Flat Table structure with all the data to process them in the Reporting System Client. And this table can be big. One page with order confirmation took 1MB of data. I do not want to see some 100page report… and I am not talking about bitmaps on the report (e.g. Company logo). But because printing is not critical operation in most cases in NAV, it is OK, because critical things like data processing are 90% of whole application. When you create sum for whole data transferred for both clients, classic client transferred around 10MB and RTC only 5.5MB. Still, RTC is much better with the transfers and this difference will be much bigger in real live when you are mostly posting and sometime printing. Another difference is the Frame count. RTC is sending much less frames but they are bigger. It can lead to change in performance too. But this is question for some Network specialist.

What is you opinion? Are you happy that I created this table? Was it helpful for you?

I am waiting for your comments…

Kamil (Kine)

What is new in Microsoft Dynamics NAV 2009

Hello all

I am happy that after long time of silence I can post this article about NAV 2009. I want to show you what is new in this version from Developer's point of view. Let's make it short:

Technology

Of course, everybody knows that NAV 2009 is 3-tier system, where you have DB Server (MS SQL 2005/2008), Service tier (processing data) and the Client tier (RoleTailored Dynamics Client - RTC). But many people think, that NAV 2009 client is "tiny" client. But this is not true. The Dynamics client is marked as "rich" client – there is no big amount of application data exchanged between client and Service tier, but there is big amount of XML data describing the Pages. I do not have stats about the amount, but because many things are configurable and all is in XML, the amount will not be too low. We will see after someone will have time to measure the data flow...

All what you can see on the client is sent to it as XML. This XML is than merged with XML data with user configuration and application setup. XML includes metadata about the fields etc. but no positions, size etc. It means all is on the Dynamics client decision, how will be each part displayed. This is done to have enough space for different devices for displaying the data. It will be just question of time when some new clients will be on the market.

As a "tiny" client can be marked the "SharePoint Dynamics client", which works as part of SharePoint site and just the resulting web page is sent to the user, but we will see.

WebService native support – this is biggest feature of this version which you can use right after you make technical upgrade. You do not need to use RTC or Service tier for that, WebService service is separate. Just publish some page or codeunit and you are ready for integration. All, including security, out of the box...

Developer environment

1) For developing is still used the C/Side classic client. No change in that. BUT! Yes, there is one change... syntax highlighting. You can see the screenshot in my previous article... I hope that some people will be very happy :-)

2) There is new object type – Page – which is like Form but for the new client – there will be needed whole new article about Pages...

3) On table fields is new property – ExtendedDatatype – [<None>,Phone No., URL, E-Mail, Ratio, Masked] – this property is used to show the data in appropriate way (as a correct hyperlink, progressbar etc.)

4) You can define field groups on the tables. These field group are used e.g. for Lookup lists. You can see more in this blog.

5) New property on boolean variables – IncludeInDataset – this property enables you to include this variable with the displayed data as new "field" to drive visibility and other properties of displayed controls on Pages.

6) Request Page on Reports – same thing as Request Form but for RTC, defined in same way as Page.

7) Layout of report for RTC client is done in Visual Studio as file with extension RDLC (Client Report Definition). It is subset of functionality of reporting services but without reporting services... ;-) This layout can be automatically suggested through function "Tool – Create Layout Suggestion" in the report designer in Classic client. Details are for separate article...

8) There are new functions on reports – REPORT.SAVEASPDF and REPORT.SAVEASEXCEL – I assume that they are working only within RTC client – it means no PDF workarounds needed, just call the function and you have the report in PDF file of your selection...

9) Dataports are replaced by XMLPort for RTC. To be able to export/import files from within RTC you need to use XMLPort, which have now many new properties to be able to work as dataport. You need to think about it much more than before, because the import/export is done on the Service tier and not on the client, it means you need to upload/download the file to the service tier first (there are new commands for that, see my article about NAV 5.0). It is for new separate article...

10) You can display BMP, EMF, GIF, ICO, JPG, PNG, TIFF, WMF pictures in the RTC (still only BMP in classic client).

What is done in another way

This is list I made from scratch and from what I remember. I know that there are more points but I miss my remarks...

1) You cannot specify where and how will be the data displayed – you can only change some metadata for the fields (Importance). But there are new possibilities to configure RTC layout.

2) No colors on Pages... sorry...

3) There is no matrix control for the RTC. It is replaced by fixed column table simulating the behavior of the matrix. It is ok for reading, but it is read-only. Edit functionality of matrix box cannot be done in this version. You can still use the Classic client for that or you can use WebService to make something like that in excel etc.

4) Errors are now handled as error bar in the RTC, not as Dialogs. There can be more errors on the page and user can solve them in any order.

5) Tabs replaced by FastTabs – sometime it means more space on screen needed to display the page...

6) You have NICE data picker if you are on Windows Vista.

7) Lookup lists with "filter as you type" on selected field. Of course – watch for performance when using that...

8) Graphs

9) Freeze Pane for tables

10) No symbols on pages (e.g. by changing font etc.) – if used for making "tree" structure, there is native support on Page (ShowAsTree, IndentationColumnName)

11) Hot keys were changed in whole application to be same as in other Microsoft applications – it can lead to some time you need to get used to new shortcuts to control the client only by keyboard. There is some area which Microsoft has for make it better, but with good feedback, it can be better in next version.

Conclusion

That's all for now. Take it as brief overview of the changes. Of course the whole RTC is big change, but to use it you need only to get used to Page designer and Report Layout tools in Visual Studio. No another change. All is still C/AL, working in same way as before. That there is C# behind you do not need to know. Open your mind for the evolution and try to find new ways how to do the things. Try to not hang on used ways, look for new opportunities you can use for you, all that is new for all of us. I know that whole Microsoft Dynamics NAV team knows the pains of the system and they are working hard on solutions. But it is not possible to release new application like that with all functionality already build in, because in this case it will be revolution and not evolution.

 

Have a nice time with NAV 2009 and I wish you many positive experiences.

Posted by kine | with no comments

Microsoft Dynamics NAV 2009 - C/AL is colored!

Yes, it is true, NAV have syntax highlighting now! What a miracle! My live is much more bright now!

 NAV 2009 syntax highlighting

What to say? No comment... :-)

What is your comment on this?

Be redy for more NAV 2009 articles, because now, NDA over NAV 2009 is gone...

 

Posted by kine | 2 comment(s)

The Art Of Nav – NAV 2009 CTP4 stats

Today I made some statistics about NAV 2009 CTP4 tables and compared the result with the stats for NAV 5.00SP1. There are the tables:

Tab 1) Field relations by type:

Relation Type

Count

(5.00SP1)

Count

(NAV 2009)

Difference

AVERAGE

5

5

0%

COUNT

109

193

+77%

EXIST

135

135

0%

LOOKUP

291

294

+1%

MAX

9

9

0%

MIN

14

14

0%

SUM

415

415

0%

-SUM

70

70

0%

TABLEREL

5468

5497

<1%

-EXIST

1

1

0%

Grand Total

6517

6633

+1.7%

 

You can notice, that the biggest difference is in COUNT flowfields. It is mainly because these new stacks in the new RoleTailored client.

Tab 2) Field data types:

Data type

Count

(5.00SP1)

Count

(NAV 2009)

Difference

Code

6377

6425

48

Decimal

2984

3013

29

Text

2310

2314

4

Integer

1603

1674

71

Boolean

1452

1467

15

Option

1279

1288

9

Date

1025

1044

19

Time

127

129

2

DateFormula

101

101

0

DateTime

57

59

2

BLOB

48

53

5

GUID

17

19

2

RecordID

6

6

0

Duration

2

2

0

TableFilter

1

1

0

BigInteger

1

3

2

Grand Total

17390

17598

208

 

Tab 3) Field data types including length:

Filed type and length

Count

(5.00SP1)

Count

(NAV 2009)

Difference

Code10

3387

3413

26

Decimal

2984

3013

29

Code20

2871

2889

18

Integer

1603

1674

71

Boolean

1452

1467

15

Option

1279

1288

9

Date

1025

1044

19

Text50

973

974

1

Text30

672

673

1

Text80

253

254

1

Text250

188

180

-8

Time

127

129

2

DateFormula

101

101

0

Text20

87

83

-4

DateTime

57

59

2

BLOB

48

53

5

Text10

39

39

0

Code30

35

38

3

Code3

21

21

0

Text100

20

20

0

Code250

19

20

1

Code100

17

16

-1

Code50

17

17

0

GUID

17

19

2

Text65

16

21

5

Text64

8

15

7

Text38

7

6

-1

RecordID224

6

6

0

Text200

6

6

0

Text249

6

3

-3

Text5

6

6

0

Code80

5

5

0

Text119

4

8

4

Text70

3

3

0

Code130

2

1

-1

Duration

2

2

0

Text150

2

2

0

Text19

2

1

-1

Text199

2

2

0

Text90

2

2

0

Text99

2

1

-1

BigInteger

1

3

2

Code1

1

1

0

Code2

1

1

0

Code98

1

1

0

TableFilter

1

1

0

Text118

1

1

0

Text127

1

1

0

Text131

1

1

0

Text14

1

1

0

Text149

1

1

0

Text151

1

1

0

Text220

1

1

0

Text3

1

1

0

Text31

1

1

0

Text32

1

1

0

Text63

1

1

0

Text7

1

0

-1

Code40

0

2

2

Text128

0

2

2

Text240

0

1

1

Text4

0

1

1

Grand Total

17390

17598

208

 

Tab 4) Overall stats:

 

NAV 5.00SP1

NAV 2009 CTP4

Tables

918

944 (+26)

Fields

17390

17598 (+208)

Fields per table (Avg)

18,94

18,64

Average count of relations per table

7,099

7,026

Percentage of fields referring other fields

37,47%

37,69%

Max fields in table

176 (Tab 39 - Purchase Line)

175 (Tab 27 - Item)

177 (Tab 39 - Purchase Line)

175 (Tab 27 - Item)

Max table relations per table (All)

93 (Tab 18 - Customer)

93 (Tab 18 - Customer)

Max table relations per table (Table Rel)

66 (Tab 81 - Gen. Journal Line)

66 (Tab 81 - Gen. Journal Line)

Max table relations per table (FlowFields)

63 (Tab 18 - Customer)

63 (Tab 18 - Customer)

Most referred table

266 (Tab 349 - Dimension Value)

258 (Tab 308 - No. Series)

235 (Tab 15 - G/L Account)

266 (Tab 349 - Dimension Value)

258 (Tab 308 - No. Series)

235 (Tab 15 - G/L Account)

 

As you can see, there is no big increase of table count and field count. The change is mainly because the new functionality for RTC. There are new tables with flowfields for calculating counts of documents and other information.

 

The Art Of Nav – NAV 5.00SP1 Stats

Today I made some statistics about NAV 5.00SP1 tables. I know that the stats have no purpose, but you can see on them how complex the system is. It can help to someone, who just want to know, how much is something used in standard etc. There are the tables:

Tab 1) Field relations by type:

Relation Type

Count

AVERAGE

5

COUNT

109

EXIST

135

LOOKUP

291

MAX

9

MIN

14

SUM

415

-SUM

70

TABLEREL

5468

-EXIST

1

Grand Total

6517

 

A you can see, there is 6517 relations between fields (TableRel, SUM, Lookup etc.) or tables (Exist, Count). Of course, most of them are table relations and SUM flowfields.

 

Tab 2) Field data types:

Data type

Count

Code

6377

Decimal

2984

Text

2310

Integer

1603

Boolean

1452

Option

1279

Date

1025

Time

127

DateFormula

101

DateTime

57

BLOB

48

GUID

17

RecordID

6

Duration

2

TableFilter

1

BigInteger

1

Grand Total

17390

 

From this table you see that most used data type is Code – of course, because most of relations are made through some code and the code fields are used everywhere... :-) Second is Decimal – yes, we are working with ERP which counts money, inventory...

Tab 3) Field data types including length:

Filed type and length

Count

Code10

3387

Decimal

2984

Code20

2871

Integer

1603

Boolean

1452

Option

1279

Date

1025

Text50

973

Text30

672

Text80

253

Text250

188

Time

127

DateFormula

101

Text20

87

DateTime

57

BLOB

48

Text10

39

Code30

35

Code3

21

Text100

20

Code250

19

Code50

17

Code100

17

GUID

17

Text65

16

Text64

8

Text38

7

Text200

6

Text249

6

RecordID224

6

Text5

6

Code80

5

Text119

4

Text70

3

Text99

2

Text90

2

Text19

2

Text150

2

Text199

2

Code130

2

Duration

2

BigInteger

1

Text7

1

Text127

1

Text3

1

Text14

1

Text151

1

Text63

1

Code2

1

Text131

1

Text220

1

Code98

1

Code1

1

TableFilter

1

Text118

1

Text149

1

Text31

1

Text32

1

Grand Total

17390

 

Most used is Code with length 10 – all basic lists are referred by this data type. But newer the longer fields are used (Code 20), which have enough space for possible longer codes. I personally when I create new table, I use the Code 20 to precede possible problems with additional extension of the field in future. Very interesting are field length at the end of the table like Text118, Code1, Text63... ;-)

Tab 4) Overall stats:

Tables

918

Fields

17390

Fields per table (Avg)

18,94

Average count of relations per table

7,099

Percentage of fields referring other fields

37,47%

Max fields in table

176 (Tab 39 - Purchase Line)

175 (Tab 27 - Item)

Max table relations per table (All)

93 (Tab 18 - Customer)

Max table relations per table (Table Rel)

66 (Tab 81 - Gen. Journal Line)

Max table relations per table (FlowFields)

63 (Tab 18 - Customer)

Most referred table

266 (Tab 349 - Dimension Value)

258 (Tab 308 - No. Series)

235 (Tab 15 - G/L Account)

 

I think that the last table doesn't need any explanation. In average, if you change some field's length, there is more than 1:2 chances that you need to change length of another field which refer to the first field.

 

I hope, that you made better picture of the complexity of whole Microsoft Dynamics NAV system. May be that you now understand, why the people around this system needs long time to gather enough experiences to understand the system. It is why the NAV is so great product, because regardless this complexity, it is easy to change and develop additional parts. Do not forget that these stats are just for the basic version of the system. If you add all addons which customer needs for his work, these stats can go much higher...

I want to congratulate to all consultants and developers, which are able to understand the system, which knows, how it works, and which are able to correctly do their every-day work with this huge amount of information. May be that this stats will help customers to understands, why sometimes there are some bugs in the system, why some things needs more time to think them out than making them.

We will see how these stats will change with new versions.

Posted by kine | with no comments

The Art Of Nav – Big Picture of NAV

Today I have for you something, what everyone wants to have, but it was nearly impossible to create. After you see it, you will know why. I prepared it for you in more formats. You can look at it as PNG file, you can walk through it with some VRML viewer, you can open it as Visio file. You can read it and import it as CSV file. You can browse it as HTML file. You can view it as SVG file. It is on you, how you will visualize the result. I must say, I have tried many ways, but in most cases the tools were weak and my 2GB of RAM was not enough.

The Big Picture Of NAV

What's the hell is this "line hell"?

Ladies and Gentleman,

"Big Picture of NAV" is there. You can download the files on the "The Art of NAV" project page. And what the files are about? The files are generated from data, describing ALL NAV TABLE RELATIONS WITH CONDITIONS AND FILTERS. In the visualizations, the tables are represented by boxes where first line is table name and rest are all fields in the table. The relation descriptions are part of the edge text (source conditions, target filters). All is based on NAV 5.00SP1 W1 objects. When you take the result files and you will want to print it in 1:1 scale, the output will be over 5x5 meters in size. Visio cannot save the diagram as bitmap for 1:1 scale (it seems because the size). You cannot see whole diagram in Visio (on common display resolution), because zoom cannot be less than 1%. My computer had problems to generate any graph from the sources. My 3GHz Core 2 DUO worked on it many minutes. I have used the excellent software Graphviz to make the graphs, but as you can see, it is too much for any tool on the world to make some nice readable chart (or I didn't find the correct settings :-)).

I prepared for you NAV objects, which I used to extract the relations from object text file. It will fill the NAV table with all necessary data and you can browse it in prepared window. You have list of all fields in the database on top, related tables for selected field in middle, and fields relating to the selected field on bottom. You need to use these objects on the database, from which the object file is created, because the form is based on the virtual tables of the database. The objects are extracting 100% of relations in the database, which are defined. You can use them to extract relations from you own databases. Just export all tables as text and run this tool in the same database.

All files can be found in the download section of project "The Art of NAV".

Do you understand now, why we do not have any official diagram with the NAV table structure? :-D

File description and viewers I tested:

SVG – vector format – ZGRViewer – sometime needs to set bigger java heap by adding parameter "-Xmx512m" (the size is on you, this example is 512MB) when calling the java package

HTML – HTML exported from MS Visio – Internet Explorer – use the Internet explorer, you can search within the graph. Firefox is showing just plain bitmap.

VRML – the graph in 3D world – FLUX Player – needs big memory, you can look at the graph in 3D space and if you use the FLUX studio, you can add cameras, interactivity, animations etc. Welcome into "NAV Space".

VSD – MS Visio file created by importing the SVG file – MS Visio – You can look at the graph in Visio and print it from there (over 5x5 meters). Hart to edit etc. because the size and SVG source limitations...

Posted by kine | with no comments

Where to place my code?

That's the question… Many rookies have problems with decision where to put the code (or from where to call it). When you watch some beginner in NAV where the code is placed and from where it is called, you can find out that in many cases used triggers are the triggers, which in 99% are not used in the application and many Pro developers nearly do not know what the triggers are doing :-).

I had a thought. To create small decision tree which can help developers to find out, which trigger will be best for calling the code, and where to place the code in (table, codeunit etc.). I placed the first file to google code repository site under project named "The Art of NAV". I will try to keep this site as the Home page of this project. We will see, maybe I will find better tool for that.

I am waiting for your opinions and comment. I will try to keep the project live and up to date. We will see, if it helps you.

This is a first step I am doing and I am thinking about it as an initial step for something I am calling "NAV Art" or "The Art of NAV". My target is to help all developers to make the code as clear as possible and make some order in the NAV word. Of course, there is no simple manual or something for that, but I want to open some discussion about that and give some ways for the specific problems. I hope that I am not alone in that and you all will try to participate in this.

I am Waiting for your comments...

Decision tree

Decision tree for table

Pay attention to permissions when upgrading DB from SQL 2000 to SQL 2005

Story

Today I solved one "mystery" which leads to data loss. But from the beginning...

We have upgraded HW and MS SQL to version 2005 on 64bit platform. Everything without problems, because SAN was used, transferring the DB was just Detach/Attach and running one SSIS task to transfer user logins. No problem. After upgrade all is working without problems. But...

Some users started to complain that specific functionality is not working correctly. What's going on?

From one card you can open another form through button. This form shows some related records, and if there are no related records, they are generated. It's simple. But now, user opens the form and there is nothing in it. I tested it and it works ok, records were created. Ok, I have something to think about and something to solve.

After few rounds of "you'll try it, I'll try it" we noticed, that when the user open the form, there is just one line in the form, looking as inserted (no asterisk on left side of the table). But after we moved cursor or just opened table filters and returned back, there is no record in the table and we are on the new line. 8-|

Ok, code looks correctly, no conditions or something else preventing the record insertion process. I started client monitor and there are all the inserts statements, but still no records in the table.

I will not go deep into the analysis of this problem, if you are interested in what I did to discover the source of the problem, post comment and maybe I will wrote another article about that. Now, I will jump to the time, when I found out what's the problem.

As everytime, the SQL Profiler helped to discover the problem. In profiler I enabled the tracing of Server errors and exceptions and after I catched the process, I saw few red lines saying this:

Exception 74 Error: 262, Severity: 14, State: 4

User Error Message 74 SHOWPLAN permission denied in database 'blablabla'.

Ok, it confirmed one thing I noticed at very beginning – if you are db_owner, it is working. If not, you have the problem. After looking into permissions and comparing the permissions from DB created on SQL 2005 and on the transfered one, I found out that there are no Database permissions "Show Plan" and "References" for the application role $ndo$shadow.

Result

Why they are not there? Because these permissions doesn't exists in MS SQL 2000. They are assigned correctly when you create the DB on MS SQL 2005, but not when you transfer the database by Detach/Attach or Backup/Restore process. These permissions are not created even when running Synchronize all process from within NAV (using Standard security model of course).

Another thing I noticed is that this bug makes visible problems only if you are working with Maximized NAV windows, if you are working with form in normal size, all seems to work correctly.

Conclusion

After upgrading MS SQL 2000 to MS SQL 2005, if you are not creating new DB but using Detach/Attach or SQL Backup/Restore process to transfer the database, you need to grant "Show Plan" and "References" permissions for the application role "$ndo$shadow" on the database. Do not forget this else you can experience mysterious data lost and behavior.

Details of the bug

The data are generated in OnRun trigger of the second form. After this trigger is finished, the first form started to be updated, because the OnAfterGetCurrRecord trigger of this first form is called and there is CurrForm.UPDATECONTROLS command in it. Because this process is still running in context of the transaction started with the OnRun trigger of second form, and because there are SHOW PLAN statements in it and the SHOW PLAN permissions are not granted to the app. role, SQL server raise exception "permission denied" and the transaction is rolled back. But NAV client doesn't catch this exception, and this is the main problem. The first form refresh process is not started if you do not have maximized forms. But the permission exception is triggered every time the forms are using internally COUNTAPPROX to read expected record count (may be because the scrollbar size?) and these calls are canceled by this. User does not know anything about this permission error...

This bug is rare to experience, but the permissions problems can be source of another bug which is not so easy to find or even notice it.

Repro steps

  1. Create new DB on MS SQL 2005, restore Cronus DB into it
  2. Remove "Show Plan" permissions from $ndo$shadow app. role on the DB. (or you can detach come cronus database from MS SQL 2000 and attach it in MS SQL 2005 - the permissions will not be there, because they are not exist in MS SQL 2000)
  3. Import attached objects
  4. Run form 90001, maximize it (this bug makes a problem only when the windows are maximized!)
  5. Click "Test - Run Form"
  6. Second window will open (maximized) but the window has no entries (or just one, until you open filter window or move the cursor, after that there are no lines in the form)
  7. Restore the windows size, close the window opened in step 6
  8. On restored window (not maximized) click again "Test - Run Form"
  9. Second window will open but this time with entered entries - it means correctly.
Objects for download
Posted by kine | with no comments

Country code in ENU fin.stx file is no longer W1

You know the situation: customer has W1 license (or another country license) but is using client for his "home" country. E.g. license is W1, but because Company is in Czech, they are using Czech client. Not problem until NAV 4.00SP3 Update 6. When you got error that "The country code in the license file does not correspond to the country code (XX) in the stx file", you just took the fin.stx from ENU folder and copied it into Client folder. You were done.

But not now. If you installed Update 6, you can find out that this replacement will not solve that problem. Why? The country code in the fin.stx in ENU folder is not W1 anymore if you installed localized version of NAV client. It means you still need to install the same country NAV client like the license Country code and take the fin.stx from there. Who is responsible for that??? I will try why this change was done.

The error is reisen when:

  1. You are using C/Front
  2. You import the license into client folder and run the client
  3. May be when you are using N/ODBC (I didn't test it)

Solution for all points is to take fin.stx from correct language version of client installation (same as the country of license) and copying them into the client folder. Another solution is to install same language version like license country code and just take and add the language code subfolder you want to run client in. If you do not have these files, in case of C/Front you can do small workaround: take the fin.stx, open it in Notepad, find the text string "00200-00001-002-1" and change the country code to same as in your license file. It will corrupt the fin.stx seal, but C/Front is not checking it (client will tell you that fin.stx was corrupted). But better than nothing.

 I hope that it will help you quickly solve your problems, because for me it was one additional night on customer site to solve this "little"problem.

Official Microsoft Dynamics NAV Blog

 Today was opened new official blog of Microsoft Dynamics NAV team. You can visit it there: http://blogs.msdn.com/nav/ . I hope, that we will see there some about future releases and other things around NAV team. We will see in next months, what the blog team prepared for us. But for now - "welcome NAV team into world of bloging".

Posted by kine | 2 comment(s)
Filed under: ,

Powerfull command in Microsoft Dynamics NAV: BEEP

Last few weeks I have worked on some project, which needed to use SendKeys to do something with NAV client. During developing this module I hade problem, how to synchronize of calling SendKeys with behaviour of the NAV client. You know, you send keys to the NAV, client is opening some window (form), it took some time, and as result the keys are sent to incorrect window. I tried to use Timer to synchronize it (wait for opening the window etc.), but it didn't work. The keys still ended in queue of another window that I wanted.

Once, I added BEEP(440,100) command into the timer when I sent the keys to know, when the event was fired and when the keys were sent into queue. And I wonder - the keys ended in correct window! I found out, that when you send some key sequence, which is opening e.g. File Save dialog, you need to wait for the dialog to open to send e.g. keys to "write" the file name into this dialog. And BEEP will help you with that. Because BEEP needs to talk with Operating system, it somehow "give" the control to OS. When you connect this with Timer event, you can do this:

1) Set timer (e.g. each 100ms), enable it

2) When the OnTimer is called first time, send first key sequence, which open the window

3) Call BEEP after sending the keys

4) In next call of OnTimer send second key sequence

5) Call BEEP after sending  the keys

6) repat the steps through all windows and keys you want to send.

7) In last OnTimer, disable the timer

 

Because you used the BEEP command, each new key sequence will end in correct window, because the beep will be called right after all the keys are processed and NAV is free for processing next command. If you use this without BEEP, all keys will be send "to fast" and will be processed in incorrect windows (may be yes, may be no...).

And if you do not want to listen the beep sound, just set the frequency to 1Hz (0Hz is not working, it just skip the command). I found out, that time of the BEEP must be over 10ms, else it will not the effect. But it seems that the value can vary between computers.

I know, that you will be able now to do the cool things with NAV (e.g. automatic version controling :-)) now, when you know how to use BEEP command to synchronize SendKeys with client. 

Take this as result of my research. I do not know exactly why the beep is working like this, but you must know, that IT IS WORKING (for me and my collegues)! 

 (tested on version 4.00SP3 and 5.0)

Have a fun with your new powerfull command BEEP!Geeked Yes

Posted by kine | 10 comment(s)

The Style Sheet Tool for Microsoft Dynamics NAV

 Microsoft has released the tool fro creating Word Stylesheets for Microsoft Dynamics NAV 5.0. You can download it from PartnerSource here. It includes one FOB file with new objects, which are used to define the stylesheets, and documentation.

 

The process of creating styleheet is very easy: you create new card for the styleheet, where in header you define the base table and in lines you define all used tables, fields you want to export and relations between tables (e.g. Sales Header and Sales Lines). After that, you can create Mail Merge document, where you can create the text, formating etc. using the Mail merge fields with all the fields you defined in NAV (you can use Work Date too...). After closing the document, NAV will import it back and will try to transform it into StyleSheet. After that, you can use this stylesheet for exporting data from the base table. Easy, isn't it?

 

Of course, the tools can have some bugs, do not forget that it is the first version. 

Posted by kine | with no comments

Microsoft Dynamics NAV 5.0 Beta available for download

I found this info on this Microsoft Dynamics NAV UK Blog. The download is accessible on the PartnerSource there. You have two possibility for download: VPC for demo, with installed Office 2007 and NAV 5.0 redy for usage, or you can download the install files.
Posted by kine | 7 comment(s)
Filed under: ,

NAV 5.0 just for Group 1 countries?

Based on MS info the NAV 5.0 will be localized and released just for countries in Group 1. For other countries only the NAV 5.1 (including NAV 5.0) will be released!

 This is big disappointment for me, because it means that all companies in Gr2 and Gr3 will have new version one year after Gr1. This market will be one year behind others and partners will have difficulties with managing that. It will be hard to explain to customer that the version we were talking about that will be released in 2007 will be one year later. I recommend to all who are in Gr2 and Gr3 to use WW1 version for preparing their products for the next version. But yes, this means BIG OUTRAGE for all of us outside the Gr1 countries. MS wants to save money? Or it is too big job for them to localize two versions in one year? (but it is not really localization of two versions, 5.1 is still 5.0 + new client. It means, if 5.0 is localized, just few things must be localized for 5.1). Nobody knows, but it seems that this decision was made and there is no way back...Angry

 
 UPDATE (27.1.2007): It seems like Gr1 and part of Gr2 will be localized. On the NAV 5.0 release page are prepared links for these countries:

AT - Austria GR2B
AU - Australia GR1
BE - Belgium GR2B
CA - Canada GR1
CH - Switzerland GR2C
DE - Germany GR1
DK - Denmark GR1
ES - Spain GR1
FR - France GR1
IE - Ireland GR1
IT - Italy GR1
NL - Netherlands GR1
NO - Norway GR2A
NZ - New Zealand GR1
SE - Sweden GR2A
UK - United Kingdom GR1
US - United States GR1
we will see...

 

Posted by kine | 11 comment(s)
Filed under: ,

NAV 5.0 Preview - Part 3 - Export to external application

Today I will try to show you one new cool feature of NAV 5.0 for end-users. NAV 5.0 is able to export any form into XML and using XSLT (template, stylesheet, transformation - you can name it in different ways) can be this XML transformed into another XML. And because MS Word and MS Excel is able to open documents defined in XML, you can create Word and Excel documents in this way (and for any other application).

How it is working

On the toolbar there are three new shortcuts, which can be used in any time when you have some form opened (Last three shortcuts on the picture, behind the watermark).

NAV 5.0 Toolbar

For example, when you open the G/L Account card, and you click on the first shortcut (Word), you will get the Word document with all data accessible on the G/L Account card.

NAV 5.0 G/L Account cardNAV 5.0 MS Word document with G/L Account card

In this Word document, there are all tabs from the form and all controls from these tabs. If you use the second shortcut (Excel), you will get slightly different result:

NAV 5.0 G/L Account exported to Excel

Each tab has own Sheet, but there are same data like in the MS Word. In both applications you can open the original NAV form through clicking on the title in exported document. It means that this document is still "linked" with the original and you don't need to search in NAV for the original form and record.

This was example of using defualt template, which is usable for all forms. But you can attach new templates for specific NAV form and than the user can select this template for exporting within template selection form (third shortcut). In the demo DB there are templates for Customer letter (on Customer card), Contact letter (on Contact card), Vendor letter (on Vendor card) and Sales Quote (on Sales quote card) and Sales Order Confirmation (on Sales order card).

NAV 5.0 Customer letter in MS WordNAV 5.0 Sales Order confirmation in MS Word

You can see that the result can include additional data that are not part of the form in NAV (e.g. Company name, Payment terms description etc.). It means that the result can be complex and you do not need to include all data on the form just because you need to export it.

What is behind

From designer point of view is this feature very complex. Developer needs to understand XML, XSLT and target application XML. If you want to customize this feature, you have two possibilities. You can change the functions in CU1 which are used for this export (you can change how it transforms the data, which template is selected etc.), or you can modify or create own XSLT template (you can change the result of the transformation).NAV 5.0 CU1 new functions
 

 In the functions, when exporting some form, the XML with description of this form is passed into function which will apply the transformation from selected XSLT template, and the result is sent into the application itself. On the screenshot of the Data XML (the XML describing the form) you can see that all controls are included with some informations. From position or size is there just the width of the control (if you change width of column on the form, the excel column in exported document will have similar width)

.NAV 5.0 Form data XML

In the example there are data from Sales order card form. There is header tab control, line subform, Info panel frame etc. This XML file than can be transformed through XSLT file. Result of this transformation can look like this:

 NAV 5.0 resulting Word XML document

On the screenshot is part of the Sales quote XSLT used to transform the XML data into MS Word XML file:

NAV 5.0 Sales quote XSLT 

In the stylesheet you can include other data from NAV into the result (Object/ResponsibilityCenter/Name, Object/CompanyInfo/Name) without being part of the source XML file describing the form. I do not know more info about that (I have no access to this new codeunit yet) but if you can include any data from NAV, this will be very cool tool for creating exports. 

After you prepared some XSLT template, you can import the template into NAV through Stylesheet setup form. You need to select the xslt file, for which application the stylesheet is prepared (which app will be launched, you can add your own), the description and if the template is for all forms or just for selected (depends on which option is selected on the setup card when you run the import).

NAV 5.0 Stylesheet setup formNAV 5.0 Stylesheet setup form

One bigges issue I have with that is, that I am not able to find any user-frendly tool for creating the stylesheet in some visual way (I am novice in XSLT and XML things...). And second thing is that the logo bitmap is part of the stylesheet and it means that you need to customize the stylesheet for each customer. And because I have no good tool  for XSLT, I do not know how I can replace the bitmap in the stylesheet...

 I hope that after documentation for NAV 5.0 is released, I will have more info to be able to create own stylesheet and to work with that like with C/AL and that customization of XSLT will be easy for me.

 

I do not know what will be in next part now.
 

 All screenshots you can find in fullsize in my library at http://msmvps.com/photos/kine

Posted by kine | 20 comment(s)
Filed under: ,

NAV 5.0 Preview - Part 2 - Links

In this part I will describe the new possibility to attach any URL to any record in database. This can be very usefull for connecting the word of documents with data in ERP. There are tools for managing these links through C/AL code (see previous part, the rec.ADDLINK command etc.)

 How it is working

At first, there is new shortcut on the toolbar (Clip). NAV 5.0 Toolbar

Through this shortcut, any user can open form, where you can enter the URL you want (HTTP, Sharepoint, local filesystem, network filesystem, anything you want). From same form you can open attached URL. Count of the atached links is not limited, length of URL can be max.  1000 chars. NAV 5.0 Related links form

For example, you can add scanned Invoice to your purchase order and when the order is posted, the attached links will be copied to Posted Invoice. All users can easilly find and open the document without searching through folders with thousends of files. User can recognize that on actual record are some links in status bar, where is new flag "LINK" near the "FILTER" flag.

NAV 5.0 part of Statusbar

 What is behind

For saving the links new virtual table is used (2000000068 - Record Link). NAV 5.0 Virtual tables

NAV 5.0 Record Link table

As you can see, links are numbered and related to the record through RecordID. Long URLs are splited into 4 fields, with possibility to enter 250 chars of description. The type and BLOB is not used yet, in my opinion the type "Note" and the BLOB "Note" is prepared for version 5.1 for the Work-flow notes. The links can be added, deleted and copied through C/AL commands.

 

That's all. It is easy but smart solution. I know that many partners used similar way for attaching the documents, but this solution is much better, because you do not need any change in C/AL code and you can attach the document to any record without customization.

 

Next part will be about possibility to export any form into Word or Excel and possibility to create templates for this export.

Posted by kine | 41 comment(s)
Filed under: ,
More Posts Next page »