Hi there, we are attempting to pull invoices from our Dynamics 365 system into our BI reporting tool via an ODBC connector, however we don't fully understand the database structure behind Dynamics. In order for me to recreate debt at a given date I need to get all of the invoices and all payments/settlements associated with a given invoice. As highlighted in another recent thread (community.dynamics.com/.../1215057), when looking at a particular invoice we can click 'View Settlements' to see all of the payments for this invoice, however we can't see how this is tied together in the database.
Right now I can see the invoices are held in the VGRCustTrans3 table and our payments in the CustomerPaymentJournalLines table. I can match VGRCustTrans3.LastSettleVoucher = CustomerPaymentJournalLines.Voucher but I believe this will only give me the most recent payment not all of the payments. I also see a CustomerPaymentJournalLines.MarkedInvoice field but this does not seem to always be populated - sometimes it shows an invoice number and sometimes it just shows *.
It seems to me like this VGRCustTrans3 which has been used for reporting in the past might have been created internally, however I believe everyone involved with setting up our Dynamics system has moved on so I am reverse-engineering a bit here. How consistent is the database structure for Dynamics 365? Is there a different, standard table that I should be using to list invoices and is there a way to link from there to a set of payments/settlements? I did see another thread referring to a CustSettlement table but I don't see anything with that name in our database.
Appreciate any guidance.
