See How the Fetch and Join Modes in Microsoft Dynamics AX Queries Work

Do you know how to use the fetch mode and the join mode in Microsoft Dynamics AX? These are important query features in Dynamics AX that we draw on when we work in STAEDEAN Connectivity Studio. In this post, we’ll take a look at how they function. In our team, we get a lot of questions especially about the fetch mode in Dynamics AX. So, let’s open the hood!

A query in Microsoft Dynamics AX has some specific features that are different from a standard SQL query. With Connectivity Studio we rely completely on the queries in Microsoft Dynamics AX. Frequent questions we get from users include these:

• I’m missing data in my export. What could be the problem?
• I’m importing data, but I need to combine multiple records to create the sales order header. How can I do this?

There are two query properties which are unclear but very helpful to solve these questions from the business: The join mode and the fetch mode. When you combine records in the mapping in Connectivity Studio you should look at the fetch mode setting.

Let’s take a look at both modes.

Join mode

The good news is that the join mode in Dynamics AX works the same as in normal SQL queries. You can specify the option in the details per record. For this explanation we use a sales order document with a header and lines.

Here’s the data set which is used to explain the inner join or outer join setting:

• Sales order: S001
• Customer: c001

• Sales order: S002
• Customer: c001

• Sales order: S003
• Customer: c001

Inner join mode

The inner join mode can be used when you want to export only sales orders that have lines. The result is S001 and S003. S002 doesn’t have lines, so that is excluded.

Outer join mode

The outer join mode can be used when you want to export all the sales orders, with and without lines. This will include all the orders S001, S002 and S003.

Query fetch mode

This is one of the most unclear features of the query in Microsoft Dynamics AX. You have two options:

• One-to-many
• One-to-one

The names are misleading and they do not explain that you have a one-to-one or one-to-many relationship. What are they doing, then? When you use the query run to get the next record set you use the method: QueryRun.next(). This will return the changed records. Let’s look at examples:

• One table per level
• Multiple tables per level

The data set to explain the result is:

• Sales order: S001
• Customer: 2014

• Sales order: S002
• Customer: 2121

One table per level

When you have one record per level it always applies the query as one-to-one. That means:

…will give the result for each next operation:

1. Sales order S001, sales line 1
2. Sales order S001, sales line 2
3. Sales order S002, sales line 1

Multiple tables per level

When you have more records on one level the behavior will be different. A query with the fetch mode on one-to-one for the custtable and sales line:

…will give the result for each next operation:

1. Sales order S001, Customer 2014, sales line 1
2. Sales order S001, Customer 2014, sales line 2
3. Sales order S002, Customer 2121, sales line 1

When you have more records on one level with a one-to-many (1-x) relation it will behave differently as well.

You then get the result for each next operation:

1. Sales order S001
2. Sales order S001, Customer 2014
3. Sales order S001, sales line 1
4. Sales order S001, sales line 2
5. Sales order S002
6. Sales order S002, Customer 2121
7. Sales order S002, sales line 1

That means, in step one, you only have the sales order available.

You can also think about a combination with one-to-one and one-to-many. The sales line is one-to-many and the custtable has a one-to-one relation.

Be aware that the one-to-many relations are listed first and the one-to-one relations are at the end. I expected it the other way around. The result will be like this:

1. Sales order S001, Customer 2014
2. Sales order S001, Customer 2014, sales line 1
3. Sales order S001, Customer 2014, sales line 2
4. Sales order S002, Customer 2121
5. Sales order S002, Customer 2121, sales line 1

Using this sequence when you’re exporting or importing to or from Dynamics AX, you can define which setting you need. The one-to-one setting is used a lot during the import because with this setting you can easily combine records.

Hope this gives you more insight into handling the Dynamics AX queries technically. It depends on your situation what the best way is to specify the different properties. 

Feel free to comment if you have other useful scenarios in mind. Get in touch by with STAEDEAN.