mibuso.com
Home› NAV/Navision Classic Client

Display all months between two dates

zulqzulq Member Posts: 204
edited 2008-01-17 in NAV/Navision Classic Client
Hi,
Would like to list all months between a start date and an end date.
For example say start date is january 2007 and end date december 2007,
would like to display all the months from january 2007 through december 2007.

Any ideas please!
](*,) ](*,) ](*,)
Few years ago we were not existing and few years to come we would be in the grave! So what will benefit us in the grave?

Answers

  • rvirrvir Member Posts: 5
    Try something like that

    UsedBeginDate := TODAY
    UsedEndDate := CALCDATE('1Y',UsedBeginDate)

    KSDate := UsedBeginDate;

    REPEAT

    KSDate := CALCDATE('1M',KSDate);

    UNTIL KSDate > UsedEndDate;
  • zulqzulq Member Posts: 204
    Hi,
    Have tried your suggestion but it does not work. Only 02/01/2008 is displayed on the report. Below is my code: 
    BeginDate := 010107D;
EndDate := TODAY;
DisplayDate := BeginDate;
REPEAT
DisplayDate := CALCDATE('1M',DisplayDate);
UNTIL DisplayDate > EndDate;
    Few years ago we were not existing and few years to come we would be in the grave! So what will benefit us in the grave?
  • wonmowonmo Member Posts: 139
    This should work.

    gdat_Start := 010108D; // ***use any start date

    gdat_End := 090108D; // ***use any end date

    REPEAT
    gtxt_Month := FORMAT(gdat_Start, 0, 4);
    gtxt_Month := COPYSTR(gtxt_Month, 1, (STRPOS(gtxt_Month, ' ') - 1));

    gtxt_Output := gtxt_Output + '\' + gtxt_Month;

    gdat_Start := CALCDATE('+1M', gdat_Start);
    UNTIL
    gdat_Start > gdat_End;

    MESSAGE(gtxt_Output);
  • kolaboykolaboy Member Posts: 446 
    gdat_Start := 010107D; // ***use any start date

gdat_End := 310107D; // ***use any end date

REPEAT
gtxt_Month := FORMAT(gdat_Start, 0, 4);
gtxt_Month := COPYSTR(gtxt_Month, 1, (STRPOS(gtxt_Month, ' ') - 1));

gtxt_Output := gtxt_Output + '\' + gtxt_Month;

gdat_Start := CALCDATE('+1M', gdat_Start);
UNTIL
gdat_Start > gdat_End;

