Answered

Error on filtering Business Central's data in PowerApps

(2) Share

Report

Posted on by andriiko

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

Sort by

Thomas2001 4 on at

Like
a
(1)

Report
Copy link

Link copied!

I get the same error message when using the Business Central Cloud. Is there a solution now?

Was this reply helpful? Yes No
Verified answer

misterpower 34 on at

Like
a
(3)

Report
Copy link

Link copied!

Hi @andriiko and @Thomas2001,
I ran into this issue when creating a PowerApp that uses the Business Central connector. The BC and BC on-prem connector currently don't support delegation (https://docs.microsoft.com/nl-nl/connectors/dynamicssmbonprem/). Delegation is the concept of processing data on the data source to reduce the workload for your local system. Since the BC-connector does not support this, this means you cannot use filter functions on the live data feed.
Instead, you have to create a collection in the OnStart event of your PowerApp, load your data in the Collection and then filter the collection. More info on collections can be found here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearcollect.
Best of luck and let me know if it works out!

Was this reply helpful? Yes No
tpurkl 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @misterpower
Thank you for your solution.
But when I use a dataset and update a record, how can I transfer it back to business central, where the data should be updated?
Kind Regards
Tobias

Was this reply helpful? Yes No
misterpower 34 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @tpurkl, your question differs from the OP's original question. Please make a new topic and state your question/case over there as detailed as possible :-). Thanks.

Was this reply helpful? Yes No
MaryT0905 170 on at

Like
a
(0)

Report
Copy link

Link copied!

is this still accurate? I'm hoping not, but fearful as I am getting the same error message

Was this reply helpful? Yes No
Daveed1973 67 on at

Like
a
(0)

Report
Copy link

Link copied!
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.

ClearCollect(colBCItems, Filter(items,itemCategoryCode = drpCategories.Selected.code))
,

Was this reply helpful? Yes No
misterpower 34 on at

Like
a
(1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
misterpower 34 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Daveed1973 67 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
misterpower 34 on at

Like
a
(1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No