Making joins between tables

xavigepe

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,

andreof

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.

philippegirod

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);

xavigepe

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

andreof

Normally flowfields are much faster then running all the records in a table

Miklos_Hollender

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.

andreof

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

Miklos_Hollender

http://mibuso.com/dlinfo.asp?FileID=515