Dynamics Community Forum Thread Details

Hi,

I have a SQL query that returns the correct rows for me which I need converting into X++

select distinct rt.itemid,erpt.NAME,iigi.ITEMGROUPID

from DataArea dat

join ReqItemTable rt on
dat.[PARTITION]=rt.[PARTITION]
and dat.id=rt.DATAAREAID

join INVENTTABLE it on
rt.[partition]=it.[partition]
and rt.DATAAREAID=it.DATAAREAID
and rt.ITEMID=it.ITEMID

join EcoResProductTranslation erpt on
it.[partition]=erpt.[partition]
and it.PRODUCT=erpt.Product

join InventItemGroupItem iigi on
rt.[partition]=iigi.[partition]
and rt.DATAAREAID=iigi.ITEMDATAAREAID
and rt.ITEMID=iigi.ITEMID

where dat.ID='xxx' 
and rt.MININVENTONHAND > 0

When I try converting the same code to X++ (using a parent filter) there appears to be an issue with a specific join:

When the following line is un-commented in the ItemQuery module I get a critical error
qbds.addLink(fieldNum(ReqItemTable, ItemId), fieldNum(InventItemGroupItem, ItemId));

Critical STOP error
Invalid Field/Related field combination.

private Query ItemQuery()
{

    Query            query;
    int              counter;
    QueryBuildDataSource        qbds;

    QueryBuildRange             qbr;

    query = new query();

    //Populate the UI declared List
    varItemGroupUI                              = strSplit(varItemGroupId.value(),';'); 

    //Get an iterator for the list of the ItemGroup values (parent)
    liItemGroupId = new ListIterator(varItemGroupUI);

    qbds = query.addDataSource(tableNum(ReqItemTable));

    //1
    query.dataSourceTable(tableNum(ReqItemTable)).addSelectionField(fieldNum(ReqItemTable, ItemId));
    qbds.fields().dynamic(NoYes::No); //.dynamic(false);
    qbds.fields().clearFieldList();
    qbds.fields().addField(fieldnum(ReqItemTable, ItemId));
    qbds.addRange(fieldnum(ReqItemTable, MinInventOnhand)).value(queryValue(">0"));
    qbds.addGroupByField(fieldNum(ReqItemTable, ItemId));

    //2
    qbds = qbds.addDataSource(tableNum(InventTable));
    qbds.fields().dynamic(NoYes::No); //.dynamic(false);
    qbds.fields().clearFieldList();
    qbds.addLink(fieldNum(ReqItemTable, ItemId), fieldNum(InventTable, ItemId));
    qbds.joinMode(JoinMode::InnerJoin);

    //3
    qbds = qbds.addDataSource(tableNum(EcoResProductTranslation));
    qbds.fields().dynamic(NoYes::No); //.dynamic(false);
    qbds.fields().clearFieldList();
    qbds.addLink(fieldNum(InventTable, Product), fieldNum(EcoResProductTranslation, Product));
    qbds.joinMode(JoinMode::InnerJoin);
    qbds.fields().addField(fieldnum(EcoResProductTranslation, Name));
    qbds.addGroupByField(fieldNum(EcoResProductTranslation, Name));

    //4
    qbds = qbds.addDataSource(tableNum(InventItemGroupItem));
    qbds.fields().dynamic(NoYes::No);
    qbds.clearDynalinks();
    qbds.fields().clearFieldList();
    //qbds.addLink(fieldNum(ReqItemTable, ItemId), fieldNum(InventItemGroupItem, ItemId));
    qbds.joinMode(JoinMode::InnerJoin);

    liItemGroupId.begin();
    //Loop through all of the selected ItemGroup parameter values
    while(liItemGroupId.more())
    {
        qbds.addRange(fieldNum(InventItemGroupItem, ItemGroupId)).value(liItemGroupId.value());

        //Move onto next
        liItemGroupId.next();
    }

    qbds.fields().addField(fieldnum(InventItemGroupItem, ItemGroupId));
    qbds.addGroupByField(fieldNum(InventItemGroupItem, ItemGroupId));



    return query;

}

Without the qbds.addLink(fieldNum(ReqItemTable, ItemId), fieldNum(InventItemGroupItem, ItemId)); code, the results of the ItemId and Name columns are duplicated or contain incorrect ItemGroup values (depending on values selected from the varItemGroupId List parent).

Can anyone help please? Is there an alternative approach/method?

Thanks in advance,

Hi!

I think you should InventItemGroupTable join to ReqItemTable, then InventTable join to InventItemGroupTable and, finally, EcoResProductTranslation join to InventTable:

//1

query.dataSourceTable(tableNum(ReqItemTable)).addSelectionField(fieldNum(ReqItemTable, ItemId));

qbds.fields().dynamic(NoYes::No); //.dynamic(false);

qbds.fields().clearFieldList();

qbds.fields().addField(fieldnum(ReqItemTable, ItemId));

qbds.addRange(fieldnum(ReqItemTable, MinInventOnhand)).value(queryValue(">0"));

qbds.addGroupByField(fieldNum(ReqItemTable, ItemId));

//2

qbds = qbds.addDataSource(tableNum(InventItemGroupItem));

qbds.fields().dynamic(NoYes::No);

qbds.clearDynalinks();

qbds.fields().clearFieldList();

qbds.addLink(fieldNum(ReqItemTable, ItemId), fieldNum(InventItemGroupItem, ItemId));

qbds.joinMode(JoinMode::InnerJoin);

//2

qbds = qbds.addDataSource(tableNum(InventTable));

qbds.fields().dynamic(NoYes::No);

qbds.fields().clearFieldList();

qbds.addLink(fieldNum(InventItemGroupItem, ItemId), fieldNum(InventTable, ItemId));

qbds.joinMode(JoinMode::InnerJoin);

//4

qbds = qbds.addDataSource(tableNum(EcoResProductTranslation));

qbds.fields().dynamic(NoYes::No); //.dynamic(false);

qbds.fields().clearFieldList();

qbds.addLink(fieldNum(InventTable, Product), fieldNum(EcoResProductTranslation, Product));

qbds.joinMode(JoinMode::InnerJoin);

qbds.fields().addField(fieldnum(EcoResProductTranslation, Name));

qbds.addGroupByField(fieldNum(EcoResProductTranslation, Name));

Community site session details

X++ ItemId Join issue

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Meet the Microsoft Dynamics 365 Contact Center Champions

Congratulations to the March Top 10 Community Leaders

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

X++ ItemId Join issue

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Meet the Microsoft Dynamics 365 Contact Center Champions

Congratulations to the March Top 10 Community Leaders

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates