How to split lines based on condition in Business Central Report

xhirvana

We have this packing list report that we need to put pallet no. Column. Per sales line. Ex. Is that if we have an item no. With 1000 qty, if this item has 200cases per pallet,the item needs to split into 5 lines with qty 200 cases and with pallet no. 1 to 5. This will repeat for all the sales lines given the no. Of cases per pallet as maximum qty per line. Kindly help me with this one please...🙏. Thank you.

Resolus

Hmm, this is only required in the report, not in BC itself?

In that case the simplest thing would be to create a global Temporary Table for your lines in the OnAfterGetRecord trigger of the header. And then use the Temporary Table as source for your Lines dataitem (and put it's property on temporary too).

You just loop through your record with the Lines (that is not Temporary).
For each line, get the Item and check that "No. Of Cases Max" has a value.
If its 0 then you don't need to split and you can just add the entire line to the Temporary Table.
If it is anything else (preferably a positive number ofcourse ), then you need to continue in code to split it.

TotalQuantity := SalesLine.Quantity;
MaxQtyPerLine := Item."No. Of Cases Max";
NoOfLines := Round(TotalQuantity  / MaxQtyPerLine, 1, '>'); // Round it up to the nearest integer above the result, so 4.5 => 5

e.g.

TotalQuantity := 900;
MaxQtyPerLine  := 200;
NoOfLines  := Round(900/ 200, 1, '>'); // Result is 5

And then just create the loop, so that would get you the code below

TotalQuantity := SalesLine.Quantity;
MaxQtyPerLine := Item."No. Of Cases Max";
NoOfLines := Round(TotalQuantity / MaxQtyPerLine, 1, '>');

for Index = 1 to NoOfLines  do begin
    NewQuantity := MaxQtyPerLine;
    if (Index = NoOfLines) then
        NewQuantity := TotalQuantity - ((NoOfLines - 1) * MaxQtyPerLine);
    TempSalesLine := SalesLine;
    TempSalesLine.Validate(Quantity, NewQuantity);
    TempSalesLine.Insert(false);
end;

Hopefully this gives you an idea of how to do it.
I believe this is the cleanest way to get your report with more lines, while keeping it in Business Central on a single line.

xhirvana

@Resolus thank you for your help! Yes you are correct, we only have the sales lines as basis and we just want to play it around the report. I will definitely try this! Thanks you ❤️☺️