[solved] NAV 2016 Query count rows

dannyt · 2016-09-16

Hi,

I have a Problem with a query in NAV 2016.
In the Sales Header table there is a new field "Sales Promotion No.".
This field is also in the Warehouse Shipment Header.
When I create a new Warehouse Shipment Header from the Sales Header, the no. will be transferred.

Now I want a Query which Shows me all Sales Promotion Nos. from the Sales Header in a Group and how many Sales Headers and Warehouse Shipment Headers exists for this Promotion No.

This is my current query:

OBJECT Query 55500 Distribution Orders
{
  OBJECT-PROPERTIES
  {
    Date=16.09.16;
    Time=12:35:31;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    CaptionML=[DEU=Verteilerauftr„ge;
               ENU=Distribution Orders];
  }
  ELEMENTS
  {
    { 1112000000;;DataItem;                  ;
               DataItemTable=Table36;
               DataItemTableFilter=Document Type=CONST(Order),
                                   Sales Promotion No.=FILTER(<>'') }

    { 1112000002;1;Column;SalesPromotionNo_SalesHeader;
               DataSource=Sales Promotion No. }

    { 1112000001;1;Column;NoOfOrders         ;
               MethodType=Totals;
               Method=Count }

    { 1112000004;1;DataItem;                 ;
               DataItemTable=Table7320;
               DataItemLink=Sales Promotion No.=Sales_Header."Sales Promotion No." }

    { 1112000003;2;Column;SalesPromotionNo_WhseShptHdr;
               DataSource=Sales Promotion No. }

    { 1112000005;2;Column;NoOfWhseShpts      ;
               MethodType=Totals;
               Method=Count }

  }
  CODE
  {

    BEGIN
    {
    }
    END.
  }
}

Unfortunately the counting of the Warehouse Shipment Header is not correct.
Both shows the total of the lines in the query.
If I have 2 Sales Headers and 2 Whse Shpt Headers with the same "Sales Promotion No.", both counts show "4".

What's wrong with my query?

Best regards
dannyt

kaspermoerch · 2016-09-21

If you try adding a unique value for each DataItem to group on (e.g. No.) then you will see why you get the same value for both counts.

An alternative solution would be to add a FlowField on your Sales Header that counts the number of Warehouse Shipments with the same Sales Promotion No.

Then in your query you only have the Sales Header DataItem and the columns Sales Promotion No., the newly created FlowField and your count column. Because the count of the FlowField will be the same for each unique Sales Promotion No. you will get the right values from your query.

dannyt · 2016-09-20

Does no one has any idea?

kaspermoerch · 2016-09-21

If you try adding a unique value for each DataItem to group on (e.g. No.) then you will see why you get the same value for both counts.

An alternative solution would be to add a FlowField on your Sales Header that counts the number of Warehouse Shipments with the same Sales Promotion No.

Then in your query you only have the Sales Header DataItem and the columns Sales Promotion No., the newly created FlowField and your count column. Because the count of the FlowField will be the same for each unique Sales Promotion No. you will get the right values from your query.

dannyt · 2016-09-23

Thank you very much for your answer kaspermoerch!
I didn't know the COUNT behaviour in the querys.
Now I created a new FlowField and everything works fine.

[solved] NAV 2016 Query count rows

Best Answer

Answers

Categories