Basic Date Calculations


Hey Friends,

This Post is all about DATE. I saw many people getting stuck as whenever there needs some date calculation logic and the only reason behind this is that there are too many permutation and combinations that comes into play whenever we start creating our logic. So I just though to add a post where I can add all those very commonly needed outcomes.

FirstDayOfMonth := CALCDATE (‘<CM-1M+1D>’, Today);

LastDayOfMonth := CALCDATE (‘<CM>’, Today);

FirstDayOfPreviousMonth := CALCDATE (‘<CM-2M+1D>’, Today);

LastDayOfPreviousMonth := CALCDATE(‘CM’,CALCDATE(‘-CM-1D’,TODAY));

Day := DATE2DMY(TODAY, 1);

Month := DATE2DMY(TODAY, 2);

Year := DATE2DMY(TODAY, 3);

WD4 The next 4th day of a week (Thursday)
CM+10D Current Month +10D
10D 10 Days from today
CW Last date of the current week (Sunday)
CM Last day of the current month
CQ Last day of the current quarter
CY Last day of the current year
1Y    1 Year ago from today
CY-2Y+1D First Day of last fiscal year
CY-1Y Last Day of last fiscal year
2W 2 weeks from today
D10 The next 10th day of the month

I will go on increasing this list. This will anyhow save our time.

There’s a virtual Table called DATE. I always remind and recommend everyone to use this Table, it really makes the work a more easier. 



Remove Attribute – Soap Request XML(XmlPort)


Hey Friends,

We all know how to remove Namespaces from the XML if we want to import it from NAV XmlPort, I just faced a situation where I was using XmlPort to send Soap Request with Default Namespace. Using default namespace creates an attribute something like this (xmlns:xx=”;) in the soap::Body which basically disrespects the Soap request as sometimes it’s not needed in the protocol of the soap request. So now if we use Remove Namespace code then it will remove the namespaces from the entire XML i.e. Soap:Header as well. In this case here is the code that you need to write to just eliminate it from the soap:Body.


Now when you load this BigTextOutputValue to your XmlDoc, you will have a XML with no Attributes in the Soap:Body and you can send this as a Soap Request via HTTP.

BigTextVaue  –   DateType  – BigText

BigTextOutputVaue  –   DateType  – BigText




Hey Friends,

I just experienced a case where Client want to see a common list Page in NAV where he can see a data which is basically a combination of multiple Tables. Though it’s achievable in NAV but you need to write a bulky code on the page which eventually turns off the Performance.

So what I did I made a SQL view for this combination which is very simple from SQL’S point of view.


When you will execute the above view in SQL you will have the exact look and feel what client was expecting in NAV. So what you need to do is you have to make a new Table with the same name as of the view and with the same columns as shown below


Now you have to turn on the below property of the Table to get this interconnected.


Now when you will run this Table you will get the same desired results of  what you experienced in SQL.

P.S – Only do this when you have a combination of multiple tables and is really critical, as it’s consuming a Table Object in NAV.

Server Side Performance – I/O Effect


Hi Friends,

I just want to share a bit of activity that we performed a couple of days before to enhance NAV and Server Performance and which literally ended with huge effect. One thing we came across was the I/O Operation which we should always consider if Server side starts performing a bit weird then before and that was our case. The I/O Operation with Read(B/sec) was more then 50 ,00,000. Check the below Screenshot


As I said the first entry was 50,00,000 which was sucking the whole systems Memory, CPU and RAM. After a bit of operation we came to a point were we got to know that there’s a SQL Procedure running which was consistently doing some kind of operation on tempdb of SQL. So now our next search operation initiated from SQL side and after struggling a bit we came to the real Query and the Procedure which was doing so. Thanks to below SQL query which dragged our attention there to that specific Stored Procedure..


After executing the above query we get the exact Stored Procedure and later on we eventually got to know that this SP was manipulating something on 500000 data and it was running every one minute as a SQL Job. Turned out to be Nightmare for us. 🙂



Virtual Table – AllObj

Hey Friends,

This Post is about Virtual Table – AllObj. AllObj virtual table contains information about all the Objects that NAV consists of. Here you can go and drag information on the Object level. Though there’s exist a virtual Table named Object as well from where you can drag multiple information but this depends on all on all your need and the information you want to pull off.


Object Type – Type of the Object – Table Data, Table, Report etc.

Object ID – ID of the Object.

Object Name – Name of the Object.



Virtual Table – System Object


Hey Friends,

As we all know there are Virtual Tables as well as System Tables. So for recognizing System Objects there’s a Virtual table – System Object. This simply consists of all the System tables which are available in the NAV System.


This Virtual Table consists of the above Fields with information as –

Object – Either it’s a Table, Page,Report and so on.

ObjectID – Unique ID of the Object.

Object Name – Name of the Object.

Object Caption – Caption of the Object.


Virtual Table – Key


Hey Friends,

This Post is about Virtual Table – KeyKey virtual table contains information about all the keys that are defined in each and every table in the database. Again like if you want to get the complete information of the keys the Standard and the one you created then can hit this virtual table. As we all know Sum Index Fields makes our work a way more easier but it’s too been recommended not to use too much of this as it reduces performance as that of Flow Fields.

So if you want to calculate how much Sum Index Fields you have made in the entire Database then you can hit this virtual table.


This virtual table gives you the following Information’s –

TableNo –  ID of the Table.

No. – Its a sequential ID assigned for multiple fields of the same Table.

TableName – Name of the Table.

Key – Combination of Fields that makes the Key.

SumIndexField – SumIndexFields defined for that Key.

SQLIndex –  Actual fields that have been defined in Microsoft Dynamics NAV and are used in the corresponding index on SQL Server instead of those defined in the key.

Enabled – Either the Field is Enabled or not.

MaintainSQLIndex – MaintainSQLIndex property has been activated or not. This field can be modified.

MaintainSIFTIndex – MaintainSIFTIndex property has been activated. This field can be modified.

Clustered – Either the Key is Clustered or not. This field can be modified.



Virtual Table – Field


Hey Folks,

This Post is about Virtual Table – Field. Field virtual table contains information about all the fields of all the Tables in the Database. If at any time you want the complete specification of the Fields from development perspective then this virtual Table is for you. You can even use this Virtual Table for comparing the fields of different tables for any sort of queries you have.


This Table consists of following specifications –

TableNo –  ID of the Table.

No. – Its a sequential ID assigned for multiple fields of the same Table.

TableName – Name of the Table.

FieldName – Name of the Field.

Type – Data Type of the Field.

Len – Length of the field(Bytes).

Class – Class of the field – FlowField,FlowFilter.

Enabled – Whether the field is enabled.

Type Name –  Data type assigned to the field with Length(Code,Text).

Field Caption –  Caption of the field in the language that has been selected.

RelationTableNo – ID of the Table on to which the Field is related.

RelationFieldNo – Number of the field in another table that the field is related to.

SQLDataType – Data type assigned to code fields.