Error on filtering Business Central's data in PowerApps

[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!

[Thomas2001]

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

[misterpower · Accepted solution]

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-clearc.... 

Best of luck and let me know if it works out!

[tpurkl]

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

[misterpower]

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.

[MaryT0905]

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

[Daveed1973]

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))

, 

[misterpower]

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_virtualen....

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.

[misterpower]

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.

[Daveed1973]

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?