Repeating items in report

Dynamics 365 Business Central/NAV User Forum
1

Hi,

I am doing a report related to Sub-contracting, the report is running fine with the values but its giving the same line many times. The tables taken are “Purchase Line” & “Item Ledger Entry”. In “Purchase Line” grouping is done on “Document No.”. In “Item Ledger Entry” grouping on “Item No.”. This is the code I hav written in “Item Ledger Entry” GroupHeader…

recILE1.RESET;
recILE1.SETRANGE(recILE1.“Document No.”,“Item Ledger Entry”.“Document No.”);
recILE1.SETFILTER(recILE1.“Item Category Code”,’%1|%2’,‘CB’,‘MB’);
recILE1.SETFILTER(recILE1.Positive,‘YES’);
recILE1.SETFILTER(recILE1.“Entry Type”,‘Transfer’);
recILE1.SETFILTER(recILE1.“Item No.”,“Item Ledger Entry”.“Item No.”);

IF recILE1.FINDFIRST THEN
BEGIN
CBMBQty := recILE1.Quantity;

recLocation.RESET;
recLocation.SETRANGE(recLocation.Code,recILE1.“Location Code”);
recLocation.SETRANGE(recLocation.Code,“Item Ledger Entry”.“Location Code”);
recLocation.SETFILTER(recLocation.“Subcontracting Location”,‘yes’);
IF recLocation.FINDFIRST THEN;
END;

Filtering and everything its doing properly except it is repeating the values in the output, where it has to be only a single line as per that document. Can anyone pls help me out with this.

Thanks in advance…

2

What is the Link between Purchase Line and Item Ledger Entry?

is ILE indented under Purchase Line?

3

Ya… it is indented.

Document No.=FIELD(Prod. Order No.)

4

is ILE indented under Purchase Line or Purchase Line indented under ILE?

In which section you have added the text boxes?

5

ILE is indented under purchase line.

Text boxes are put in ILE groupheader.

6

is this condition corret?

which means ILE Document No. = Purchase line Prod. Order No.

7

Hi,

