Finance | Project Operations, Human Resources, ...

vendinvoicetrans and ledgerjournaltrans connection

(2) Share

Report

Posted on by CU27050456-1

Hi,

Does anyone know the connection between vendinvoicetrans and ledgerjournaltrans?

I am trying to connect these 2 tables but unable to find the common keys to connect.

I want one to many relationship from vendinvoicetrans to ledgerjournaltrans.

Is there direct connection, if not I would like to know the bridge table(s) and columns involved in connection of these 2 tables?

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Anton Venter 20,293 Super User 2025 Season 2 on at

Like (0)

Report

vendinvoicetrans and ledgerjournaltrans connection

Hi,

There is not direct relation for this (or bridge table as far as I know). Those are separate entities and could be used independently of each other. What is the business requirement for this? Why do expect there to be a direct relation? Vendor invoices can originate from different places in the system. It does not have to originate from a general journal (i.e. LedgerJournalTrans).

Edit:

"After transactions are posted, it's common for organizations to require visibility into subledger data, so that the accounting entries that are generated from those transactions can be analyzed. Today, organizations use fields such as the document number, description, or financial dimensions to track subledger data in the general ledger, because it's difficult to navigate the data model to the subledger data. The types of subledger data that are often tracked include sales order or purchase order numbers, vendor or customer names, payment references, invoice numbers, or reference numbers from external transactions that are imported into Microsoft Dynamics 365 Finance. In addition to being used for analytics, the subledger data is used for processes such as ledger settlement."

Source:

https://learn.microsoft.com/en-us/dynamics365/finance/general-ledger/financial-tag

Was this reply helpful? Yes No
CU27050456-1 9 on at

Like (0)

Report

vendinvoicetrans and ledgerjournaltrans connection

I want to track the journey from Purchase Order to Payment in a Power Bi Report and so far i have found the following relationship:
PurchLine[InventTransId] -> VendPackingSlipTrans[InventTransId]

VendPackingSlipTrans[InvoiceTransRefReqId] <- VendInvoiceTrans[RecId]

I have found a table called VendTrans:

VendTrans[RecId] -> LedgerJournalTrans[VendTransId]

So even if I am able to connect VendInvoiceTrans and VendTrans, I will be able to get link between VendInvoiceTrans and ledgerjournaltrans

Was this reply helpful? Yes No
Suggested answer

Navneeth Nagrajan 2,179 Super User 2025 Season 2 on at

Like (1)

Report

vendinvoicetrans and ledgerjournaltrans connection

Hi CU27050456-1,

There is no direct relationship between VendInvoiceTrans and LedgerJournalTrans.

The relationship can be based on the following:

1. LedgerJournalTrans.VendTransId == VendTrans.RecId

or

LedgerJournalTable.JournalNum == VendTrans.JournalNum and
LedgerJournalTrans.JournalNum == LedgerJournalTable.JournalNum

2. VendTrans.Voucher == VendInvoiceJour.LedgerVoucher

VendTrans.AccountNum == VendInvoiceJour.InvoiceAccount

VendTrans.TransDate == VendInvoiceJour.InvoiceDate

3. VendInvoiceJour.PurchId == VendInvoiceTrans.PurchId

VendInvoiceJour.Invoiceid == VendInvoiceTrans.InvoiceId

VendInvoiceJour.InvoiceDate == VendInvoiceTrans.InvoiceDate

VendInvoiceJour.numberSequenceGroup == VendInvoiceJour.numberSequenceGroup

VendInvoiceJour.InternalInvoiceId == vendInvoiceTrans.InternalInvoiceId

In addition to this, you can use the existing view in standard D365 to capture the required data in PowerBI (if you are not using the Fabirc link for D365 FO).

VendInvoiceJourJoinVendTrans (DataSource - VendInvoiceJour, VendTrans) -> VendInvoiceTransListPageView (Data Sources - VendInvoiceTrans). You can extend the VendInvoiceJourJoinVendTrans to join VendTrans> LedgerJournalTable -> LedgerJournalTrans.
.

Note: if you are using the Fabric link for D365 FO then the view mode (Point number 2) will not work because you will have to publish the individual tables to One Data lake.

Hope this helps. Happy to answer questions, if any.

Was this reply helpful? Yes No
CU27050456-1 9 on at

Like (0)

Report

vendinvoicetrans and ledgerjournaltrans connection

Hello @Navneeth Nagrajan,

Thank you for the detailed answer.

I have already achieved the relationships that you mentioned in the points from 1 to 3.

I have to track the Purchase Order through the following stages:
Purchase Order -> Goods Receipt Note -> Prepayment -> Invoice -> Payment

I have achieved the following relationships:

PurchTable[PurchId] -> PurchLine[PurchId]

VendPackingSlipJour[RecId] -> VendPackingSlipTrans[VendPackingSlipJour]

VendInvoiceJour -> VendInvoiceTrans (Relationship based on the columns that you mentioned in point 3)

PurchLine[InventTransId] -> VendPackingSlipTrans[InventTransId]

VendPackingSlipTrans[InvoiceTransRefRecId] <- VendInvoiceTrans[RecId]

PurchLine[InventTransId] -> VendInvoiceTrans[InventTransId]

These represent the Purchase Order -> Goods Receipt Note -> Invoice, and I can track and visualize PO in these 3 stages in Power Bi.

So I am not able to figure out what tables should I use to go from Invoice to Payment process tracking.

Also there is prepayment stage, where I am not able to troubleshoot that which tables are involved.

I also have VendTrans, LedgerJournalTable, LedgerJournalTrans tables, but not sure if I need those in my tracking process.

Are there other tables that I am supposed to use to track the payment and prepayment?

Was this reply helpful? Yes No