How to Count No. of Months?

kolaboy

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.

kine

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...

Alex_Chow

Do you mean the DATE2DMY function?

kolaboy

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.

JohnCon

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.

kolaboy

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.

Savatage

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

kolaboy

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_Cox

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

Savatage

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.
  }
}

kolaboy

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.

kriki

[Topic moved from Navision forum to Navision Tips & Tricks forum]