Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance forum / Query Group By not wor...
Finance forum

Query Group By not working as expected

(0) ShareShare
ReportReport
Posted on by Emilio Molina 105

I am trying to make a query for a lookup which selects all purchase orders that are either in Received Status or in BackOrder status with at list one packingslip not received using this query:

Query query = new Query();
QueryBuildDataSource qbds;
QueryBuildDataSource QbdsJoin;
QueryBuildDataSource QbdsJoin2;
QueryBuildDataSource QbdsJoin3;
// Instantiate sysTableLookup object using table which will provide the visible fields
SysTableLookup sysTableLookup = sysTableLookup::newParameters(tableNum(PurchTable), _formControl);


// Create the query.
qbds = query.addDataSource(tableNum(PurchTable));
Qbds.addSelectionField(fieldNum(PurchTable, PurchId));
Qbds.addSortField(fieldNum(PurchTable, PurchId));

qbds.addRange(fieldNum(PurchTable, PurchStatus)).value(queryValue(PurchStatus::Received));
qbds.addRange(fieldNum(PurchTable, PurchStatus)).value(queryValue(PurchStatus::Backorder));
qbds.addRange(fieldNum(PurchTable, OrderAccount)).value(ISCPContrasenasHeader.AccountNum);
qbds.orderMode(OrderMode::GroupBy);
qbds.addGroupByField(fieldNum(PurchTable, PurchId));

QbdsJoin = qbds.addDataSource(tableNum(VENDPACKINGSLIPJOUR));
QbdsJoin.addLink(fieldNum(VENDPACKINGSLIPJOUR, PurchId),fieldNum(PurchTable, PurchId));
QbdsJoin.addSortField(fieldNum(VENDPACKINGSLIPJOUR, PurchId));
QbdsJoin.addOrderByField(fieldnum(VENDPACKINGSLIPJOUR,PurchId));
QbdsJoin.orderMode(OrderMode::GroupBy);

QbdsJoin2 = QbdsJoin.addDataSource(tableNum(vendinvoiceinfoline));
QbdsJoin2.joinMode(JoinMode::NoExistsJoin);
QbdsJoin2.addLink(fieldNum(VENDPACKINGSLIPJOUR, PurchId),fieldNum(vendinvoiceinfoline, origpurchid));

QbdsJoin3 = QbdsJoin2.addDataSource(tableNum(vendInvoiceInfoSubLine));
QbdsJoin3.addLink(fieldNum(VENDPACKINGSLIPJOUR, PACKINGSLIPID),fieldNum(vendInvoiceInfoSubLine, DocumentId));
QbdsJoin3.addLink(fieldNum(vendInvoiceInfoSubLine, LineRefRecId),fieldNum(vendinvoiceinfoline, recid));

Witch resulted on a duplicate PurchId.

I even try reducing my query to this:

Query query = new Query();
QueryBuildDataSource qbds;
QueryBuildDataSource QbdsJoin;
// Instantiate sysTableLookup object using table which will provide the visible fields
SysTableLookup sysTableLookup = sysTableLookup::newParameters(tableNum(PurchTable), _formControl);


// Create the query.
qbds = query.addDataSource(tableNum(PurchTable));
Qbds.addSelectionField(fieldNum(PurchTable, PurchId));
//Qbds.addSelectionField(fieldNum(PurchTable, OrderAccount));

qbds.addRange(fieldNum(PurchTable, PurchStatus)).value(queryValue(PurchStatus::Received));
qbds.addRange(fieldNum(PurchTable, PurchStatus)).value(queryValue(PurchStatus::Backorder));
qbds.addRange(fieldNum(PurchTable, OrderAccount)).value(ISCPContrasenasHeader.AccountNum);


QbdsJoin = qbds.addDataSource(tableNum(VENDPACKINGSLIPJOUR));
QbdsJoin.addLink(fieldNum(VENDPACKINGSLIPJOUR, PurchId),fieldNum(PurchTable, PurchId));
QbdsJoin.addSortField(fieldNum(VENDPACKINGSLIPJOUR, PurchId));
QbdsJoin.addOrderByField(fieldnum(VENDPACKINGSLIPJOUR,PurchId));
QbdsJoin.orderMode(OrderMode::GroupBy);

