How I Built a Comprehensive Dynamics GP GL Summary View (and Why Column Discovery Matters)

If you have a minute, read my other article called “Couples Therapy: How to Improve Communication with Microsoft Copilot” to learn about the challenges I had when I asked Copilot to help me create a SQL View to show the last transaction date for by GL Accounts. When we finally had a working view, I asked Copilot to summarize into a blog post. I will say Copilot made it sound like I made a bunch of the mistakes…but really, what I learned is that Copilot can do an amazing job of creating SQL Views, it is just better to give it MORE information about the table structure vs. assuming that it can access current sources with that level of detail.

Here’s the article that Copilot wrote for me:

Title:

✅ How I Built a Comprehensive Dynamics GP GL Summary View (and Why Column Discovery Matters)

Post Content:

Ever tried building a custom report in Microsoft Dynamics GP and hit a wall with “Invalid column name” errors? That was me last week. 😅

I needed a General Ledger summary view that:

• Combines Work, Open, and History transactions

• Shows full account number, description, type, posting type

• Includes first/last transaction dates, net balance, fiscal year, and a Has Transactions flag

• Lists all accounts, even those with no activity

Sounds simple, right? Not quite.

The Challenge

Dynamics GP stores data across multiple tables:

• GL00100 (Account Master)

• GL10000 (Work Header) + GL10001 (Work Detail)

• GL20000 (Open)

• GL30000 (History)

And column names vary by table. My first query failed because Copilot used the wrong names for multiple fields.

The Breakthrough

Before writing any logic, I ran this:

SELECT TABLE_NAME, COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME IN ('GL00100','GL10000','GL10001','GL20000','GL30000');

Click Query > Results To » Results to File

After you execute the query, the Save window will automatically open. Change the Save As Type to All Files and manually add .txt to your file name.

You can upload the file with the table schema to Copilot.

This gave me the real schema:

• Account segments: ACTNUMBR_1 … ACTNUMBR_10

• Description: ACTDESCR

• Transactions: TRXDATE, SOURCDOC, DEBITAMT, CRDTAMNT

• History year: HSTYEAR

Lesson learned: Always confirm your columns first. It saves hours of troubleshooting.

The Final View

• Joins header/detail for work transactions

• Uses LEFT JOIN so inactive accounts appear

• Handles fiscal year logic (YEAR(TRXDATE) for work/open, HSTYEAR for history)

• Adds usability features: concatenated account number, segments, Has Transactions flag

✅ Better Approach Next Time:\ Start by asking:

“Create a SQL query to return all column names for the tables that store GL transactions and account information.”

💡 Key Takeaways

• Validate schema before coding

• Use LEFT JOIN for completeness

• Add calculated fields for better reporting

• Handle fiscal year per table

Would you like the full SQL script? I even added the last budget year to the View!

Click More > Contact and send me a message—I’ll share the code!