Finance | Project Operations, Human Resources, ...

Get spending data with Vendor and Main Accounts details

(2) Share

Report

Posted on by CB-03072329-0

Hi,
I’m a developer working with Dynamics 365 data and I don't have a really good understanding about Accounting, probably this is one my problems, but anyway. I'm trying to get spending data from companies, I've started with Invoices and I just need totals per headers and lines, a constraint that I have though that has been hard to solve for me is that I need the Invoice Accounts and the Main Accounts as details, so for instance, for a specific invoice I want the totals per Vendor but also the Main Account per each invoice line, I don't care about the taxes at the moment, so just the totals excluding taxes.

I've been trying to pull in fields about Invoices from the VendInvoiceJourBiEntities, VendInvoiceTransBiEntities, GeneralJournalEntryBiEntities, GeneralJournalAccountEntryBiEntities tables—because I can see that I can get the Main Accounts from GeneralJournalAccountEntryBiEntities, but it looks like they are aggregated already so I can't link them back to the invoice lines and connect them.

Could anyone guide me on how to implement this? Which key fields or relationships should I use to ensure accurate data mapping? At the moment I'm focusing in just doing this with the entities that I have access by default, but if is not possible to get this information maybe I should create a specific view for this?.

Thank you in advance for your help!

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Suggested answer

Abhilash Warrier 8,543 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
Hey,
Please find below the explanation in detail –

Step-by-Step Guide
1. Understand the Entities and Their Relationships

VendInvoiceJour: Contains header information for vendor invoices.

VendInvoiceTrans: Contains line details for vendor invoices.

GeneralJournalEntry: Contains general journal entries.

GeneralJournalAccountEntry: Contains account entries for general journals.

2. Identify Key Fields

VendInvoiceJour: InvoiceId, VendorAccount, TotalAmount

VendInvoiceTrans: InvoiceId, LineAmount, MainAccount

GeneralJournalEntry: JournalEntryId, InvoiceId

GeneralJournalAccountEntry: JournalEntryId, MainAccount, Amount

3. Linking the Tables

VendInvoiceJour to VendInvoiceTrans: Link using InvoiceId.

VendInvoiceTrans to GeneralJournalAccountEntry: Link using InvoiceId and JournalEntryId.

4. Creating a Custom View

If the default entities do not provide the necessary details, create a custom view that joins these tables. This view can aggregate the data as needed and provide a comprehensive dataset.

5. Field Mapping

Ensure that the fields you are mapping are of the same type and format. You can use the solution explorer in Dynamics 365 to define and edit these mappings.

Detailed Steps in Dynamics 365

Create a Data Entity:

Go to Data Management > Data Entities.

Create a new data entity that includes the necessary tables (VendInvoiceJour, VendInvoiceTrans, GeneralJournalAccountEntry).

Define Relationships:

Define the relationships between these tables using the InvoiceId and JournalEntryId fields.

Create a Custom View:

In Visual Studio, create a new view that joins these tables.

Use the example query provided above as a basis for your view.

Deploy the View:

Deploy the view to your Dynamics 365 environment.

Ensure that the view is accessible and that the data is correctly aggregated.

Test the View:

Test the view to ensure that it returns the correct totals per Vendor and Main Account.

Validate the data to ensure accuracy.

Use the View in Reports:

Use the custom view in your reports to get the totals per Vendor and Main Account for each invoice line.

You can connect with me if you require help with detailed analysis.

If this helped, please mark it as "Verified" for others facing the same issue.
Keep in mind that it is possible to mark more than one answer as verified.

Was this reply helpful? Yes No
CB-03072329-0 9 on at

Like (1)

Report
Copy link

Link copied!

Thanks Abhilash Warrier for the response, very clear and understandable, but because in the account that I'm using I don't have access to some entities that you are telling me to use, specifically these ones VendInvoiceJour, VendInvoiceTrans, GeneralJournalEntry or GeneralJournalAccountEntry, I just have access to the BiEntities version of them, and it looks like some aggregation is already done on those views, I'm wondering if in this case I need more access, I'm trying to implement an app for a third party integration so I was trying to just use what I have access as default, I'm using the Finance Trial Demo account as a baseline.

Do you reckon that in this case I'd need more access from the third party so I'm able to access these other entities and possibly to create a specific View for this as you said in your response? thanks in Advance.

Was this reply helpful? Yes No