Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K 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
- 298 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 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
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
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
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 luck
My Blog - nav.education
Does anyone have any idea. I will be greatful to see a code that will do the magic.
Thanks.
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.
My Blog - nav.education
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.
My Blog - nav.education