You’re offline. This is a read only version of the page.
Announcements
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.
Share
Report[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!
ievgen Miroshnikov,
I think I missed the query.addCompanyRange(...) line earlier. That seems to do the trick!
I also figured out a way to solve this by passing a CallContext with a dynamic query as explained in the article below.
code.msdn.microsoft.com/Walkthrough-of-using-73c07391
Thanks for your help everyone!
Hi Mike,
I gave you solution that does company change in a different way and highlighted that it could be a problem but you ignored it.
Let me repeat it again. Please try it and let us know.
All responses (
Answers (
[SysEntryPointAttribute]
public void initialize()
{
QueryBuildDataSource qbds;
query = new Query(queryStr(PurchUpdate));
query.allowCrossCompany(true);
query.addCompanyRange(queryValue(args.parmDataAreaId()));
qbds = query.dataSourceTable(tableNum(PurchTable));
if (args.parmPurchId())
{
SysQuery::findOrCreateRange(qbds, fieldNum(PurchTable, PurchId)).value(queryValue(args.parmPurchId()));
}
queryRun = new QueryRun(query);
}
Vilmos,
I was thinking the same thing but I don't see any way to set the call context when calling QueryServiceClient.ExecuteDynamicQuery().
Since you are trying to call the AIF service from C#, did you set the call context? That is where you could specify a company account.
I discovered something this morning in AX. The default company for my account is "def". I was trying to query a PO in company "abc", which wasn't giving me PurchLine info. When I created a new test PO in "def" I can the PO header and the line items.
So now that I know the issue is related to companies, I'm not sure how to solve it. I tried wrapping my query inside of a changeQuery() like below but that doesn't seem to help. Anybody have suggestions?
changeCompany(args.parmDataAreaId()) {
// 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);
}
ievgen Miroshnikov,
Thanks for your efforts. I should re-emphasize that I'm not having trouble with the query in AX. If my query was incorrect then my test job wouldn't display POs and line items. It's only when I call the exact same dynamic query from C# via ExecuteDynamicQuery that I do not receive PurchLine data.
As I mentioned I have this code working end to end with other entities like Customer. PurchOrder though is a bit different because of the one to many relationship with PurchLines. I'm starting to think that has something to do with it.
Hi MikeUnderwood,
I tried to repro your issues in a simplified manner but it works for me.
I created args class:
[DataContractAttribute]
class MyPurchaseOrderQueryBuilderArgs extends AifQueryBuilderArgs
{
PurchId purchId;
DataAreaId dataAreaId;
}
[DataMemberAttribute]
public PurchId parmPurchId(PurchId _purchId = purchId)
{
purchId = _purchId;
return purchId;
}
[DataMemberAttribute]
public DataAreaId parmDataAreaId(DataAreaId _dataAreaId = dataAreaId)
{
dataAreaId = _dataAreaId;
return dataAreaId;
}
and query builder
class MyPurchorderQueryBuilder extends AifQueryBuilder
{
MyPurchaseOrderQueryBuilderArgs args;
}
public void setArgs(AifQueryBuilderArgs _args)
{
args = _args;
}
public MyPurchaseOrderQueryBuilderArgs getArgs()
{
return args;
}
[SysEntryPointAttribute]
public void initialize()
{
QueryBuildDataSource qbds;
query = new Query(queryStr(PurchUpdate));
query.allowCrossCompany(true);
query.addCompanyRange(queryValue(args.parmDataAreaId()));
qbds = query.dataSourceTable(tableNum(PurchTable));
if (args.parmPurchId())
{
SysQuery::findOrCreateRange(qbds, fieldNum(PurchTable, PurchId)).value(queryValue(args.parmPurchId()));
}
queryRun = new QueryRun(query);
}
As you can see here I'm using standard query PurchUpdate and I'm setting company in a different way (that could be an issue).
I did an incremental build in the AOT. The service I'm hitting is the QueryService, so I'm not sure how to "deploy" that since it's not listed with other services (I think because it's a built in service).
The C# code is below and is a minimal example that recreates the problem. It is functionally identical to the X++ test job I created. I create a client, create and populate the queryArgs, and then execute the dynamic query. The problem is that the dataset does not contain values for the PurchLine table (it does however have the PurchTable data).
var client = new QueryServiceClient();
// set paging to null
QueryService.Paging paging = null;
// set args
var queryArgs = new AbcPurchaseOrderQueryBuilderArgs();
queryArgs.parmDataAreaId = "abc";
queryArgs.parmPurchId = "XXXXXXX";
var dataSet = client.ExecuteDynamicQuery("AbcPurchaseOrderQueryBuilder", queryArgs, ref paging);
Hi MikeUnderwood,
You showed us code that works (x++ part) but did not showed c# that does not. Obviously you calling it in a different way and we cannot comment on it.
Did you build CIL and deployed service after that ?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,142
Super User 2024 Season 2
#2
Martin Dráb
227,979
Super User 2024 Season 2
#3
nmaenpaa
101,148