Filter sales order headers by sales order line item no.

Business Central/NAV User Forum
1

I pretend filter all sales header that have a Sales Lines No. like X.

How can I do That?

2

What you are actually trying to do?

3

I pretende select all sales header, with a specific Product Group Code in Sales Line.

So. If sales Order has a line with a specific Product Group code, then I want to print that order in my report.

If a sales order, on sales line does not have any line with this specific Product group code, so this sales order mustn’t be printed.

Thanks

4

You can take sales Header and sales line as Indented under sales header.

You can write code in OnPreDataport to filter from user input

show the data in sections.

5

Hello Mohana

I have tried this:

Sales Header - OnPreDataItem()
LastFieldNo := FIELDNO(“No.”);
SalesLine.SETFILTER(SalesLine.“Product Group Code”,’%1’,‘X’);
IF SalesLine.FINDSET(TRUE) THEN;

But with this all sales header are printed in my report, even the ones that hasn’t one sales line like Product Group Code" =‘X’.

6

You can try something like

SalesLine.RESET;
SalesLine.SETRANGE(“Document Type”,“Document Type”);
SalesLine.SETRANGE(“Document No.”,“No.”);
SalesLine.SETFILTER(“Product Group Code”,’%1’,‘X’);
Skip := SalesLine.FINDFIRST;

IF NOT Skip THEN
CurrReport.SKIP;

In OnAfterGetRecord trigger…

Where skip is a boolean…

7

Afternoon

The reason for the printing of all the sales headers are that you have not set the property PrintOnlyIfDetail = YES on the “Sales Header” table.

You can actually do your report almost without coding by simply adding SalesLine.“Product Group Code” field on the ReqFilterFields-property on the salesline.

NOTICE one thing though by doing this you ONLY get the lines printed with exact this “Product Group Code” and NOT other lines on the very same orders.

Nevertheless this seems to be an odd reques - why do you want to do this? There might be a working solution without any coding/object change at all depending of your reason :slight_smile:

If you stick to that you want all reports with this function then you should make it a bit more advanced and perhaps print a normal Sales Order/unposted Sales Invoice or Credit Memo by making a codeunit than runs through all Sales Lines that gets the Sales Header and marks them - and then afterwards do a Print Marked Only :slight_smile:

8

Hello

Thanks for your idear, but the problem is that I pretend all sales lines, not only the ones that has my Product Group Code like X.

So, let’s suppose I have 3 Sales order, but only two has, on one or more lines my Producto Group Code X. In this case, my reports must print those 2 order sales completly. With all sales line, the ones that has PGC = X ant the other lines that has different PGC.

Should be something like:

SELECT * FROM SALES HEADER

WHERE “No.” in ( SELECT Document No. FROM SALES LINES WHERE Gen. Prod. Posting Group = X)

How can I implement this in Navision?

Thanks

9

Hello Mohana

Your idear was good, help me a lot.

Thanks

10

Welcome [:)]