Customer data not linked to data on Cust. Ledger entry table
Freaky
Member Posts: 125
Hi Guys,
I have this report that I uses three tables indented in this way
Customer
-Date
--Cust. ledger entry
On the section of the customer body am printing out "customer No." and on the "Cust. ledger entry" body section I want to print out the details of that customer. But the report prints out the details of all customers. How an I solve this. This is the object in text
Can you please?
Thanks in advance.
I have this report that I uses three tables indented in this way
Customer
-Date
--Cust. ledger entry
On the section of the customer body am printing out "customer No." and on the "Cust. ledger entry" body section I want to print out the details of that customer. But the report prints out the details of all customers. How an I solve this. This is the object in text
OBJECT Report 75046 Mortgage
{
OBJECT-PROPERTIES
{
Date=13/05/09;
Time=11:31:37;
Modified=Yes;
Version List=NCM/MLS;
}
PROPERTIES
{
OnPreReport=BEGIN
DateFilt:=Customer.GETFILTER(Customer."Date Filter");
CompanyInformation.GET;
CompanyInformation.CALCFIELDS(Picture);
END;
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table18;
NewPagePerRecord=No;
PrintOnlyIfDetail=No;
OnPreDataItem=BEGIN
IF "Cust. Ledger Entry".GETFILTERS <> '' THEN
SETFILTER("No.","Cust. Ledger Entry"."Customer No.");
FilterText := 'Filter: ' + "Cust. Ledger Entry".GETFILTERS;
END;
OnAfterGetRecord=BEGIN
FinChrgTerm.GET("Fin. Charge Terms Code");
IF NOT FinChrgTerm.GET("Fin. Charge Terms Code") THEN
FinChrgTerm."Interest Rate" :=0;
//setrange("date filter","cust. ledger entry"."posting date",date."period start");
//SETFILTER("No.","Cust. Ledger Entry"."Customer No.");
END;
ReqFilterFields=No.,Date Filter,Customer Posting Group;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=20400;
SectionHeight=5922;
}
CONTROLS
{
{ 1000000002;TextBox;15000;0 ;3150 ;423 ;HorzAlign=Right;
SourceExpr=FORMAT(TODAY,0,4) }
{ 1000000003;TextBox;0 ;423 ;5400 ;423 ;Visible=No;
Enabled=No;
FontSize=8;
SourceExpr=COMPANYNAME }
{ 1000000004;TextBox;17700;423 ;450 ;423 ;CaptionML=ENU=Page;
SourceExpr=CurrReport.PAGENO }
{ 1000000005;Label ;16950;423 ;750 ;423 ;ParentControl=1000000004 }
{ 1000000042;Label ;4950 ;4653 ;9900 ;423 ;HorzAlign=Center;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=MORTGAGE STATEMENT }
{ 1000000050;Label ;7650 ;5076 ;1800 ;423 ;Visible=Yes;
HorzAlign=Left;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=Between: }
{ 1000000027;TextBox;9300 ;5076 ;4500 ;423 ;HorzAlign=Left;
FontSize=9;
FontBold=Yes;
SourceExpr=GETFILTER("Date Filter") }
{ 1000000054;PictureBox;6150;0 ;6750 ;4230 ;SourceExpr=CompanyInformation.Picture }
{ 1000000031;Label ;14250;846 ;2250 ;846 ;HorzAlign=Left;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Accumulated Arrears }
{ 1000000001;Label ;14250;846 ;1500 ;846 ;HorzAlign=Left;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Arrears }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=20400;
SectionHeight=2961;
}
CONTROLS
{
{ 1000000008;TextBox;3150 ;0 ;1500 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="No." }
{ 1000000009;Label ;0 ;0 ;3000 ;423 ;ParentControl=1000000008;
FontSize=8;
FontBold=Yes }
{ 1000000011;TextBox;3150 ;423 ;6150 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=Name }
{ 1000000012;Label ;0 ;423 ;3000 ;423 ;ParentControl=1000000011;
FontSize=8;
FontBold=Yes }
{ 1000000014;TextBox;12300;423 ;3000 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Monthly Install" }
{ 1000000015;Label ;9600 ;423 ;2250 ;423 ;ParentControl=1000000014;
FontSize=8;
FontBold=Yes }
{ 1000000017;TextBox;3150 ;1269 ;1800 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Loan Amount" }
{ 1000000018;Label ;0 ;1269 ;3000 ;423 ;ParentControl=1000000017;
FontSize=8;
FontBold=Yes;
CaptionML=ENU=Plot Cost }
{ 1000000020;TextBox;3150 ;1692 ;1800 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Down Payment" }
{ 1000000021;Label ;0 ;1692 ;3000 ;423 ;ParentControl=1000000020;
FontSize=8;
FontBold=Yes }
{ 1000000023;TextBox;3150 ;2115 ;1500 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Plot No." }
{ 1000000024;Label ;0 ;2115 ;3000 ;423 ;ParentControl=1000000023;
FontSize=8;
FontBold=Yes }
{ 1000000036;TextBox;12300;1269 ;1800 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr=FinChrgTerm."Interest Rate" }
{ 1000000026;Label ;9600 ;1269 ;2100 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU=Interest Rate }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table2000000007;
PrintOnlyIfDetail=No;
OnPreDataItem=BEGIN
//setfilter(custom."No.","custL."customer no.");
END;
OnAfterGetRecord=BEGIN
//SETRANGE(custl."customer no.",custom."no.");
END;
DataItemLink=Period Start=FIELD(Date Filter);
}
SECTIONS
{
}
}
{ PROPERTIES
{
DataItemIndent=2;
DataItemTable=Table21;
DataItemTableView=SORTING(Customer No.,Posting Date)
WHERE(Reversed=CONST(No));
PrintOnlyIfDetail=No;
OnPreDataItem=BEGIN
//setfilter(custom."No.","cust. ledger entry"."customer no.");
//"Cust. Ledger Entry".SETFILTER("Posting Date",DateFilt);
FinchargeTotal:=0;
PrincipalTotal:=0;
ArreasVarTotal:=0;
CustBal1 := 0;
TotCustBal1:=0;
NumPayments:=0;
Tpay := 0;
diff:=0;
T4:=0;
month:=0;
month1:=0;
END;
OnAfterGetRecord=BEGIN
//setfilter("customer no.",customer."no." );
IF Customer.GET("Customer No.") THEN BEGIN
postdate:="Cust. Ledger Entry"."Posting Date";
monthyear:=FORMAT("Posting Date",0,'<month text> <year4>');
docnum:="Cust. Ledger Entry"."Document No.";
des:="Cust. Ledger Entry".Description;
pay1:="Cust. Ledger Entry".Amount;
//-----------------------------------------------------------------------------------------------
START :=("Cust. Ledger Entry"."Posting Date");
IF "Cust. Ledger Entry1".NEXT() <> 0 THEN BEGIN
enddate :=("Cust. Ledger Entry1"."Posting Date");
diff := ABS(enddate - START);
END
ELSE BEGIN
diff := 0;
END;
//IF ("Posting Date" =010107D) OR ("Posting Date" =021208D) THEN BEGIN
//dIFF:=0;
//currReport.skip;
//END;
//---------------------------------------------------
VARIABLE[1]:=VARIABLE[2];
VARIABLE[2] := diff;
//Good
IF ("Posting Date" =010107D) OR ("Posting Date" =120208D) THEN BEGIN
T4:=0;
//CurrReport.SKIP;
END
ELSE
T4:=ABS(VARIABLE[2] - VARIABLE[1]);
T5 := T4 DIV 30;
//-----------------------------------------------------
Fincharge:=0;
Principal:=0;
ArreasVar:=0;
//NumPayments:=0;
CustBal :=CustBal+"Cust. Ledger Entry".Amount;
TotalCustBal := "Detailed Cust. Ledg. Entry"."Amount (LCY)";
CustBal1 := "Cust. Ledger Entry". Amount;
//CustBal :=CustBal + "Cust. Ledger Entry". Amount;
TotCustBal1 += CustBal1;
FromDate := 010107D;
ToDate := TODAY;
//Amount := 0;
CustLedEntryMonth := '';
MonthsArrears := 0;
//Code to Count Months
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 - 1;
END ELSE
Months := 0;
//Code to count two or more Payments as one in a particular month.
IF ("Document Type" = "Document Type"::Payment) AND
(InitMonth <> DATE2DMY("Posting Date",2) ) THEN BEGIN
NumPayments := NumPayments +1;
InitMonth:= DATE2DMY("Posting Date",2)
END;
IF "Document Type"=0 THEN BEGIN
Amount := CustBal1-CustBal1;
END;
IF "Document Type"="Document Type":: Invoice THEN BEGIN
Amount := CustBal1-CustBal1;
END;
IF "Document Type"="Document Type":: Refund THEN BEGIN
Amount := CustBal1-CustBal1;
END;
IF "Document Type"="Document Type":: Refund THEN BEGIN
CustBal1 := Amount;
END;
//TotCustBal1+=CustBal1;
IF "Document Type"="Document Type":: "Finance Charge Memo" THEN BEGIN
// Fincharge :=-ABS(Amount);
END;
IF "Document Type" = "Document Type"::Payment THEN BEGIN
Pay := ABS(Amount);
Tpay += ABS(Pay);
FinCharg := 0;
END;
IF "Document Type" = "Document Type"::"Finance Charge Memo" THEN BEGIN
FinCharg := Amount;
Pay := 0;
END;
IF "Document Type"="Document Type":: "Finance Charge Memo" THEN BEGIN
Amount :=ABS(Amount)-ABS(Amount);
END;
IF "Document Type"="Document Type"::Payment THEN BEGIN
Principal:= "Closed by Amount";
IF Principal=0 THEN
Fincharge :=0
ELSE
Fincharge :=ABS(Amount)-ABS(Principal);
// FinchargeTotal:=ABS(FinchargeTotal+ ABS(Fincharge));
PrincipalTotal :=PrincipalTotal+Principal;
IF Customer."Monthly Install" = 0 THEN BEGIN
ArreasVar:=0;
END ELSE
ArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount;
//ArreasVarTotal:=ArreasVarTotal+ArreasVar;
END;
FinchargeTotal+=-Fincharge;
absFinchargeTotal:=ABS(FinchargeTotal);
MonOwed:= Months - NumPayments;
OtherArrears := MonOwed * -Customer."Monthly Install";
TotArrs := OtherArrears + month1;
//------------------------------------------
IF (Pay>=0) AND (T4<>0) THEN BEGIN
//month:=Customer."Monthly Install" + Pay+Customer."Monthly Install"*FinChrgTerm."Interest Rate"/100*T4/365-Pay;
month:=Customer."Monthly Install" * T4/30 + Customer."Monthly Install" * FinChrgTerm."Interest Rate"/100 * T4/365 - Pay;
month1+=month;
END;
END;
//-----------------------------------------------
END;
ReqFilterFields=Posting Date,Document Type;
CalcFields=Amount;
DataItemLink=Posting Date=FIELD(Period Start);
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
SectionWidth=20400;
SectionHeight=1269;
}
CONTROLS
{
{ 1000000013;Label ;2550 ;0 ;1500 ;846 ;ParentControl=1000000010;
HorzAlign=Center;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000051;Label ;8250 ;0 ;1500 ;846 ;HorzAlign=Center;
FontSize=8;
FontBold=Yes;
CaptionML=ENU=Payment }
{ 1000000019;Label ;4500 ;0 ;3600 ;846 ;ParentControl=1000000016;
HorzAlign=Left;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000067;Label ;9900 ;0 ;2550 ;846 ;HorzAlign=Center;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Accumulated Arrears }
{ 1000000007;Label ;12600;423 ;3000 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU=MonthYear }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=20400;
SectionHeight=423;
}
CONTROLS
{
{ 1000000010;TextBox;1800 ;0 ;2550 ;423 ;HorzAlign=General;
SourceExpr="Document No." }
{ 1000000041;TextBox;8250 ;0 ;1500 ;423 ;HorzAlign=General;
SourceExpr=Pay }
{ 1000000016;TextBox;4500 ;0 ;3450 ;423 ;HorzAlign=General;
SourceExpr=Description }
{ 1000000022;TextBox;12600;0 ;3000 ;423 ;HorzAlign=Left;
SourceExpr=monthyear }
{ 1000000025;TextBox;0 ;0 ;1500 ;423 ;SourceExpr=postdate }
{ 1000000065;TextBox;9900 ;0 ;2550 ;423 ;HorzAlign=Center;
BlankZero=No;
SourceExpr=ArreasVar }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=20400;
SectionHeight=6345;
}
CONTROLS
{
{ 1102750004;TextBox;6450 ;0 ;1050 ;423 ;Visible=No;
Enabled=Yes;
HorzAlign=Center;
FontSize=8;
FontBold=Yes;
BlankZero=Yes;
SourceExpr=FinchargeTotal }
{ 1102750005;TextBox;7500 ;0 ;750 ;423 ;Visible=No;
Enabled=Yes;
HorzAlign=Center;
FontSize=8;
FontBold=Yes;
BlankZero=Yes;
SourceExpr=PrincipalTotal }
{ 1000000035;TextBox;15900;2961 ;2250 ;423 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
SourceExpr=TotArrs }
{ 1000000037;Label ;8100 ;846 ;4350 ;423 ;FontSize=7;
FontBold=Yes;
CaptionML=ENU=Arrears for months Skipped: }
{ 1000000006;TextBox;12450;846 ;2550 ;423 ;Visible=Yes;
HorzAlign=Left;
FontSize=8;
FontBold=Yes;
SourceExpr=OtherArrears }
{ 1000000034;TextBox;13050;0 ;2850 ;423 ;Visible=No;
HorzAlign=Left;
FontSize=8;
FontBold=Yes;
SourceExpr=ArreasVarTotal }
{ 1000000038;Label ;11700;2961 ;4200 ;423 ;FontSize=10;
FontBold=Yes;
CaptionML=ENU=Net Arrears }
{ 1000000039;TextBox;4500 ;846 ;1800 ;423 ;HorzAlign=Left;
FontSize=7;
FontBold=Yes;
SourceExpr=MonOwed }
{ 1000000040;Label ;450 ;846 ;3750 ;423 ;FontSize=7;
FontBold=Yes;
CaptionML=ENU="Number of Months Skipped =" }
{ 1000000043;Label ;450 ;2961 ;4200 ;423 ;FontSize=10;
FontBold=Yes;
CaptionML=ENU=Closing Balance }
{ 1000000044;TextBox;4650 ;2961 ;3000 ;423 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
BlankZero=Yes;
SourceExpr=TotCustBal1 }
{ 1000000045;Label ;0 ;3807 ;19650;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU="==========================================================================================================================================================================================" }
{ 1000000046;Label ;3900 ;4653 ;10950;423 ;HorzAlign=Center;
FontSize=8;
CaptionML=ENU=ERRORS AND OMMISSIONS EXEMPTED }
{ 1000000047;Label ;3900 ;5076 ;10950;423 ;HorzAlign=Center;
FontSize=8;
CaptionML=ENU=ENQUERIES TEL: 4222271-6 EXT 307, 342 }
{ 1000000048;Label ;1050 ;4230 ;1650 ;423 ;FontSize=8;
CaptionML=ENU=NOTE: }
{ 1000000055;TextBox;4500 ;1692 ;2550 ;423 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
SourceExpr=Tpay }
{ 1000000056;Label ;450 ;1692 ;3750 ;423 ;HorzAlign=Left;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=Total Payment }
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
}
}
CODE
{
VAR
Fincharge@1000000042 : Decimal;
CustBal@1000000041 : Decimal;
Principal@1000000040 : Decimal;
DateFilt@1000000039 : Text[50];
FinchargeTotal@1000000038 : Decimal;
PrincipalTotal@1000000037 : Decimal;
ArreasVar@1000000036 : Decimal;
ArreasVarTotal@1000000035 : Decimal;
"Plot No."@1000000034 : Code[10];
Name@1000000033 : Text[30];
TotalCustBal@1000000032 : Decimal;
"Balance (LCY)"@1000000031 : Decimal;
"Detailed Cust. Ledg. Entry"@1000000030 : Record 379;
CustBal1@1000000029 : Decimal;
TotCustBal1@1000000028 : Decimal;
BAK@1000000027 : Code[10];
"Fin. Charge Terms Code"@1000000026 : Code[10];
CustFilt@1000000025 : Text[50];
Cust@1000000024 : Record 18;
FromDate@1000000023 : Date;
ToDate@1000000022 : Date;
Calendar@1000000021 : Record 2000000007;
Months@1000000020 : Integer;
CustLedEntryMonth@1000000019 : Text[30];
Amt@1000000018 : Decimal;
TotalArr1@1000000017 : Decimal;
TotMonthsArr@1000000016 : Integer;
NumPayments@1000000015 : Integer;
Arrears2@1000000014 : Decimal;
TotMonOut@1000000013 : Decimal;
MonthsPaid@1000000012 : Integer;
MonthsArrears@1000000011 : Decimal;
"Monthly Install"@1000000010 : Decimal;
Payment@1000000009 : Option;
OtherArrears@1000000008 : Decimal;
MonOwed@1000000007 : Integer;
TotArrs@1000000006 : Decimal;
LastFieldNo@1000000005 : Integer;
GTotArrs@1000000004 : Decimal;
GTotCustBal1@1000000003 : Decimal;
GPrincipalTotal@1000000002 : Decimal;
GFinchargeTotal@1000000001 : Decimal;
Gtotal@1000000000 : Decimal;
absFinchargeTotal@1000000043 : Decimal;
CompanyInformation@1000000044 : Record 79;
"Finance Charge Terms"@1000000045 : Record 5;
FinChrgTerm@1000000046 : Record 5;
IntRate@1000000047 : Decimal;
InitMonth@1000000048 : Integer;
FilterText@1000000049 : Text[30];
Pay@1000000050 : Decimal;
FinCharg@1000000051 : Decimal;
Tpay@1000000052 : Decimal;
diff@1000000053 : Integer;
T4@1000000054 : Integer;
VARIABLE@1000000055 : ARRAY [2] OF Integer;
START@1000000056 : Date;
enddate@1000000057 : Date;
"Cust. Ledger Entry1"@1000000058 : Record 21;
month@1000000059 : Decimal;
month1@1000000060 : Decimal;
T5@1000000061 : Integer;
custom@1000000062 : Record 18;
custl@1000000063 : Record 21;
monthyear@1000000064 : Text[30];
postdate@1000000065 : Date;
docnum@1000000066 : Code[300];
des@1000000067 : Text[300];
pay1@1000000068 : Decimal;
BEGIN
END.
}
}
Can you please?
Thanks in advance.
0
Comments
-
canu post the code in *Code* format
click code on top of the mail u r creating and place the code in between them0 -
In the OnPreDataItem of the Cust. Ledger Entry dataitem, you need to set a filter on customer.no. the line IS there in the code, but commented out.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions