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,
- Date
- Week
- Month
- Quarter
- 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.
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)
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
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
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
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.
Thanks Tharanga – it is very a very useful virtual table that helps one avoid a lot of coding.
This comment has been removed by a blog administrator.