Microsoft Dynamics 365 | Integration, Dataverse...

{Power Automate} Limited number of Fields from Customers Table

(2) Share

Report

Posted on by SO-02121639-0

Hi all,

I have created a power automate flow to pull data from our BC environment & upload the resulting csv to our Blob storage.

However, there are very few fields in this.

Are the # of fields restricted over the api?

How do I get the full table dump?

There are 198 fields in the Customers Table when I check in BC, but only 31 retrieved over the API ?!?!? There must be a way for me to access this data easily for use in QlikSense.

Categories:

Dynamics 365 general

All responses (2)

Answers (0)

SO-02121639-0 8 on at

Like (0)

Report

{Power Automate} Limited number of Fields from Customers Table

@Amit Katariya007

Would you be able to help with explaining how I "Navigate to the Customers table (or any other table)."

I cannot see where I can access a table directly in BC, I can only see a limited view of the data, and the fields I can add are limited within this view.

Thanks
Suggested answer

Amit Katariya007 8,699 Super User 2024 Season 1 on at

Like (1)

Report

{Power Automate} Limited number of Fields from Customers Table

In your scenario, you're facing an issue where the full set of fields from the Business Central (BC) table is not being retrieved via the API, and you're only getting a subset of fields. Here’s how you can resolve this issue.

Understanding the API Limitations

The issue you're experiencing is likely due to API query limitations or field selection when pulling data from Business Central via the API. The API might only return a limited set of fields by default or may be limited in how it handles large tables.

Steps to Retrieve the Full Table Dump

1. Check the $select Parameter: When you query data from the BC API, it may use a $select parameter that restricts the fields returned. If you want all fields, make sure the $select parameter is not limiting the fields.

Example of retrieving all fields (if no $select is applied):

GET https://api.businesscentral.dynamics.com/v2.0/{tenant_id}/sandbox/ODataV4/Customers

If $select is used, it may restrict the number of fields returned. You can remove or modify this to get all fields.

Example:

GET https://api.businesscentral.dynamics.com/v2.0/{tenant_id}/sandbox/ODataV4/Customers?$select=CustomerID,CustomerName,Address,Phone

Remove the $select or use * (if supported) to retrieve all fields.

2. Use $expand for Related Data: Sometimes, related data (like complex fields or associated records) may not be included unless you explicitly ask for it using the $expand parameter.

Example:

GET https://api.businesscentral.dynamics.com/v2.0/{tenant_id}/sandbox/ODataV4/Customers?$expand=CustomerDetails

3. Check for Field-Level Permissions: Ensure that the API user has the necessary permissions to access all the fields. The API may only expose the fields that the authenticated user is authorized to access.

Go to Business Central and check the API permission sets for the user or service principal making the API call.

4. Retrieve Full Data using OData V4: If you're still unable to retrieve all fields, try using OData V4 for full data retrieval. Business Central supports OData V4 for querying larger datasets and more complex requests.

OData V4 also supports filtering, expanding, and selecting columns better than older versions.

Example endpoint for OData V4:

GET https://api.businesscentral.dynamics.com/v2.0/{tenant_id}/sandbox/ODataV4/Customers

6. Export Data via Business Central Web Interface:

If you cannot retrieve the full set of fields through the API, you could also consider using Business Central's data export capabilities:

Go to the Web Client of Business Central.

Navigate to the Customers table (or any other table).

Use the Export to Excel option to export all data (including all fields).

This will give you an Excel or CSV file that you can upload to your Blob Storage.