0

I want to create a complex User defined AIF Query service by using Visual studio and writing code for query service in C#. I am using two custom tables among which one is header and one is line table. I have added fields in the query by using both the tables and applies proper ranges as well and it works fine. Now I want to make a relation between these two tables So I can fetch data based on relation.

Please help me out with how to make a relation between multiple tables in AIF query service.

query.DataSources = new QueryDataSourceMetadata[2];    
query.Name = "AXCustomerInfo";    
QueryDataSourceMetadata custTableDS = new QueryDataSourceMetadata();    
custTableDS.Name = "CustTable";    
custTableDS.Table = "CustTable";    
custTableDS.Enabled = true;    
query.DataSources[0] = custTableDS;    
custTableDS.DynamicFieldList = false;    
custTableDS.Fields = new QueryDataFieldMetadata[1];  
QueryDataFieldMetadata accountNum;    
accountNum = new QueryDataFieldMetadata();    
accountNum.FieldName = "AccountNum";
accountNum.SelectionField = SelectionField.Database;    
custTableDS.Fields[0] = accountNum;
custTableDS.HasRelations = true;
custTableDS.JoinMode =JoinMode.InnerJoin;
QueryDataSourceMetadata dirPartyTableDS = new QueryDataSourceMetadata();
dirPartyTableDS.Name = "DirPartyTable";
dirPartyTableDS.Table = "DirPartyTable";    
dirPartyTableDS.Enabled = true;    
query.DataSources[1] = dirPartyTableDS;    
dirPartyTableDS.DynamicFieldList = false;    
dirPartyTableDS.Fields = new QueryDataFieldMetadata[1];        
QueryDataFieldMetadata name;    
name = new QueryDataFieldMetadata();    
name.FieldName = "Name";    
name.SelectionField = SelectionField.Database;    
dirPartyTableDS.Fields[0] = name;    
dirPartyTableDS.HasRelations = false;        
result = client.ExecuteQuery(query, ref paging);
foreach (DataRow row in result.Tables[0].Rows)    
{    
    Console.WriteLine(String.Format("{0}", row[0]));
    foreach (DataRow row1 in result.Tables[1].Rows)
    {    
        Console.WriteLine(String.Format("{0}", row1[0]));
    }
}
ekad
  • 14,436
  • 26
  • 44
  • 46
Gaurav maheshwari
  • 1

2 Answers2

0

See how to add multiple datasources to a query.

Either:

  • Set the Relations property to Yes on the child data source.

or:

  • Add a relation by doing the following:
    1. Set the Relations property to No on the child data source.
    2. Right-click the Relations node, and then click New Relation.
    3. Select a field from the parent data source in the Field property.
    4. Select a field from the child data source in the RelatedField property.
    5. Save all modifications.

You can then access the data of the query using OData Query Service.

Also see this question.

Community
  • 1
  • 1
Jan B. Kjeldsen
  • 17,817
  • 5
  • 32
  • 50
  • I am not using Axapta AOT to create the relation. All I have to do is to create a user defined Query in C# by using AIF query service. I have written full code in C# but I am unable to make a relation or join between two tables. – Gaurav maheshwari Sep 19 '16 at 16:33
0

Gaurav,

In your code, you are not creating a hierarchical structure of data sources. Hence relations will not work. You will get errors like : You can not add relation to root data source. I already shared my response on AX community. Here is the link: https://community.dynamics.com/ax/f/33/p/212065/573674#573674

This will work.

arabinda mandal
  • 11
  • 1