Does anyone know how I can produce a report to show customers who pay later than their terms state on their customer card.
We wish to create a blacklist of late payers, so we can maybe change their terms of sale.
You have to process all Customer ledger entry (Document Type: Invoice or Credit Memo) and, for each of them, compare the Orignal Due Date with the Actual Payment Date.
CustLedgEntry2.RESET;
CustLedgEntry.SETRANGE("Customer No.","No.");
CLEAR(CustLedgEntry2);
// YOU CAN FILTER ON DOC TYPE (Invoice, Credit Memo)
IF CustLedgEntry.FIND('-') THEN
REPEAT
CLEAR(CustLedgEntry2);
CustLedgEntry2.RESET;
IF NOT CustLedgEntry.Open THEN BEGIN
IF CustLedgEntry."Closed by Entry No." <> 0 THEN BEGIN
CustLedgEntry2.GET(CustLedgEntry."Closed by Entry No.");
CustLedgEntry2.MARK(TRUE);
END;
CustLedgEntry2.SETCURRENTKEY("Closed by Entry No.");
CustLedgEntry2.SETRANGE("Closed by Entry No.",CustLedgEntry."Entry No.");
IF CustLedgEntry2.FIND('-') THEN
REPEAT
CustLedgEntry2.MARK(TRUE);
UNTIL CustLedgEntry2.NEXT = 0;
CustLedgEntry2.SETCURRENTKEY("Entry No.");
CustLedgEntry2.SETRANGE("Closed by Entry No.");
CustLedgEntry2.MARKEDONLY(TRUE);
IF CustLedgEntry2.FIND('-') THEN REPEAT
IF (NOT CustLedgEntry2.Open) AND (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Payment) THEN BEGIN
TempVarDelay += (CustLedgEntry2."Due Date" - CustLedgEntry."Due Date");
CounterMov += 1;
END;
UNTIL CustLedgEntry2.NEXT = 0;
END
Hello.
If you are on customer card and you click on Customer button->Entry Statistics
On No. of Documents Tab you'll see Avg. Collection Period (Days).
All the logic is in there and it will give you the average based on period
Here is report that will give you what you are looking for. You simply specify the date range and the report will give you the avg. collection period, it also prints payment terms so you can compare them and make changes to your customers.
thanks guys,
I there not a standard report that can report the average payment time for all customers rather than looking at individual customer cards.
The two reports you have offered may do the full blown report I asked for but looks a little more complicated to set up.
Jut noticed that if I run the report for a month or two or three
the Ave Monthly Sales is Correct - But If I run it for a year it divides the
Sales ($) by 13 not 12 making the Avg Monthly Sales calc incorrect.
Date Filter: 010107..123107
Comments
Create a Report wit Customer as DataItem.
On Customer - OnAfterGetRecord put code like:
AverageDelay := 0;
TempVarDelay := 0;
TempVarDelay := 0;
CustLedgEntry2.RESET;
CustLedgEntry.SETRANGE("Customer No.","No.");
CLEAR(CustLedgEntry2);
// YOU CAN FILTER ON DOC TYPE (Invoice, Credit Memo)
IF CustLedgEntry.FIND('-') THEN
REPEAT
CLEAR(CustLedgEntry2);
CustLedgEntry2.RESET;
IF NOT CustLedgEntry.Open THEN BEGIN
IF CustLedgEntry."Closed by Entry No." <> 0 THEN BEGIN
CustLedgEntry2.GET(CustLedgEntry."Closed by Entry No.");
CustLedgEntry2.MARK(TRUE);
END;
CustLedgEntry2.SETCURRENTKEY("Closed by Entry No.");
CustLedgEntry2.SETRANGE("Closed by Entry No.",CustLedgEntry."Entry No.");
IF CustLedgEntry2.FIND('-') THEN
REPEAT
CustLedgEntry2.MARK(TRUE);
UNTIL CustLedgEntry2.NEXT = 0;
CustLedgEntry2.SETCURRENTKEY("Entry No.");
CustLedgEntry2.SETRANGE("Closed by Entry No.");
CustLedgEntry2.MARKEDONLY(TRUE);
IF CustLedgEntry2.FIND('-') THEN REPEAT
IF (NOT CustLedgEntry2.Open) AND (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Payment) THEN BEGIN
TempVarDelay += (CustLedgEntry2."Due Date" - CustLedgEntry."Due Date");
CounterMov += 1;
END;
UNTIL CustLedgEntry2.NEXT = 0;
END
UNTIL CustLedgEntry.NEXT = 0;
AverageDelay:= TempVarDelay /TempVarDelay
If you are on customer card and you click on Customer button->Entry Statistics
On No. of Documents Tab you'll see Avg. Collection Period (Days).
All the logic is in there and it will give you the average based on period
Here is report that will give you what you are looking for. You simply specify the date range and the report will give you the avg. collection period, it also prints payment terms so you can compare them and make changes to your customers.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
I there not a standard report that can report the average payment time for all customers rather than looking at individual customer cards.
The two reports you have offered may do the full blown report I asked for but looks a little more complicated to set up.
alan
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
Great sharing spirit !
Jon.
Out of 55K companies out there, I'm guessing 30-50 percent of the reports are same report that is rewritten for the customer.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
- thanks for the report.
http://www.BiloBeauty.com
http://www.autismspeaks.org
the Ave Monthly Sales is Correct - But If I run it for a year it divides the
Sales ($) by 13 not 12 making the Avg Monthly Sales calc incorrect.
Date Filter: 010107..123107
Anyone else?
http://savatage99.googlepages.com/Incor ... yTotal.pdf
http://www.BiloBeauty.com
http://www.autismspeaks.org
I fixed the object above. so you can copy and paste the object.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
http://www.BiloBeauty.com
http://www.autismspeaks.org
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n