Monday, May 29, 2017

Grouping In Export To Excel Report Without Layout in MS Dynamics NAV 2017

Grouping In Report with Code in A/L

Hi All,

Today I Got a Requirement for Excel Buffer Report in Navision and with Grouping of Location 

Code in Item Ledger Entry.

Typically it was tough requirement , but thanks to MS Dynamics Community and Saurabh Dhyani Sir

for his valuable time which shared a most important thing and rarest blog of Navision.

Direct Link For Grouping of Reports via Coding in MS Dynamics NAV

I had used the same method  in MS Dynamics NAV 2017. I am Sharing my FOB with you all to 

download and understand the issue better.

Useful Points about this Report

1. We can use grouping via C/AL Code.

2. We can arrange the Function via boolean Field.

3. Use of Exit Function in MS Dynamics NAV.

Tuesday, May 23, 2017

How to do dynamic implementation of decimal place in RDLC Reports?

Dear All,

Today  I had requirement from report in RDLC to implement dynamics Decimal Places. Initially it was to realise but I followed report 206 and once again I got my solution done soon.

For those who still not understand check the red screen there we have dynamic decimal place Value.

Solution :-

To implement this we have to use custom decimal formatting in RDLC reports and in that we had to define Format Value.

For Reference see the screen Shot.

This post also explain to juniors when you need to use Format Value in RDLC Reporting.

Thanks for your time to read my blog post.

Sunday, May 21, 2017

How NAV Calculate Unit cost of items in NAV?

How to Calculate Unit cost in Reports of NAV?

Using the below code MS Dynamics NAV Calculates Unit Cost in Report of 746.



ItemRec.SETFILTER(ItemRec."Date Filter",'%1..%2',0D,CALCDATE('-1D',SDate));



AverageCost := ROUND(AverageCost,GLSetup."Unit-Amount Rounding Precision");

How to Select Boolean Field from page in Nav 2017?

Hi All,

This is code which works very dynamic in selection of boolean field.

We have to create a boolean field in the desired table.

Upgrading from NAV 4.0, NAV 5.0, NAV 2009 SP1, NAV 2009 R2 to NAV 2017

Download Upgrade document For MS Dynamics NAV

If we are upgrading from one of these below versions then

  • Microsoft Dynamics NAV 2009 SP1

  • Microsoft Dynamics NAV 2009 R2

  • Microsoft Dynamics NAV 5.0

  • Microsoft Dynamics NAV 4.0
Use the following links given below to upgrade to NAV 2013 first & then follow the above steps to

 upgrade to NAV 2017:

1. NAV 2013 Upgrade Part I - TextFormatUpgrade2013 Tool

2. NAV 2013 Upgrade Part II - Upgrading the Standard Objects

3. NAV 2013 Upgrade Part III - Data Upgrading

4. NAV 2013 Upgrade Part IV - SQL Migration

**Another useful link for SQL Migration:

Saturday, May 20, 2017

Workflow status Field on journals in NAV 2017.

Hi All,

This was one of my finding which I had done on implementation of Approval status Field in MS 

Dynamics NAV 2017.

In all journals we have workflow but we cant find out which lines are sent for approval nd which are 

approved or rejected.

To get this functionality developed I had put this code on Validate trigger of Status Field in NAV 

table ID 454 Approval Entry.

I had added Field on NAV Table 81 which changing its value acording to the status field on this table.

I hope this post will help many of the NAV developers I am also attaching the FOB in the Post below.

Download the Fob Now

Summary of Pages Article provided by Microsoft

This post is related to the summary I followed from article provided by Microsoft. You can Download the complete article from here.

Note 1 :-

Expression properties let you easily control the same behavior of several controls at the same time. By binding the same property, such as Visible, to the

same Boolean variable, you can toggle visibility of multiple controls from a single line of code. Do not forget to set the IncludeInDataset variable property to Yes.

Note 2 :-

Note 3 :-

You can define both the OnAction trigger and the RunObject property.If you define both, the client runs both the object that is defined in the RunObject

property, and the C/AL code that is defined in the OnAction trigger.

Note 4 :-

Linking Pages and Page Parts

SubPageView Sets a specific table view that is applied to the page part.You can choose the key, the sorting order, and the table filter that are applied to the page part.

SubPageLink Sets up a link between the main page, and the page part. The link lets you filter the page part by field values on the main page, and to apply additional page filters.

Tuesday, May 16, 2017

NAV Tech Days 2017

