Exposing data is an important topic for every ERP implementation. Well, within the Microsoft ecosystem it is much easier to exchange the data between two applications (For example from business central to PowerApps). With Dynamics 365 Business Central on-premise you have the possibility to access the ERP data by directly querying the SQL Server database, but for business central online (SaaS) things are different. Database access is not possible, exposing your data as a service is the only option.
There are several APIs exposed by base in Business central. You can directly access these services without any customizations. But there are also a lot of tables that are not exposed by the base system, along with these the custom tables also won’t be exposed through these built in Webservices. You must create a custom API to expose this data.
On a Dynamics 365 Business Central SaaS environment, you have three choices to expose your Data:
- Expose a Page Object as Webservice.
- Expose a Query Object as Webservice.
- Publish a dedicated API page and use REST APIs
For using this data in PowerApps, the third option is the best one. But creating a simple API page and publishing it won’t let the PowerApps user push data into the Business central. For this you will have to do a bit more than just adding the fields to an API Page.
Let us see this by using an example of the “comment Line” table in Business Central.
1) Let us begin with the properties for the API Page.
2) Now for this step you will need a temporary field Buffer.
3) Now create a procedure that will help store the data in the actual table by using the temporary field buffer.
4) For the first field in API Page, you must add the field “SystemId”, this field will work similar to a unique Id when pushing data to Business central.
5) Now you can add all the other fields to the API page. In the OnValidate trigger of each field you must use the above procedure (RegisterFieldSet). For example:
6) For the Primary Keys or the fields that you cannot Change while editing a record, you can use the following code.
Add these labels in the “Var” section of the API page.
Once you follow all these steps you can run the code. To check if the code is working you can go to PowerApps and create a new Connection with business central using the Above API. To see all the APIs published by the same Publisher you can search for the Publisher name while creating the Connection in PowerApps.
You can use this method to expose Custom Tables and other Built-in tables and use them in PowerApps.