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
-
asembereng wrote: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
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.0 -
Sorry for confusing you. I am attaching the current report i am trying to design. the last field on the report, that is the balance is not suppose to be affect by the filter, it is suppose to add all the amount to date for a particular customer while the rest of the other fields will add the amount within the data range filter. Can you help me explain more on what to do by using the uploaded report as a reference?
Thank youOBJECT 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. } }0 -
If I get this correctly then:..
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...0 -
CustomerRec2.GET(Customer."No.");
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?0 -
The above code does the following:
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.0 -
thank you. That worked. I tried doing it to another field but it rejected it because that field is not flow field. tried making the field a flow field but because there is already data in that table, it was impossible to. Is there anyway i can force the same principle to a non flow field table?0
-
Before attempting changing the fields recommend studying te documentation.
For example, a FlowField does not exist in SQL server. It is calculated from Sift tables or indexed views in latest versions.Is there anyway i can force the same principle to a non flow field table?
Please give an example of what you want to do so we can help more.0 -
OK I have a field on the customer table called monthly installment. this field is use to calculate the arrears of the customer base on months not paid. and the field is not a flow field so how best can i do the same principle that you help with on this field?0
-
asembereng wrote:OK I have a field on the customer table called monthly installment. this field is use to calculate the arrears of the customer base on months not paid. and the field is not a flow field so how best can i do the same principle that you help with on this field?
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.0 -
below is the code that i am using to get the arrears. Sorry about the confusion i am causing. the variable totArrs is not be included in the filter.
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;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