This is a really interesting topic I am going to share in this blog. Sometimes the most interesting learning come from areas where you least expect them. I was using the CountRows() function with the Account table of CDS and below is the error I got.
In this blog I will walk you through the troubleshooting steps if you get the error – “The data returned by the service is invalid“. My blog will focus on how you can troubleshoot if you are using CDS as datasource.
Check 1 : Make sure delegation is enabled if you are using CDS datasource.
Navigate to App Settings -> Advanced Settings and check for the property – “Enhanced Delegation for Common Data Service” Make sure the setting is enabled.
Checkpoint 2: Check if the user access have access on the data source.
Access can be one issue if you get this error. The user account may not have access to CDS entity. While this may be the most unlikely scenario, it’s still better to cross the user access on the CDS table. Make sure the user have appropriate security role with proper privileges to access the CDS table on which you are using the CountRows() function.
Especially if you are receiving this error for a specific table of Common Data service, chances are more likely that it’s an issue with the security role of the user.
Checkpoint 3: Use a property of the Data source inside CountRows() function instead of just the Datasource.
So you cross checked both the above points and realize it still does not work for you. No need to worry. This might just save your day. The trick is to use a property of the data source inside the CountRows() function instead of just using the Data source.
So instead of using
you can use the below formula
You can see I am using the AccountId field here of the data source and not any other field. This is because other fields may be blank and may give inaccurate count. Hence using the primary key.
And surprisingly the error vanishes.
Hope this helped!
You may also like the below posts
Debajit Dutta
Business Solution MVP