Abstract Query

Finance and Operations/AX Developer Forum
1

Hello all,

I am given two two unknown table IDs ( Common ), let’s call them fromTable and toTable. One is an actual populated common buffer with the table values.

I’d like to run a find operation on the toTable using relations on the two tables. I’m trying to use QueryBuildDataSource to use the natural relations for the join, and would like to pass in the populated common buffer to set the ‘where’ clause.

Is this possible?

Regards,
Silvano

2

All what you need for creating an instance of QueryBuildDataSource is table ID, so that’s not any problem at all. You can also use QueryBuildDataSource.relations() to load a relation between two data sources, just don’t forget that not all two tables have a relation to each other.

Nevertheless I don’t know what you mean by “to pass in the populated common buffer to set the ‘where’ clause”. Could you explain it a little bit?

3

Martin,

Thanks as always for your advice! I started down this path that you have suggested, and it appears to be working somewhat…although I wonder if there are any major flaws to this. I think one solid assumption is that this can only work for two data sources that have a relation already setup in the AOT as it would be impossible to know how to build a new relation on the fly without knowing the identity of the Datasources.

Below is my sample:

if(fromTable) //common buffer populated with data

{

enumerator = toList.getEnumerator(); //list of desired tableIds for the “toTable”

while (enumerator.moveNext())

{

query = new Query();

qbds = query.addDataSource(fromTable.TableId);

sdTable = new SysDictTable(fromTable.TableId); //SysDictTable

qbds.addRange(sdTable.primaryKeyField()).value(fromTable.(sdTable.primaryKeyField())); //passing in populated common values to create a where clause on the PK…not sure if this is correct

qbds = qbds.addDataSource(enumerator.current());

qbds.relations(true);

qbds.joinMode(JoinMode::InnerJoin);

queryRun = new QueryRun(query);

while(queryRun.next())

{

toTable = queryRun.get(enumerator.current());

ttsbegin;

while select refInitial

index RefIdx

where refInitial.RefCompanyId == _from.DataAreaId &&

refInitial.RefTableId == _from.TableId &&

refInitial.RefRecId == _from.RecId

{

refDestination.data(docuRef);

refDestination.RefCompanyId = _to.DataAreaId;

refDestination.RefTableId = _to.TableId;

refDestination.RefRecId = _to.RecId;

refDestination.insert();

}

ttscommit;

}

}

}

4

Thanks, I think I understand your goal now. Your code misses at least a conversion to query range value and support for composite primary keys. I would rewrite the relevant part in this way:

private Query buildQuery(Common _fromTable, TableId _toTableId)
{
    Query query = new Query();
    QueryBuildDataSource dsFrom = query.addDataSource(_fromTable.TableId);
    QueryBuildDataSource dsTo;
    
    SysQuery::queryAddUniqueRangeAndValue(query, _fromTable);
        
    dsTo = dsFrom.addDataSource(_toTableId);
    dsTo.relations(true);
    
    return query;
}

Using short methods with clear responsibility makes development, testing a maintenance significantly easier. For example, you can test the method above by the following piece of code:

private void testQuery()
{
    Query q;
    CustTable ct;
    
    select firstOnly ct;
    
    q = this.buildQuery(ct, tableNum(CustTrans));
    info(q.dataSourceNo(1).toString());
}
5

The process I’m hoping to work around Event handlers, allowing for similar processing of various actions ( table inserts, class actions such as Validating a quality order, etc. ).

Thank you for helping me resolve this in an efficient and elegant fashion.

Regards,
S