Group By on a Dataport?

Business Central/NAV Developers Forum
1

Hi,

Is it possible to "Group By " on a dataport (Export), if yes can someone show me how!

Thanks

2

Could you be a little bit more precise on your question ?

What is the situation and what do you want to achieve with your dataport ?

3

I want to export the lines of a sales invoice but i want them to be grouped by item number, totaling the quantity and amounts.

4

This requires quite some manual action (coding) in order to achieve.

Does this question relate to the “Excel copy” story ?

In order to do this you would need to:

  • Create a temporary record variable of the subtype “Item” as a global in your dataport.

  • Create a record variable of the subtype “Sales Invoice Line” as a global in your dataport

  • Add some code into the “OnBeforeExportRecord”:

    IF “Sales Invoice Line”.Type = “Sales Invoice Line”.Type::Item THEN BEGIN
    IF TempItem.GET(“Sales Invoice Line”.“No.”) THEN
    CurrDataport.SKIP
    ELSE BEGIN
    TempItem.“No.” := “Sales Invoice Line”.“No.”;
    TempItem.INSERT;
    SalesInvoiceLine.SETRANGE(“Document No.”,“Sales Invoice Line”.“Document No.”);
    SalesInvoiceLine.SETRANGE(Type, SalesInvoiceLine."Type::Item);
    SalesInvoiceLine.SETRANGE(“No.”,“Sales Invoice Line”.“No.”);
    SalesInvoiceLine.FINDSET;
    “Sales Invoice Line”.“Quantity (Base)” := 0;
    “Sales Invoice Line”.“Amount” := 0;
    REPEAT
    "Sales Invoice Line.“Quantity (Base)” := "Sales Invoice Line.“Quantity (Base)” + SalesInvoiceLine.“Quantity (Base)”;
    "Sales Invoice Line.“Amount” := "Sales Invoice Line.“Amount” + SalesInvoiceLine.“Amount”;
    UNTIL SalesInvoiceLine.NEXT = 0;
    END;
    END;

That should be it.
I did not test the code, just typed it in here. But it should give you an idea.

What we actually do is to prevent a certain item no. to appear twice in the dataport. It will always show the total for an item at the first occurance. Anyway I would rather retrieve the information from the “Item Ledger Entry” table rather than from the “Sales Invoice Line” as that includes sales AND credits.

Be also careful to not export the “Quantity” as it could have different UoM defined in different lines. So always use “Quantity (Base)” instead.

Hope that helps.

5

In other words: no you can’t technically group a dataport the way that you can group a report, but you can program a dataport to simulate grouping.

6

Hi

thanks DenSter thats all i really needed to know, (You would think that, it should be there though?) Thanks Thomas for your reply i really appreciate you putting in your time to help me out [:)].

this topic is now closed.