16 & 17 november 2017, Antwerp (Belgium)

Welcome to one of the most awaited event for Nav. You can register from below link.

Few mentioned events and hints about this Tech days
Pre-conference workshops
Making Visual Studio Code in MS Dynamcis NAV 2017,Dynamics NAV with Power BI, Extensions with VS Code to implement in real transactions. 
Event Sessions
– Building dedicated Mobile Experiences
– SaaSification guidelines
– Deep dive into the new development tools

Some Important Links

Stay tuned !

Report Summary For RDLC

Hi ,

Today while going through GetData and Setdata functionality I got to know about a book which is the best one i can suggest to all my NAV individual developers.

Please go through this book although all pages are not available to read but you can buy this book from below link on Amazon.

You can also have look and bookmark this link for any guidance regarding reports in MS Dynamics NAV.

I liked the feature explained in it about Repeat Header on every page of Body using Advanced Mode.

Direct Link to read Book for Reporting Tools

Go to Buy this Book.

Click This  Book to Buy

Saturday, May 13, 2017

Some imporant points Summary Post 2 Tables from


You can have a look for Tables in MS Dynamics NAV from the below link. I am summarising the

concept below. If you like then do comment and subscribe to my blog post.

Tables by microsoft.

Note 1.

RECORDID Datatype :- It Stores RECORD ID and Primary key values.

Note 2.

ExtendedDataType property

When you select the Phone No. field without closing the No. -

Property window, notice that now the Phone No. – Property window is shown.

By selecting a value in the ExtendedDataType property, you will change the layout

and behavior of controls on a page. Use the value to add an icon next to an input

field to indicate whether the field relates to a phone number, email address, or URL.

Note 3.

Why we use Field Groups in Navision?

Note: If you do not define any Field Groups for the drop-down control, by

default, you will only see data from two fields in the source table: the primary key

and description fields, because these are indexed automatically.

Note 4.

Up to 40 keys can be associated to a table, and the first on the list is the primary

key. All other keys are secondary keys and optional.

The primary key is composed of up to 20 fields in a record. The combination of

values in fields in the primary key makes it possible for Microsoft SQL Server to

perform a unique identification of each record. The primary key determines the

logical order in which records are stored, regardless of their physical placement on

a disk.

Secondary keys are used to view records in an order that differs from the one in

which they are sorted, according to the primary key fields.

Note 5.

Note: The field ID that is specified in the TableRelation property must be in

the primary key of the table that is specified by the table ID in the property. If the

specified field is not the first field in the primary key, the other fields that are listed

before it in the key must be filtered to one value.

Note 6.

7 types of flowfields Sum, Average,Lookup,Exist,Min, Max,Count

Note 7.


A SumIndexField is a decimal field that can be attached to a key definition. This is

the fundamental feature of the Microsoft Dynamics NAV that constructs the basis

for FlowFields. SumIndexFields enable fast calculation of numeric columns in


Note 8.
Each key can have at most 20 SumIndexFields. During database design, a decimal field can

be associated with a key as a SumIndexField.

The type of SumIndexField must be numeric (Decimal, BigInteger, Integer or Duration).

Friday, May 12, 2017

Summary of CAL CODE Programming guide


Today I get a look on the below link for CAL Code present in NAV. I am summarising all the points from this PDF.

Note 1. There must be 1 character space between operators.

Note 2. Do not use any blank lines at the beginning and end of the function.

Note 3. Use 2 character space in alignment.

Note 4. If you want to break lines in CAL Code then use 2 character space in all.

For Eg.

MyVariable := Variable1 + Variable2 * 2 +
                     Variable3 * 3;  

Note 5. Use parenthesis alignment uniquely.

For Eg.
aaaaaaaaaa := ((xxxxxxxxxxx / yyyyyyyyyyyyy) -
                         (1 + zzzzzzzzzz / 100)) * 100;
IF (xxx <> '') AND
     ((A = 1) OR
      (B = 2)) THEN ...

Note 5. Always use // for comment with 1 space character.

Note 6. Use Value of option instead of hard Code or Integer values.

Note 7.Use Parameters to transfer Values in a Function.

... P(0); ...

PROCEDURE P(MyOption : 'Value0,Value1,Value2');
MyOption OF MyOption::Value0:
x := x * 10;
MyOption::Value1: x := x * 15;
MyOption::Value2: x := x * 20;

Note 8.  The variable you are operating on or comparing to something else must always come first in expressions.

