Joining AX form datasource with no direct relation through query x++

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi,

i’ve need to join different data sources in a form with X++ because the relations I want to use are not declared in the AOT.

The final goal is to show an overview form with some gathered informations.

I have something like this (with even a couple of ranges for each table). It works fine with a job that outputs a string and quite fine with a temporary table (but this solution requires too high loading times… )

while select MainTable join SalesTable where MainTable.SalesId == SalesTable.SalesId

join MainTableDescription where MainTable.Id == MainTableDescription.Id

{

…

}

My question is, how would I achieve this manipulating the Form datasources’s queries with X++?

I have this situation (As already said, I can’t declare relations in the datasource nodes):

OverviewForm

|–MainTable_ds

|–SalesTable_ds

|–MainTableDescription_ds

Do i have to work on executeQuery() methods of these dataSources? I can’t figure out which is the right order to proceed…

Thank in you in advance

regards

2

executeQuery() merely runs the query defined before. What you would need is manipulating the query when initializing your form. Get the child data source, call queryBuildDataSource() method to get QueryBuildDataSource object and then call addLink() to define join conditions.

But why don’t you simply add the relation to AOT? These manipulations make sense only if the query can’t be build at design time.

3

Could you give me little bit more details please? which object inside Forms has got queryBuildDatasource method?

I’m experiencing some issues trying to populate an empty Query object and then adding it to my Form as a datasource.

This approach is not working fine…

Query query = new Query();

MainTableQbds = query.addDataSource(tablenum(MainTable));

SalesTableQbds = MainTableQbds.addDatasource(tableNum(SalesTable));
SalesTableQbds.joinMode(JoinMode::InnerJoin);
SalesTableQbds.relations(false);
SalesTableQbds.addLink(fieldNum(Maintable, SalesId), fieldNum(SalesTable, SalesId));

Quite hard to do so, because i have need to join SalesTable with CustTable on InvoiceAccount. Better to avoid such strong edits to standard tables, right? So i would like to declare that association just on runtime on my form.

Thank you very much indeed for helping

regards

4

The method is on the form data source. If your data source is called Table1, for example, you can access it through an automatically created variable called Table1_ds.

You don’t have to do any “strong edits” to define the relation in AOT. Create a new query in AOT, set the relation and use the query in your form. Assuming that your version of AX allows that; unfortunately you forgot to attach a tag without your version…

5

Sorry for the missing tag, i work on Ax 2009.

I feel little confident with AOT query building, because sometimes the design is quite misleading in my opinion.

By the way, I achieve the wanted result by joining all form datasource tables in their AOT properties (i set up each “table_ds” object)

I did a debug run to find out which relations were already meeting my needs.

Then I changed the wanted relations in the init() method of each dataSource

Thank you again for helping, sometimes things look more easy once written down on screen…

If someone’s interested I can explain the solution in detail.

Regards