英文:
I can't read specific tables of Dynamics BC with Power BI
问题
我在将我们的ERP（Dynamics Business Central）的某些表格与Power BI同步时遇到了问题。我正在执行的步骤如下：
- 获取数据
- 搜索Dynamics 365 Business Central
- 搜索相关表格
这是当我尝试预览名为'salesCreditMemoLines'的表格中的信息时，Power BI不让我进行预览（与我可以无问题地查看其他表格不同）。
我感激您在这个问题上的帮助。
英文:
I am having troubles sincronising certain tables of our ERP (Dynamics Business Central) with Power BI. The steps that I am doing are explained below:
- Get Data
- Search Dynamics 365 Business central
- Search for the relevant tables
This is when Power BI doesn´t let me preview the information within the table called 'salesCreditMemoLines' (
), contrary to other tables that I can see without troubles (
)
I appreciate your help in this issue.
答案1
得分: 1
这是预期的错误。在Business Central API中，文档行集合需要在请求中包含相应的文档 ID，否则请求将失败。
以下是API页面中引发此错误的代码部分。
IdFilter := GetFilter(SystemId);
DocumentIdFilter := GetFilter("Document Id");
if (IdFilter = '') and (DocumentIdFilter = '') then
Error(IDOrDocumentIdShouldBeSpecifiedForLinesErr);
有两种方法可以发送文档 ID。我下面的示例是针对销售订单的查询，但对所有文档集合都适用。
第一种方法是使用 $expand 语法，请求文档头和文档行：
https://api.businesscentral.dynamics.com/v2.0/{{tenantid}}/{{environmentname}}/api/v2.0/companies({companyId})/salesOrders(orderId)$expand=salesOrderLines
另一种选择是通过添加 $filter 参数来查询文档行：
https://api.businesscentral.dynamics.com/v2.0/{{tenantid}}/{{environmentname}}/api/v2.0/companies(companyId)/salesOrderLines?$filter=documentId eq salesOrderId
筛选条件可以包括范围，因此可以请求多个文档的行集合。
https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/use-filter-expressions-in-odata-uris
然而，这两种方法都不适用于 Power BI 数据源选择。一个替代方法是在“Web Services（传统）”选项下使用实体销售文档行。是的，它显示为传统，但迄今为止，Microsoft 还没有宣布移除 OData Web 服务的计划。
英文:
This is expected error. Document lines collections in Business Central API require the respective document ID to be present in the request, otherwise it fails.
This is the piece of code from the API page that throws this error.
IdFilter := GetFilter(SystemId);
DocumentIdFilter := GetFilter("Document Id");
if (IdFilter = '') and (DocumentIdFilter = '') then
Error(IDOrDocumentIdShouldBeSpecifiedForLinesErr);
There are two ways to send the document ID. My examples below are querying sales orders, but the same applies to all document collections.
First is request the lines along with the document header using $expand syntax:
https://api.businesscentral.dynamics.com/v2.0/{{tenantid}}/{{environmentname}}/api/v2.0/companies({companyId})/salesOrders(orderId)$expand=salesOrderLines
Another option is to query the document lines adding the $filter parameter:
https://api.businesscentral.dynamics.com/v2.0/{{tenantid}}/{{environmentname}}/api/v2.0/companies(companyId)/salesOrderLines?$filter=documentId eq salesOrderId
Filters can include ranges, so this way it's possible to request a collection of lines from multiple documents.
https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/use-filter-expressions-in-odata-uris
Neither of these methods is going to work in Power BI data source selection, though. An alternative way is to use an entity salesDocumentLines under the Web Services (legacy) option. Yes, it shows as legacy, but so far Microsoft has not announced any plans to remove the OData web services.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论