Hi all,
Hope this is the appropriate forum.
I'm part of a team implementing a BI solution with D365 data.
For now, the way we've identified vendors for our financial entries was through following joins (which is also what I see when I investigate the relationship online):
FROM d365fo_generaljournalentry gje
LEFT JOIN d365fo_generaljournalaccountentry gjae
ON gje.recid = gjae.generaljournalentry
AND gje.dataareaid = gjae.dataareaid
LEFT JOIN d365fo_vendtrans vt
ON gje.subledgervoucher = vt.voucher
AND gje.subledgervoucherdataareaid = vt.dataareaid
However, when I do this, we have some specific entries that get duplicated, which is because the finance entry voucher and the vendor transaction voucher has multiple vendors present.
To illustrate the issue, here's a COUNT(1) on the query with and without the VendTrans join (both use same filters):
Here's a snippet of the voucher when we look in the VendTrans view in D365 (each line represents a payment to a vendor):
How do I go about finding the right vendor for the right row in GeneralJournalAccountEntry?
The ideal solution would be the ability to join VendTable to it, but it seems it goes through VendTrans?