I changed this report a bit inorder to get proper values by replacing “Purchase Line” table with “Prod. Order Line” table. Now its not repeating the items, but its showing only the first line i.e only the first item [:(] Can anyone help me out with this pls…

Hav written this code in “Prod. Order Line” OnAfterGetRecord.

recILE5.RESET;
recILE5.SETRANGE(recILE5.“Prod. Order No.”,“Prod. Order Line”.“Prod. Order No.”);
recILE5.SETFILTER(recILE5.“Item Category Code”,’%1|%2’,‘PM’,‘MFG-CON’);
recILE5.SETFILTER(recILE5.Positive,‘YES’);
recILE5.SETFILTER(recILE5.“Entry Type”,‘Transfer’);
// recILE5.SETFILTER(recILE5.“Item No.”,“Item Ledger Entry”.“Item No.”);

IF recILE5.FINDFIRST THEN
BEGIN
PMQty := recILE5.Quantity;

recLocation3.RESET;
recLocation3.SETRANGE(recLocation3.Code,recILE5.“Location Code”);
recLocation3.SETRANGE(recLocation3.Code,“Prod. Order Line”.“Location Code”);
recLocation3.SETFILTER(recLocation3.“Subcontracting Location”,‘yes’);
IF recLocation3.FINDFIRST THEN;
END;

recILE6.RESET;
recILE6.SETRANGE(recILE6.“Prod. Order No.”,“Prod. Order Line”.“Prod. Order No.”);
recILE6.SETFILTER(recILE6.“Item Category Code”,’%1|%2’,‘PM’,‘MFG-CON’);
recILE6.SETFILTER(recILE6.Positive,‘YES’);
recILE6.SETFILTER(recILE6.“Entry Type”,‘Transfer’);
recILE6.SETFILTER(recILE6.“Entry Type”,‘Positive Adjmt.’);
recILE6.SETFILTER(recILE6.“Location Code”,’%1|%2|%3|%4’,‘ST-PKT01’,‘RST-MYS01’,‘BR-BNR01’,‘BR-CHM01’);
// recILE6.SETFILTER(recILE6.“Item No.”,“Item Ledger Entry”.“Item No.”);

IF recILE6.FINDFIRST THEN
PMReturnQty := recILE6.Quantity;

recILE9.RESET;
recILE9.SETRANGE(recILE9.“Prod. Order No.”,“Prod. Order Line”.“Prod. Order No.”);
recILE9.SETFILTER(recILE9.“Item Category Code”,’%1|%2’,‘PM’,‘MFG-CON’);
recILE9.SETFILTER(recILE9.Positive,‘no’);
recILE9.SETFILTER(recILE9.“Entry Type”,‘consumption’);
// recILE9.SETFILTER(recILE9.“Item No.”,“Item Ledger Entry”.“Item No.”);

IF recILE9.FINDFIRST THEN
BEGIN
PMConsumptionQty := recILE9.Quantity;

recLocation6.RESET;
recLocation6.SETRANGE(recLocation6.Code,recILE9.“Location Code”);
recLocation6.SETRANGE(recLocation6.Code,“Prod. Order Line”.“Location Code”);
recLocation6.SETFILTER(recLocation6.“Subcontracting Location”,‘yes’);
IF recLocation6.FINDFIRST THEN;
END;

recILE10.RESET;
recILE10.SETRANGE(recILE10.“Prod. Order No.”,“Prod. Order Line”.“Prod. Order No.”);
recILE10.SETFILTER(recILE10.“Item Category Code”,’%1|%2’,‘PM’,‘MFG-CON’);
recILE10.SETFILTER(recILE10.Positive,‘no’);
recILE10.SETFILTER(recILE10.“Entry Type”,‘consumption’);
recILE10.SETFILTER(recILE10.“Entry Type”,‘Negative Adjmt.’);
recILE10.SETFILTER(recILE10.“No. Series”,’%1’,‘shortclose’);
// recILE10.SETFILTER(recILE10.“Item No.”,“Item Ledger Entry”.“Item No.”);

IF recILE10.FINDFIRST THEN
BEGIN
PMShtcQty := recILE10.Quantity;

recLocation7.RESET;
recLocation7.SETRANGE(recLocation7.Code,recILE10.“Location Code”);
recLocation7.SETRANGE(recLocation7.Code,“Prod. Order Line”.“Location Code”);
recLocation7.SETFILTER(recLocation7.“Subcontracting Location”,‘yes’);
IF recLocation7.FINDFIRST THEN;
END;

I am not able to attach the sections n output screen also… its giving some error [:(]

8

What is your exact goal of report?

why do you need these many ILE record variables? [*-)]

recILE6.RESET;
recILE6.SETRANGE(recILE6.“Prod. Order No.”,“Prod. Order Line”.“Prod. Order No.”);
recILE6.SETFILTER(recILE6.“Item Category Code”,’%1|%2’,‘PM’,‘MFG-CON’);
recILE6.SETFILTER(recILE6.Positive,‘YES’);
recILE6.SETFILTER(recILE6.“Entry Type”,‘Transfer’);
recILE6.SETFILTER(recILE6.“Entry Type”,‘Positive Adjmt.’);

What does it do with Entry Type?

9

Actually the report should show Issue,Consumption,Shortclose and Return values for each item based on the “Item Category Code” of ILE. So for each of these conditions the filters are different. So I have taken that many ILE variables.

10

as you are saving the values in a variable, you can work with single ILE variable with different filters…

where did you show the values in sections?

11

But for Issue I hav diff filters, Consumption diff filters, Shortclose diff filters n returns also diff filters… How wil I do it with a single ILE variable?? Hav shown the values in “Prod Order Line” body.

12

You can create a function with filters and send the filters as parameters and get the value through return variable…

13

Im sorry… Im still a beginner [:(] Can u pls help me a bit more wid wat u said…

14

Please contact your senior technical for how to use functions…

15

I usually use CurrReport.SHOWOUTPUT(condition here) to break out certain records.

Create multiple body as needed.