MESSAGE(gtxt_Output);

    I used the above code but it only display the starting month if gdat_Start is used in the section. I also used the gdat_End and it gives the end date too.

    the ideas is to display all months between gdat_Start and gdat_End
    How is the above code going to archeive this? Can you explain further maybe i don't understand it. Tell me what variable is going to be displayed.
    Thanks
  • wonmowonmo Member Posts: 139
    gtxt_Output displays all the months in a message box. You should be seeing a message box with all the months displayed. The expression "gtxt_Month := FORMAT(gdat_Start, 0, 4);" would convert 010108D into "January 1, 2008". the COPYSTR function is extracting just the month portion of this string and adding it to the gtxt_Output variable.
  • kolaboykolaboy Member Posts: 446
    I want the date to display on the report but not on a mesage box. Each of these dates should have a corresponding figure picked from a table.How can i display the months/ Also the months are not displaying instead it display \1 and when i click on OK, it diplays \1.\1 and so on.
    Any ideas.
  • wonmowonmo Member Posts: 139
    didn't consider that you may be using a different date format.

    Instead of using:
    gtxt_Month := FORMAT(gdat_Start, 0, 4);
    gtxt_Month := COPYSTR(gtxt_Month, 1, (STRPOS(gtxt_Month, ' ') - 1));

    Use this instead:
    gtxt_Month := FORMAT(TODAY,0,'<Month Text>'));

    Having it display on a report instead of a message box should be easy after you get this part.
  • kolaboykolaboy Member Posts: 446
    I have done what you suggested but nothing is working.
    Have a look at my report maybe you can import it and try it on you database: 
    OBJECT Report 75022 Employee Profile
{
  OBJECT-PROPERTIES
  {
    Date=16/01/08;
    Time=19:07:58;
    Modified=Yes;
    Version List=SSMS1.5.4;
  }
  PROPERTIES
  {
    OnPreReport=BEGIN
                  IF (Empid = '')
                  THEN ERROR(EmpidNull);
                  //IF (FromDate = 0D) OR (ToDate = 0D) THEN
                     // ERROR(DateBlank);
                  //IF (FromDate > ToDate) THEN
                     // ERROR(DateError);

                  //IF (BenefitsPayable = 0.00)
                  //THEN MESSAGE(bepaynull);
                END;

    Orientation=Portrait;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table5207;
        DataItemTableView=SORTING(From Date,To Date)
                          ORDER(Ascending);
        PrintOnlyIfDetail=No;
        OnAfterGetRecord=BEGIN
                           // Get Name
                           Employee.GET(Empid);
                           EmployeeName := Employee."First Name" + Space + Employee."Middle Name" + Space + Employee."Last Name";
                           BirthDate := Employee."Birth Date";
                           EVALUATE(NewBirthDate,BirthDate);
                           RetireDate1 := CALCDATE('<60Y>',NewBirthDate);
                           RetireDate := FORMAT(RetireDate1,0,'<Day>/<Month>/<Year4>');
                           AdmissionDate := Employee."Admission Date";
                           EmployerCode := Employee."Employer No.";
                           Address := Employee.Address + Employee."Address 2";
                           Customer.GET(EmployerCode);
                           EmployerName := Customer.Name;
                           Balances.GET(Empid);
                           BalBrFwd := Balances.BalBrFwd;
                           User.GET(USERID);
                           UserName := User.Name;
                           // Get Name ends above.

                           // Initialize variables
                           FromDate :=ApplicationDate;
                           ToDate :=TODAY;
                           Amount := 0; TotalAmount := 0; CurrAccTotal := 0;
                           ContMonth := '';
                           MonthContAmount := 0;
                           // Variable initialization ends above.

                           // Code to get Rate from Benefits
                           BenefitsKey.SETRANGE("Period Start",FromDate,ToDate);
                           BenefitsKey.SETFILTER("Period Start",'<=%1',FromDate);
                           BenefitsKey.SETFILTER("Period End",'>=%1',ToDate);
                           // Code Ends above.



                           // Code to Get Previous Contributionsributions
                           ContributionsOld.SETRANGE("Employee No.",Empid);
                           ContributionsOld.SETRANGE("From Date",FromDate,ToDate);
                           ContributionsOld.SETFILTER("From Date",'<%1',FromDate);
                           ContributionsOld.SETFILTER("To Date",'<%1',ToDate);
                           // Code Ends Above.

                           IF ContributionsOld.FIND('-')
                           THEN BEGIN
                           OldAmount := ContributionsOld.Quantity;
                           IF ContributionsOld.NEXT <> 0
                           THEN REPEAT
                           OldAmount := OldAmount + ContributionsOld.Quantity;
                           UNTIL ContributionsOld.NEXT = 0;
                           END;


                           IF BenefitsKey.FIND('-')
                           THEN BEGIN
                           Rate := BenefitsKey.Rate;
                           END;

                           //First Code brought down here
                           Contributions.SETRANGE("Employee No.",Empid);
                           Contributions.SETRANGE("From Date",FromDate,ToDate);
                           Contributions.SETFILTER("From Date",'>=%1',FromDate);
                           Contributions.SETFILTER("To Date",'<=%1',ToDate);


                           //Code to Count Months
                           //IF (Contributions."From Date" <> 0D) AND (Contributions."To Date" > Contributions."From Date") THEN BEGIN
                              // Calendar.RESET;
                             //  Calendar.SETRANGE("Period Type",Calendar."Period Type"::Month);
                              // Calendar.SETRANGE("Period Start",Contributions."From Date",Contributions."To Date");
                             // Months := Calendar.COUNT;
                           //END ELSE
                           //     Months := 0;
                           // Code Ends above.

                           //First Code brought down here
                           Cont.SETRANGE("Employee No.",Empid);
                           Cont.SETRANGE("From Date",FromDate,ToDate);
                           Cont.SETFILTER("From Date",'>=%1',FromDate);
                           Cont.SETFILTER("To Date",'<=%1',ToDate);

                           IF Cont.FIND('-')
                           THEN  BEGIN
                           TotalAmount := Cont.Quantity;
                           Months := Cont.COUNT;
                           IF Cont.NEXT <> 0
                           THEN REPEAT
                           TotalAmount := TotalAmount + Cont.Quantity;
                           UNTIL Cont.NEXT = 0;
                           END;


                           Amount := (TotalAmount * Rate * Months)/24;
                           CurrAccTotal := Amount + TotalAmount;

                           BalInt := BalBrFwd * Rate * Months/12;
                           GrandTotal := BalBrFwd + CurrAccTotal + BalInt;
                           CompanyInfo.GET;
                           CompanyInfo.CALCFIELDS(Picture);

                           //Formatting Months Below
                           ContMonth := FORMAT(Contributions."To Date",0,'<Month Text>, <Year4>');
                           MonthContAmount := Contributions.Quantity;
                           EndDate := FORMAT(ToDate,0,'<Day> <Month Text>,  <Year4>');
                           BeginDate := FORMAT(FromDate,0,'<Day> <Month Text>,  <Year4>');
                           // Formatting Ends above line.

                           IF (ContMonth = '' ) AND (MonthContAmount = 0.00)
                           THEN BEGIN
                            CurrReport.SKIP;
                            END;

                            RateDisplay := Rate * 100;
                            AmountPayable := (BenefitsPayable * GrandTotal)/100;




                         END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=19800;
            SectionHeight=10575;
          }
          CONTROLS
          {
            { 1000000002;TextBox;300  ;423  ;1800 ;423  ;HorzAlign=Left;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=TODAY }
            { 1000000003;Label  ;3900 ;3807 ;8250 ;423  ;HorzAlign=Center;
                                                         VertAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=SOCIAL SECURITY AND HOUSING FINANCE CORPORATION }
            { 1000000004;Label  ;4500 ;4230 ;5850 ;423  ;HorzAlign=Center;
                                                         VertAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=NATIONAL PROVIDENT FUND SCHEME }
            { 1000000005;Label  ;4500 ;4653 ;6450 ;423  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=EMPLOYEE MEMBERSHIP PROFILE }
            { 1000000007;PictureBox;3900;0  ;8550 ;3384 ;Border=Yes;
                                                         SourceExpr=CompanyInfo.Picture }
            { 1000000008;Label  ;0    ;5922 ;4050 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=SOCIAL SECURITY NUMBER: }
            { 1000000009;Label  ;0    ;6768 ;2550 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=EMPLOYEE NAME: }
            { 1000000010;Label  ;7800 ;5922 ;3300 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=CURRENT EMPLOYER: }
            { 1000000011;TextBox;4050 ;5922 ;2550 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=Empid }
            { 1000000012;TextBox;2700 ;6768 ;4200 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=EmployeeName }
            { 1000000013;TextBox;11100;5922 ;8250 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=EmployerName }
            { 1000000006;Label  ;0    ;7614 ;3450 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=DATE OF BIRTH            : }
            { 1000000014;Label  ;0    ;8460 ;3450 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=DATE OF ADMISSION  : }
            { 1000000060;Label  ;0    ;9306 ;3450 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=DATE OF RETIREMENT: }
            { 1000000061;TextBox;3750 ;7614 ;2100 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=BirthDate }
            { 1000000062;TextBox;3750 ;8460 ;2100 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=AdmissionDate }
            { 1000000016;TextBox;3750 ;9306 ;2100 ;423  ;HorzAlign=Left;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=RetireDate }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=19800;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1000000000;TextBox;0    ;0    ;2550 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=ContMonth }
            { 1000000001;TextBox;2700 ;0    ;2850 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=MonthContAmount }
            { 1000000033;TextBox;0    ;846  ;1500 ;423  ;SourceExpr=gtxt_Output }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=19800;
            SectionHeight=3807;
          }
          CONTROLS
          {
            { 1000000021;Label  ;0    ;1692 ;2700 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=BALANCE  B/FWD [ }
            { 1000000022;TextBox;2700 ;1692 ;3450 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=BeginDate }
            { 1000000023;TextBox;6750 ;1692 ;2550 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr=BalBrFwd }
            { 1000000027;Label  ;0    ;846  ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=ACCUMULATED MONTHS OF CONTRIBUTIONS FROM [ }
            { 1000000018;TextBox;7500 ;846  ;1650 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=FromDate }
            { 1000000020;Label  ;9000 ;846  ;450  ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=] : }
            { 1000000024;TextBox;10050;846  ;1500 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=Months }
            { 1000000025;Label  ;6150 ;1692 ;450  ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=] : }
            { 1000000015;Label  ;0    ;3384 ;8850 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=PREVIOUS EMPLOYER (S) : }
            { 1000000030;Label  ;12000;3384 ;2250 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=From Date }
            { 1000000032;Label  ;14700;3384 ;2100 ;423  ;HorzAlign=Right;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=To Date }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=0;
        DataItemTable=Table5214;
        DataItemTableView=SORTING(Employee No.,Line No.)
                          ORDER(Ascending);
        OnAfterGetRecord=BEGIN
                           "Misc. Article Information".SETRANGE("Employee No.",Empid);
                           "Misc. Article Information".SETFILTER("Employee No.",Empid);
                         END;

        DataItemLinkReference=Contributions;
        DataItemLink=Employee No.=FIELD(Employee No.);
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=19800;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000026;TextBox;0    ;0    ;2400 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr="Employer No." }
            { 1000000029;TextBox;2250 ;0    ;9450 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=Employer }
            { 1000000028;TextBox;11550;0    ;2400 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr="From Date" }
            { 1000000031;TextBox;14700;0    ;2250 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr="To Date" }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=19800;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1000000017;Label  ;0    ;846  ;2250 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=PRINTED BY: }
            { 1000000019;TextBox;2250 ;846  ;11850;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=UserName }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9900;
      Height=6490;
    }
    CONTROLS
    {
      { 1000000000;Label  ;550  ;880  ;3300 ;440  ;FontBold=Yes;
                                                   CaptionML=ENU=Employee ID }
      { 1000000001;Label  ;440  ;3080 ;3300 ;440  ;Visible=No;
                                                   FontBold=Yes;
                                                   CaptionML=ENU=Total Benefit Payable }
      { 1000000002;TextBox;5060 ;880  ;1810 ;440  ;SourceExpr=Empid;
                                                   TableRelation=Employee }
      { 1000000003;TextBox;5060 ;3080 ;1700 ;440  ;Visible=No;
                                                   SourceExpr=BenefitsPayable }
      { 1000000004;Label  ;550  ;1650 ;3300 ;440  ;Visible=No;
                                                   FontBold=Yes;
                                                   CaptionML=ENU=Date From }
      { 1000000005;Label  ;550  ;2310 ;3300 ;440  ;Visible=No;
                                                   FontBold=Yes;
                                                   CaptionML=ENU=Date To }
      { 1000000006;TextBox;5060 ;1650 ;1700 ;440  ;Visible=No;
                                                   SourceExpr=FromDate }
      { 1000000007;TextBox;5060 ;2420 ;1700 ;440  ;Visible=No;
                                                   SourceExpr=ToDate }
    }
  }
  CODE
  {
    VAR
      Empid@1000000000 : Code[20];
      BenefitsPayable@1000000001 : Decimal;
      TotalAmount@1000000003 : Decimal;
      Amount@1000000004 : Decimal;
      ClosingBalance@1000000005 : Decimal;
      Calendar@1000000006 : Record 2000000007;
      Months@1000000007 : Integer;
      OpeningBalance@1000000008 : Decimal;
      CurrAccTotal@1000000009 : Decimal;
      BenefitsKey@1000000010 : Record 75012;
      FromDate@1000000022 : Date;
      ToDate@1000000023 : Date;
      CompanyInfo@1000000011 : Record 79;
      BalBrFwd@1000000012 : Decimal;
      BalInt@1000000013 : Decimal;
      Employee@1000000014 : Record 5200;
      EmployeeName@1000000015 : Text[250];
      EmployerCode@1000000016 : Code[20];
      EmployerName@1000000017 : Text[250];
      Customer@1000000018 : Record 18;
      Space@1000000020 : TextConst 'ENU=" "';
      BirthDate@1000000019 : Text[30];
      AdmissionDate@1000000021 : Text[30];
      OldAmount@1000000025 : Decimal;
      GrandTotal@1000000026 : Decimal;
      ContMonth@1000000027 : Text[250];
      MonthContAmount@1000000028 : Decimal;
      Rate@1000000029 : Decimal;
      ContributionsOld@1000000002 : Record 5207;
      Cont@1000000024 : Record 5207;
      EmpidNull@1000000030 : TextConst 'ENU=You must enter Employee #';
      DateBlank@1000000031 : TextConst 'ENU=You must enter Date From and Date To';
      DateError@1000000032 : TextConst 'ENU=Date From must be earlier than Date To';
      msg@1000000033 : TextConst 'ENU=You did not enter Bal. B/FWD. % System will automatically calculate it if contributions exist prior to specified date.';
      BeginDate@1000000035 : Text[30];
      EndDate@1000000034 : Text[30];
      Balances@1000000036 : Record 75020;
      RateDisplay@1000000037 : Decimal;
      AmountPayable@1000000038 : Decimal;
      bepaynull@1000000039 : TextConst 'ENU=You did not provide percentage of  Benefits payable';
      Address@1000000040 : Text[250];
      User@1000000041 : Record 2000000002;
      UserName@1000000042 : Text[100];
      RetireDate@1000000043 : Text[30];
      NewBirthDate@1000000044 : Date;
      BirthText@1000000045 : Text[30];
      RetireDate1@1000000046 : Date;
      AdmissionDate1@1000000047 : Date;
      EmploymentDate@1000000048 : Date;
      ApplicationDate@1000000049 : Date;
      gdat_Start@1000000050 : Date;
      gdat_End@1000000051 : Date;
      gtxt_Month@1000000052 : Text[30];
      gtxt_Output@1000000053 : Text[30];

    PROCEDURE @1000000000();
    BEGIN
    END;

    BEGIN
    END.
  }
}
    Thanks.
  • Alex_ChowAlex_Chow Member Posts: 5,063
    Check out the Date table.

    Just filter on the following:
    Period Type = Month
    Period Start = 010107..123107

    No complex calculation or programming required. :D
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • kolaboykolaboy Member Posts: 446
    Using the date Table is giving all the months filtered. But the problem is to have this months with there corresponding contribution amount against them. If a months is not contributed for, then the amount should be zero. We getting the contributions amount from a different table called Contributions.
    How can this work?
  • krikikriki Member, Moderator Posts: 9,110
    Each time you have a date-record, use the start and begin date in it to filter the records you need in your other table.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • DenSterDenSter Member Posts: 8,305
    Take a look at the fields in the Date table and see for yourself. There's a start date and an end date. You could filter the date table like Alex suggested, loop trough them and get the amounts from the other table.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • kolaboykolaboy Member Posts: 446
    Sorry, I did but still not working. I use the date table as one of the dataitem but not indented. i wrote my code on the date section to loop through the contribution table and display amounts for months that are contributed for and display zero for months that are not contributed for.
    Please can i see a sample code for this, Maybe i will see what i am not doing.
  • Alex_ChowAlex_Chow Member Posts: 5,063
    Before you post your code, have you looked at the Date table and its structures? The concept is right, however, I think the execution of it may be off.

    Go out, take a deep breath, smoke a cigarette, and come back with a fresh mind. It's just programming. :D
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
Sign In or Register to comment.