Oct 14, 2011
Filed under: #daxmusings #bizapps

This is based on a question asked on the Microsoft Dynamics AX Community forums, you can find the original post here. I’ve been asked the question of adding datasources to a query object and linking them together a lot. The main issue exists around trying to use the .addLink() method on the datasource and how to avoid AX doing automatic linking.

First off, when I say automatic linking, this is not to be confused with DynaLink, which is what forms use to stay connected (when you select a new record in one form, the other form updates its query in relation to the form you selected in). What we’re talking about here is explicitly giving the relationship between two tables, using Query objects. One thing to remember is that the query object and related objects represent a data structure which is the runtime variant of the modeled queries in the AOT. That also means, rather than creating the query in code completely, you can instantiate the query object based on the modeled query in the AOT by passing it the name of a query in the AOT, like so:

Query query = new Query(queryStr(Cust));

</code>where “Cust” is the name of a query in the AOT. That also means sorting, ranges, etc behave the same on the query object as they do on the modeled query in the AOT. That also means you can use the Query node in the AOT as your guideline.

Now, when linking two datasources, you have the option of either using whatever default relationship AX comes up with (based on the modeled relations on the tables), or you can specify your own relation between the tables. To do this successfully, there is a flag “Relations” on the datasource (both in AOT and the object). Unfortunately, in AX 2009, the AOT property “Relations” has a helptext that reads: “Specify whether database relations used for data source linking are explicitly giving”. This seems to imply setting the property to “yes” means you will be explicitly giving the relation. Unfortunately, it’s the other way around. This label was luckily changed in AX 2012 and it now reads “To use the relations defined in the AOT Data Dictionary, set to Yes; otherwise No.”

So, below is a full example of linking two datasources with an explicit relation, from code. Note this code works in AX 2009 and AX 2012 both (in AX 2012 you wouldn’t necessarily need the semi-colon separating the declaration).

static void QueryExample(Args _args)
{
    Query                   query;
    QueryBuildDatasource    datasource;
    ;

    query = new Query();

    // Add SalesTable main datasource
    datasource = query.addDataSource(tableNum(SalesTable));

    // Add child datasource "SalesLine" to previously created DS
    datasource = datasource.addDataSource(tableNum(SalesLine));

    // Set the join mode
    datasource.joinMode(JoinMode::InnerJoin);

    // Indicate you don't want to use relations automatically
    datasource.relations(false);

    // Add link between parent field and child field
    datasource.addLink(fieldNum(SalesTable, SalesId),
        fieldNum(SalesLine, SalesId));

    info(query.xml());
}

</code>

Note how at the end I put the XML representation of the query in the infolog. I have found this to be helpful when troubleshooting issues with query objects, since you can see the actual definition and result of your code:

 

There is no comment section here, but I would love to hear your thoughts! Get in touch!