Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 326 Dynamics CRM
- 110 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 994 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Why is it that the Aged Accounts Receivable Report
dohertyk
Member Posts: 94
Some background,
I started working on the Aged Accounts Receivable Report. I actually copied it, and then created a new report.
In this new report I ended up writting out a lot of the fields that are not only in this report. But also some from Form 302, Customer Entry Statistics.
I thought I had all the information written to the file correctly. Until it came to verifying some numbers.
You can imagine the egg on my face when my supervisor showed me the easiest way to check. Just take all the customer ledger entries for any given customer and then total the "Amount" field.
It's not a little descrepency.. the numbers are completely off.
Then I talk to other people later, who tell me it's been like that for sometime. I guess when they speak with the guys who they contract out to development, they are known for sending back code with errors in it.
Apparently the information that gets sent back is accurate only on the day it is generated. However, if you change the Workdate to the days date that you want to generate the report for, it's completely off.
Now I've already given a sample report of previously incorrect data to a third party reporting agency. And now I have to fix it so that way when it comes to giving the real data. That it is accurate, as we will be reporting this information on a monthly basis.
Any assistance at this point would be much appreciated, Let me know what you would like to see.
Sincerely,
Kevin
I started working on the Aged Accounts Receivable Report. I actually copied it, and then created a new report.
In this new report I ended up writting out a lot of the fields that are not only in this report. But also some from Form 302, Customer Entry Statistics.
I thought I had all the information written to the file correctly. Until it came to verifying some numbers.
You can imagine the egg on my face when my supervisor showed me the easiest way to check. Just take all the customer ledger entries for any given customer and then total the "Amount" field.
It's not a little descrepency.. the numbers are completely off.
Then I talk to other people later, who tell me it's been like that for sometime. I guess when they speak with the guys who they contract out to development, they are known for sending back code with errors in it.
Apparently the information that gets sent back is accurate only on the day it is generated. However, if you change the Workdate to the days date that you want to generate the report for, it's completely off.
Now I've already given a sample report of previously incorrect data to a third party reporting agency. And now I have to fix it so that way when it comes to giving the real data. That it is accurate, as we will be reporting this information on a monthly basis.
Any assistance at this point would be much appreciated, Let me know what you would like to see.
Sincerely,
Kevin
0
Answers
You can get the the sp1 version of the report and apply the changes.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
{ *****************************************************************
Much of this Source Code was taken from different Reports or Forms
******************************************************************* }
{***** My Source *****}
Report 10040 - Aged Accounts Receivable
This Report was copied and then modified to generate this report.
This particular Report was used as a starting off point in order to get the information regarding
current & past due amounts.
Form 10011 - Credit Management
Form 10007 - Customer Credit Information
Form 302 - Customer Entry Statistics
These Forms were were eached used as a sample to gather information such as the Last Sale Date, the Average
Collection Date, and to get the Highest Balance over the past 2 years.
Report - OnInitReport()
{ ***********************************************
This is to open up the file for writing.
************************************************* }
{***** My Source *****}
IF WORKDATE = 0D THEN BEGIN
WorkDateString := '';
END ELSE BEGIN
WorkDateString := FORMAT(WORKDATE, 0, '<Day,2><Month,2><Year,2>');
END;
ExportFile.TEXTMODE(FALSE);
ExportFile.CREATE(ENVIRON('UserProfile') + '\Desktop\'+COMPANYNAME+' Trade File - As of ' + WorkDateString + '.txt');
ExportFile.CREATEOUTSTREAM(Stream);
Report - OnPreReport()
{ *********************************************** }
{***** Not My Source *****}
IF AgingMethod = AgingMethod::"Due Date" THEN BEGIN
PeriodEndingDate[2] := PeriodEndingDate[1];
FOR j := 3 TO 4 DO
PeriodEndingDate[j] := CALCDATE('-('+PeriodCalculation+')',PeriodEndingDate[j-1]);
END ELSE BEGIN
FOR j := 2 TO 4 DO
PeriodEndingDate[j] := CALCDATE('-('+PeriodCalculation+')',PeriodEndingDate[j-1]);
END;
PeriodEndingDate[5] := 0D;
CompanyInformation.GET('');
GLSetup.GET;
FilterString := Customer.GETFILTERS;
Report - OnPostReport()
{ ***********************************************
This closes the file after having written to it.
************************************************* }
{***** My Source *****}
ExportFile.CLOSE;
Report - OnCreateHyperlink(VAR URL : Text[1024])
{ *********************************************** }
Report - OnHyperlink(URL : Text[1024])
{ *********************************************** }
InsertTemp(VAR CustLedgEntry : Record "Cust. Ledger Entry")
{ *********************************************** }
{***** Not My Source *****}
WITH TempCustLedgEntry DO BEGIN
IF GET(CustLedgEntry."Entry No.") THEN
EXIT;
TempCustLedgEntry := CustLedgEntry;
CASE AgingMethod OF
AgingMethod::"Due Date" :
"Posting Date" := "Due Date";
AgingMethod::"Document Date" :
"Posting Date" := "Document Date";
END;
INSERT;
END;
CalcPercents(Total : Decimal;Amounts : ARRAY [4] OF Decimal)
{ *********************************************** }
{***** Not My Source *****}
CLEAR(PercentString);
IF Total <> 0 THEN
FOR i := 1 TO 4 DO BEGIN
Percent := Amounts / Total * 100.0;
IF STRLEN(FORMAT(ROUND(Percent))) + 4 > MAXSTRLEN(PercentString[1]) THEN
PercentString := PADSTR(PercentString,MAXSTRLEN(PercentString),'*')
ELSE BEGIN
PercentString := FORMAT(ROUND(Percent));
j := STRPOS(PercentString,'.');
IF j = 0 THEN
PercentString := PercentString + '.00'
ELSE IF j = STRLEN(PercentString) - 1 THEN
PercentString := PercentString + '0';
PercentString := PercentString + '%';
END;
END;
GetCurrencyRecord(VAR Currency : Record Currency;CurrencyCode : Code[10])
{ *********************************************** }
{***** Not My Source *****}
IF CurrencyCode = '' THEN BEGIN
CLEAR(Currency);
Currency.Description := GLSetup."LCY Code";
Currency."Amount Rounding Precision" := GLSetup."Amount Rounding Precision";
END ELSE
IF Currency.Code <> CurrencyCode THEN
Currency.GET(CurrencyCode);
GetCurrencyCaptionCode(CurrencyCode : Code[10]) : Text[80]
{ *********************************************** }
{***** Not My Source *****}
IF PrintAmountsInLocal THEN BEGIN
IF CurrencyCode = '' THEN
EXIT('101,1,' + Text001)
ELSE BEGIN
GetCurrencyRecord(Currency,CurrencyCode);
EXIT('101,4,' + STRSUBSTNO(Text001,Currency.Description));
END;
END ELSE
EXIT('');
UpdateDaysToPay(NoOfDays : Integer)
{ *********************************************** }
{***** Not My Source *****}
DaysToPay := DaysToPay + NoOfDays;
NoOfInv := NoOfInv + 1;
Totals - OnPreDataItem()
{ *********************************************** }
{***** Not My Source *****}
CurrReport.CREATETOTALS(AmountDueToPrint,AmountDue);
SETRANGE(Number,1,TempCustLedgEntry.COUNT);
TempCustLedgEntry.SETCURRENTKEY("Customer No.","Posting Date");
Totals - OnAfterGetRecord()
{ *********************************************** }
{***** Not My Source *****}
IF Number = 1 THEN
TempCustLedgEntry.FIND('-')
ELSE
TempCustLedgEntry.NEXT;
TempCustLedgEntry.SETRANGE("Date Filter",0D,PeriodEndingDate[1]);
TempCustLedgEntry.CALCFIELDS("Remaining Amount","Remaining Amt. (LCY)");
IF TempCustLedgEntry."Remaining Amount" = 0 THEN
CurrReport.SKIP;
IF TempCustLedgEntry."Currency Code" <> '' THEN
TempCustLedgEntry."Remaining Amt. (LCY)" :=
ROUND(
CurrExchRate.ExchangeAmtFCYToFCY(
PeriodEndingDate[1],
TempCustLedgEntry."Currency Code",
'',
TempCustLedgEntry."Remaining Amount"));
IF PrintAmountsInLocal THEN BEGIN
TempCustLedgEntry."Remaining Amount" :=
ROUND(
CurrExchRate.ExchangeAmtFCYToFCY(
PeriodEndingDate[1],
TempCustLedgEntry."Currency Code",
Customer."Currency Code",
TempCustLedgEntry."Remaining Amount"),
Currency."Amount Rounding Precision");
AmountDueToPrint := TempCustLedgEntry."Remaining Amount";
END ELSE
AmountDueToPrint := TempCustLedgEntry."Remaining Amt. (LCY)";
CASE AgingMethod OF
AgingMethod::"Due Date" : BEGIN
AgingDate := TempCustLedgEntry."Document Date";
AgeDate := TempCustLedgEntry."Due Date";
END;
AgingMethod::"Trans Date" : BEGIN
AgingDate := TempCustLedgEntry."Document Date";
AgeDate := TempCustLedgEntry."Posting Date";
END;
AgingMethod::"Document Date" : BEGIN
AgingDate := TempCustLedgEntry."Document Date";
AgeDate := TempCustLedgEntry."Document Date";
END;
END;
j := 0;
WHILE AgeDate <= PeriodEndingDate[j+1] DO
j := j + 1;
IF j = 0 THEN
j := 1;
AmountDue[j] := AmountDueToPrint;
"BalanceDue$"[j] := "BalanceDue$"[j] + TempCustLedgEntry."Remaining Amt. (LCY)";
"Cust. Ledger Entry" := TempCustLedgEntry;
{ *********************************************** }
Totals - OnPostDataItem()
{ *********************************************** }
InsertTemp(VAR CustLedgEntry : Record "Cust. Ledger Entry")
{ *********************************************** }
{***** Not My Source *****}
WITH TempCustLedgEntry DO BEGIN
IF GET(CustLedgEntry."Entry No.") THEN
EXIT;
TempCustLedgEntry := CustLedgEntry;
CASE AgingMethod OF
AgingMethod::"Due Date" :
"Posting Date" := "Due Date";
AgingMethod::"Document Date" :
"Posting Date" := "Document Date";
END;
INSERT;
END;
AfterAgeDateEntry - OnPreDataItem()
{ *********************************************** }
{***** Not My Source *****}
// Find ledger entries which are posted after the aging date and if they apply
// to ledger entries posted before the aging date, re-add those entries.
SETFILTER("Posting Date",'%1..',PeriodEndingDate[1]+1);
IF DepositsOnly THEN
SETFILTER("Deposit Document No.",'<>%1','');
AfterAgeDateEntry - OnAfterGetRecord()
{ *********************************************** }
{***** Not My Source *****}
CASE ReceivablesToPrint OF
ReceivablesToPrint::"Non Sale Type Lease":
BEGIN
IF NOT ("Document Type" IN ["Document Type"::" ","Document Type"::Payment]) THEN
IF "Sale Type Lease" THEN
CurrReport.SKIP;
END;
ReceivablesToPrint::"Sale Type Lease":
BEGIN
IF NOT ("Document Type" IN ["Document Type"::" ","Document Type"::Payment]) THEN
IF NOT "Sale Type Lease" THEN
CurrReport.SKIP;
END;
END;
EntryAppMgt.GetAppliedCustEntries(TempAppliedCustLedgEntry,AfterAgeDateEntry,FALSE);
TempAppliedCustLedgEntry.RESET;
TempAppliedCustLedgEntry.SETRANGE("Posting Date",0D,PeriodEndingDate[1]);
IF TempAppliedCustLedgEntry.FIND('-') THEN
REPEAT
InsertTemp(TempAppliedCustLedgEntry);
UNTIL TempAppliedCustLedgEntry.NEXT = 0;
CurrReport.SKIP; // this fools the system into thinking that no details "printed"...yet
AfterAgeDateEntry - OnPostDataItem()
{ *********************************************** }
InsertTemp(VAR CustLedgEntry : Record "Cust. Ledger Entry")
{ *********************************************** }
{***** Not My Source *****}
WITH TempCustLedgEntry DO BEGIN
IF GET(CustLedgEntry."Entry No.") THEN
EXIT;
TempCustLedgEntry := CustLedgEntry;
CASE AgingMethod OF
AgingMethod::"Due Date" :
"Posting Date" := "Due Date";
AgingMethod::"Document Date" :
"Posting Date" := "Document Date";
END;
INSERT;
END;
Cust. Ledger Entry - OnPreDataItem()
{ *********************************************** }
{***** Not My Source *****}
// Find ledger entries which are open and posted before the date of the aging
SETRANGE("Posting Date",0D,PeriodEndingDate[1]);
IF DepositsOnly THEN
SETFILTER("Deposit Document No.",'<>%1','');
Cust. Ledger Entry - OnAfterGetRecord()
{ *********************************************** }
{***** Not My Source *****}
CASE ReceivablesToPrint OF
ReceivablesToPrint::"Non Sale Type Lease":
BEGIN
IF NOT ("Document Type" IN ["Document Type"::" ","Document Type"::Payment]) THEN
IF "Sale Type Lease" THEN
CurrReport.SKIP;
END;
ReceivablesToPrint::"Sale Type Lease":
BEGIN
IF NOT ("Document Type" IN ["Document Type"::" ","Document Type"::Payment]) THEN
IF NOT "Sale Type Lease" THEN
CurrReport.SKIP;
END;
END;
SETRANGE("Date Filter",0D,PeriodEndingDate[1]);
CALCFIELDS("Remaining Amount");
IF "Remaining Amount" <> 0 THEN
InsertTemp("Cust. Ledger Entry");
CurrReport.SKIP; // this fools the system into thinking that no details "printed"...yet
Cust. Ledger Entry - OnPostDataItem()
{ *********************************************** }
InsertTemp(VAR CustLedgEntry : Record "Cust. Ledger Entry")
{ *********************************************** }
{***** Not My Source *****}
WITH TempCustLedgEntry DO BEGIN
IF GET(CustLedgEntry."Entry No.") THEN
EXIT;
TempCustLedgEntry := CustLedgEntry;
CASE AgingMethod OF
AgingMethod::"Due Date" :
"Posting Date" := "Due Date";
AgingMethod::"Document Date" :
"Posting Date" := "Document Date";
END;
INSERT;
END;
Customer - OnPreDataItem()
{ *********************************************** }
{***** Not My Source *****}
CLEAR("BalanceDue$");
Customer - OnAfterGetRecord()
{ *********************************************** }
{***** Not My Source *****}
IF PrintAmountsInLocal THEN BEGIN
GetCurrencyRecord(Currency,"Currency Code");
CurrencyFactor := CurrExchRate.ExchangeRate(PeriodEndingDate[1],"Currency Code");
END;
IF Blocked <> Blocked::" " THEN
BlockedDescription := STRSUBSTNO(Text002, Blocked)
ELSE
BlockedDescription := '';
IF "Credit Limit (LCY)" = 0 THEN BEGIN
CreditLimitToPrint := Text003;
OverLimitDescription := '';
END ELSE BEGIN
SETRANGE("Date Filter",0D,PeriodEndingDate[1]);
CALCFIELDS("Net Change (LCY)");
IF "Net Change (LCY)" > "Credit Limit (LCY)" THEN
OverLimitDescription := Text004
ELSE
OverLimitDescription := '';
IF PrintAmountsInLocal AND ("Currency Code" <> '') THEN
"Credit Limit (LCY)" :=
CurrExchRate.ExchangeAmtLCYToFCY(PeriodEndingDate[1],"Currency Code","Credit Limit (LCY)",CurrencyFactor);
CreditLimitToPrint := FORMAT(ROUND("Credit Limit (LCY)",1));
END;
TempCustLedgEntry.DELETEALL;
LedgEntryLast := 0;
{ ***********************************
Date Account Opened (Format Date)
*********************************** }
{***** My Source *****}
IF Customer."Business Start Date" = 0D THEN BEGIN
BusinessStartDateString := '';
END ELSE BEGIN
BusinessStartDateString := FORMAT(Customer."Business Start Date", 0, '<Day,2><Month,2><Year,2>');
END;
{ ***********************************
Search for Last Sale Date & Format Date
*********************************** }
{***** My Source *****}
LastSaleDate :=0D;
"Cust. Ledger Entry".RESET;
"Cust. Ledger Entry".SETCURRENTKEY("Customer No.","Posting Date","Currency Code");
"Cust. Ledger Entry".SETRANGE("Cust. Ledger Entry"."Customer No." , Customer."No.");
IF "Cust. Ledger Entry".FIND('+') THEN BEGIN
LastSaleDate := "Cust. Ledger Entry"."Posting Date";
END ELSE BEGIN
LastSaleDate := 0D;
END;
IF LastSaleDate = 0D THEN BEGIN
LastSaleDateString := '';
END ELSE BEGIN
LastSaleDateString := FORMAT(LastSaleDate, 0, '<Day,2><Month,2><Year,2>');
END;
{ **********************************************
Calculates Highest Balance & Average Pay Days
********************************************** }
{***** Part My Source *****}
SETRANGE("No.");
FOR m:= 1 TO 6 DO BEGIN
CustLedgEntry[m].SETCURRENTKEY("Document Type","Customer No.","Posting Date");
CustLedgEntry[m].SETRANGE("Document Type",m); // Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund
CustLedgEntry[m].SETRANGE("Customer No.","No.");
IF CustLedgEntry[m].FIND('+') THEN
CustLedgEntry[m].CALCFIELDS(Amount,"Remaining Amount");
END;
CustLedgEntry2.SETCURRENTKEY("Customer No.",Open);
CustLedgEntry2.SETRANGE("Customer No.","No.");
CustLedgEntry2.SETRANGE(Open,TRUE);
IF CustLedgEntry2.FIND('+') THEN
REPEAT
m := CustLedgEntry2."Document Type";
IF m > 0 THEN BEGIN
CustLedgEntry2.CALCFIELDS("Remaining Amt. (LCY)");
TotalRemainAmountLCY[m] := TotalRemainAmountLCY[m] + CustLedgEntry2."Remaining Amt. (LCY)";
END;
UNTIL CustLedgEntry2.NEXT(-1) = 0;
DateFilterCalc.CreateAccountingPeriodFilter(CustDateFilter[1],CustDateName[1],WORKDATE,0);
DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[2],CustDateName[2],WORKDATE,0);
DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[3],CustDateName[3],WORKDATE,-1);
FOR i := 1 TO 3 DO BEGIN
CustLedgEntry2.RESET;
CustLedgEntry2.SETCURRENTKEY("Customer No.","Posting Date");
CustLedgEntry2.SETRANGE("Customer No.","No.");
CustLedgEntry2.SETFILTER("Posting Date",CustDateFilter);
CustLedgEntry2.SETRANGE("Posting Date",0D,CustLedgEntry2.GETRANGEMAX("Posting Date"));
DtldCustLedgEntry2.SETCURRENTKEY("Customer No.","Posting Date");
CustLedgEntry2.COPYFILTER("Customer No.",DtldCustLedgEntry2."Customer No.");
CustLedgEntry2.COPYFILTER("Posting Date",DtldCustLedgEntry2."Posting Date");
DtldCustLedgEntry2.CALCSUMS("Amount (LCY)");
CustBalanceLCY := DtldCustLedgEntry2."Amount (LCY)";
HighestBalanceLCY := CustBalanceLCY;
DaysToPay := 0;
NoOfInv := 0;
CustLedgEntry2.SETFILTER("Posting Date",CustDateFilter);
IF CustLedgEntry2.FIND('+') THEN
REPEAT
m := CustLedgEntry2."Document Type";
IF m > 0 THEN
NoOfDoc[m] := NoOfDoc[m] + 1;
CustLedgEntry2.CALCFIELDS("Amount (LCY)");
CustBalanceLCY := CustBalanceLCY - CustLedgEntry2."Amount (LCY)";
IF CustBalanceLCY > HighestBalanceLCY THEN
HighestBalanceLCY := CustBalanceLCY;
// Optimized Approximation
IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND
NOT CustLedgEntry2.Open
THEN
IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN
UpdateDaysToPay(CustLedgEntry2."Closed at Date" - CustLedgEntry2."Posting Date")
ELSE
IF CustLedgEntry2."Closed by Entry No." <> 0 THEN BEGIN
IF CustLedgEntry3.GET(CustLedgEntry2."Closed by Entry No.") THEN
UpdateDaysToPay(CustLedgEntry3."Posting Date" - CustLedgEntry2."Posting Date");
END ELSE BEGIN
CustLedgEntry3.SETCURRENTKEY("Closed by Entry No.");
CustLedgEntry3.SETRANGE("Closed by Entry No.",CustLedgEntry2."Entry No.");
IF CustLedgEntry3.FIND('+') THEN
UpdateDaysToPay(CustLedgEntry3."Posting Date" - CustLedgEntry2."Posting Date");
END;
UNTIL CustLedgEntry2.NEXT(-1) = 0;
IF NoOfInv <> 0 THEN
AvgDaysToPay := DaysToPay / NoOfInv
ELSE
AvgDaysToPay := 0;
END;
HighestBalance := FORMAT(HighestBalanceLCY,10, '<Integer,10>');
IF HighestBalanceLCY > -0.01 THEN
HighestBalance := HighestBalance + '+'
ELSE
HighestBalance := HighestBalance + '-';
AvgDaysToPayText := FORMAT(AvgDaysToPay,3,'<Integer,3><Filler,0>');
{ ***********************************************
Setup the Customer Status Code
************************************************* }
{***** My Source *****}
{***** Sample :: PADSTR(Customer.Blocked, 30, ' ') *****}
IF Customer.Blocked = 3 THEN BEGIN
CustomerStatusCode := PADSTR('All', 10, ' ')
END ELSE IF Customer.Blocked = 2 THEN BEGIN
CustomerStatusCode := PADSTR('Invoice', 10, ' ')
END ELSE IF Customer.Blocked = 1 THEN BEGIN
CustomerStatusCode := PADSTR('Ship', 10, ' ')
END ELSE IF Customer.Blocked = 0 THEN BEGIN
CustomerStatusCode := PADSTR('', 10, ' ');
END;
{ ***********************************************
This genereates the setup of the text file.
************************************************* }
{***** My Source *****}
CR:=13;
LF:=10;
CRLF:=FORMAT(CR)+FORMAT(LF);
CurrentBalance := FORMAT(AmountDue[1],10,'<Integer,10>');
IF AmountDue[1] > -0.01 THEN
CurrentBalance := CurrentBalance + '+'
ELSE
CurrentBalance := CurrentBalance + '-';
PastDue30 := FORMAT(AmountDue[2],10,'<Integer,10>');
IF AmountDue[2] > -0.01 THEN
PastDue30 := PastDue30 + '+'
ELSE
PastDue30 := PastDue30 + '-';
PastDue60 := FORMAT(AmountDue[3],10,'<Integer,10>');
IF AmountDue[3] > -0.01 THEN
PastDue60 := PastDue60 + '+'
ELSE
PastDue60 := PastDue60 + '-';
PastDue90 := FORMAT(AmountDue[4],10,'<Integer,10>');
IF AmountDue[4] > -0.01 THEN
PastDue90 := PastDue90 + '+'
ELSE
PastDue90 := PastDue90 + '-';
FixedFormatText := ' ';
FixedFormatText := FixedFormatText + PADSTR(Customer."No." , 20 , ' ');
FixedFormatText := FixedFormatText + PADSTR(Customer.Name , 30 , ' ');
FixedFormatText := FixedFormatText + PADSTR(Customer.Address , 30 , ' ');
FixedFormatText := FixedFormatText + PADSTR(Customer.City , 30 , ' ');
FixedFormatText := FixedFormatText + PADSTR(Customer.State , 30 , ' ');
FixedFormatText := FixedFormatText + PADSTR(Customer."Post Code", 20 , ' ');
FixedFormatText := FixedFormatText + PADSTR(Customer."Phone No.", 30 , ' ');
FixedFormatText := FixedFormatText + PADSTR(AvgDaysToPayText , 3 , ' ');
FixedFormatText := FixedFormatText + PADSTR(WorkDateString , 6, ' ');
FixedFormatText := FixedFormatText + PADSTR(BusinessStartDateString, 6, ' ');
FixedFormatText := FixedFormatText + PADSTR(LastSaleDateString , 6, ' ');
Stream.WRITETEXT(FixedFormatText+HighestBalance+CurrentBalance+PastDue30+PastDue60+PastDue90);
Stream.WRITETEXT(CustomerStatusCode+PADSTR(Customer."Shortcut Dimension 6 Code", 20 , ' '));
Stream.WRITETEXT(PADSTR(Customer."Tax Exemption No.", 30, ' '));
Stream.WRITETEXT(CRLF);
Customer - OnPostDataItem()
{ *********************************************** }
{***** My Source *****}
BusinessStartDateString := '';
InsertTemp(VAR CustLedgEntry : Record "Cust. Ledger Entry")
{ *********************************************** }
{***** Not My Source *****}
WITH TempCustLedgEntry DO BEGIN
IF GET(CustLedgEntry."Entry No.") THEN
EXIT;
TempCustLedgEntry := CustLedgEntry;
CASE AgingMethod OF
AgingMethod::"Due Date" :
"Posting Date" := "Due Date";
AgingMethod::"Document Date" :
"Posting Date" := "Document Date";
END;
INSERT;
END;
{ *********************************************** }
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
Thanks Rashed, I wasn't going to paste the report.
That's why I decided to just paste the source, and then a description of the issue.
The report is very big.. and believe me you don't need it. If you wanted to you could always recreate the information on your end with Tables 18 & 21 from your own data.
Or you could compare the information I just pasted with what you have in your Reports, and see what the difference is. That way, you maybe able to tell me which lines are incorrect and which need addressing.
Thanks for all your time,
Kevin
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
I am using Navision 4.00 with Service Pack 3
Thank you for getting back to me Miklos,
I am very new to Navision, and I have no prior experience other then at this job. Can you help me understand how it is that you came to this conclusion? And which change specifically that you are talking about?
Maybe if I see what the change is that you've picked up on and if you could explain to me or post for me a solution which would work in my given situation, that would help me out a tremendous amount.
Thanks for your insight,
Kevin
Rashed,
How do I go about getting a copy of the SP1 version of the Aged Accounts Receivable Report?
And then I can modify that one so that way I can then have my text file created.
Thank you very much in advance,
Kevin
on the Data Item Cust.Ledger entry
open the properties and select
DataItemTableView and remove the table filter
Open=Const(Yes)
the problem with running the report with any date other then today, is it filters out items that are closed after that date with a application entry.
If you remove the filter listed above, the report will look at all the entries and then correctly identify which ones were open at the date you listed and the report will be accurate.
I am not a developer, but this is the best I could figure out, without actually going through it all. this does fix the report for 4.0.
It runs slowers, but it is correct
Wow... Thank you for your suggestion, I will try it out right away.
I really appreciate it.
Kevin
I give it a try, it didn't work.
But on a positive note, it did exactly what you would said with reference to the speed of the report. It made the running of the Aged Accounts Receivable Report much slower, so of course I put it back to the way it was. And the numbers were just as inaccurate.
So that you know what I did to test it. I actually went back to the unmodified version of Report 10040 (prior to me putting stuff into it that allowed it to write it out to a text file. I tried changing the Work Date to 12/31/06 & also the Due Date in the Options tab when you run the Report. And the Aged date by 12/31/06.
There was no success with the results of this report.
If you have any other suggestions, let me know and I will give them a try.
Thanks again,
Kevin
the amount field is the original invoice amount, the remaining can be lower is credit memo's were applied, if partial payment were applied, ect.
Also, you may have payments that were not applied to anything and remain open on the account.
i know with the one change I made above, I can print an aging as of any date and it matches exactly with the general ledger.
Can you give an example of how the report is off.
your report shows
cust#1000 balance $1250 and what makes that up.
and then your actual customer ledger entries. then start to look at which entries on the report are different then the actual customer ledger entries.
Okay..
Here is a perfect example..
Customer Number 1:
His actual balance when done through an SQL query on all the entries for him, credits and debits.
His actual Balance at the end of 2006 is $0.00
However, when done through Navision using the Aged Accounts Receivables Report: with the Workdate and the Due Date and Aged Date By: 12/31/06 is $5,466.29
You can see that this is quite the descrepency. And it's like this for every single entry.
Hope this helps you out.
Kevin
It was suggested to me, that I might want to think about re-writing the entire Aged Accounts Receivable Report. So if anyone has a working fob.. that would be super.
if the customer had a balance at 12/31/06 of $5466.29, and he sent you a check on 1/1/07 for the $5466.29, and you applied that to the customers open entries as normal it would update the remaining amount field to zero. and on 1/1/07 in Navision the aging report would show zero. But on 12/31/06 the aging report would still show $5466.29
If you ran a query on the sql tables, it would show a zero balance, because the sql table query is just looking at the entries through 12/31/06, and all of those are closed and now zero, because of the 1/1/07 payment.
In the above example, if you had no other transactions on 1/1/07 except posting the payment, and you ran your sql quary through 1/1/07 it would show a zero balance also.
I totally agree with this analysis. I have seen it very often, the client does a copy paste of entries into Excel, comes up with a number, and needs it to reconcile. There is no way that you can just report the entries and filter by a date, the numbers will not be correct.
Many times I have had to copy paste all those entries, and manually one by one reconcile them by date to eventually show the customer that the numbers are correct.
of course the report may be wrong, but first step is to be sure that you are using the right answers before looking at the question.
I understand what it is that you are telling me, and If it were just one or two instances I would agree. The problem is that it's 1000's of customers that this problem is happening to.
Actually, I still have yet to find one that has the correct balance for what they should have.
I know that the report is wrong. Even without the aid of the SQL telling me what the numbers should be.
I've even done the report using todays date, and I've also copied and pasted all the entries from the Customer Entry Ledger into Excel and then calculate the totals. And none of the numbers match.
I am convinced there is something wrong with the original Aging Accounts Receivable Report that I originally started this work with. I had even spoken with other people about this who later explained to me that it's been quite some time since it has worked properly.
We even had a conversation with an Accountant about how the numbers couldn't be "that far off", but the fact of the matter is that they are.
If you have source to an Aged Accounts Receivable Report, I would love it if you could share this information with me.
Or do me a favour and just compare the differences between your .fob and the one that I had pasted earlier. That could really help me in resolving my issue.
Thanks again,
Kevin
David,
Another way that I have tried the report is to run it as of today's date.
And also to have put all of the Customer Ledger Entries into excel and run the totals there.
And I have done an SQL query as of today's date.
The Excel and the SQL always match. But Navision isn't quite yet there.
I am not saying that my code is flawless, it could be as simple as when I am writing out to the file at the wrong time or that the totals in the report are being done at the wrong time.
Ex. Customer - OnAfterGetRecord() vs. Totals - OnAfterGetRecord()
Ex. Customer - OnAfterGetRecord() vs. Customer - OnPostDataItem()
I find myself in an aweful situation, it was mostly working properly when I had it in the dataport. but when I took Report 10040, and then copied it and started to modify it to what I had this dataport doing, then things became all buggered up.
I've found myself second guessing a lot of things since.
I am just trying to get this thing done at this point and then to move on to the next.
Kevin
This sort of puts a different twist on it. It sounds like you did not start with a standard Navision report, but based you report on one that had already been broken by a developer somewhere.
The fact that its some time since it worked properly generally implies that at some time it did work, until someone actually broke it.
I think that step one here is to import a standard unmodified AR report, and see if it gives the right numbers. If so I would use it and start again from scratch.
The real kick in the teeth is that when I first started my work with this report. No one had taken the time to tell me that the report was broken.
So now here I am the new guy trying to fix someone else's broken code, and then trying to put in the new stuff. And the fact is, is that I am not that strong a Navision programmer at this time. To really have that be my undertaking unassisted.
I don't know where to get an unmodified Aged Accounts Receivable Report from, but if you have one. I would very much appreciate it if you could share this bit of information with me, and I would be happy to re-do the stuff that I had just finished changing.
Thanks for any assistance that you can provide,
Kevin
No problem, do you want 4.00SP2? What object number is good? Something in the 500xx range?
You can use Report 50082.
Will I have any problems with using that revision 4.00 SP2, if I have navision 4.00 SP3?
Thanks again,
Kevin
http://www.mibuso.com/forum/viewtopic.php?t=15960
I used the Customer Trigger as an example you might have to put it on the c/al code of the totals section of the report to get the proper totals.
Have you tried showing all the values that are making up that $5000 number to see if it belongs to that customer and if an entry is missing or extra???
Note: notice on
http://www.mibuso.com/forum/viewtopic.php?p=72130
I had to put the code on Customer Totals (5) to get the correct amount.
I just added a little code to the already existing Aging Report.
http://www.BiloBeauty.com
http://www.autismspeaks.org
The short answer is yes. It is the same report. I will take what you are telling me here, and try it out. But if you want to post some of what you have done, that would be useful as well.
Did you not find that the totals were off?
I ended up getting a copy of SP2's untouched version of Navision 4.0 Aged Accounts Receivable Report and I will try working with that this week as well.
I will definately report back if the progress I make resolves my own issue. Otherwise I will most likely continue to post in this thread.
Thanks again,
Kevin
Thank you again David,
I am starting to modify this report as soon as I get it to compile properly.
Right now I am getting this error.
You have specified an unknown variable.
SetUseInfoSheed
Define the variable under 'Global C/AL symbols'.
I've got no idea as to how to get this resolved. I ended up trying to define a varialble named SetUseInfoSheed, I even tried SetUseInfoSheet.
The problem with this is, that it just went to the next line and said the same thing but this time instead of SetUseInfoSheed, it instead used the term AddInfoColumn.
So I put it back to the way that it was, I even did the import again, thinking that something had gone wrong with the first import. I do have Navision & the SDK (so I should have JET installed), I also have the NODBC drivers installed. (not applicable in this instance, but I thought I might mention it.)
I am surprised that this just didn't work for me right away. Any suggestions are very welcome. Also how would I specify where the Excel file will be generated. Or it's location.
Thanks in advance,
Kevin
I suggest you delete report 50082, and then reimport the FOB file, then open the report in designer and press F11 to see if it compiles.
Then try out the numbers to see if they are correct.
That report I sent you is basically Report 10040 from US 4.00 SP2 and renamed to 50082.
Sorry I don't have US SP3, but I think it will work.
Okay...
Here I am several hours after trying out things on my own for a bit.
I am no further ahead with respect to the problem with Excel. Instead as a result I just remarked out anything having to do with Excel. I would like to know whether or not I need an ocx or a dll to make that work. Although I don't believe it to be the same as any other automation object. And who knows maybe I could find a use for it. Even though I still need to generate the text file. Sometimes it's just nice to have the excel spreadsheet.
I think with respect to some of the numbers, I am getting the correct numbers, just on the following line. Which leads me to believe that although I have the correct calculations, that I am doing them in the wrong spots.
I've streamlined the code to be just the modifications that I have put into the .fob that was given to me by David Singleton, which was an untouched Nav 4.00 SP2 fob of Report 10040 Aged Accounts Receivable.
It's nearly the end of the workday for me today. But I will check back tomorrow for more suggestions and I will continue trying to just get things done.
Again, thank you all for your help and advice.
Sincerely,
Kevin