How to Count No. of Months?

kolaboykolaboy Member Posts: 446
edited 2008-07-02 in NAV Tips & Tricks
Hi Experts,
I am working on the Cust. Ledger Entry table trying to design the customer Balance to date report.

My problem is i want to count the No. of months from the Posting dates so that i can get the no. of months that are paid in the report.

The posting dates Correspond to the payments made.

I need Assistance in this Please. I want to count the no. of months paid for.
Any idea please?
Thanks.

Comments

  • kinekine Member Posts: 12,562
    What does it means "No. of months". Is it based on calendar month? Or is one month 30 days? Is difference between 30.6.2007 and 1.7.2007 one month or zero? (dd.mm.yyyy) You need to clear this before you can think about how to calculate it...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • kolaboykolaboy Member Posts: 446
    Hi Experts,
    Sorry for the late reply.
    The No. of Months here is based on calendar month. Basically, is like Customers paying for mortgages by installment every Month. Any month they pay should be subtracted from the Principal and each of this months payment is indicated in the Cust. Ledger Entry under the document type as Payment.

    I am trying to use this document type called payment to Count the No. of Months that are being paid, because when there is no payment made the document type column in the report will be empty indicating no payment, so should not be count. Only the payments should be counted for each month.

    I also need the total months in the period i.e From 01/01/07 to date. This one is counting correctly, but it is the No. of months paid that is not counting Correctly.
    Here is the Sample Code i have wrote:

    //Setting Date range(Date to be consirdered)
    FromDate := 010107D;
    ToDate :=TODAY;

    //Code to Count Months in the Period(010107D to TODAY)
    //This one is working correctly
    IF (FromDate <> 0D) AND (ToDate > FromDate) THEN BEGIN
    Calendar.RESET;
    Calendar.SETRANGE("Period Type",Calendar."Period Type"::Month);
    Calendar.SETRANGE("Period Start",FromDate,ToDate);
    Months := Calendar.COUNT;
    END ELSE
    Months := 0;

    //Date Range for the payments
    FromDate1 := 101017D;
    ToDate1 := "Date Filter";


    //Code to Count the No. of Months Paid(Payment mONTHS)
    //This one is the one not working correctly(This is the Problem)
    IF (FromDate1 <> 0D) AND (ToDate1 > FromDate1) THEN BEGIN
    Calendar.RESET;
    Calendar.SETRANGE("Period Type",Calendar."Period Type"::Month);
    Calendar.SETRANGE("Period Start",FromDate1,ToDate1);
    MonthsPaid1 := Calendar.COUNT;
    END ELSE
    MonthsPaid1 := 0;
    //Code Ends above.

    // Code to Calculate payments in Installment
    // This one is working correctly
    IF "Cust. Ledger Entry".FIND('-') THEN BEGIN
    Cust.GET("Customer No.");
    MonthsPaid1 := Calendar.COUNT;
    Arrears := Cust."Monthly Install" + OriginalAmt;
    END ELSE BEGIN
    Arrears := Arrears + Cust."Monthly Install";
    END;
    TotalArr += Arrears;

    TotMonthsArr := Months - MonthsPaid1;
    Arrears2 := TotMonthsArr * Cust."Monthly Install";
    TotArr := TotalArr + Arrears2;

    Can you please check my code and see where the Code that is not working has a problem. I will also like to see the correct code that can solve my problem.
    Thanks.
  • JohnConJohnCon Member Posts: 55
    What is the value of your "Date Filter", is this really a filter?
    You will need to deal with partial months. Try using just a date as the ToDate1 value.
  • kolaboykolaboy Member Posts: 446
    Hi Experts,
    The Date Filter is a filter field. It is used to filter the Dates.

    I would like to have the Code to loop through the Cloumn called Document type and loook for payment under this column and then count it as month. So if it see payment, it should be counted as one and continue the loop untill it finds and payment and count it as two,and so on. This will give me the No. of months paid. This is the only thing that is not working.
    Can anyone help and show me a code that can do this. I will be greatfull if i found one here. Then my problem will be solved.
    Thanks.
  • SavatageSavatage Member Posts: 7,142
    sounds like you just want a COUNT of the number of payment type entries.
  • kolaboykolaboy Member Posts: 446
    Hi Experts,

    sounds like you just want a COUNT of the number of payment type entries.

    Savatage, this is exactly what i am trying to do. But i just don't know how to do it. I tried all i could but to no avail.

    Can you please help me with the code that can solve my problem. I will be greatful if anyone can help me solve this.
    There is suppose to be a loop that can go and search for payment type entry and count as one and skip the ones that are not payment or has blanks. It should count the payment type entries.
    Any idea please?
    Thanks.
  • David_CoxDavid_Cox Member Posts: 509
    kolaboy wrote:
    Hi Experts,

    sounds like you just want a COUNT of the number of payment type entries.

    Savatage, this is exactly what i am trying to do. But i just don't know how to do it. I tried all i could but to no avail.

    Can you please help me with the code that can solve my problem. I will be greatful if anyone can help me solve this.
    There is suppose to be a loop that can go and search for payment type entry and count as one and skip the ones that are not payment or has blanks. It should count the payment type entries.
    Any idea please?
    Thanks.

    Try this Untested code but should give you the help!
    IF "Monthly Install" <= 0 THEN
        CurrReport.SKIP;
    
    SETRANGE("Date Filter");
    CALCFIELDS(Balance);
    IF BALANCE <= 0 THEN
       CurrReport.SKIP;
    
    Calendar.RESET; 
    Calendar.SETRANGE("Period Type",Calendar."Period Type"::Month); 
    
    CustLedgeEntry.RESET;
    CustLedgeEntry.SETCURRENTKEY("Customer No.",Open,Positive);
    CustLedgeEntry.SETRANGE("Customer No.","No.");
    CustLedgeEntry.SETRANGE(Open,TRUE);
    CustLedgeEntry.SETRANGE(Positive,TRUE);
    //Un Applied Payments or Credit Notes ERROR or SKIP
    IF CustLedgeEntry.FINDFIRST THEN
        ERROR('There are Un-Applied payments for %2',"No."); 
    
    CLEAR(AmtDue);
    
    // Find the Debt
    CustLedgeEntry.SETRANGE(Positive,FALSE);
    CustLedgeEntry.FINDFIRST;
    REPEAT
        FromDate := CustLedgeEntry."Posting Date";
        ToDate := TODAY;
        //Or last day Last month
        ToDate := CALCDATE('-CM-1D',TODAY);
        Calendar.SETRANGE("Period Start",FromDate,ToDate); 
        Months := Calendar.COUNT;
        CustLedgeEntry.CALCFIELDS(Amount,"Remaining Amount");
        //Find the Amount Paid over what period (Months Paid)
        IF  ROUND((Amount - "Remaining Amount") / "Monthly Install",1) 
                        < Months THEN
             AmtDue := AmtDue + ("Monthly Install" * Months)-
                                              (Amount - "Remaining Amount");
    UNTIL CustLedgeEntry.NEXT = 0;
    
    IF AmtDue = 0 
       CurrReport.SKIP;
    
    

    David
    Analyst Developer with over 17 years Navision, Contract Status - Busy
    Mobile: +44(0)7854 842801
    Email: david.cox@adeptris.com
    Twitter: https://twitter.com/Adeptris
    Website: http://www.adeptris.com
  • SavatageSavatage Member Posts: 7,142
    Here's a quick report I threw together that counts & shows each payment by customer #. you can use Posting date as your date range...

    if you're not using report # 50064 then here is the fob you can import
    http://savatage99.googlepages.com/Report50064.fob

    I made a post here that shows you how to add Summary or Detail in case you don't want each payment info to show.
    http://www.mibuso.com/forum/viewtopic.php?p=89276

    It will look something like this.
    http://savatage99.googlepages.com/CLEreportsample.JPG

    else here is the code you can playwith

    It basically tablefilterview "Payments" only & count's them - I'm not sure what other info you needed to have. but perhaps this can help
    OBJECT Report 50064 Payment Counter Using CLE
    {
      OBJECT-PROPERTIES
      {
        Date=07/02/07;
        Time=[ 4:00:58 PM];
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
      }
      DATAITEMS
      {
        { PROPERTIES
          {
            DataItemTable=Table21;
            DataItemTableView=SORTING(Customer No.)
                              WHERE(Document Type=FILTER(Payment));
            OnPreDataItem=BEGIN
                            LastFieldNo := FIELDNO("Customer No.");
                            NumPayments := 0;
                          END;
    
            OnAfterGetRecord=BEGIN
                               NumPayments := NumPayments +1;
                             END;
    
            ReqFilterFields=Customer No.,Posting Date;
            TotalFields=Amount;
            GroupTotalFields=Customer No.;
          }
          SECTIONS
          {
            { PROPERTIES
              {
                SectionType=Header;
                PrintOnEveryPage=Yes;
                SectionWidth=18150;
                SectionHeight=1692;
              }
              CONTROLS
              {
                { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                             FontBold=Yes;
                                                             CaptionML=ENU=Payment Counter Using Cust. Ledger Entry }
                { 1000000002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right;
                                                             SourceExpr=FORMAT(TODAY,0,4) }
                { 1000000003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
                { 1000000004;TextBox;17700;423  ;450  ;423  ;CaptionML=ENU=Page;
                                                             SourceExpr=CurrReport.PAGENO }
                { 1000000005;Label  ;16950;423  ;750  ;423  ;ParentControl=1000000004 }
                { 1000000006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right;
                                                             SourceExpr=USERID }
              }
               }
            { PROPERTIES
              {
                SectionType=Header;
                PrintOnEveryPage=Yes;
                SectionWidth=18150;
                SectionHeight=1269;
              }
              CONTROLS
              {
                { 1000000015;Label  ;3900 ;0    ;2400 ;846  ;ParentControl=1000000014;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 1000000018;Label  ;6750 ;0    ;1800 ;846  ;ParentControl=1000000017;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 1000000021;Label  ;9000 ;0    ;4500 ;846  ;ParentControl=1000000020;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 1000000024;Label  ;13950;0    ;1800 ;846  ;ParentControl=1000000023;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
                { 1000000013;Label  ;0    ;0    ;1500 ;846  ;VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes;
                                                             CaptionML=ENU=Payment Number }
                { 1000000016;Shape  ;0    ;846  ;18150;423  ;BorderWidth=2pt;
                                                             ShapeStyle=HorzLine }
                { 1000000012;Label  ;1800 ;0    ;1800 ;846  ;ParentControl=1000000011;
                                                             HorzAlign=Right;
                                                             VertAlign=Bottom;
                                                             FontBold=Yes;
                                                             MultiLine=Yes }
              }
               }
            { PROPERTIES
              {
                SectionType=GroupHeader;
                SectionWidth=18150;
                SectionHeight=0;
                OnPreSection=BEGIN
                               CurrReport.SHOWOUTPUT := FooterPrinted;
                               FooterPrinted := FALSE;
                             END;
    
              }
              CONTROLS
              {
              }
               }
            { PROPERTIES
              {
                SectionType=GroupHeader;
                SectionWidth=18150;
                SectionHeight=846;
                OnPreSection=BEGIN
                               CurrReport.SHOWOUTPUT :=
                                 CurrReport.TOTALSCAUSEDBY = "Cust. Ledger Entry".FIELDNO("Customer No.");
                             END;
    
              }
              CONTROLS
              {
                { 1000000008;TextBox;3150 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                             FontSize=10;
                                                             FontBold=Yes;
                                                             SourceExpr="Customer No." }
                { 1000000009;Label  ;0    ;0    ;3000 ;423  ;ParentControl=1000000008;
                                                             FontSize=10;
                                                             FontBold=Yes }
              }
               }
            { PROPERTIES
              {
                SectionType=GroupHeader;
                SectionWidth=18150;
                SectionHeight=0;
                OnPreSection=BEGIN
                               CurrReport.SHOWOUTPUT :=
                                 CurrReport.TOTALSCAUSEDBY = LastFieldNo;
                               NumPayments := 1;
                             END;
    
              }
              CONTROLS
              {
              }
               }
            { PROPERTIES
              {
                SectionType=Body;
                SectionWidth=18150;
                SectionHeight=423;
              }
              CONTROLS
              {
                { 1000000014;TextBox;3750 ;0    ;2700 ;423  ;HorzAlign=Left;
                                                             SourceExpr="Document Type" }
                { 1000000017;TextBox;6750 ;0    ;1950 ;423  ;HorzAlign=Left;
                                                             SourceExpr="Document No." }
                { 1000000020;TextBox;9000 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                             SourceExpr=Description }
                { 1000000023;TextBox;13950;0    ;1800 ;423  ;HorzAlign=Right;
                                                             SourceExpr=Amount }
                { 1000000000;TextBox;150  ;0    ;1500 ;423  ;SourceExpr=NumPayments }
                { 1000000011;TextBox;1800 ;0    ;1800 ;423  ;SourceExpr="Posting Date" }
              }
               }
            { PROPERTIES
              {
                SectionType=GroupFooter;
                SectionWidth=18150;
                SectionHeight=0;
                OnPreSection=BEGIN
                               IF NOT FooterPrinted THEN
                                 LastFieldNo := CurrReport.TOTALSCAUSEDBY;
                               CurrReport.SHOWOUTPUT := NOT FooterPrinted;
                               FooterPrinted := TRUE;
                             END;
    
              }
              CONTROLS
              {
              }
               }
            { PROPERTIES
              {
                SectionType=GroupFooter;
                SectionWidth=18150;
                SectionHeight=1269;
                OnPreSection=BEGIN
                               CurrReport.SHOWOUTPUT :=
                                 CurrReport.TOTALSCAUSEDBY = "Cust. Ledger Entry".FIELDNO("Customer No.");
                             END;
    
              }
              CONTROLS
              {
                { 1000000025;TextBox;0    ;423  ;4500 ;423  ;FontBold=Yes;
                                                             SourceExpr=TotalFor + FIELDCAPTION("Customer No.") }
                { 1000000026;TextBox;13950;423  ;1800 ;423  ;HorzAlign=Right;
                                                             FontBold=Yes;
                                                             SourceExpr=Amount }
                { 1000000010;TextBox;4950 ;423  ;900  ;423  ;FontBold=Yes;
                                                             SourceExpr=NumPayments }
                { 1000000007;Label  ;6000 ;423  ;1800 ;423  ;HorzAlign=Left;
                                                             FontBold=Yes;
                                                             CaptionML=ENU=Payments Made }
                { 1000000019;Label  ;9000 ;423  ;4500 ;423  ;HorzAlign=Right;
                                                             FontBold=Yes;
                                                             CaptionML=ENU=Total Payments: }
                { 1000000022;Shape  ;0    ;0    ;18150;423  ;BorderWidth=Hairline;
                                                             ShapeStyle=HorzLine }
              }
               }
          }
           }
      }
      REQUESTFORM
      {
        PROPERTIES
        {
          Width=9020;
          Height=3410;
        }
        CONTROLS
        {
        }
      }
      CODE
      {
        VAR
          LastFieldNo@1000000000 : Integer;
          FooterPrinted@1000000001 : Boolean;
          TotalFor@1000000002 : TextConst 'ENU="Total for "';
          NumPayments@1000000003 : Integer;
    
        BEGIN
        END.
      }
    }
    
  • kolaboykolaboy Member Posts: 446
    Hi Experts,
    Thanks you very much. The No. of months paid is working correctly. =D>
    I have one more problem. I want to elliminate the first entry under the arreas column in the report.

    The first entry is the principal amount, therefore it should indicate Zero under the arrears column instead of 560. Here the monthly installment of 60 was added to the 500 under the arrears column to give 560. This addition should start with the second entry and so on. The first entry should have zero as arrears.
    Your helps are welcome again.
    Thanks.
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from Navision forum to Navision Tips & Tricks forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.