Finance | Project Operations, Human Resources, ...

How to get only in each salesid, maximum salesqty's data only.

(0) Share

Report

Posted on by Community Member

Hi,

How to get maximum salesqty's(salesline) salesid(salestable) and itemid(salesline) and custaccount(salestable).

    Query                   query;
    QueryBuildDataSource    qbds,qbds1,qbds2;
    QueryRun                queryrun;
    SalesTable              salestable;
    SalesLine               salesline;

    query                   =   new query();

    qbds                    =   query.addDataSource(tableNum(SalesTable));
    qbds.addGroupByField(fieldNum(SalesTable,SalesId));
    info(query.toString());

    qbds1                   =   qbds.addDataSource(tableNum(SalesLine));
    qbds1.addLink(fieldNum(SalesTable,SalesId),fieldNum(SalesLine,SalesId));
    qbds1.addGroupByField(fieldNum(salesline,ItemId));
    qbds1.addRange(fieldNum(salesline, SalesId)).value('000002,000003');
    qbds1.addSelectionField(fieldNum(SalesLine,SalesPrice),SelectionField::Max);

    info(query.toString());

    queryrun                =   new QueryRun(query);
    while(queryrun.next())
    {
        salesline           =   queryrun.get(tableNum(SalesLine));
        salestable          =   queryrun.get(tableNum(SalesTable));

        info(strFmt("%1  -  %2  -  %3  ",salestable.SalesId,Salesline.SalesPrice,Salesline.ItemId));

    }

It's showing but not expected.

I need only in each salesid, maximum salesqty's data only.

but showing all.

Thanks in advance

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Martin Dráb 236,279 Most Valuable Professional on at

Like (0)

Report

RE: How to get only in each salesid, maximum salesqty's data only.

No, it's not the same.

For example, if you group by SalesId only (2nd requirement), you lose information about ItemId (1st requirement). And if you want ItemId and there is an order with two items, you'll get two records with the same SalesId and different item IDs.

If you want to get a single record for each line, which ItemId do you want to see? You can use aggregation functions to get the lowest or the highest, but I'm not sure if it was useful. It depends on the business requirement you're trying to address (which is unknown to me).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: How to get only in each salesid, maximum salesqty's data only.

Thanks to your replay martin,

it's not two different requirements, It's same

I need saleslid,itemid,custaccount for maximum salesqty. salesid shouldn't repeat

In, I mentioned the above code, I used this line

"qbds1.addGroupByField(fieldNum(salesline,ItemId));"

if I don't use this line It's showing like this

this one is I expected but itemid is not showing. but I need itemid.

Regards,

Ashok kumar M.

Was this reply helpful? Yes No
Martin Dráb 236,279 Most Valuable Professional on at

Like (0)

Report

RE: How to get only in each salesid, maximum salesqty's data only.

You mentioned two different requirements: "to get maximum salesqty's(salesline) salesid(salestable) and itemid(salesline) and custaccount(salestable)" and "I need only in each salesid, maximum salesqty's data only". Which one do you actually want?

By the way, please don't use the category "Other" for questions about development. Use "Development / Customization / SDK" instead.

Was this reply helpful? Yes No