Options

Filtering date using another field

LeroyLeroy 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.

Comments

  • Options
    garakgarak Member Posts: 3,263
    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".

    Regards
    Do you make it right, it works too!
  • Options
    LeroyLeroy Member Posts: 199
    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 help
  • Options
    DaveTDaveT Member Posts: 1,039
    Hi,
    Try

    SalesLine.setfilter("Shipment Date",FORMAT(DateFilterVariable));
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • Options
    LeroyLeroy Member Posts: 199
    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.
  • Options
    DaveTDaveT Member Posts: 1,039
    Hi Leroy,

    Can you post some code and I can have a look.
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • Options
    LeroyLeroy Member Posts: 199
    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.
  • Options
    garakgarak Member Posts: 3,263
    edited 2008-12-04
    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!
  • Options
    LeroyLeroy Member Posts: 199
    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.
  • Options
    garakgarak Member Posts: 3,263
    let us see your report design as text
    Do you make it right, it works too!
  • Options
    LeroyLeroy Member Posts: 199
    Sorry, how i do that?.
    Thanks.
  • Options
    SavatageSavatage Member Posts: 7,142
    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.
  • Options
    LeroyLeroy Member Posts: 199
    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.
      }
    }
    
    
  • Options
    garakgarak Member Posts: 3,263
    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

    Regards
    Do you make it right, it works too!
  • Options
    LeroyLeroy Member Posts: 199
    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.
  • Options
    DaveTDaveT Member Posts: 1,039
    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 dataitem
    WHERE(Document Type=CONST(Order),
                                    Outstanding Quantity=FILTER(<>0),
                                    Document No.=FILTER(PV*|PM*));
    
    filtering out the record for the 5/12/08 ?
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • Options
    LeroyLeroy Member Posts: 199
    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.
  • Options
    LeroyLeroy Member Posts: 199
    Any suggestion please?, I know it's difficult.
    Thanks in advance.
Sign In or Register to comment.