GET number of invoices

bekio

Hello every one!

I have created a report from table Value Entry table that groups sales by customer. Does navision has any command to get the number of invoices created for that user for that filter applied,like in SQL Distinct.

Thanks,

mohana_cse06

Did you try "COUNT"

bekio

COUNT i know but it returns me not exactly numer og invoices, because at Value Entry one invoice could have more items and for that number of invoices is repeated. So if i have Document XX with two items YX and YZ count returns me 2, but in real it is just one invoice.

vijay_g

where you writing your code?
Try to group with "sales by customer" and "Document No.".

AntidotE

I did similar research with the following unoptimized code (needed quantity of Sales orders, that have specified Item in lines), this is for customized informational pane on Item Card:

    PROCEDURE GetSalesOrderCount@50000(pvItemNo@50000 : Code[20]) : Integer;
    VAR
      ltSalesHeader@50001 : Record 36;
      ltSalesLine@50002 : Record 37;
      ltTempSalesHeader@50003 : TEMPORARY Record 36;
    BEGIN
      ltSalesLine.SETCURRENTKEY(Type,
                               "No.",
                               "Variant Code",
                               "Drop Shipment",
                               "Location Code",
                               "Document Type",
                               "Shipment Date");
      ltSalesLine.SETRANGE(Type, ltSalesLine.Type::Item);
      ltSalesLine.SETRANGE("No.",pvItemNo);
      ltSalesLine.SETRANGE("Document Type", ltSalesLine."Document Type"::Order);
      IF ltSalesLine.FINDSET THEN
        REPEAT
          ltSalesHeader.GET(ltSalesHeader."Document Type"::Order,ltSalesLine."Document No.");
          IF NOT ltTempSalesHeader.GET(ltTempSalesHeader."Document Type"::Order,ltSalesLine."Document No.") THEN BEGIN
            ltTempSalesHeader.INIT;
            ltTempSalesHeader.TRANSFERFIELDS(ltSalesHeader);
            ltTempSalesHeader.INSERT;
          END;
        UNTIL ltSalesLine.NEXT = 0;
      EXIT(ltTempSalesHeader.COUNT);
    END;

You can easily use this agorithm for filtering out necessary Value Entries. And don't forget to set key to closest to your filter, otherwise you will get performance issue .