Microsoft Dynamics AX (Archived)

2012 R2 - AIF WebService - QueryServiceClient - Best way to define company (dataAreaId) on static query

(0) Share

Report

Posted on by adam260

1,871

I was wondering if anyone had any suggestions on the best way to call a static query method using the

QueryServiceClient within C# to access the default webservice 'QueryService' for AX to filter by company.

For example I am just getting a list of customer id's in a basic query that is defined with the AOT. However I want to filter by operating company (dataareaId)

Is the best way to do this is just copy the query and have a "Company1Query" & "Company2Query" (the query itself is pretty simple, 2 fields on 2 tables)?

or change it to a dynamic query and add 'dataareaid' as a range then populate the range on the fly within C#?

Is there an easier way? I know AX has a lot of areas to define which company to filter by but I don't seem to find it on the

client.ExecuteStaticQuery method within C#. Is there a way thru metadata properties? Properties on the webservice itself within VS?

The 2 query's option is the quick and dirty way but I don't like creating the same object twice just with different properties, seems like there should be a better way.

If your suggestion is to populate on the fly do you have a suggestion on how to do this? It seems there are multiple methods such as finding the range on the fly (https://community.dynamics.com/ax/b/daxmusings/archive/2013/04/30/mixing-dynamic-and-static-queries-with-system-services-in-ax-2012)

or even creating a data contract like you would with a dp/report (https://community.dynamics.com/ax/b/shashisaxblog/archive/2013/01/15/walkthrough-calling-the-query-service-with-a-dynamic-query-ax-2012)

or is there an easier way?

How do you handle calling query objects and adding a company filter on the fly?

Thanks for the suggestions

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (2)

Verified answer

Community Member on at

Like (0)

Report
I haven't tried this yet, but this is what I would do.

You've got code like...

QueryMetadata query; QueryDataSourceMetadata datasource; query = new QueryMetadata(); query.AllowCrossCompany = false; query.DataSources = new QueryDataSourceMetadata[1]; datasource = new QueryDataSourceMetadata(); datasource.Company = "your company id"; /* other stuff that you would normally do */ query.DataSources[0] = datasource;

... and so on.

I think this would work because the query metadata corresponds to the properties of the queries in the AOT. If you look at an AOT query you will see that the datasources have a property for the company. Most queries are also set to AllowCrossCompany:No by default, but you might want to set this in C# code if that's not the case or maybe just to be safe. I don't think that these settings should conflict with each other, but you might run into issues if the user account is logging into a different company by default or does not have access to the company you are trying to query.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,805 Most Valuable Professional on at

Like (1)

Report

You don't have to create the whole query by yourself; you can get the metadata from the metadata service (GetQueryMetadataByName()).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ahhh.... I see. Thanks for that. I just went looking for it and found it. If anyone else follows up on this the method is a public member function of the AxMetadataServiceClient class.

Sweet Martin! That will save me some time.

Was this reply helpful? Yes No
adam260 1,871 on at

Like (0)

Report

Thanks for the help guys. If I use Frazier's method of creating the query from scratch then everything works like it should however this is a pretty simple query so I would like to get Martin's suggestion working as well for when I have to address more advanced queries so I can manage the structure/field list within AX when necessary. However it doesn't seem to return an results even without any company filters added. However the dataset that gets returned has the structure of the defined query just no rows on dataset so that tells me the metaClient.GetQueryMetaDataByName() is working correctly. It also appears that it converts correctly from the metadataservice.querymetadata -> querservice.querymetadata or else the structure wouldn't be correct within the returned dataset.

Below is my code... Does anything stick out to you two that might cause 0 rows to be returned?

AXMetadataService.AxMetadataServiceClient metaClient = new AXMetadataService.AxMetadataServiceClient();

AXQueryService.QueryServiceClient client = new AXQueryService.QueryServiceClient();

AXMetadataService.QueryMetadata metaQuery = metaClient.GetQueryMetadataByName(new string[] { "EscalusCustomers" })[0];

AXQueryService.QueryMetadata query = new AXQueryService.QueryMetadata();

AXQueryService.Paging paging = null;

using (MemoryStream memoryStream = new MemoryStream())

{

DataContractSerializer metaSerializer = new DataContractSerializer(typeof(AXMetadataService.QueryMetadata));

DataContractSerializer querySerializer = new DataContractSerializer(typeof(AXQueryService.QueryMetadata));

metaSerializer.WriteObject(memoryStream, metaQuery);

memoryStream.Seek(0, SeekOrigin.Begin);

query = (AXQueryService.QueryMetadata)querySerializer.ReadObject(memoryStream);

}

dataSet = client.ExecuteQuery(query, ref paging);

Here's a screen shot of the query within the AOS

Thanks for the help

Was this reply helpful? Yes No
Martin Dráb 237,805 Most Valuable Professional on at

Like (0)

Report

It works all right for me (with my own query).

Do you check correctly for the number of returned rows? Do you have data in the default company?

Was this reply helpful? Yes No
adam260 1,871 on at

Like (0)

Report

yeah it seems like it should work but doesn't for some odd reason. If I call it using the ExecuteStaticQuery it returns all of the rows. (same query name)

The default company does have data, and I've tried adding in the company filter to see if that helps via

query.DataSources[0].Company = "fwm";

query.DataSources[0].DataSources[0].Company = "fwm";

and

AXQueryService.QueryDataRangeMetadata range = new AXQueryService.QueryDataRangeMetadata()

{

Enabled = true,

FieldName = "dataAreaId",

Value = axCompanyName

};

query.DataSources[0].Ranges = new AXQueryService.QueryRangeMetadata[] { range };

query.DataSources[0].DataSources[0].Ranges = new AXQueryService.QueryRangeMetadata[] { range };

(along with just applying it to table1/datasource[0])

But still nothing... I have tried checking the same way via all of the other ways that returns data (dataset.Tables[0].Rows.Count)

also DataTable test = dataSet.Tables["CustTable"]; then test.Rows.Count but that really utilizes the same method. I've also just looked at the properties of dataset while debugging and no counts are anywhere.

I was going thru the results via

//go thru all of the customer results

for (int custCounter = 0; custCounter <= dataSet.Tables[0].Rows.Count - 1; ++custCounter)

{

//get the current customer row

DataRow custRow = dataSet.Tables["CustTable"].Rows[custCounter];

DataRow dirPartyTableRow = dataSet.Tables["DirPartyTable.DirPartyTable"].Rows[custCounter];

AXCustomer axCustomer = new AXCustomer()

{

AccountNum = custRow["AccountNum"].ToString(),

Name = dirPartyTableRow["Name"].ToString()

};

//add the new current customer to the main list

axList.Add(axCustomer);

}

I've also tried adding in the query.ReturnFlatDataSet property to see if that helped but still the same thing.

Was this reply helpful? Yes No
adam260 1,871 on at

Like (0)

Report

ok this was weird. I updated my 2 web service references in the project from aos1 to aos2 (its part of a 3 aos cluster) and now it works... Even though no changes to the query were ever made and aos1 worked if called via static. I wonder if Visual Studio had something cached...

Was this reply helpful? Yes No