Making joins between tables

xavigepexavigepe Member Posts: 185
Hi. I have two tables: sales shipment header and sales shipment line. I want to show in a form ONLY the sales shipment header with one (or more) shipment lines where the condition "quantity shipped not invoiced <>0" is true. Using a report it would be easy, but is there an easy way for doing it in a form?.

Thanks,

Answers

  • andreofandreof Member Posts: 133
    I think you can do something like:

    Create a flowfield in the header table that checks if there are lines connected to that header with "quantity shipped not invoiced" <>0, returning true or false. Then you can use this field to filter in your form.
    Andre Fidalgo
    My world: Dynamics NAV,SQL and .NET

    CEO at Solving Dynamics
    http://www.solvingdynamics.com
  • philippegirodphilippegirod Member Posts: 191
    Before to open the form, launch a job to filter the table "Sales Header"
    Something like:
    SalesHeader.RESET; //to suppress all previous filters
    
    IF SalesHeader.FIND('-') THEN REPEAT
       SalesLine.RESET;
       SalesLine.SETFILTER("Document Type",'%1',SalesHeader."Document Type");
       SalesLine.SETFILTER("Document No.",'%1',SalesHeader."No.");
       SalesLine.SETFILTER("Qty. Shipped Not Invoiced",'<>0');
       IF SalesLine.COUNT <> 0 THEN
          SalesHeader.MARK;
    UNTIL SalesHeader.NEXT = 0;
    
    SalesHeader.MARKEDONLY; //To have only "Marked" records
    FORM.RUN(YourForm,SalesHeader);
    
    My candle burns by both ends, it will not last the night,
    But oh my foes and oh my friends, it gives a lovely light
  • xavigepexavigepe Member Posts: 185
    Thanks for your answers. I'm worried about performance. I think searching in all sales shipments for lines with quantity shipped not invoiced could be quite slow. It's faster using a flowfield?.

    Thanks so much
  • andreofandreof Member Posts: 133
    Normally flowfields are much faster then running all the records in a table
    Andre Fidalgo
    My world: Dynamics NAV,SQL and .NET

    CEO at Solving Dynamics
    http://www.solvingdynamics.com
  • Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    This is one of the things reports are for (otherwise we'd just report on forms): if you just write a report on sales headers and in the OnPreSection enter the code
    CLEAR(SaleLine);
    SalesLine.SETRANGE("Document Type","Document Type");
    SalesLine.SETRANGE("Document No.","No.");
    CurrReport.SHOWOUTPUT(SalesLine.COUNT>0);
    

    That's quite fast I think.
  • andreofandreof Member Posts: 133
    Yes that's true. But you cannot use reports for everything some times you just want the result in a form (for example to run a function in one specific line, easier to change filters and see results, drilldown, etc).
    Andre Fidalgo
    My world: Dynamics NAV,SQL and .NET

    CEO at Solving Dynamics
    http://www.solvingdynamics.com
Sign In or Register to comment.