Setting Date filter
kolaboy
Member Posts: 446
Hi Experts,
I want to set a date filter to read As at (the date the statement is filter not the current date). This is because satements can be issues months back and the statements should only have informations as at that peroid(filter i quess).
Here is my statement Report:
Thanks
I want to set a date filter to read As at (the date the statement is filter not the current date). This is because satements can be issues months back and the statements should only have informations as at that peroid(filter i quess).
Here is my statement Report:
OBJECT Report 50304 Mortgage Statement
{
OBJECT-PROPERTIES
{
Date=09/20/07;
Time=[ 3:26:37 PM];
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;
DataItemTableView=SORTING(No.)
ORDER(Ascending)
WHERE(Monthly Install=FILTER(<>0),
Fin. Charge Terms Code=FILTER(<>''));
NewPagePerRecord=Yes;
PrintOnlyIfDetail=Yes;
OnAfterGetRecord=BEGIN
FinChrgTerm.GET("Fin. Charge Terms Code");
IF NOT FinChrgTerm.GET("Fin. Charge Terms Code") THEN
FinChrgTerm."Interest Rate" :=0;
END;
ReqFilterFields=No.;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=19650;
SectionHeight=5499;
}
CONTROLS
{
{ 1000000001;Label ;0 ;0 ;5400 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU=Mortgage Customer Statement }
{ 1000000002;TextBox;15000;0 ;3150 ;423 ;HorzAlign=Right;
SourceExpr=FORMAT(TODAY,0,4) }
{ 1000000003;TextBox;0 ;423 ;5400 ;423 ;Visible=No;
Enabled=No;
SourceExpr=COMPANYNAME }
{ 1000000004;TextBox;17700;423 ;450 ;423 ;CaptionML=ENU=Page;
SourceExpr=CurrReport.PAGENO }
{ 1000000005;Label ;16950;423 ;750 ;423 ;ParentControl=1000000004 }
{ 1000000041;Label ;5100 ;4230 ;9900 ;423 ;HorzAlign=Center;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=SOCIAL SECURITY AND HOUSING FINANCE CORPORATION }
{ 1000000042;Label ;4800 ;4653 ;9900 ;423 ;HorzAlign=Center;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=MORTGAGE STATEMENT }
{ 1000000049;TextBox;5400 ;5076 ;9900 ;423 ;HorzAlign=Center;
FontSize=10;
FontBold=Yes;
SourceExpr=FORMAT(TODAY,0,4) }
{ 1000000050;Label ;6900 ;5076 ;2250 ;423 ;HorzAlign=Center;
FontSize=10;
FontBold=Yes;
CaptionML=ENU=AS AT }
{ 1000000051;PictureBox;5850;0 ;8850 ;3807 ;SourceExpr=CompanyInformation.Picture }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=19650;
SectionHeight=3384;
}
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;13950;423 ;1800 ;423 ;HorzAlign=Left;
FontSize=8;
SourceExpr="Monthly Install" }
{ 1000000015;Label ;10800;423 ;3000 ;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;13950;1269 ;1800 ;423 ;HorzAlign=Center;
SourceExpr=FinChrgTerm."Interest Rate" }
{ 1000000026;Label ;10800;1269 ;3000 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU=Interest Rate }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table21;
DataItemTableView=SORTING(Customer No.,Posting Date)
WHERE(Reversed=CONST(No));
OnPreDataItem=BEGIN
//"Cust. Ledger Entry".SETFILTER("Posting Date",DateFilt);
FinchargeTotal:=0;
PrincipalTotal:=0;
ArreasVarTotal:=0;
CustBal1 := 0;
TotCustBal1:=0;
NumPayments:=0;
Months:=0;
END;
OnAfterGetRecord=BEGIN
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 no. of payments
IF "Document Type"="Document Type"::Payment THEN BEGIN
NumPayments := NumPayments +1;
END;
MonOwed:= Months - NumPayments;
OtherArrears := MonOwed * -Customer."Monthly Install";
TotArrs := OtherArrears + ArreasVarTotal;
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":: "Finance Charge Memo" THEN BEGIN
Amount :=ABS(Amount)-ABS(Amount);
END;
IF "Document Type"="Document Type"::Payment THEN BEGIN
Principal:= "Closed by Amount";
Fincharge :=ABS(Amount)-ABS(Principal);
// FinchargeTotal:=ABS(FinchargeTotal+ ABS(Fincharge));
PrincipalTotal :=PrincipalTotal+ABS(Principal);
ArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount;
ArreasVarTotal:=ArreasVarTotal+ArreasVar;
END;
FinchargeTotal+=Fincharge;
absFinchargeTotal:=ABS(FinchargeTotal);
END;
CalcFields=Amount;
DataItemLink=Customer No.=FIELD(No.);
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
SectionWidth=19650;
SectionHeight=1269;
}
CONTROLS
{
{ 1000000007;Label ;0 ;0 ;1200 ;846 ;ParentControl=1000000000;
HorzAlign=Right;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000013;Label ;3000 ;0 ;1500 ;846 ;ParentControl=1000000010;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000019;Label ;4650 ;0 ;3600 ;846 ;ParentControl=1000000016;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000025;Label ;8550 ;0 ;1800 ;846 ;ParentControl=1000000022;
HorzAlign=Right;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Repayment }
{ 1000000029;Label ;1500 ;0 ;1350 ;846 ;ParentControl=1000000028;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000031;Label ;16650;0 ;1500 ;846 ;ParentControl=1000000030;
HorzAlign=Left;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Arreas }
{ 1000000033;Label ;10500;0 ;1800 ;846 ;HorzAlign=Right;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Interest Charge }
{ 1102750002;Label ;12450;0 ;1500 ;846 ;HorzAlign=Left;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Paid Principal }
{ 1102750003;Label ;14100;0 ;1950 ;846 ;HorzAlign=Right;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Acc Balance }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=19650;
SectionHeight=423;
}
CONTROLS
{
{ 1000000000;TextBox;150 ;0 ;1200 ;423 ;SourceExpr="Posting Date" }
{ 1000000010;TextBox;3000 ;0 ;1500 ;423 ;HorzAlign=Center;
SourceExpr="Document No." }
{ 1000000016;TextBox;4650 ;0 ;3450 ;423 ;SourceExpr=Description }
{ 1000000022;TextBox;8550 ;0 ;1800 ;423 ;SourceExpr=Amount }
{ 1000000028;TextBox;1500 ;0 ;1350 ;423 ;SourceExpr="Document Type" }
{ 1000000032;TextBox;10500;0 ;1800 ;423 ;BlankZero=No;
SourceExpr=-Fincharge }
{ 1102750000;TextBox;14100;0 ;1950 ;423 ;BlankZero=No;
SourceExpr=TotCustBal1 }
{ 1102750001;TextBox;12450;0 ;1500 ;423 ;BlankZero=No;
SourceExpr=Principal }
{ 1000000030;TextBox;16650;0 ;1500 ;423 ;HorzAlign=Left;
BlankZero=No;
SourceExpr=ArreasVar }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=19650;
SectionHeight=6345;
}
CONTROLS
{
{ 1102750004;TextBox;10800;0 ;2100 ;423 ;Visible=No;
Enabled=No;
HorzAlign=Center;
FontSize=8;
FontBold=Yes;
BlankZero=Yes;
SourceExpr=absFinchargeTotal }
{ 1102750005;TextBox;12600;0 ;1650 ;423 ;Visible=No;
Enabled=No;
HorzAlign=Center;
FontSize=8;
FontBold=Yes;
BlankZero=Yes;
SourceExpr=PrincipalTotal }
{ 1000000035;TextBox;16200;2961 ;2250 ;423 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
SourceExpr=TotArrs }
{ 1000000037;Label ;12000;846 ;4350 ;423 ;FontSize=7;
FontBold=Yes;
CaptionML=ENU=Arrears for months Skipped: }
{ 1000000006;TextBox;16650;846 ;2550 ;423 ;Visible=Yes;
HorzAlign=Left;
FontSize=7;
SourceExpr=OtherArrears }
{ 1000000034;TextBox;16650;0 ;1500 ;423 ;HorzAlign=Left;
SourceExpr=ArreasVarTotal }
{ 1000000038;Label ;11850;2961 ;4200 ;423 ;FontSize=10;
FontBold=Yes;
CaptionML=ENU=Amount Due }
{ 1000000039;TextBox;5550 ;846 ;1800 ;423 ;HorzAlign=Left;
FontSize=7;
FontBold=Yes;
SourceExpr=MonOwed }
{ 1000000040;Label ;600 ;846 ;5100 ;423 ;FontSize=7;
FontBold=Yes;
CaptionML=ENU="Number of Months Skipped =" }
{ 1000000043;Label ;11850;2115 ;4200 ;423 ;FontSize=10;
FontBold=Yes;
CaptionML=ENU=Closing Balance }
{ 1000000044;TextBox;16200;2115 ;1950 ;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: }
}
}
}
}
}
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;
BEGIN
END.
}
}
Any ideas please. Will love to see the code that can do the magic.Thanks
0
Comments
-
I'm kind of confused as to what you are asking.
If you need to set a filter on a date field it's just like any normal filter. For this report I would probably set it on posting date. Something like 010107..013107
If you need to set a date filter for a FlowFilter field you do it the same way and then do a CALCFIELDS on the FlowField that uses that FlowFilter.
If that is not what you are looking for perhaps you can be a bit more specific.
Good luck0 -
I want to use the Posting date to filter on the report. This date filter should be shown on the Customer Header(1) on the report section design to read as say AS AT (05 0ctober 2007). But this posting date is in the Cust. Ledger Entry table and the place where I want this date filter to display AS AT (FILTER DATE) is on the Customer, Header(1) on the report section in the design on the report.
Does anyone have any idea. I will be greatful to see a code that will do the magic.
Thanks.0 -
If you have set a filter on the Customer Ledger Entry record you can access the filter you have set by using code like this:
MyDateFilter := CustLedgEntry.GETFILTER("Posting Date");
where MyDateFilter is a text variable.
Stick it in onPreReport so it doesn't process every time it gets to a new customer. Now you can show MyDateFilter using a textbox in whatever section you want.0 -
matttrax,
I have put the code you have suggested in the onPreReport, Defined CustLedgEntry as record of the Cust. Ledger entry and MyDateFilter as text variable. I have also put MyDateFilter in a text box on the Customer,header(1) in the section design.
But when i run the report to test, the request form gives only the fields in the customer table for filtering. There is no Posting date in the customers fields that i can use to filter.
How can i get this posting date to filter records in the cust. Ledger entry by using posting date in the report and the date show on where i put MyDateFilter text box? The posting date is not part of the customers filters that i can filter with.
Thanks.0 -
Try adding "Posting Date" to the Req.Filter Fields for Cust. Ledg. Entry0
-
Yeah, Customers don't have dates associated with them, ledger entries do. You will either have to add a Date Filter field to your table, use the options form on the report to type in the date filter, or type in a filter for the posting date on the customer ledger entries tab of the report.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