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
- 296 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
Excluding a particular field from being filtered
asembereng
Member Posts: 220
I am writing a report using the Cust. Ledger entry table. The output of this report should be filtered by date. how can i exclude a particular field not to be filtered but the rest filtered. the excluded field should display all entries to date while the rest should only display entries within the date filtered range.
Thank you
Thank you
0
Comments
sorry, I could not exactly understand your question but these 2 tips can help you:
1. If you use dataItemtableview property of a dataitem, set a key in it and not use ReqFilterFields, users can not filter that dataitem.
2. If you use this kind of code in OnPreDataItem of a dataitem, user filter for that field will be skipped and your certain filter will be regarded at runtime.
SETRANGE("Posting Date")ORSETRANGE("Posting Date",0D,31122008D)hope this helps.
Thank you
OBJECT Report 75034 Mortgage Customer2 { OBJECT-PROPERTIES { Date=15/10/08; Time=21:18:15; Modified=Yes; Version List=; } PROPERTIES { OnPreReport=BEGIN datTheLastDate := "Cust. Ledger Entry".GETRANGEMAX("Posting Date"); CompanyInformation.GET; CompanyInformation.CALCFIELDS(Picture); END; } DATAITEMS { { PROPERTIES { DataItemTable=Table18; DataItemTableView=SORTING(No.) ORDER(Ascending) WHERE(Fin. Charge Terms Code=FILTER(<>''), Monthly Install=FILTER(<>0)); PrintOnlyIfDetail=No; OnPreDataItem=BEGIN IF "Cust. Ledger Entry".GETFILTERS <> '' THEN FilterText := 'Filter: ' + "Cust. Ledger Entry".GETFILTERS; CurrReport.CREATETOTALS(TotCustBal1); CurrReport.CREATETOTALS(TotArrs); CurrReport.CREATETOTALS(GTotArrsLCY); CurrReport.CREATETOTALS(GTotCustBal1LCY); END; ReqFilterFields=Date Filter,No.,Customer Posting Group; } SECTIONS { { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=19950; SectionHeight=7614; } CONTROLS { { 1000000001;Label ;0 ;0 ;5400 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=Cust. Ledger Entry } { 1000000003;TextBox;0 ;423 ;5400 ;423 ;SourceExpr=COMPANYNAME } { 1000000002;TextBox;14850;0 ;3150 ;423 ;HorzAlign=Right; SourceExpr=FORMAT(TODAY,0,4) } { 1000000004;TextBox;17550;423 ;450 ;423 ;CaptionML=ENU=Page; SourceExpr=CurrReport.PAGENO } { 1000000005;Label ;16800;423 ;750 ;423 ;ParentControl=1000000004 } { 1000000006;TextBox;15750;846 ;2250 ;423 ;HorzAlign=Right; SourceExpr=USERID } { 1000000012;Label ;0 ;6345 ;2700 ;846 ;ParentControl=1000000011; VertAlign=Bottom; FontSize=8; FontBold=Yes; MultiLine=Yes } { 1000000009;Label ;7050 ;6345 ;1500 ;846 ;Visible=Yes; HorzAlign=Right; VertAlign=Bottom; FontSize=8; FontBold=Yes; CaptionML=ENU=Payment } { 1000000018;Label ;9000 ;6345 ;1800 ;846 ;HorzAlign=Left; VertAlign=Bottom; FontSize=8; FontBold=Yes; MultiLine=Yes; CaptionML=ENU=Interest } { 1000000023;Label ;11250;6345 ;1950 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=Yes; CaptionML=ENU="Principal " } { 1000000007;Label ;14250;6345 ;1500 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=Yes; CaptionML=ENU=Balance } { 1000000000;Label ;6000 ;4653 ;6000 ;423 ;HorzAlign=Center; FontSize=8; FontBold=Yes; CaptionML=ENU=MORTGAGE REPAYMENT CONTROL } { 1000000027;TextBox;9900 ;5499 ;5400 ;423 ;FontSize=9; FontBold=Yes; SourceExpr=GETFILTER("Date Filter") } { 1000000029;Label ;3000 ;6768 ;3600 ;423 ;FontSize=8; FontBold=Yes; CaptionML=ENU=Name } { 1000000014;Label ;16350;6345 ;1950 ;846 ;VertGlue=Top; HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=Yes; CaptionML=ENU=Arrears } { 1000000034;PictureBox;6150;0 ;5700 ;4230 ;SourceExpr=CompanyInformation.Picture } } } { PROPERTIES { SectionType=Body; SectionWidth=19950; SectionHeight=0; } CONTROLS { } } { PROPERTIES { SectionType=Footer; PlaceInBottom=No; SectionWidth=19950; SectionHeight=2115; } CONTROLS { { 1000000010;TextBox;8850 ;0 ;1950 ;423 ;HorzAlign=Center; FontSize=9; FontBold=Yes; SourceExpr=GTotAmount } { 1000000013;TextBox;11250;0 ;2250 ;423 ;HorzAlign=Center; FontSize=9; FontBold=Yes; SourceExpr=-GTotPrincipal } { 1000000015;Label ;0 ;0 ;2250 ;423 ;FontSize=9; FontBold=Yes; CaptionML=ENU=Total } { 1000000008;TextBox;6300 ;0 ;2400 ;423 ;Visible=No; HorzAlign=Center; FontSize=9; FontBold=Yes; SourceExpr=GTPyment } { 1000000017;TextBox;6300 ;846 ;2400 ;423 ;Visible=No; Enabled=Yes; HorzAlign=Center; FontSize=10; FontBold=Yes; SourceExpr=GTPymentLCY } { 1000000019;TextBox;8850 ;846 ;2250 ;423 ;HorzAlign=Center; FontSize=10; FontBold=Yes; SourceExpr=GTotAmountLCY } { 1000000020;TextBox;11250;846 ;2550 ;423 ;HorzAlign=Center; FontSize=10; FontBold=Yes; SourceExpr=-GTotPrincipalLCY } { 1000000025;Label ;0 ;846 ;3000 ;423 ;FontSize=10; FontBold=Yes; CaptionML=ENU=Total ( LCY) } { 1000000031;TextBox;13950;846 ;2400 ;423 ;HorzAlign=Center; FontSize=10; FontBold=Yes; SourceExpr=GTotCustBal1LCY } { 1000000024;TextBox;14100;0 ;2100 ;423 ;HorzAlign=Center; FontSize=9; FontBold=Yes; SourceExpr=TotCustBal1 } { 1000000032;TextBox;16500;0 ;1950 ;423 ;HorzAlign=Center; FontSize=9; FontBold=Yes; SourceExpr=TotArrs } { 1000000033;TextBox;16500;846 ;2700 ;423 ;HorzAlign=Left; FontSize=10; FontBold=Yes; SourceExpr=GTotArrsLCY } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table21; DataItemTableView=SORTING(Customer No.,Posting Date) WHERE(Reversed=CONST(No)); NewPagePerGroup=No; NewPagePerRecord=No; OnPreDataItem=BEGIN //IF "Cust. Ledger Entry".GETFILTERS <> '' THEN //FilterText := 'Filter: ' + "Cust. Ledger Entry".GETFILTERS; //LastFieldNo := FIELDNO("Customer No."); CurrReport.CREATETOTALS(TotAmount,TPyment,TotPrincipal,GTPyment); Customer.CALCFIELDS("Balance (LCY)"); //CurrReport.CREATETOTALS(vBalance); //CurrReport.CREATETOTALS("Cust. Ledger Entry". Amount); NofMonths := getMonths(FromDate,ToDate); NumPayments:=0; ArreasVarTotal:=0; CustBal1 := 0; TotCustBal1:=0; MonOwed:=0; //CurrReport.CREATETOTALS(ArreasVar) END; OnAfterGetRecord=BEGIN ArreasVar:=0; CustBal1:=0; GTotCustBal1 :=0; Pyment :=0; CustBal1 := "Cust. Ledger Entry". Amount; TotCustBal1 += CustBal1; EmployerName.GET("Customer No."); CustomerFullName := EmployerName.Name; IF "Document Type"=0 THEN BEGIN Amount :=0; END; IF "Document Type" = "Document Type"::Payment THEN BEGIN IF Customer."Monthly Install" = 0 THEN BEGIN ArreasVar:=0; END ELSE ArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount; ArreasVarTotal:=ArreasVarTotal+ArreasVar; END; //END; P1:= -Customer."Monthly Install"; // variable never used (MLJ) P2 :="Cust. Ledger Entry".Amount; // variable never used (MLJ) //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; MonOwed:= NofMonths - NumPayments; OtherArrears := MonOwed * -Customer."Monthly Install"; TotArrs := OtherArrears + ArreasVarTotal; IF "Document Type"="Document Type"::Payment THEN BEGIN Pyment:= Amount; Amount := 0; END ELSE IF "Document Type"="Document Type"::"Finance Charge Memo" THEN BEGIN Pyment:= 0; Int:=Amount; END; Principal :=Pyment- Amount; TPyment += Pyment; GTPyment := GTPyment + TPyment; IF "Document Type"="Document Type"::"Finance Charge Memo" THEN BEGIN TotAmount +=Int-"Cust. Ledger Entry"."Remaining Amount"; END; TotPrincipal := TPyment + TotAmount; TBalances := TBalances + "Cust. Ledger Entry"."Remaining Amount"; GTotAmount := GTotAmount + TotAmount; GTotPrincipal += TotPrincipal ; Currency.InitRoundingPrecision; IF "Currency Code" <> '' THEN GTPymentLCY := ROUND( CurrExchRate.ExchangeAmtFCYToLCY( datTheLastDate,"Currency Code", GTPyment,CurrExchRate.ExchangeRate(datTheLastDate,"Currency Code")), Currency."Amount Rounding Precision") ELSE GTPymentLCY := ROUND(GTPyment,Currency."Amount Rounding Precision"); Currency.InitRoundingPrecision; IF "Currency Code" <> '' THEN GTotAmountLCY := ROUND( CurrExchRate.ExchangeAmtFCYToLCY( datTheLastDate,"Currency Code", GTotAmount,CurrExchRate.ExchangeRate(datTheLastDate,"Currency Code")), Currency."Amount Rounding Precision") ELSE GTotAmountLCY := ROUND(GTotAmount,Currency."Amount Rounding Precision"); Currency.InitRoundingPrecision; IF "Currency Code" <> '' THEN GTotPrincipalLCY := ROUND( CurrExchRate.ExchangeAmtFCYToLCY( datTheLastDate,"Currency Code", GTotPrincipal,CurrExchRate.ExchangeRate(datTheLastDate,"Currency Code")), Currency."Amount Rounding Precision") ELSE GTotPrincipalLCY := ROUND(GTotPrincipal,Currency."Amount Rounding Precision"); Currency.InitRoundingPrecision; IF "Currency Code" <> '' THEN GTotCustBal1LCY := ROUND( CurrExchRate.ExchangeAmtFCYToLCY( datTheLastDate,"Currency Code", TotCustBal1,CurrExchRate.ExchangeRate(datTheLastDate,"Currency Code")), Currency."Amount Rounding Precision") ELSE GTotCustBal1LCY := ROUND(TotCustBal1,Currency."Amount Rounding Precision"); Currency.InitRoundingPrecision; IF "Currency Code" <> '' THEN GTotArrsLCY := ROUND( CurrExchRate.ExchangeAmtFCYToLCY( datTheLastDate,"Currency Code", TotArrs,CurrExchRate.ExchangeRate(datTheLastDate,"Currency Code")), Currency."Amount Rounding Precision") ELSE GTotCustBal1LCY := ROUND(TotArrs,Currency."Amount Rounding Precision"); END; ReqFilterFields=Customer No.,Posting Date,Customer Posting Group; TotalFields=Amount,Remaining Amount; GroupTotalFields=Customer No.; CalcFields=Amount,Remaining Amount; DataItemLink=Customer No.=FIELD(No.), Posting Date=FIELD(Date Filter); } SECTIONS { { PROPERTIES { SectionType=Header; PrintOnEveryPage=No; SectionWidth=19950; SectionHeight=0; } CONTROLS { } } { PROPERTIES { SectionType=Body; SectionWidth=19950; SectionHeight=0; } CONTROLS { } } { PROPERTIES { SectionType=GroupFooter; SectionWidth=19950; SectionHeight=423; } CONTROLS { { 1000000021;TextBox;7050 ;0 ;1500 ;423 ;Visible=Yes; SourceExpr=TPyment } { 1000000011;TextBox;0 ;0 ;2400 ;423 ;HorzAlign=Left; SourceExpr="Customer No." } { 1000000016;TextBox;8850 ;0 ;1950 ;423 ;HorzAlign=Center; SourceExpr=TotAmount } { 1000000022;TextBox;11250;0 ;2100 ;423 ;HorzAlign=Center; SourceExpr=-TotPrincipal } { 1000000030;TextBox;2700 ;0 ;3750 ;423 ;SourceExpr=CustomerFullName } { 1000000028;TextBox;14100;0 ;2100 ;423 ;HorzAlign=Center; SourceExpr=TotCustBal1 } { 1000000026;TextBox;16350;0 ;1950 ;423 ;SourceExpr=TotArrs } } } } } } REQUESTFORM { PROPERTIES { Width=9020; Height=3410; } CONTROLS { { 1000000000;Label ;550 ;440 ;3630 ;440 ;CaptionML=ENU=Start Date } { 1000000001;Label ;550 ;1320 ;3630 ;440 ;CaptionML=ENU=End Date } { 1000000002;TextBox;4290 ;440 ;1920 ;440 ;SourceExpr=FromDate } { 1000000003;TextBox;4290 ;1320 ;1870 ;440 ;SourceExpr=ToDate } } } CODE { VAR LastFieldNo@1000000000 : Integer; FooterPrinted@1000000001 : Boolean; TotalFor@1000000002 : TextConst 'ENU="Total for "'; TotalCustBal@1000000003 : Decimal; CustBal1@1000000004 : Decimal; TotCustBal1@1000000005 : Decimal; "Detailed Cust. Ledg. Entry"@1000000006 : Record 379; Pyment@1000000007 : Decimal; Principal@1000000008 : Decimal; GTotCustBal1@1000000009 : Decimal; TPyment@1000000010 : Decimal; TotAmount@1000000011 : Decimal; TotPrincipal@1000000012 : Decimal; GTPyment@1000000013 : Decimal; GTotAmount@1000000014 : Decimal; GTotPrincipal@1000000015 : Decimal; Currency@1000000020 : Record 4; CurrExchRate@1000000019 : Record 330; AmountLCY@1000000018 : Decimal; AmountFCY@1000000017 : Decimal; InitMonth@1000000016 : Integer; GTPymentLCY@1000000021 : Decimal; GTotAmountLCY@1000000022 : Decimal; GTotPrincipalLCY@1000000023 : Decimal; GTotCustBal1LCY@1000000024 : Decimal; AmtPaid@1000000025 : Decimal; vBalance@1000000026 : Decimal; Int@1000000027 : Decimal; TBalances@1000000028 : Decimal; GTBalancesLCY@1000000029 : Decimal; FilterText@1000000030 : Text[30]; datTheLastDate@1000000031 : Date; EmployerName@1000000032 : Record 18; CustomerFullName@1000000033 : Text[150]; EndPeriodMonth@1000000034 : Integer; StartPeriodYear@1000000035 : Integer; EndPeriodYear@1000000036 : Integer; NofMonths@1000000037 : Decimal; StartPeriodMonth@1000000038 : Integer; startDate@1000000039 : Date; endDate@1000000040 : Date; FromDate@1000000042 : Date; ToDate@1000000041 : Date; NumPayments@1000000043 : Integer; OtherArrears@1000000044 : Decimal; TotArrs@1000000045 : Decimal; ArreasVarTotal@1000000046 : Decimal; MonOwed@1000000047 : Decimal; ArreasVar@1000000048 : Decimal; P1@1000000049 : Decimal; P2@1000000050 : Decimal; GTotArrsLCY@1000000051 : Decimal; CompanyInformation@1000000052 : Record 79; PROCEDURE getMonths@1000000000(startDate@1000000000 : Date;endDate@1000000001 : Date) months : Decimal; BEGIN StartPeriodMonth := DATE2DMY(startDate, 2); EndPeriodMonth := DATE2DMY(endDate, 2); StartPeriodYear := DATE2DMY(startDate, 3); EndPeriodYear := DATE2DMY(endDate, 3); IF (startDate = endDate) THEN BEGIN months := 0; END ELSE IF (EndPeriodYear = StartPeriodYear) THEN BEGIN months := (EndPeriodMonth - StartPeriodMonth + 1 ) END ELSE BEGIN months := (EndPeriodMonth - StartPeriodMonth + 1 ); months := months + ((EndPeriodYear - StartPeriodYear ) * 12); END; months := months - 2; months += (DATE2DMY(DMY2DATE(1, (StartPeriodMonth MOD 12) + 1, StartPeriodYear)-1,1) - DATE2DMY(startDate, 1) + 1)/ DATE2DMY(DMY2DATE(1, (StartPeriodMonth MOD 12) + 1, StartPeriodYear)-1,1); months += DATE2DMY(endDate, 1)/ DATE2DMY(DMY2DATE(1, (EndPeriodMonth MOD 12) + 1, EndPeriodYear)-1,1); IF (startDate = endDate) THEN months := 0; END; BEGIN END. } }Declare cutomerRec2 as Record Customer.
Then:
CustomerRec2.GET(Customer."No.");
CustomerRec2.COPYFILTERS(Customer);
CustomreRec2.SETRANGE("Date Filter");
CustomerRec2.CACFIELDS(Balance);
.
.
.
The customerRec2 will be used to calculate the balance.
Just remember to check which filters affect the balance. Maybe you'll need to clear also the dimension filters...
CustomerRec2.COPYFILTERS(Customer);
CustomreRec2.SETRANGE("Date Filter");
CustomerRec2.CACFIELDS(Balance);
when you use the above codes is not going to sum the balance base on the date filter specify?
a) Finds the customer currently from the Dataitem to CustomerRec2. ->CustomerRec2.GET(Customer."No.");
b) Copies all the filters you have applied on customer to CustomerRec2. ->CustomerRec2.COPYFILTERS(Customer);
c) Removes any filters from "Date filter" only on CustomerRec2. ->CustomreRec2.SETRANGE("Date Filter");
d) Calculates the flowfields with only the non-removed filters. ->CustomerRec2.CACFIELDS(Balance);
So: If you don't remove the Dimension Filters then the balance will be calculated Using dimension filters and skipping the date filters.
For example, a FlowField does not exist in SQL server. It is calculated from Sift tables or indexed views in latest versions.
Please give an example of what you want to do so we can help more.
I don't quite understand what you mean.
If the field is not a flowfield it means that at any given time the field contains 1 specific value that is stored on the record. You want this field to behave like the Balance and display something similar? You want this field to participate in the flowfilters and change the balance flowfield depending on it's value, similarly to Date filter".
Please give some more details so we can provide more help.
How best to exclude it.
IF "Document Type" = "Document Type"::Payment THEN BEGIN IF Customer."Monthly Install" = 0 THEN BEGIN ArreasVar:=0; END ELSE ArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount; ArreasVarTotal:=ArreasVarTotal+ArreasVar; END; //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; MonOwed:= NofMonths - NumPayments; //SETRANGE("Posting Date",startDate,endDate); OtherArrears := MonOwed * -Customer."Monthly Install"; TotArrs := OtherArrears + ArreasVarTotal;