AX 2012 problem with Query distinct

Hi,

I want to create a lookup with the product master that has a variant.So i created a query with a innerjoin relation. It worked well until I added a group by to remove the duplicated product number.When i added the group by the lookup is empty.

The problem seems to be the combination of relation and group by. Because the lookup is filled with the relation without the group by,and it is also filled with the group by withouthe relation.

Can someone help me ?

Here is my code :

public void lookup()

{

SysTableLookup sysTableLookup;

QueryBuildDataSource qbEcoREsProduct;

Query query = new Query();

sysTableLookup =

SysTableLookup::newParameters(tableNum(EcoREsProductMaster), this);

qbEcoREsProduct = query.addDataSource(tableNum(EcoREsProductMaster));

qbEcoREsProduct = qbEcoREsProduct.addDataSource(tableNum(EcoResDistinctProductVariant));

qbEcoREsProduct.joinMode(JoinMode::InnerJoin);

qbEcoREsProduct.relations(false);

qbEcoREsProduct.addLink(fieldNum(EcoREsProductMaster, RecId),

fieldNum(EcoResDistinctProductVariant, ProductMaster));

//Group by section

qbEcoREsProduct.addGroupByField(fieldnum(EcoREsProductMaster, DisplayProductNumber));

qbEcoREsProduct.addSortField(fieldnum(EcoREsProductMaster, DisplayProductNumber));

qbEcoREsProduct.orderMode(OrderMode::GroupBy);

sysTableLookup.addLookupfield(fieldNum(EcoREsProductMaster, DisplayProductNumber));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

}

You’re victim of your own bad coding practices. You used a single variable for two datasources: qbEcoREsProduct first represented EcoREsProductMaster and than you overwrote it by EcoResDistinctProductVariant. Therefore when you’re trying to group by EcoREsProductMaster.DisplayProductNumber, you’re doing it on wrong datasource!

First thanks for your reply Martin.

Ok so I changed my code, but I have the same result, as soon as I add group by code, my look up is empty. I don’t know if it is related but the table EcoResDistinctProductVariant extends the table EcoResDistinctProduct wich extends the table EcoResProduct.

Here is my new code :

public void lookup()

{

SysTableLookup sysTableLookup;

QueryBuildDataSource qbEcoREsProduct;

QueryBuildDataSource qbEcoREsVariant;

Query query = new Query();

sysTableLookup =

SysTableLookup::newParameters(tableNum(EcoREsProductMaster), this);

qbEcoREsProduct = query.addDataSource(tableNum(EcoREsProductMaster));

qbEcoREsVariant = qbEcoREsProduct.addDataSource(tableNum(EcoResDistinctProductVariant));

qbEcoREsVariant.joinMode(JoinMode::InnerJoin);

qbEcoREsVariant.relations(false);

qbEcoREsVariant.addLink(fieldNum(EcoREsProductMaster, RecId),

fieldNum(EcoResDistinctProductVariant, ProductMaster));

//Group by code

qbEcoREsVariant.addGroupByField(fieldnum(EcoResDistinctProductVariant, ProductMaster));

qbEcoREsVariant.addSortField(fieldnum(EcoResDistinctProductVariant, ProductMaster));

qbEcoREsVariant.orderMode(OrderMode::GroupBy);

sysTableLookup.addLookupfield(fieldNum(EcoREsProductMaster, DisplayProductNumber));

sysTableLookup.addLookupfield(fieldNum(EcoResDistinctProductVariant, searchname));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

}

You should first look look at your query before trying to use in a lookup or anywhere.

Does it group data? It does!

So what’s the problem? You group by EcoResDistinctProductVariant.ProductMaster but try to read data from EcoResProductMaster.DisplayProductNumber and EcoResDistinctProductVariant.searchname, which are naturally empty.

You will get field value only if you group by the field or say the database how to aggregate values from several records to a single value. Use aggregation functions for that, such as count() or maxOf(). An example in the query framework would look like this: qbds.addSelectionField(fieldNum(T1, F1), SelectionField::Max).

I already tried tried the query different ways.

It returns something when the lines addGroupByField or addSortField but when either one of those line is uncommented, the resulting query is empty.

Here is the query that i think should work :

{

QueryBuildDataSource qbEcoREsProduct;

QueryBuildDataSource qbEcoREsVariant;

Query query = new Query();

QueryRun queryrun;

EcoREsProductMaster ecoprod;

qbEcoREsProduct = query.addDataSource(tableNum(EcoREsProductMaster));

qbEcoREsVariant = qbEcoREsProduct.addDataSource(tableNum(EcoResDistinctProductVariant));

qbEcoREsVariant.joinMode(JoinMode::InnerJoin);

qbEcoREsVariant.relations(false);

qbEcoREsVariant.addLink(fieldNum(EcoREsProductMaster, RecId),

fieldNum(EcoResDistinctProductVariant, ProductMaster));

//Group by code

qbEcoREsVariant.addGroupByField(fieldnum(EcoREsProductMaster, DisplayProductNumber));

qbEcoREsVariant.addSortField(fieldnum(EcoREsProductMaster, DisplayProductNumber));

qbEcoREsVariant.orderMode(OrderMode::GroupBy);

queryrun = new QueryRun(query);

while (QueryRun.next())

{

ecoprod = QueryRun.get(tableNum(EcoREsProductMaster));

info(strFmt(“ecoprod = %1” , ecoprod.DisplayProductNumber));

}

Thanks for your patience.

Unfortunately you still repeat the same mistakes - you try to add group by on EcoResProductMaster, but you use EcoResDistinctProductVariant datasource.

Try to call qbEcoResProduct.toString() - you’ll get this query:

SELECT * FROM EcoResProductMaster(EcoResProductMaster_1)
**GROUP BY EcoResDistinctProductVariant.DisplayProductNumber**
JOIN * FROM EcoResDistinctProductVariant(EcoResDistinctProductVariant_1)
ON EcoResProductMaster.RecId = EcoResDistinctProductVariant.ProductMaster

Your query returns records, but you try to read data from EcoResProductMaster datasource, which has no group-by field nor any aggregated field, therefore all fields are empty.

I don’t know what exactly you want to achieve, but you have now two options:

  • To get rid off EcoResProductMaster, group by EcoResDistinctProductVariant.DisplayProductNumber and read the value from there.

  • Group by EcoResProductMaster. If you call group by on qbEcoResProduct, you’ll get this query and you’ll be able to read data from EcoResProductMaster.DisplayProductNumber:

    SELECT * FROM EcoResProductMaster(EcoResProductMaster_1)
    **GROUP BY EcoResProductMaster.DisplayProductNumber**
    JOIN * FROM EcoResDistinctProductVariant(EcoResDistinctProductVariant_1)
    ON EcoResProductMaster.RecId = EcoResDistinctProductVariant.ProductMaster