80
Business Central Online- Doing Consolidated reporting.- Sales orders and Invoices.
There appears to be relationships and tables not exposed to REST API- I need to relate Invoices to Sales Orders. Can anyone give guidance on what data to extract? I have Sales orders and related sales order line items . I have Invoices. But cannot related them due to no common field. Sales order and line items get removed when posted to an invoice.
Steven- thank you for your reply. I voted up and commented on the link you recommended. Tim
Rick-- thank your for taking the time to address my issues. -- I have restated this issue and provided additional screen shots- apologize for the large font.
I am trying to create power bi financial reports which need to relate invoices to sales order at the line item level as the workflow moves from order to ship to invoice. When the invoice is posted the connector entity does not include the sales order in the invoice or invoice line items (the rest api does). The sales order data is deleted and is not available after invoice is posted.
Blocked until I can unravel this-- help appreciated.
Hi Tim,
I recommend not using the current API's for Power BI reports.
Because they, usually, are: slow, contain a lot of fields you don't need and don't contain the fields you need. Imho it's much better to use the development environment to create queries for the tables you need in Power BI. Create a star or snowflake schema and filter and aggregate the queries using group-by's.
Also, normally in BC, when a sales order is invoiced (and completely posted) it's deleted, so you can't import it anymore in PBI... If you want to be able to import posted sales orders, you need to archive them in BC and then import from the archive, or manually create an archive. (This might require a code-modification in BC, depending on your localisation.)
Actually, you should have a good understanding on the BC data and process model, before you start to create reports, but it's also very true that there's very little information available in the docs.
I suggest voting for this idea: experience.dynamics.com/.../ ;-)
What exactly do you need to relate? Do you need the Order No on the Invoice, or do you need more? The Order No should be available in the Sales Invoice Header/Line table(s).
Also know that when you use the SIH/L table(s) you will not have the credit memos, returns,... That is why it might be better to use the Customer Ledger Entry table (or other ledger entry tables). It all depends on the requirements for the reports.
Thank you for your reply-- the rest api call appears to have the ability to relate them... the power bi get data -- list of data sources is different and does not match what is available thru a rest api call in the docs. The names are different and the fields are different. would appreciate a guide to pull this together. I do not find the ability to relate them and have profiled the data sources and processed step-by-step thru a specific workflow of a sales order to ship to invoice. I am planning to use power bi for a suite of financial reports but maybe this is not possible to use power bi in this case. Is it possibles to expose the rest api fields to power bi GET Data to help resolve the issues?
Hello,
I am not sure I understood the request entirely, but did you have a look here for all the standard API resource types that are available OOB?
You can see on the left side all the standard API resource types, their methods, properties, and relationships.
SalesInvoice for example:
docs.microsoft.com/.../dynamics_salesinvoice
Hope this helps.
Has anyone any further info on data definitions and structure as well as work flow? I only have found on old 2018 Nav one and it would help to have some further definitions to understand the fields and workflow. The data in salesDocument and salesDocumentLines need relationship to nativeInvoicingSalesInvoiceOverview and nativeInvoicingSalesInvoices. The sample Invoice print shows that it is being related..Are there other relevant REST calls or data sets ?
Moved to the Dynamics 365 Business Central forum.
#1
André Arnaud de Cal...
294,261
Super User 2025 Season 1
#2
Martin Dráb
233,013
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
