You’re offline. This is a read only version of the page.
7
I've added a "Business Central (on-premises)" (uses API) connection to powerapps and I'm trying to represent something like a sales invoice card in small. I'm using the standard datasets salesInvoice, salesInvoiceLines, in accordance with the pages 5475 Sales Invoice Entity, 5476 Sales Invoice Line Entity.
When I try to filter lines per document the server response with error is shown (see screenshot below):
Error: Logical operator 'Equal' is not allowed. To allow it, set the 'AllowedLogicalOperators' property on EnableQueryAttribute or QueryValidationSettings.
If I use connector "Dynamics NAV" (uses the web services) I don't have such problem.
May be some CustomSettings's or objects's properties... no property from the error was found in CustomSettings file.
What can be the cause of this error? Any suggestions?
Thank you in advance!
Actually I think I have found the answer..
https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/powerplatform/powerplat-faq
@misterpower wrote:Hi Daveed. What's the reason you want to store more than 2000 records in your app? CDS does not retrieve more than 2000 records. When using CDS it becomes possible to delegate your BC data, meaning you will only retrieve the data you need.
You can filter your datasource (CDS) to only retrieve the records you need. That's a better approach since you bring in less records, therefore it does not degrade performance. Your app will not show more than 500 records, but the moment you apply a filter the records that match the filter condition will be shown :-).
1. Configure virtual entities so that the item table becomes available in the CDS: https://community.dynamics.com/business/b/andreysnavblog/posts/expose-business-central-api-as-cds-virtual-entity-without-code.
2. Filtering your DataSource: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup.
Let me know if the above works out.
Me again, I'm hoping you can clarify something. I have the virtual tables working a treat but when testing with another user who doesn't have a Business Central license they cannot use the PowerApp as the credentials keep getting rejected. However, when I give them a Business Central license they can use the app.
So my question is, to use CDS virtual tables from Business Central in a PowerApp do all users who intend to use the app need a business central license?
Happy to hear I could be of help. Cheers and have a great day! 😊
You sir are awesome, this is JUST the thing I was after. Thank you 😀
Hi Daveed. What's the reason you want to store more than 2000 records in your app? CDS does not retrieve more than 2000 records. When using CDS it becomes possible to delegate your BC data, meaning you will only retrieve the data you need.
You can filter your datasource (CDS) to only retrieve the records you need. That's a better approach since you bring in less records, therefore it does not degrade performance. Your app will not show more than 500 records, but the moment you apply a filter the records that match the filter condition will be shown :-).
1. Configure virtual entities so that the item table becomes available in the CDS: https://community.dynamics.com/business/b/andreysnavblog/posts/expose-business-central-api-as-cds-virtual-entity-without-code.
2. Filtering your DataSource: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup.
Let me know if the above works out.
Thanks for your reply. Sorry, I seem to be missing something though. I have installed the Virtual entities extension on my BC tenant but how do I then get a the Items table from BC into CDS so I can bring through the 2000+ records?
Hi Mary, this is still accurate. See my solution posted below :-). You can solve this issue using virtual entities and the CDS. I find it quite cumbersome that the BC-connector is a little bit incomplete in terms of data filtering, but at least there's a solution for it.
Hey Daveed, you cannot. 2000 rows is still the maximum limit for Power Apps. The BC-connector still does not support delegation. You have two approaches to solve your issue:
1. Use Power Automate Flows to load over 2000 records (you can find this approach on Google).
2. Install the virtual entities extension on your Business Central tenant, making the BC-entities of the API available in the Common Data Service. This is supported from BC17 and up. You can then use the CDS-connector to delegate your datasource.
My preferred approach is number two as you can leave all the logic in Power Apps. To make this work, you do the following:
1. Install the Virtual entities extension on your BC-tenant: https://appsource.microsoft.com/en-us/product/dynamics-365/microsoftdynsmb.businesscentral_virtualentity?tab=overview.
2. Connect to the CDS in your Power App, select the correct datasource.
3. Use delegation on your CDS datasource to filter the records directly on the datasource: https://docs.microsoft.com/nl-nl/powerapps/maker/canvas-apps/delegation-overview.
Hi, I have tried the above solution, and whilst it works and brings in the data, it is limited to 2000 rows and will not dea bring in any more than that to add to the collection. Is there a way I can filter the clearcollect so that it only brings in the values I need?
For example, I have two connectors to Business Central. One brings in Item Categories of which there are only 7. The other brings in the Items of which there should be over 2000. Every item will be assigned to a item category. So I would like the user to be able to select a category from a dropdown and have a clearconnect run and bring in only those items which are assigned to the category the user selected.
I thought something like this may work, but get the same error as described above.
Share
Report
All responses (
Answers (
ClearCollect(colBCItems, Filter(items,itemCategoryCode = drpCategories.Selected.code)),
#1
WarrenBelz
146,524
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,906
Most Valuable Professional
