Filtering date using another field
Leroy
Member Posts: 199
Hello, I've create a date type variable called datefilter and I put i on the request form. The report shows sale orders (from sales line table) and I filter them buy Shipment Date on running the report. I want that the variable "datefilter" form request form, acts as the field Shipment Date and filter the orders as it was the Shipment Date field.
Thanks in advance.
Thanks in advance.
0
Comments
-
do you mean this:
Use the DateFilter variable on Request form instead of a Filter in field "Shipment DAte" for filtering all recs on SalesLine with filter on "Shipment Date" base on DateFilter variable?
If yes, use this:XYZ- OnPreDataItem() if (DateFilterVariable <> 0D) then SalesLine.setfilter("Shipment Date",DateFilterVariable);
Now, if the user has entered a value in the DateFilter field on Request form, the report filter the Sales Line based on this value for field "Shipment Date".
RegardsDo you make it right, it works too!0 -
Yes, it is. Thanks for help. I've tried the code but it shows me an error message saying that "the conversion of types is not possible....." Text:=Date", the datefiltervarible is a Date type one. How can I do it?.
Thanks again for help0 -
Hi,
Try
SalesLine.setfilter("Shipment Date",FORMAT(DateFilterVariable));0 -
Thank you, it works well with Format instruction. But I think there is somethig wrong, because I filter by a date and the report don't show any register. I put the same code (on PreDataItem also) but refering to the date from Purchase line and it works well, but not on the first dateitem that is the Sales line table refering to the Shipment Date. Do you know what can be the problem?.
Thank you very much for your help.0 -
Hi Leroy,
Can you post some code and I can have a look.0 -
Of course; it is:
These are the Dataitems:
Item <Item>
Sales Line <Sales Line>
Purchase Line <Purchase Line>
Purchase Line <Purchase Line2>
Purchase Header <Purchase Header>
Purch. Inv. Line <Purch. Inv. Line>
Item Ledger Entry <Item Ledger Entry>
Entero <Entero>
Entero Entero2
And, on PreDataItem from Sales Line Dataitem put the code:
IF ( <> 0D) THEN
"Sales Line".SETFILTER("Shipment Date", FORMAT(DateFilterVariable));
That's all; afater, on OnAfetergetRecord from Sales Line Dataitem there is more code; but is much and I don't know if it's important; if you want that I put it there is any problem.
Thanks for help.0 -
On Sales Line - OnPreDataItem() is there some code like:
"Sales Line".reset <-- this return all filters
or
"Sales Line".setrange("Shipment Date"); <-- this return the filter on the field
The setfitler function you only need if your DateVariable inclueds a filtersting like 01.01.2008..31.12.2008.
If it is only one Date you can use
"Sales Line".setrange("Shipment Date",DateVariable);Do you make it right, it works too!0 -
Thanks you, but if I put the code like this:
"Sales Line".RESET;
"Sales Line".SETRANGE("Shipment Date",datevaribale);
the report don't filter me by date, it shows all the sales orders without filtering by date, and the Shipment Date change into the date that I have put on de datevariable. Any other idea please?.
Thanks for help.0 -
let us see your report design as textDo you make it right, it works too!0
-
Sorry, how i do that?.
Thanks.0 -
tools->object designer->File->Export->"save as type" TEXT (need permission)
Copy & paste it into a post. Select it all then hit the [Code] button.0 -
Ok, thanks, here it is:
OBJECT Report 50121 Previsi¢n Compras { OBJECT-PROPERTIES { Fecha=04/12/08; Hora=13:23:06; Modificado=S¡; Lista versiones=ES2.00; } PROPERTIES { LeftMargin=1100; OnInitReport=BEGIN //Indica que usuarios no pueden acceder al report IF USERID = 'RMERCADER' THEN ERROR('No tiene permiso para ejecutar este report'); IF USERID = 'MFERNANDEZ' THEN ERROR('No tiene permiso para ejecutar este report'); IF USERID = 'DMARTIN' THEN ERROR('No tiene permiso para ejecutar este report'); IF USERID = 'CGONZALEZ' THEN ERROR('No tiene permiso para ejecutar este report'); IF USERID = 'JCVAZQUEZ' THEN ERROR('No tiene permiso para ejecutar este report'); IF USERID = 'HLAMA' THEN ERROR('No tiene permiso para ejecutar este report'); IF USERID = 'AVACAS' THEN ERROR('No tiene permiso para ejecutar este report'); END; OnPreReport=BEGIN //FiltClient := Cliente.GETFILTERS; FiltroLinVenta := "Sales Line".GETFILTERS; NomPeriodo := "Sales Line".GETFILTER("Shipment Date"); END; } DATAITEMS { { PROPERTIES { DataItemTable=Table27; DataItemTableView=SORTING(Vendor No.); PrintOnlyIfDetail=No; OnPreDataItem=BEGIN CurrReport.NEWPAGEPERRECORD := ImpriSoloUnoporPag; CurrReport.CREATETOTALS(ImpPedVtaDL); END; ReqFilterFields=No.,Vendor No.,Familia,Inventory Posting Group,Product Group Code; } SECTIONS { { PROPERTIES { SectionType=Header; PrintOnEveryPage=S¡; SectionWidth=18900; SectionHeight=1692; } CONTROLS { { 1 ;Label ;0 ;0 ;7500 ;423 ;FontSize=9; FontBold=S¡; CaptionML=ESP=Previsi¢n de Compras } { 2 ;TextBox ;15000;0 ;3150 ;423 ;HorzAlign=Right; SourceExpr=FORMAT(TODAY,0,4) } { 3 ;TextBox ;0 ;423 ;7500 ;423 ;SourceExpr='Fecha env¡o: ' + NomPeriodo } { 4 ;Label ;16950;423 ;750 ;423 ;ParentControl=5 } { 5 ;TextBox ;17700;423 ;450 ;423 ;CaptionML=ESP=P g.; SourceExpr=CurrReport.PAGENO } { 6 ;TextBox ;0 ;846 ;7500 ;423 ;SourceExpr=COMPANYNAME } { 7 ;TextBox ;15900;846 ;2250 ;423 ;HorzAlign=Right; SourceExpr=USERID } } } { PROPERTIES { SectionType=Header; SectionWidth=18900; SectionHeight=846; OnPreSection=BEGIN CurrReport.SHOWOUTPUT(ImprImportesEnDL); END; } CONTROLS { { 12 ;Label ;0 ;0 ;3000 ;423 ;CaptionML=ESP=Importes en divisa local. } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=S¡; SectionWidth=18900; SectionHeight=423; OnPreSection=BEGIN CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FiltClient <> '')); END; } CONTROLS { { 9 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr='test' } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=S¡; SectionWidth=18900; SectionHeight=423; OnPreSection=BEGIN CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FiltroLinVenta <> '')); END; } CONTROLS { { 1000000000;TextBox;0 ;0 ;18150;423 ;SourceExpr='L¡n. pedido venta: ' + FiltroLinVenta } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=S¡; SectionWidth=18900; SectionHeight=1269; OnPreSection=BEGIN //CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND ((FiltClient <> '') OR (FiltroLinVenta <> ''))); END; } CONTROLS { { 21 ;Label ;8400 ;0 ;1950 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Proveedor } { 22 ;Label ;4500 ;0 ;2550 ;846 ;VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Descripci¢n } { 44 ;Label ;0 ;0 ;4350 ;846 ;VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Referencia } { 49 ;Label ;17550;0 ;1350 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Cdad. a Pedir } { 20 ;Label ;10500;0 ;1350 ;846 ;VertGlue=Top; HorzAlign=General; VertAlign=Bottom; FontSize=8; FontBold=S¡; CaptionML=ESP=Stk. Seg. } { 60 ;Label ;12000;0 ;1050 ;846 ;ParentControl=59; HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Embje. } { 17 ;Label ;13200;0 ;1050 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Ped. Comp. } { 14 ;Label ;14400;0 ;1050 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Ped. Venta } { 13 ;Label ;15450;0 ;1050 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Exist. } { 1103351002;Label ;16500;0 ;1050 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Resrv. } { 1000000023;Label ;7200 ;0 ;1050 ;846 ;ParentControl=1000000022; HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; MultiLine=S¡; CaptionML=ESP=Qty m. pedir } } } { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=1269; OnPreSection=BEGIN prove.INIT; nocompra:=TRUE; precio:=0; ptas:=0; stockseguridad:="Reorder Point"+"Stock total aceptado"; IF prove.GET ("Vendor No.") THEN; CurrReport.SHOWOUTPUT((Inventory+"Qty. on Purch. Order"-"Qty. on Sales Order"-stockseguridad-"Cantidad a reservar")<0); IF (Inventory+"Qty. on Purch. Order"-"Qty. on Sales Order"-stockseguridad-"Cantidad a reservar")<0 THEN ver:=TRUE ELSE ver:=FALSE; END; } CONTROLS { { 24 ;TextBox ;0 ;423 ;4350 ;423 ;HorzAlign=Left; FontSize=8; FontBold=S¡; SourceExpr="No." } { 25 ;TextBox ;4500 ;423 ;2550 ;423 ;FontSize=7; FontBold=S¡; SourceExpr=Description } { 8 ;TextBox ;8400 ;423 ;1950 ;423 ;HorzAlign=Center; FontSize=7; FontBold=S¡; SourceExpr=prove."Search Name" } { 26 ;TextBox ;13200;423 ;1050 ;423 ;HorzAlign=Center; SourceExpr="Qty. on Purch. Order" } { 27 ;TextBox ;14400;423 ;1050 ;423 ;HorzAlign=Center; SourceExpr="Qty. on Sales Order" } { 30 ;TextBox ;15600;423 ;900 ;423 ;HorzAlign=Center; FontSize=7; SourceExpr=Inventory } { 48 ;TextBox ;17550;423 ;1350 ;423 ;HorzAlign=Center; FontSize=7; FontBold=S¡; DecimalPlaces=0:0; SourceExpr=Inventory+"Qty. on Purch. Order"-"Qty. on Sales Order"-stockseguridad-"Cantidad a reservar" } { 56 ;TextBox ;10500;423 ;1350 ;423 ;HorzAlign=Center; FontSize=7; FontBold=S¡; SourceExpr=stockseguridad } { 59 ;TextBox ;12000;423 ;1050 ;423 ;HorzAlign=Center; FontSize=7; FontBold=S¡; SourceExpr="Shelf/Bin No." } { 1000000004;TextBox;0 ;846 ;4350 ;423 ;HorzAlign=General; SourceExpr="PBFree / RoHS" } { 1000000007;Shape ;10350;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000009;Shape ;11850;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000008;Shape ;13050;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000010;Shape ;14250;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000011;Shape ;15450;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000012;Shape ;16500;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000013;Shape ;8250 ;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000014;Shape ;4350 ;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000015;TextBox;5400 ;0 ;2550 ;423 ;Visible=No; SourceExpr="Stock total aceptado" } { 1000000016;TextBox;8250 ;0 ;2100 ;423 ;Visible=No; SourceExpr="Reorder Point" } { 1103351000;TextBox;16650;423 ;750 ;423 ;SourceExpr="Cantidad a reservar" } { 1103351001;Shape ;17400;423 ;150 ;423 ;ShapeStyle=VertLine } { 1000000022;TextBox;7200 ;423 ;1050 ;423 ;HorzAlign=Center; SourceExpr="Reorder Quantity" } { 1000000024;Shape ;7050 ;423 ;150 ;423 ;ShapeStyle=VertLine } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table37; DataItemTableView=SORTING(Shipment Date,Document Type,Bill-to Customer No.,Currency Code) WHERE(Document Type=CONST(Order), Outstanding Quantity=FILTER(<>0), Document No.=FILTER(PV*|PM*)); ReqFilterHeadingML=ESP=L¡n. pedido venta; OnPreDataItem=BEGIN CurrReport.CREATETOTALS(ImpPedVtaDL,ImpPedVta); CurrReport.CREATETOTALS("Outstanding Quantity"); //IF (fechafiltro <> 0D) THEN //"Sales Line".SETFILTER("Shipment Date", FORMAT(fechafiltro)); "Sales Line".RESET; //"Sales Line".SETRANGE("Shipment Date"); "Sales Line".SETRANGE("Shipment Date",fechafiltro); END; OnAfterGetRecord=BEGIN PedNuevo := "Document No." <> CabPedVta."No."; IF PedNuevo THEN CabPedVta.GET(1,"Document No."); IF "Shipment Date" <= WORKDATE THEN CdadPedPndte := "Outstanding Quantity" ELSE CdadPedPndte := 0; IF (Quantity <> 0) THEN ImpPedVta := ROUND("Outstanding Quantity" * Amount / Quantity) ELSE ImpPedVta := 0 ; ImpPedVtaDL := ImpPedVta; IF CabPedVta."Currency Code" <> '' THEN BEGIN IF CabPedVta."Currency Factor" <> 0 THEN ImpPedVtaDL := ROUND( TipoCambDivisa.ExchangeAmtFCYToLCY( WORKDATE,CabPedVta."Currency Code", ImpPedVtaDL,CabPedVta."Currency Factor")); IF ImprImportesEnDL THEN BEGIN "Unit Price" := ROUND( TipoCambDivisa.ExchangeAmtFCYToLCY( WORKDATE,CabPedVta."Currency Code", "Unit Price",CabPedVta."Currency Factor")); ImpPedVta := ImpPedVtaDL; END; END; CodDivisa2 := CabPedVta."Currency Code"; IF ImprImportesEnDL THEN CodDivisa2 := ''; MemIntTotDivisa.UpdateTotal( CodDivisa2, ImpPedVta, Contador1, Contador1); //////////////////////////////////////////////////////////////////// >> MONOLITIC sAvanzada := '' ; IF (CabPedVta."Payment Method Code" = 'AVANZADA') OR (CabPedVta."Payment Method Code" = 'TR-AVANZAD') THEN sAvanzada := CabPedVta."Payment Method Code" + ' !!!!' ; sContado := '' ; IF (CabPedVta."Payment Method Code" = 'C') THEN sContado :='CONTADO' ; //////////////////////////////////////////////////////////////////// << MONOLITIC END; ReqFilterFields=Shipment Date; DataItemLink=No.=FIELD(No.); } SECTIONS { { PROPERTIES { SectionType=Header; SectionWidth=18900; SectionHeight=1269; OnPreSection=BEGIN CurrReport.SHOWOUTPUT(ver); IF sindetalle = TRUE THEN CurrReport.SHOWOUTPUT(FALSE); END; } CONTROLS { { 35 ;Label ;900 ;423 ;1500 ;846 ;VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Doc } { 36 ;Label ;2700 ;423 ;5550 ;846 ;HorzAlign=Left; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Cliente } { 37 ;Label ;10500;423 ;1350 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Total Pedido } { 40 ;Label ;12150;423 ;1950 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Pendientes } { 41 ;Label ;14400;423 ;1350 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Fecha Env¡o } { 52 ;Label ;8550 ;423 ;1650 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Precio venta excl.I } } } { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=423; OnPreSection=BEGIN //CurrReport.SHOWOUTPUT((PedNuevo) AND (NOT ImprImportesEnDL)); clie.INIT; IF clie.GET("Sell-to Customer No.") THEN; CurrReport.SHOWOUTPUT(("Outstanding Quantity">0) AND ver); IF sindetalle = TRUE THEN CurrReport.SHOWOUTPUT(FALSE); //IF (fechafiltro <> 0D) AND ("Outstanding Quantity">0) THEN //CurrReport.SHOWOUTPUT(ver) ELSE CurrReport.SHOWOUTPUT(FALSE); //IF (fechafiltro <> 0D) THEN //"Sales Line".SETFILTER("Shipment Date", FORMAT(fechafiltro)); //IF (fechafiltro <> 0D) THEN //"Sales Line".SETRANGE("Sales Line"."Shipment Date",fechafiltro); END; } CONTROLS { { 1000000036;TextBox;900 ;0 ;1500 ;423 ;FontBold=S¡; CaptionML=ESP=N§ pedido; SourceExpr=CabPedVta."No." } { 1000000037;TextBox;2700 ;0 ;5550 ;423 ;SourceExpr=clie.Name } { 1000000031;TextBox;8550 ;0 ;1650 ;423 ;HorzAlign=Center; SourceExpr="Unit Price" } { 1000000033;TextBox;10500;0 ;1350 ;423 ;HorzAlign=Center; SourceExpr=Quantity } { 1000000035;TextBox;14400;0 ;1350 ;423 ;HorzAlign=Center; CaptionML=ESP=Fecha env¡o; SourceExpr="Shipment Date" } { 1000000039;TextBox;16050;0 ;2700 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; SourceExpr=sAvanzada } { 1000000038;TextBox;16050;0 ;2700 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; SourceExpr=sContado } { 1000000034;TextBox;12150;0 ;1950 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; SourceExpr="Outstanding Quantity" } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table39; DataItemTableView=SORTING(Fecha confirmaci¢n); PrintOnlyIfDetail=No; DataItemLink=No.=FIELD(No.); } SECTIONS { { PROPERTIES { SectionType=Header; SectionWidth=18900; SectionHeight=1269; OnPreSection=BEGIN prove.INIT; precio:="Direct Unit Cost"; IF prove.GET("Buy-from Vendor No.") THEN; divisa:=prove."Currency Code"; CurrReport.SHOWOUTPUT(("Outstanding Quantity">0) AND ver ); IF sindetalle = TRUE THEN CurrReport.SHOWOUTPUT(FALSE); //nocompra:=FALSE; //CurrReport.SHOWOUTPUT(ver); //IF sindetalle = TRUE THEN CurrReport.SHOWOUTPUT(FALSE); END; } CONTROLS { { 33 ;Label ;13050;423 ;1800 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Pendientes } { 43 ;Label ;11400;423 ;1500 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Total Ped. } { 46 ;Label ;2700 ;423 ;5400 ;846 ;HorzAlign=Left; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Proveedor } { 47 ;Label ;900 ;423 ;1500 ;846 ;VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Doc } { 29 ;Label ;8250 ;423 ;1800 ;846 ;ParentControl=28; HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Precio compra } { 50 ;Label ;10200;423 ;1050 ;846 ;HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=Divisa } { 1000000003;Label ;15000;423 ;1950 ;846 ;ParentControl=1000000002; HorzAlign=Center; VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡ } } } { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=423; OnPreSection=BEGIN prove.INIT; precio:="Direct Unit Cost"; IF prove.GET("Buy-from Vendor No.") THEN; divisa:=prove."Currency Code"; CurrReport.SHOWOUTPUT(("Outstanding Quantity">0) AND ver ); IF sindetalle = TRUE THEN CurrReport.SHOWOUTPUT(FALSE); //IF (fechafiltro <> 0D) AND ("Outstanding Quantity">0) THEN //CurrReport.SHOWOUTPUT(ver) ELSE CurrReport.SHOWOUTPUT(FALSE); //IF (fechafiltro <> 0D) THEN //"Purchase Line".SETRANGE("Purchase Line"."Fecha confirmaci¢n",fechafiltro); END; } CONTROLS { { 28 ;TextBox ;8250 ;0 ;1800 ;423 ;HorzAlign=Center; FontItalic=S¡; DecimalPlaces=0:4; SourceExpr="Direct Unit Cost" } { 19 ;TextBox ;2700 ;0 ;5400 ;423 ;SourceExpr=prove.Name } { 11 ;TextBox ;900 ;0 ;1500 ;423 ;FontBold=S¡; SourceExpr="Document No." } { 61 ;TextBox ;10200;0 ;1050 ;423 ;HorzAlign=Center; FontBold=S¡; SourceExpr="Currency Code" } { 42 ;TextBox ;11400;0 ;1500 ;423 ;HorzAlign=Center; SourceExpr=Quantity } { 23 ;TextBox ;13050;0 ;1800 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; SourceExpr="Outstanding Quantity" } { 1000000002;TextBox;15000;0 ;1950 ;423 ;HorzAlign=Center; SourceExpr="Fecha confirmaci¢n" } { 1000000028;Label ;0 ;0 ;900 ;423 ;VertAlign=Bottom; FontSize=8; FontBold=S¡; FontUnderline=No; MultiLine=S¡; CaptionML=ESP=prob } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table39; DataItemTableView=SORTING(Fecha confirmaci¢n); DataItemVarName=<Purchase Line2>; PrintOnlyIfDetail=No; DataItemLink=No.=FIELD(No.); } SECTIONS { { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=423; OnPreSection=BEGIN ////////C¢digo para el siguiente body prove.INIT; precio:="Direct Unit Cost"; IF prove.GET("Buy-from Vendor No.") THEN; divisa:=prove."Currency Code"; CurrReport.SHOWOUTPUT(("Outstanding Quantity">0) AND ver ); IF sindetalle = TRUE THEN CurrReport.SHOWOUTPUT(FALSE); //fechafiltro //salesline.RESET; //lineacompra.SETRANGE("Document Type", PurchHeader."Document Type"); //fechacalc:=CALCDATE("Plazo transporte","Fecha confirmaci¢n"); //reserva.CALCFIELDS("Existencias producto"); //fechacalc:=CALCDATE(fechafiltro,"Fecha confirmaci¢n"); //"Purchase Line".SETRANGE("Purchase Line"."Fecha confirmaci¢n",StartDate,EndDate); //"Purchase Line".SETRANGE("Purchase Line"."Fecha confirmaci¢n",fechafiltro); //intentar con SETRANGE, para cada fecha con el valor de tu filtro fecha del requestform //y me imagino que tendrias que hacer un CALCDATE() END; } CONTROLS { { 1000000001;TextBox;900 ;0 ;1500 ;423 ;FontBold=S¡; SourceExpr="Document No." } { 1000000019;TextBox;2700 ;0 ;5400 ;423 ;SourceExpr=prove.Name } { 1000000020;TextBox;8250 ;0 ;1800 ;423 ;HorzAlign=Center; FontItalic=S¡; DecimalPlaces=0:4; SourceExpr="Direct Unit Cost" } { 1000000021;TextBox;10200;0 ;1050 ;423 ;HorzAlign=Center; FontBold=S¡; SourceExpr="Currency Code" } { 1000000025;TextBox;11400;0 ;1500 ;423 ;HorzAlign=Center; SourceExpr=Quantity } { 1000000026;TextBox;13050;0 ;1800 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; SourceExpr="Outstanding Quantity" } { 1000000027;TextBox;15000;0 ;1950 ;423 ;HorzAlign=Center; SourceExpr="Fecha confirmaci¢n" } } } { PROPERTIES { SectionType=Footer; SectionWidth=18900; SectionHeight=846; OnPreSection=BEGIN ///Calculo de cantidad a pedir seg£n tiempo-Eloy CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FiltroLinVenta <> '')); IF (fechafiltro <> 0D) AND ("Outstanding Quantity">0) THEN CurrReport.SHOWOUTPUT(ver) ELSE CurrReport.SHOWOUTPUT(FALSE); IF sindetalle = TRUE THEN CurrReport.SHOWOUTPUT(FALSE); //fechahoy:=TODAY; //IF ("Sales Line"."Shipment Date" <> 0D) AND ("Fecha confirmaci¢n" <> 0D) THEN //calcfecha:="Sales Line"."Shipment Date"-"Fecha confirmaci¢n" //ELSE //calcfecha:=0; //fechacalc:=CALCDATE("Plazo transporte","Fecha confirmaci¢n"); /////Fin de calculo END; } CONTROLS { { 1000000017;Label ;10650;423 ;5100 ;423 ;HorzAlign=Left; FontSize=8; FontBold=S¡; CaptionML=ESP=Cdad. a tener en almacen en fecha } { 1000000032;TextBox;15750;423 ;1350 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; DecimalPlaces=0:4; SourceExpr=fechafiltro } { 1000000018;TextBox;17100;423 ;1650 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; DecimalPlaces=0:0; SourceExpr=Item.Inventory+"Outstanding Quantity"-Item."Qty. on Sales Order"-stockseguridad-Item."Cantidad a reservar" } } } } } { PROPERTIES { DataItemIndent=2; DataItemTable=Table38; PrintOnlyIfDetail=No; DataItemLink=No.=FIELD(Document No.); } SECTIONS { } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table123; DataItemTableView=; DataItemLink=No.=FIELD(No.); } SECTIONS { { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=0; OnPreSection=BEGIN nocompra:=FALSE; prove.INIT; precio:="Direct Unit Cost"; //IF prove.GET("Buy-from Vendor No.") THEN; //divisa:=prove."Currency Code"; IF cabcompra.GET("Buy-from Vendor No.") THEN; divisa2:=cabcompra."Currency Code"; //IF precio = 0 THEN // precio := LastDirUnitCostDiffFrom0.Rec; END; } CONTROLS { } } { PROPERTIES { SectionType=Footer; SectionWidth=18900; SectionHeight=846; OnPreSection=BEGIN CurrReport.SHOWOUTPUT(ver); END; } CONTROLS { { 53 ;Label ;0 ;423 ;3944 ;423 ;HorzAlign=Left; FontSize=8; FontBold=S¡; CaptionML=ESP=éltimo Precio de Compra : } { 54 ;TextBox ;4024 ;423 ;1500 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; DecimalPlaces=0:4; SourceExpr=precio } { 55 ;TextBox ;5524 ;423 ;1500 ;423 ;HorzAlign=Left; FontSize=8; FontBold=S¡; SourceExpr=divisa } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table32; DataItemTableView=SORTING(Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date) WHERE(Entry Type=FILTER(Purchase)); DataItemLink=Item No.=FIELD(No.); } SECTIONS { { PROPERTIES { SectionType=Header; SectionWidth=18900; SectionHeight=0; OnPreSection=BEGIN CurrReport.SHOWOUTPUT(ver); END; } CONTROLS { } } { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=0; OnPreSection=BEGIN //////////////////////////////////////////////////////// UPG //IF "Precio unitario" <> 0 THEN ptas:="Precio unitario"; //nocompra:=FALSE; END; } CONTROLS { } } { PROPERTIES { SectionType=Footer; SectionWidth=18900; SectionHeight=2115; OnPreSection=BEGIN CurrReport.SHOWOUTPUT(ver); END; } CONTROLS { { 57 ;Label ;0 ;423 ;7200 ;423 ;HorzAlign=Left; FontSize=8; FontBold=S¡; CaptionML=ESP=éltimo Precio de Compra Registrado (en euros) : } { 58 ;TextBox ;7200 ;423 ;1500 ;423 ;HorzAlign=Center; FontSize=8; FontBold=S¡; DecimalPlaces=0:4; SourceExpr=ptas } { 1000000006;Shape ;0 ;1269 ;18900;423 ;BorderWidth=Hairline; ShapeStyle=HorzLine } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table2000000026; DataItemTableView=SORTING(N£mero) WHERE(N£mero=FILTER(1..)); OnAfterGetRecord=BEGIN IF N£mero = 1 THEN Aceptar := MemIntTotDivisa.FIND('-') ELSE Aceptar := MemIntTotDivisa.NEXT <> 0; IF NOT Aceptar THEN CurrReport.BREAK; MemIntTotDivisa2.UpdateTotal( MemIntTotDivisa."Currency Code", MemIntTotDivisa."Total Amount", Contador1, Contador1); END; OnPostDataItem=BEGIN MemIntTotDivisa.DELETEALL; END; } SECTIONS { { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=2115; OnPreSection=BEGIN CurrReport.SHOWOUTPUT(nocompra AND ver); nocompra:=FALSE; END; } CONTROLS { { 16 ;Label ;0 ;423 ;15750;423 ;HorzAlign=Left; FontSize=8; FontBold=S¡; CaptionML=ESP=éltimo Precio de Compra : Nunca ha registrado una Compra de este Producto en Navision ! } { 1000000005;Shape ;0 ;1269 ;18900;423 ;BorderWidth=Hairline; ShapeStyle=HorzLine } } } { PROPERTIES { SectionType=Footer; SectionWidth=18900; SectionHeight=0; OnPreSection=BEGIN CurrReport.SHOWOUTPUT(nocompra AND ver); nocompra:=FALSE; END; } CONTROLS { } } } } { PROPERTIES { DataItemTable=Table2000000026; DataItemTableView=SORTING(N£mero) WHERE(N£mero=FILTER(1..)); DataItemVarName=Entero2; OnAfterGetRecord=BEGIN IF N£mero = 1 THEN Aceptar := MemIntTotDivisa2.FIND('-') ELSE Aceptar := MemIntTotDivisa2.NEXT <> 0; IF NOT Aceptar THEN CurrReport.BREAK; END; OnPostDataItem=BEGIN MemIntTotDivisa2.DELETEALL; END; } SECTIONS { { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=0; OnPreSection=BEGIN //CurrReport.SHOWOUTPUT(ImprImportesEnDL); END; } CONTROLS { } } { PROPERTIES { SectionType=Body; SectionWidth=18900; SectionHeight=0; OnPreSection=BEGIN CurrReport.SHOWOUTPUT(NOT ImprImportesEnDL); END; } CONTROLS { } } } } } REQUESTFORM { PROPERTIES { Width=6710; Height=5060; SaveValues=S¡; } CONTROLS { { 1 ;CheckBox ;4510 ;4070 ;440 ;440 ;ShowCaption=No; CaptionML=ESP=Mostrar importes en DL; SourceExpr=ImprImportesEnDL } { 21 ;Label ;1100 ;4070 ;3300 ;440 ;ParentControl=1 } { 2 ;CheckBox ;4510 ;3410 ;440 ;440 ;ShowCaption=No; CaptionML=ESP=P gina nueva por cliente; SourceExpr=ImpriSoloUnoporPag } { 22 ;Label ;1100 ;3410 ;3300 ;440 ;ParentControl=2 } { 1000000002;CheckBox;3960;2200 ;440 ;440 ;Visible=S¡; ShowCaption=No; CaptionML=ESP=Mostrar lineas con detalle; SourceExpr=sindetalle } { 1000000003;Label ;440 ;2200 ;3300 ;440 ;Visible=S¡; CaptionML=ESP=Mostrar lineas sin detalle } { 1000000000;Label ;440 ;2640 ;3300 ;440 ;Visible=S¡; FontItalic=S¡; CaptionML=ESP=(Por defecto con detalle) } { 1000000001;Label ;440 ;550 ;3080 ;440 ;Visible=S¡; CaptionML=ESP=Calculo stock a fecha: } { 1000000004;TextBox;3740 ;550 ;1650 ;440 ;CaptionML=ESP=fechafiltro; SourceExpr=fechafiltro } } } CODE { VAR TipoCambDivisa@1103351000 : Record 330; MemIntTotDivisa@1103351001 : TEMPORARY Record 332; MemIntTotDivisa2@1103351002 : TEMPORARY Record 332; CabPedVta@1103351003 : Record 36; FiltClient@1103351004 : Text[250]; FiltroLinVenta@1103351005 : Text[250]; ImpPedVta@1103351006 : Decimal; ImpPedVtaDL@1103351007 : Decimal; ImprImportesEnDL@1103351008 : Boolean; NomPeriodo@1103351009 : Text[30]; ImpriSoloUnoporPag@1103351010 : Boolean; CdadPedPndte@1103351011 : Decimal; PedNuevo@1103351012 : Boolean; Aceptar@1103351013 : Boolean; Contador1@1103351014 : Integer; CodDivisa2@1103351015 : Code[10]; prove@1103351016 : Record 23; clie@1103351017 : Record 18; ver@1103351018 : Boolean; precio@1103351019 : Decimal; nocompra@1103351020 : Boolean; divisa@1103351021 : Text[30]; ptas@1103351022 : Decimal; lolo@1103351023 : Record 39; sAvanzada@1000000000 : Text[200]; sContado@1000000001 : Text[200]; sindetalle@1000000003 : Boolean; stockseguridad@1000000002 : Decimal; cabcompra@1103351024 : Record 122; divisa2@1103351025 : Text[30]; calcfecha@1000000004 : Integer; fechahoy@1103351026 : Date; cantpedventa@1000000005 : Decimal; cantpedventatotal@1000000006 : Decimal; fechacalc@1000000007 : Date; fechafiltro@1000000008 : Date; PROCEDURE @1000000000(); BEGIN END; BEGIN END. } }0 -
a little tip: if you export your code as text, do this in english and not in your local language. The english version can everybody import ... ;-)
after analyzing your code .......
Your date Filter Variable is "fechafiltro".
This you set on the request form. There the user can insert a date as filter.
These filter you want to use in your linked dataitems (Purchase Line, Sales Line).
So, to use this date as filter condition, the correct "place" is the OnPreDataItem of the selected DataItem.
Sales Line - OnPreDataItem()
setrange("Shipment Date",fechafiltro); //this is a filter on field Shipment Date only on table Sales Line
This select definitly only the Sales Lines for your selected Item (here Sales Line) on DataItem Item.
To check if the filter is working you can also set a error message in
Sales Line - OnAfterGetRecord()
error('The Filtered Shipment Date = %1,"Shipment Date");
and if your report doesn't show Sales Lines, is the other question: Exist there some sales lines with your selected Shipment date?
Also check the value of variable "sindetalle". if it is true, you doesn't print the sections
RegardsDo you make it right, it works too!0 -
First of all thank you very much for your interest and forgiveness for what the language, so I have in mind.
The truth is I'm doing exactly what he told me, but I'm being filtered instruction in the OnpreDataItem of SalesLine, making it well I get everything in white, not use the "no detail". However, if I remove the code from OnpredataItem and put it in the Sales Line body it filtered properly, but only a record of which are within the time indicated that, if there is not more than one sample, is as if it not journey the whole the table.
For example, I put a filter on the date 12/12/08, and there are two registers that satisfy the condition, the first dated 5/12/08 followed by another dated 8/11/07 but it only shows me the 8/11/07 dated, skips the first register that also satisfy the condition. I don't know if that can give you some hint of what may be happening.
Thank you very much indeed for your help and your time.0 -
Hi Leroy,
As Garak says it's difficult to import this version for me so I'm just reading the code.
Is the dataitem filter on the sales line dataitemWHERE(Document Type=CONST(Order), Outstanding Quantity=FILTER(<>0), Document No.=FILTER(PV*|PM*));filtering out the record for the 5/12/08 ?0 -
The filter It's correct, filtering out of 5/12/08 occurs the same, I thik is that don't look for across the whole table for any problem.
Thanks for reply and help.0 -
Any suggestion please?, I know it's difficult.
Thanks in advance.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
- 322 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