Small and medium business | Business Central, N...

Business Central Database

(7) Share

Report

Posted on by CS-04021000-0

Hi all

I'm hoping someone can help with below request.

At really high level i want to know if there's anyway I can link what's posted in the General Ledger to a purchase order? I know you can do it within the BC application but from what i see it doesn't look like it can be done linking various tables using a business central database using SQL .

Any help appreciated!

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (9)

Answers (0)

Ahmad Subhani 581 on at

Like (4)

Report
Hi,

I can maximum help Functionally.

General Ledger to Purchase Order Linking in Business Central G/L Entries (G/L Entry table) do not directly store references to the Purchase Order (Purchase Header) or Purchase Line tables. Instead, the link is indirect, and goes through intermediate documents like: Purchase Receipt → Posted Purchase Receipt Line Purchase Invoice → Posted Purchase Invoice Line Then into the Value Entries, Item Ledger Entries, or Vendor Ledger Entries And finally, into the G/L Entry via Posting via Posting Groups

Mark the Answer as Verified if this is Helpful.

Was this reply helpful? Yes No
Suggested answer

Jainam M. Kothari 15,631 Super User 2025 Season 2 on at

Like (3)

Report

Hello,

You can trace General Ledger (G/L) entries back to purchase orders within the application, doing so directly via SQL is challenging due to the lack of direct links between tables.

The connection typically flows from the Purchase Order to posted documents (like invoices or receipts), then to Vendor Ledger Entries, and finally to G/L Entries using indirect fields like Document No. and Source Type.

SQL-based linking requires complex joins across multiple tables, so using AL code or APIs is generally more reliable for this purpose.

Was this reply helpful? Yes No

Suggested answer

Sumit Singh

10,079 on at

Like (5)

Report

In Business Central, the General Ledger Entries (G/L Entry table, usually table ID 17) do not directly store a reference to the Purchase Order (Purchase Header, table ID 38). Instead, the link is indirect, typically through Posted Purchase Receipts, Posted Purchase Invoices, and Value Entries.
Here’s a simplified path you might follow:

G/L Entry → Document No. and Source Type/Source No.
Use Document No. to find a match in Vendor Ledger Entry or Purch. Inv. Header (Posted Purchase Invoice).
From Purch. Inv. Header, you can trace back to the Purchase Order via the Order No. field.
Alternatively, use Value Entry and Item Ledger Entry to trace inventory-related transactions.

Key Tables Involved

Table Name	Table ID	Purpose
G/L Entry	17	General ledger transactions
Vendor Ledger Entry	25	Vendor-related financial entries
Purch. Inv. Header	120	Posted purchase invoices
Purch. Rcpt. Header	120	Posted purchase receipts
Purchase Header	38	Purchase orders
Value Entry	5802	Costing and inventory value
Item Ledger Entry	32	Inventory movements

Why It’s Hard in SQL

Business Central uses flow fields and virtual tables, which are calculated at runtime and not directly queryable via SQL.
The linking logic is embedded in the application layer, not the database layer.
Some fields like Document No. are reused across different document types, making joins ambiguous without additional context.

Alternatives

Use Business Central APIs or OData: These expose relationships more cleanly and are safer than querying SQL directly.
Use Power BI with Business Central connector: It understands the data model and can help visualize links.
AL Extensions: If you're customizing Business Central, you can write AL code to expose the relationships you need.

Note: This response was developed in collaboration with Microsoft Copilot to ensure clarity and completeness. I hope it helps to some extent.
Mark the Answer as Verified if this is Helpful.

Was this reply helpful? Yes No

Suggested answer

Mansi Soni 8,907 Super User 2025 Season 2 on at

Like (7)

Report

Hello,

This link General Ledger entries to purchase orders in Business Central, but not directly via a single field or join in SQL. Instead, the link is typically made through the Value Entry and Item Ledger Entry (for inventory-related purchases) or via the Detailed Vendor Ledger Entry and Document No./Entry No. fields. You often trace it this way: Purchase Order > Posted Purchase Receipt/Invoice > Vendor Ledger Entry > G/L Entry, using fields like Document No., Source Type, and Source No. For accurate SQL-based reporting, you must join multiple tables across these relationships manually.

Hope this answer will help you!

Regards,
Mansi Soni

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,331 Super User 2025 Season 2 on at

Like (3)

Report

Are you using the On-Pre version? Want to get data directly from SQL?

GL Entries and Purchase Orders are not directly related, and Purchase Orders can be automatically or manually deleted after they are fully posted. It is not recommended to associate data in this way.
You can find the association between Posted Purchase Invoices and GL Entries. You can view their relationship through the following standard functions, which also works in SQL.

Hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No
Gerardo Rentería Ga... 25,213 Most Valuable Professional on at

Like (3)

Report

Hi, good day

I suggest you rely on a query object to obtain this information.

Query object - Business Central | Microsoft Learn

The accounting impact of a purchase is similar to the following:

Posting Groups #1 – Item Purchase Posting | Totovic Dynamics 365 Blog

Best Regards
Gerardo

Was this reply helpful? Yes No
CS-04021000-0 55 on at

Like (4)

Report

really appreciate the help with this one , seems it works depending on what GL codes i filter on plus depending on the document type, i'm using two different tables to get the purchase order ID and in some cases it returns all the vendor details i need, but the purchase order ID is null for some GL entries but for the large majority it works.

Many Thanks

CS

Was this reply helpful? Yes No
Suggested answer

Jeffrey Bulanadi 8,760 on at

Like (1)

Report
Hi,

Linking General Ledger entries directly to purchase orders via SQL is tricky because BC doesn’t store a native reference between the G/L Entry and Purchase Header tables. The connection is indirect and flows through posted documents and ledger entries.

Here’s how you can trace it:

Start with the G/L Entry table and use fields like Document No., Source Type, and Source No. to identify the origin.

Join to Vendor Ledger Entry using Document No. and Vendor No. — this gives you financial context.

From there, link to Purch. Inv. Header (Posted Purchase Invoice) using Document No. and Vendor No. again.

If needed, trace back to the original Purchase Header using the Order No. field in the invoice header.

For inventory-related transactions, you’ll need to go through Value Entry and Item Ledger Entry to reach the purchase receipt or order.

It’s not a single join — you’ll need to stitch together multiple tables depending on whether the transaction is item-based, service-based, or purely financial.

Helpful References
Purchase Order Processing and the Various General Ledger Entries – Crestwood
Custom Fields Flow from Purchase Order to G/L Entry – Dynamics Community
Data Item Links and Joins in AL – Microsoft Learn

If you find this helpful, feel free to mark this as the suggested or verified answer.

Cheers
Jeffrey

Was this reply helpful? Yes No
Suggested answer

Sohail Ahmed 11,136 Super User 2025 Season 2 on at

Like (1)

Report

At a high level, yes — you can link General Ledger entries to a Purchase Order, but it's not a direct one-table connection in the database. The link is indirect through tables like:

Value Entry

Item Ledger Entry

Detailed Vendor Ledger Entry

Document Entry No. and External Document No. fields

"Applies-to ID" or "Source No."

If you're working with SQL (on-premise only), you'd need to join G/L Entry → Vendor Ledger Entry → Purchase Header/Line, depending on what was posted (invoice, receipt, etc.).

It’s doable, but takes multiple joins and depends on the transaction type.

✅ Mark this answer as verified if it helps you.

Was this reply helpful? Yes No