https://youtu.be/P5Aw8rCK2J8

Welcome back to our series teaching D365 Finance and Operations from zero! In our previous lesson, we gave Derek his first SQL homework: write queries to retrieve purchase order headers and lines from the database. Today, we’re reviewing his work, addressing common mistakes, and reinforcing critical SQL concepts. Remember, learning happens through mistakes, so let’s dive into what went wrong, why it matters, and how to fix it.

Understanding Headers vs. Lines in D365

In D365 Finance and Operations, transactional documents are split into two parts stored in separate database tables.

Header Records

The header contains information about the overall purchase order:

• Purchase order number (PURCHID)
• Vendor account (ORDERACCOUNT)
• Order date (PURCHASEDATE)
• Delivery information
• Overall status (PURCHSTATUS)
• Total amounts

In the interface, you can view header information by clicking the Header tab on the purchase order form.

The header data is stored in the PURCHTABLE table.

Line Records

The lines contain individual items being ordered:

• Line number (LINENUMBER)
• Item number (ITEMID)
• Item name (NAME)
• Quantity (PURCHQTY)
• Unit price (PURCHPRICE)
• Line amount

Lines appear in the grid at the bottom of the purchase order form.

The line data is stored in the PURCHLINE table.

The Homework Assignment

Write a SQL query to select the header and lines from a specific purchase order in the USMF company. 

Steps to complete this: 

1. Navigate to a purchase order in the interface 
2. Right-click to get form information 
3. Use Visual Studio to find the data sources 
4. Identify the table and field names 
5. Write the SQL query with proper joins and DataAreaId filtering 

The assignment required two queries:

1. Retrieve the header record for purchase order 00000041 in company USMF
2. Retrieve the lines for purchase order 00000041 in company USMF

Query 1: Retrieving the Purchase Order Header

Common Mistake: Using ORDERACCOUNT Instead of PURCHID
A common error is filtering by vendor account instead of purchase order number:

SELECT TOP 10 * 
FROM PURCHTABLE 
WHERE DATAAREAID = 'USMF' 
AND ORDERACCOUNT = 'US-104'

This query filters by ORDERACCOUNT (vendor account) instead of PURCHID (purchase order number).

The problem: ORDERACCOUNT is the vendor account number. Multiple purchase orders can have the same vendor. This query returns all purchase orders from vendor US-104, not the specific purchase order you want.

The solution: Use PURCHID to identify a specific purchase order

The Correct Solution

SELECT * 
FROM PURCHTABLE 
WHERE DATAAREAID = 'USMF' 
AND PURCHID = '00000041'

This query retrieves the header record for purchase order 00000041 in the USMF company.

SELECT * FROM PURCHTABLE Retrieves all columns from the purchase table (header table).

WHERE DATAAREAID = ‘USMF’ Filters to only records from the USMF company. This is mandatory for all D365 queries.

AND PURCHID = ‘00000041’ Filters to the specific purchase order number 00000041.

The result should be exactly 1 record because each purchase order has one header.

This query correctly filters to purchase order 00000041 in the USMF company.

To determine which field uniquely identifies a purchase order you can watch it in the youtube video or in the previous blogpost.

Alternative: Selecting Specific Columns

Instead of SELECT *, you can specify exactly which columns you need:

SELECT PURCHID, ORDERACCOUNT, PURCHSTATUS, CREATEDDATETIME 
FROM PURCHTABLE 
WHERE DATAAREAID = 'USMF' 
AND PURCHID = '00000041'

This query retrieves only the purchase order number, vendor account, status, and creation date for purchase order 00000041 in the USMF company.

Selecting specific columns improves query performance and makes your code more maintainable.

Query 2: Retrieving the Purchase Order Lines

The most common error is omitting the DATAAREAID filter:

SELECT LINENUMBER, ITEMID, NAME, PURCHPRICE 
FROM PURCHLINE 
WHERE PURCHID = '00000041' 
ORDER BY LINENUMBER DESC, NAME DESC

This query filters by purchase order number but does not filter by company.

The problem: Purchase order numbers can be reused across different companies. Without DATAAREAID filtering:

• USMF might have purchase order 00000041 with 4 lines
• USRT might have purchase order 00000041 with 3 lines
• USSI might have purchase order 00000041 with 2 lines

The query would return 9 lines from 3 different companies, giving you completely incorrect mixed data.

The solution: Always include DATAAREAID. The correct solution:

SELECT LINENUMBER, ITEMID, NAME, PURCHQTY, PURCHPRICE 
FROM PURCHLINE 
WHERE DATAAREAID = 'USMF' 
AND PURCHID = '00000041' 
ORDER BY LINENUMBER ASC

This query retrieves the line number, item ID, item name, quantity, and price for all lines on purchase order 00000041 in the USMF company, sorted by line number.

SELECT LINENUMBER, ITEMID, NAME, PURCHQTY, PURCHPRICE FROM PURCHLINE Retrieves specific columns from the purchase line table.

WHERE DATAAREAID = ‘USMF’ Filters to only records from the USMF company.

AND PURCHID = ‘00000041’ Filters to lines belonging to purchase order 00000041.

ORDER BY LINENUMBER ASC Sorts the results by line number in ascending order (1, 2, 3, 4…).

This query correctly retrieves only the lines for purchase order 00000041 in the USMF company.

Where can you watch this series?

You can follow the series on YouTube: https://www.youtube.com/playlist?list=PLkBkUhaE-CdRCUdddCuRBC-cAxGWGtn8Z

You can find even more videos on: https://www.youtube.com/@TRJHTECH

We’ll be releasing lessons regularly, complete with homework assignments and supplemental content. Have questions? Drop them in the comments on any video, your question might even become its own dedicated tutorial!