Hello Everyone! I'm new to AX (I'm on AX2012 r2) but have been programming for a few years. I'm writing a data layer in C# to make it easier to query data from AX and map to some business objects. I have this working with other entities in AX (vendors, items, etc) but am having trouble with Purchase Orders and Purchase Lines.
My C# code is calling ExecuteDynamicQuery on an X++ query builder that extends AifQueryBuilder. The query builder loads a query I modelled in the AOT and just adds one or more ranges to the appropriate datasource based on what parameters are passed in via a class that derives from AifQueryBuilderArgs.
All of this works fine, and purchase order header information is returned. The problem is that no purchase line rows are returned in my dataset in C# (I get DBBnull values). What is further confusing me is that I created a job and tested my dynamic query class in AX and it IS returning purchase order header info and the purchase line info. Below is in initialize method from my query builder class.
[SysEntryPointAttribute]
public void initialize()
{
QueryBuildDataSource dataSource;
QueryBuildDataSource purchLineDataSource;
changeCompany('abc') {
// get predefined query from AOT
query = new Query("AbcPurchaseOrderQuery");
// get datasource to add Ranges to
dataSource = query.dataSourceName("PurchTable");
// filter by company
if (args.parmDataAreaId() != "")
dataSource.company(args.parmDataAreaId());
// filter by purchId
if (args.parmPurchId() != "")
SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchId)).value(args.parmPurchId());
// filter by purchName
if (args.parmPurchName() != "")
SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchName)).value(args.parmPurchName());
// filter by purchStatus
if (args.parmPurchStatus() != 0)
SysQuery::findOrCreateRange(dataSource, fieldNum(PurchTable, PurchStatus)).value(enum2str(args.parmPurchStatus()));
queryRun = new QueryRun(query);
}
}
Here is the test job I am using to verify the dynamic query.
static void AbcDynamicPurchaseOrderTest(Args _args)
{
QueryBuildDataSource dataSource;
AbcPurchaseOrderQueryBuilder abcPurchaseOrderQueryBuilder;
QueryRun queryRun;
PurchTable po;
PurchLine pl;
AbcPurchaseOrderQueryBuilderArgs args;
PurchId purchId;
changeCompany('abc') {
purchId = "XXXXXXXX";
args = new AbcPurchaseOrderQueryBuilderArgs();
args.parmPurchId(purchId);
args.parmDataAreaId("abc");
abcPurchaseOrderQueryBuilder = new AbcPurchaseOrderQueryBuilder();
abcPurchaseOrderQueryBuilder.setArgs(args);
// Looping through query results.
abcurchaseOrderQueryBuilder.initialize();
queryRun = abcPurchaseOrderQueryBuilder.getQueryRun();
while (queryRun.next())
{
// get tables
po = queryRun.get(tableNum("PurchTable"));
pl = queryRun.get(tableNum("PurchLine"));
// show results
info( strFmt ('%1 - %2',po.PurchId, po.PurchName));
info( strFmt ('%1 %2', pl.Name, pl.LineAmount));
}
}
}
So in summary, this dynamic query is working in AX but now when called from C#. I feel like I am missing a small detail somewhere and I just need more experienced eyes on this problem. Thank you!