Qbds.addSortField(fieldNum(PurchTable, PurchId));
Qbds.addOrderByField(fieldNum(PurchTable, PurchId));
Qbds.orderMode(OrderMode::GroupBy);


When debugging I get the query into this:

SELECT PurchId FROM PurchTable(PurchTable_1) GROUP BY PurchTable.PurchId, VendPackingSlipJour.PurchId WHERE ((PurchStatus = 2) OR (PurchStatus = 1)) AND ((OrderAccount = N'1118676')) JOIN * FROM VendPackingSlipJour(VendPackingSlipJour_1) ON PurchTable.PurchId = VendPackingSlipJour.PurchId

Witch also return a duplicated purchid. In my understanding this translates to this SQL Statement:

SELECT PurchTable.PurchId FROM PurchTable
JOIN VendPackingSlipJour ON PurchTable.PurchId = VendPackingSlipJour.PurchId
WHERE ((PurchStatus = 2) OR (PurchStatus = 1)) AND ((PurchTable.OrderAccount = N'1118676'))
GROUP BY PurchTable.PurchId, VendPackingSlipJour.PurchId

Witch correctly result in only one PurchId as I expected.

What could it be wrong?

Categories:
Development / Customization / SDK
  • Emilio Molina Profile Picture
    Emilio Molina 105 on at
    RE: Query Group By not working as expected

    Thanks a lot for your time Martin. This was really helpful!

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 231,872 Most Valuable Professional on at
    RE: Query Group By not working as expected

    You can simplify your query to this:

    Query query = new Query();
QueryBuildDataSource purchDs;
QueryBuildDataSource packSlipDs;

purchDs = query.addDataSource(tableNum(PurchTable));
purchDs.addGroupByField(fieldNum(PurchTable, PurchId));
purchDs.addRange(fieldNum(PurchTable, PurchStatus)).value(queryValue(PurchStatus::Received));
purchDs.addRange(fieldNum(PurchTable, PurchStatus)).value(queryValue(PurchStatus::Backorder));
purchDs.addRange(fieldNum(PurchTable, OrderAccount)).value(ISCPContrasenasHeader.AccountNum);

packSlipDs = purchDs.addDataSource(tableNum(VendPackingSlipJour));
packSlipDs.relations(true);
packSlipDs.addGroupByField(fieldNum(VendPackingSlipJour, PurchId));

    I also don't see any reason for returning PurchId from VendPackingSlipJour, if it's the same as PurchTable.PurchId, therefore you could use an exists join:

    Query query = new Query();
QueryBuildDataSource purchDs;
QueryBuildDataSource packSlipDs;

purchDs = query.addDataSource(tableNum(PurchTable));
purchDs.addGroupByField(fieldNum(PurchTable, PurchId));
purchDs.addRange(fieldNum(PurchTable, PurchStatus)).value(queryValue(PurchStatus::Received));
purchDs.addRange(fieldNum(PurchTable, PurchStatus)).value(queryValue(PurchStatus::Backorder));
purchDs.addRange(fieldNum(PurchTable, OrderAccount)).value(ISCPContrasenasHeader.AccountNum);

packSlipDs = purchDs.addDataSource(tableNum(VendPackingSlipJour));
packSlipDs.relations(true);
packSlipDs.joinMode(JoinMode::ExistsJoin);
  • Martin Dráb Profile Picture
    Martin Dráb 231,872 Most Valuable Professional on at
    RE: Query Group By not working as expected

    I immediately see a bug in addLink(). The first argument should be the parent field, therefore it should be

    qbdsJoin.addLink(fieldNum(PurchTable, PurchId), fieldNum(VendPackingSlipJour, PurchId));

    instead of

    qbdsJoin.addLink(fieldNum(VendPackingSlipJour, PurchId), fieldNum(PurchTable, PurchId));

    By the way, can't you simply use qbdsJoin.relations(true)?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,097 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,872 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans