preloader-matrix

Alfasith

Dynamic query by linking 2 tables in AX

Hi,

Query by linking two tables


static void queryEmpDept(Args _args)

{
Query query;
QueryBuildDataSource queryBuildDataSource1,queryBuildDataSource2;
QueryBuildRange queryBuildRange;
QueryBuildLink queryBuildLink;
QueryRun queryRun;
DeptTrainingTable deptTrainingTable;
EmpTrainingTable empTrainingTable;
;
// Create a new query object
query = new Query();
// Add the first data source to the query
queryBuildDataSource1 = query.addDataSource(tablenum(DeptTrainingTable));
// Add the range to this first data source
queryBuildRange = queryBuildDataSource1.addRange(fieldnum(DeptTrainingTable, DeptId));
// Set the range
queryBuildRange.value(“ID002”);
// Add the second datasource to the first data source
queryBuildDataSource2 =queryBuildDataSource1.addDataSource(tablenum(EmpTrainingTable));
// Add the link from the child data source to the parent data
//source
queryBuildLink = queryBuildDataSource2.addLink(fieldnum(DeptTrainingTable,DeptId),fieldnum(EmpTrainingTable, DeptId));
// Create a new QueryRun object based on the query definition
queryRun = new QueryRun(query);
// Loop through all the records returned by the query
while (queryRun.next())
{
// Get the table data by using the get() method
deptTrainingTable = queryRun.get(tablenum(DeptTrainingTable));
empTrainingTable = queryRun.get(tablenum(EmpTrainingTable));
info (strfmt(“Dept Id %1, Dept Name %2, Employee Id %3, Employee Name %4 “, deptTrainingTable.DeptId,deptTrainingTable.DeptName,empTrainingTable.EmpTrainingId,empTrainingTable
.Name));
}
}

Regards,

Leave a Reply

Your email address will not be published.