IF (Date < a) OR (Date > b) THEN Date := c;

Note 9.  IF and THEN should normally be on the same line. ELSE should be on a separate line.

Note 10.  If the last statement in the THEN part of an IF-THEN-ELSE statement is an EXIT or an ERROR, don’t continue with an ELSE statement.

IF x <> y THEN
x := x * 2;
y := y - 1;
IF x < y THEN
x := x * 2;
y := y - 1;

Note 12.  
When BEGIN follows THEN, ELSE or DO, it should be on the same line, preceded by one space 



IF (x = y) AND (a = b) THEN BEGIN
x := a;
y := b;
IF (xxx = yyyyyyyyyy) AND (aaaaaaaaaa = bbb) THEN BEGIN
x := a;
x := y;
a := y;
y := x;
y := a;

Note 13.
Indentation of REPEAT statements:

Simple case:

     UNTIL ;

Complex case:


should always be alone on a line.
           x := x + 1;
          a := a - 1;
     UNTIL   x = y;
               b := x;

Note 14.  If there are more than two alternatives, use a CASE statement. Otherwise, use IF.

Note 15.  Within WITH-DO blocks, do not repeat the name of the object with the member variable or 
function. For example, in the following example do not replace the call of the member function 

INSERT with MyRecord.INSERT.
WITH MyRecord DO

Note 15. use TESTFIELD FUNCTION to check if value is not blank before assigning.

Note 16. 

To set properties from C/AL, use the following style:

"Customer No.".Visible := TRUE;
Cust.MARK := TRUE;
Do not write:
Customer." No.".Visible(TRUE);

Note 17.
Remember to set the property Editable=No on FlowFields unless you want to be able to enter values in the field.

Note 18.
As default, set the property NotBlank=Yes on the primary key fields in a table. No other fields in a table should have this property

Note 19.
Since a disabled field cannot be included in a form, never release a table with disabled fields.

Note 20.
When you apply the property ValidateTableRelation=No to a field, you should also apply the property TestTableRelation=No. Otherwise a database test on the field relations in a database may fail.

Note 21.
When programming the OnLookup trigger for a field, remember that the system will not call the code in the field’s OnValidate trigger unless you call Field.VALIDATE explicitly.
Remember also that if errors can occur in the validation, you must operate on a copy of the Rec-variable (as shown in the example below) instead of directly on Rec.


Department Code – OnLookup
Cust := Rec;
Dept.Code := "Department Code";
"Department Code" := Dept. Code;
VALIDATE("Department Code");
Rec := Cust;

Note 22.
As a rule, use 20 characters for a code field that is likely to be visible to external companies or organizations. Otherwise, use 10 characters.

Note 23.
To assign a value to data type DateFormula, whether it is a field or a variable, you must use the 

EVALUATE function. EXAMPLE IF FORMAT(Dateformulavariable) = ' ' THEN EVALUATE(Dateformulavariable, '1W'); 

You must use the FORMAT function to make a comparison with a text string. If you do not use this function, the IF statement will fail, because you can not compare DateFormula with data type Text.

Note 24.
To make Lookup work on a field that has a table relation to a system table, you must always explicitly set the LookupFormID property on controls showing the field

Note 25.
Remember to set the LookupFormID and DrillDownFormID properties on most tables. You cannot anticipate when a user will need to be able to activate a Lookup or DrillDown button – for example, if someone makes a report with a filter tab on the table, the Lookup button on the filter tab will not appear unless the LookupFormID property is set on the table.

Note 26.
If it is necessary to change the key before accessing a table in the database, first set the correct key, then set the correct filters, and finally, access the table. Put only the necessary key fields in a call of SETCURRENTKEY. That is, if the table order is not important, use only the fields that are used in the subsequent calls of SETRANGE and SETFILTER. This makes it possible to change the definition of the key (as long as it still includes the fields mentioned in the call of SETCURRENTKEY – in the order given) without having to change any code.


In the example, a possible key is Field 1, Field2, Field 3. Without changing the code above, the key could be changed to Field1, Field3, Field2.

Thursday, May 11, 2017

Summarise post 1 for ms dynamics NAV 2013.

Q1. Can another developer overwrite an object locked by you?


Q2. Which object type is new in Microsoft Dynamics NAV 2013?


Q3.What are the elements of the logical database structure in Microsoft Dynamics NAV 2013?
Field, record, table, and company.

