Thursday, February 2, 2017

Use of Virtual Table : Date - Microsoft Dynamics NAV

This blog post is about something powerful but hidden within Microsoft Dynamics NAV. I believe many developers always work with dates over and over during their day-today life and i'm sure many of you have done many functions which convert, split and do all sort of things with date.

Saying that many developers does not know the virtual table "Date" in Dynamics NAV. In this blog post I am going to briefly explain about the different options you can have with the Date table.

As per the demonstrate purpose I quickly create a page (Source : Date Table) and open it. Table has 5 columns which are

  • Period Type 
  • Period Start Date 
  • Period End Date 
  • Period No. 
  • Period Name 
Period Type :
Period Type has 5 different options which are,

  1. Date 
  2. Week 
  3. Month 
  4. Quarter 
  5. Year 
Other four (04) columns have different use depending on the Period Type. Let me go through each Period Type and explain the use of other 4 columns.


Date
I applied Date as a filter to "Period Type" column and check below page on how the data get filtered.

Period Start : Date which you have selected
Period End : Date which you have selected
Period No. : Integer value of the day within a week (1 - Monday, 2 - Tuesday)
Period Name : Name of the date which you have selected

In this level this make not much sense as this can be done quickly using small C/AL code.
Let's check on our next option.

Week
I applied Week as a filter to "Period Type" column and check below page on how the data get filtered.

Period Start : Date which you have selected
Period End : Date which the week ends
Period No. : which No. of week of the year (First week of the year is 1 and then it gets increase per week.)
Period Name : Same information as Period No.

Let's check on our next option.

Month
I applied Month as a filter to "Period Type" column and check below page on how the data get filtered.

Period Start : Date which you have selected
Period End : Date which the month ends
Period No. : Month in integer (1 - January, 2 - February) 
Period Name : Name of the month

Quarter
I applied Quarter as a filter to "Period Type" column and check below page on how the data get filtered.

Period Start : Date which you have selected
Period End : Date which the quarter ends
Period No. : Quarter No. in integer 
Period Name : Name of the month

Year
I applied Year as a filter to "Period Type" column and check below page on how the data get filtered.

Period Start : Date which you have selected
Period End : Date which the year ends
Period No. : Year
Period Name : Same information as 

Practical Use
Let's think you have a date and you want to know the start date of the week which your date falls in. You can do it by using different ways, but this is how quickly you can do that using Date record variable :

 Date.SETRANGE("Period Start",0D,YourDate);  
 Date.SETRANGE("Period Type",Date."Period Type"::Week);  
 IF Date.FINDLAST THEN  
  PeriodStart := Date."Period Start";  

That is it, that is all the code you need to write in order to make it work. I will keep other options to your imagination and to your creativity.

Credit of this blog post goes to : Craig Barberini for bringing this back to my memory.

Please provide your feedback with a comment.
Thank you and Regards,
Tharanga Chandrasekara

3 comments:

  1. Nice!!!
    As an example I think we can give the matrix page- "Item Availability Lines"(Page 353) which is a part of "Item Availability by Periods" page , this page uses virtual date table.

    ReplyDelete
  2. Thanks Tharanga - it is very a very useful virtual table that helps one avoid a lot of coding.

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete