Microsoft Dynamics 365 for Finance and Operations is a relationship database. This means there are many tables that contain data that relates to each other. D365 joins combine rows from two or more tables together, based on related columns. Developers use D365 joins in x++ select statements to read and update the data they are interested in working with. Understanding how these work is a crucial skillset for anyone working with data.
Select and While Select Statements
D365 joins are used when writing a select statement or a while select statement. I covered how to write these two ways of querying data in a previous articles. In x++ these statements allow developers to write code that will retrieve data from the database, and store it into a table buffer variable. This is extremely useful.
Together with D365 joins, these statements become even more powerful. Data can be loaded into more than one table buffer variable. In addition, using joins allows the data to be filtered using columns that exist in multiple tables.
Similarly, joins can also be found on Data source forms. There is a ‘join type’ property that can be used to specify how that Data source is joined to another Data source. I will cover that functionality in a separate article. For now, I will focus on how joins are used in the x++ language.
T-SQL joins
Ultimately any code that is written in x++ will end up running a SQL statement against the database. However, it is important to note that the syntax of D365 joins is different than Transaction-SQL. Often called T-SQL. Therefore, for people who are already familiar with T-SQL, it is important to understand the differences.
In T-SQL, there exists the following joins:
- Inner Join – returns row when there is a match in both tables.
- Left Join – returns all rows from the left table, even if there are no matches in the right table.
- Right Join – returns all rows from the right table even if there are no matches in the left table
- Full Outer Join – returns all rows when there is a match in one of the tables
- Self Join – used to join a table to itself, as if the table were two tables.
Additionally, there are even some more, such as cross join.
Consequently, there are many other tutorials written on how to understand these joins. And what it means to have a ‘match’ in a table. See the W3schools articles and samples here. I highly recommend you read these. Specifically, you an click on the ‘Try it yourself‘ buttons and test out the SQL code right in the browser.
Since the concepts behind many of these joins are the same as in D365 joins, learning SQL will be very helpful. And, as stated in earlier articles, you can use SQL Server Management Studio to help when writing a query and viewing the resulting data set. This program is already installed on the Azure development machines. However, you can also download and install the program for free.
X++ D365 Joins And Their Differences
In the X++ language, developers can write code that looks very similar to T-SQL code to retrieve data. And load it into a table buffer variable for further use. This includes using joins. However, X++ joins really only support these four types of joins. See the summary below. Afterwards, I will go into further detail and show examples.
- Join – This is the same as an ‘inner join’ in T-SQL. However, in the X++ language the word ‘inner’ is not written. This returns a row when there is a match in both tables.
- Outer Join – This is the same as a ‘Left Join’ in T-SQL. This will return all rows from the left table, even if there is not a match in the right table. Note: There is not ‘right join’ in x++. However, a developer can just change which table is first, and which table is second to come up with the same result as a ‘right join’.
- Exists join – In T-SQL, there is an EXISTS keyword. However, it is not used as a ‘join’. In X++, this word is used to achieve a similar effect. But, it used differently. See example below. Using the exists join will return records from the first table, only if there exists a record in the second table. Note: It will NOT return any records from the second table. And importantly, if there are many records in the second table, the number of rows in the result set will not increase. The system will stop looking after it finds one match in the second table.
- NotExists join – This returns a row in the first table, if there does NOT exist a match in the second (or joined) table.
The best way to learn is to look at and play with some examples.
Understanding Joins
Do not get discouraged if you do not understand joins on your first try. Or if you have to refer regularly to what each join means. Like anything, you will get better with practice. I recommend you play around using SQL Server Management Studio until you get the query right. This tool makes it easy to see the results. Then bring the query back into X++. And make the adjustments in syntax that are needed.
In my opinion, there are a few key things to keep in mind when learning joins.
First, in X++ D365 joins, the system is essentially looking at each record in the first table, and deciding whether it should be included in the result, or thrown away. The Venn diagrams below will help you get the data you are looking for. Joins help with getting data on related tables. But they also help you filter the data based on whether rows on the second table exist. As well as using ‘where’ clauses on the columns of the joined tables.
Second, it is really important to understand that joins can result in more records than just those that exist in the first table. When using a ‘join’ or an ‘outer join’, if there are many matches in the second table you are joining to, the system will return a record for each match. And the values in the columns of the row in the first table will be duplicated.
I will provide an example in the Join section next.
Join
This is the same as an ‘inner join’ in T-SQL. However, in the X++ language the word ‘inner’ is not written. This returns a row when there is a match in both tables.
Consider this example. You wish to select a particular sales order, and view the customer’s name. You can do so, using the below select statement. First, create a D365 Project. Second, add a runnable class (job). I named mine ‘JoinTypesTutorial’. Thirdly, right click on the runnable class (job), and select ‘Set as Startup Object’.
Fourthly, copy the below code into the ‘main’ method.
SalesTable salesTable;
CustTable custTable;
select firstonly salesTable
where salesTable.SalesId == "012506"
join custTable
where custTable.AccountNum == salesTable.CustAccount;
if (salesTable.RecId != 0
&& custTable.RecId != 0)
{
Info(strFmt("Sales order %1 was found with customer name %2."
,salesTable.SalesId
,custTable.name()
)
);
}
else
{
Info("Sales order was not found.");
}The above code finds the first and only sales order with sales ID “012506” in the USRT company of D365. The system uses the ‘join’ keyword to pull in the record from the CustTable table whose AccountNum value matches the value in the CustAccount field on the SalesTable table.
The data found in the SalesTable record is stored into a table buffer variable named salesTable. And the data found in the custTable record is stored into a table buffer variable named custTable.
Next, the system checks to see if the RecId column on each of the tables is NOT 0. This indicates that a record was found. When a record is not found, the RecId value will be 0.
Finally, the system uses the Info method to display a message to the browser.
Note: Because a ‘join’ statement was used, either both records will be found and loaded into the table buffer variables, or none of them will.
Click the ‘Play’ button in Visual Studio to run the job, and see the results.
SQL Join
It is often useful to use SQL Server Management Studio to write a query and view the results. First, pen SQL Server Management Studio. Second, click ‘Ok’ on the dialog that opens up to connect to the server. Thirdly, click the ‘New Query’ button. Fourthly, change the drop down next to the ‘Execute’ button to read ‘AxDB’. (Or whatever the name of your database is). Fifthly, copy in the following SQL code.
select st.SALESID, ct.ACCOUNTNUM, * from SalesTable st
join CustTable ct
on ct.dataAreaId = st.DataAreaId
and ct.ACCOUNTNUM = st.CUSTACCOUNT
where st.DataAreaId = 'usrt'
and st.SALESID = '012506'Finally, click the ‘Execute’ button to run the SQL code and few the results.
To explain, there are a few details that are different when we write the same statement in SQL. These will really help you if you are already used to writing T-SQL code.
- Instead of using the keyword ‘firstonly’, we write ‘top 1’.
- Instead of using the name of the table buffer variable, we use the actual name of the table. Next, you can add an alias right after the name of the table. For example ‘st’ or ‘ct’. These can be whatever you wish.
- X++ select statements use two equal signs. For example, ‘==’. Whereas, T-SQL uses one equal sign.
- In addition, when you write an x++ select statement, the system will automatically append a ‘where DataAreaId = ‘<legal entity>’ to every table that is company specific. Whereas, in T-SQL, a DataAreaID join and where clause must be explicitly added when appropriate.
- In X++ select statement, you can have multiple ‘where’ clauses. Including one after every ‘join’ statement. On the contrary, in T-SQL, you can only have a ‘where’ clause after all the joins. And any ‘join’ statements need to use the ‘on’ keyword for the first relation.
- Finally, in x++, the characters ‘&&’ and ‘||’ to represent ‘and’ and ‘or’ when more than one relation or where clause is used. T-SQL uses the words ‘and’ and ‘or’.
Join That Results In More Records
It is very important to understand that D365 joins can bring in more records than just the number of records in the first table.
To demonstrate, consider that you want to view the information on a particular sales order header, along with all of the information in the sales lines. The X++ query would look like this:
SalesTable salesTable;
SalesLine salesLine;
while select salesTable
where salesTable.SalesId == "012506"
join salesLine
where salesLine.SalesId == salesTable.SalesId
{
Info(strFmt("Sales order %1 has salesLine with ItemId %2."
,salesTable.SalesId
,salesLine.ItemId)
);
}In this example, we need to use a ‘while select’ statement, because we know more than one record could be returned.
As can be seen, even though we are still just using a ‘join’ statement, the number of records returned is more than just the one in the SalesTable table. This is because there are multiple SalesLine table records that have the same SalesId value as the SalesTable record.
In this case, the same data will be loaded into the salesTable table buffer variable each time through the loop. However, the data loaded into the salesLine table buffer variable will change for each record found in the SalesLine table.
SQL Join With Multiple Records
See the previous query written using T-SQL:
select st.SALESID, sl.ITEMID, * from SalesTable st
join SalesLine sl
on sl.dataAreaId = st.DataAreaId
and sl.SALESID = st.SALESID
where st.DataAreaId = 'usrt'
and st.SALESID = '012506'
By using SQL Server Management Studio, we can quickly view how many records are returned by this query. Additionally, we can very easily look at all the values in each column without writing an info statement, or using the Visual Studio Debugger.
Conclusion
In the next article, I will explain how to use outer joins, exists joins, and NotExists joins. In this article you learned what joins are and how they are used in select and while select statements. Additionally, you learned that joins can both filter data, as well as increase the number of rows in a result set. Lastly, you learned how to write T-SQL in SQL Server Management Studio to quickly test and view the data returned by your statement.
Automatic number sequence for product receipt for PO ,create product receipt manually