Users may frequently forget to lock the objects before opening them in the
designer. You can configure Microsoft Dynamics NAV Development Environment
to automatically lock the objects when a user designs them.
To switch auto-locking on, click Tools > Options, to open the Options window.
In the Options window, set the Auto-Lock on Design property to Yes, and then
click OK.

Note: This change affects only the user who has made it. Every user must set
this option himself or herself.

Note: There is no automatic unlocking in Microsoft Dynamics NAV 2013. As
soon as you auto-lock an object, the object remains locked until you unlock it.

Note: You can access drop-down, look-up or a specific property editor by
pressing the F6 key or the ALT+DOWN combination on your keyboard.

Note: The mark on an object is automatically cleared when you compile
multiple objects again, and the object compiles successfully. To toggle the mark on
an object manually, selecting the object in the Object Designer, and then on the
Edit menu, click Toggle Mark, or press CTRL+F1 on your keyboard.

Page N Of M and HTML Formatting in RDlC Reports

Hi ,

Today while going through my requirement in reporting i made a book mark which i think is the most important

bookmark for alll NAV and RDLC/SSRS developer.

How to develop page N of M in RDLC Reporting?

This post also includes how can we use hTML formatting in our RDLC Reports.

however I had not get into such type of requirement but i will be glad if some day i had handson for this requirement.

i hope you will surely look into this reporting methodology and also bookmark this link as i did.

How to print a next line in RDLC report using AL Code in MS Dynamics Navision?

There is requirement from my client to add all dimensions in RDLC layout with next line to be printed with new line. So I had to handle all things using Cal code.

Procedure :-

Define 2 CHAR variables.

Initialise these 2 variables.
Assign with format in CAL and add this copy dimension in dataitem and put it in RDLC layout your result will be shown as follows

How to remove space between Body and Footer on last page in RDLC reports?

Requirement :-

To remove Gap between footer and body on Last page in RDLC report.

Steps :-

I am sharing my steps if any other developer knows other methods then please do comment below.

Step 1.

Understand the concept of  ReportItems .

Step 2.

I had created the whole footer part in my tablix and then setup visibility condition on behalf of ReportItems in RDLC. I am sharing the screen reference here.

Step 3.
Right click on Footer properties and untick repeat on last page in navision.
I hope this method has helped a lot to my junior developers.

Wednesday, May 10, 2017

How to Print footer on last page in PDF as well as preview

Print footer on last page in PDF as well as preview
  1. Design your footer in your visual basic
     (While designing use text box in place of line and use Format TAB near SQL TAB)


  1. On each text box, in hidden properties Put this condition.
                (if first page = last page then true)
  1. Save and exit

Problem : -If you want that your report page should be broken after specified number on Line

Problem : -If you want that your report page should be broken after specified number on line
Solution :  declare two  variables (:-  PageVar and LineVar  as integer
                     Write code on after get record section
                                IF (LineVar MOD 20) = 0 THEN
                                NewPageVar := NewPageVar + 1;             
LineVar := LineVar + 1;

In VB assign group of NewPageVar

How to Repeat table header/Footer on multiple pages.

Repeat table header on multiple pages.

How to resolve If header is repeating on blank page

  • If header is repeating on  blank page
  • Cause :- report height and width is more than specified in report properties
  • Solution:     
      • Go to Report properties in Visual basic
      • Click on centimeter of page specified and click ok
      • Again go to Report TAB
      • Click on View-à Ruler
      • Reduce your report length and width within the size

Ex: for A4 Page Report should be with  in 21cm and height 29 cm

How to fix number of lines in RDLC report?

Problem :- If you want to fix number of lines in your report
Solution :-  declare integer table and variables
Datatype             Data Source        Name
DataItem             Integer                                 No
  Column                 K                                          No
  Column                 SHOWBOOl                     No

Step 2

Step 3

Step 4

New page per record for Grouping

Hi All ,

This is reference screen for creating new page per record of group in RDLC Reporting.

How to resolve resolve GL inconsistency issue in MS Dynamics NAV 2017?

Procedure to resolve GL Inconsistency Issue :- 

Stpe 1. Create new Code Unit with below code.

FOB For Codeunit

Text File For Codeunit

Step 2. Add Below code in CU 12 Function Finish posting.

Step 3. After modification in code, close nav window client.

Step 4.Run CU 50099 single instance and window client will run.

Step 5.Do the activity which is causing GL Inconsistency.

Step 6.After facing issue again run CU 50099.

Step 5.It will  show the all entries that entries that are going to be insert in GL and causing inconsistency