≡ Menu




In statement functionality dynamics ax++

Sometimes we have to get rows from one table based on the list of value form other tables. We use in statement queries. For example I have to display only those customers which have at least one sales order in sales table. In this scenario we have to use in statement. But there is no in statement queries in dynamics Ax, We achieve this functionality with Exist join. Consider following code which result only Customer  form Custtable who have entries in SalesTable.

 

 

Query                   query;

QueryBuildDatasource    datasource,SalesDataSource,CustomerDataSource;

QueryRun   queryrun;

CustTable custtable;

SalesTable salesTable;

 

query = new Query();

CustomerDataSource  = query.addDataSource(tableNum(CustTable ));

SalesDataSource  = CustomerDataSource.addDataSource(tableNum(SalesTable ));

 

SalesDataSource.joinMode(JoinMode::ExistsJoin   );

 

SalesDataSource.relations(false);

 

SalesDataSource.addLink(fieldNum(CustTable, AccountNum),

fieldNum(SalesTable  , CustAccount ));

queryrun = new QueryRun(query);

 

while(queryRun.next())

{

custtable = queryRun.get(tablenum(CustTable ));

 

Info(custtable.AccountNum);

}

{ 0 comments… add one }

Leave a Comment