Filter gallery items based on a lookup field :(

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I have two screens:

First screen contains a gallery which displays the records in entity "Product category"

Second Screen contains a gallery which displays the records in entity "Product"

I'm trying to filter the data in the second screen by displaying only products having the category selected on the first screen.

I got that error:

Part of this filter formula cannot be evaluated due to service limitations. The local evaluation may produce suboptimal or partial results. If possible, please simplify the formula. For more information, please see the Filter function documentation.

Any feedbacks?

ths

Categories:

Building Power Apps

I have the same question (0)

All responses (19)

Answers (2)

Sort by

Meneghino 6,949 on at

Like
a
(0)

Report
Copy link

Link copied!

Could you please supply the Items property of your second gallery and also tell me what your datasource type is (i.e. SQL server, SP list etc.)
Then I will be able to help

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Meneghino thank you for your answer

Both datasources type is CMD,

BrowseGalleryProduct.Items=Filter(Product;ProductCategory=BrowseGalleryCategory.Selected.IDCategory)
ProductCategory is the lookup field

and then i got this error:
Part of this filter formula cannot be evaluated due to service limitations. The local evaluation may produce suboptimal or partial results. If possible, please simplify the formula. For more information, please see the Filter function documentation.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like
a
(0)

Report
Copy link

Link copied!
I think I have seen this before, and there is a workaround.

Please place a text box (I think it doesn't matter in which screen and you can hide later if you like by making it not visible), call it MyProductCategoryID and set:
MyProductCategoryID.Text = BrowseGalleryCategory.Selected.IDCategory

Then set:
BrowseGalleryProduct.Items=Filter(Product, ProductCategory=Value(MyProductCategoryID.Text))
PS I think you data source is CDS (if not I am not sure what CMD is), also I guess you are in a different locale because you have a semicolon instead of a comma in your filter function. If you are in a locale with commas then maybe simply that is your problem.

Please let me know how you get on

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

it doesn't work 😞 i got the same error message,

And yes i meant CDS sorry .

All functions are written with semicolon even those automatically generated by PowerApps

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like
a
(0)

Report
Copy link

Link copied!
Ok, let me try to reproduce the issue and I will post again. I assume that:
Both ProductCategory and Product are entities in CDS (i.e. ProductCategory is not a Picklist)
You are using the production version of CDS (not a preview version of the old CDM)
You have properly established a relation between the two entities in CDS itself and you are not simply using an ID field as key
Can you please confirm or let me know if something is unclear?
Then I will try to reproduce the problem

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Exactly

Was this reply helpful? Yes No
Verified answer

Meneghino 6,949 on at

Like
a
(4)

Report
Copy link

Link copied!
OK, I have reproduced the problem and there is a "solution".

There are two separate issues:
The filtering does not work
There is a warning message regarding service limitations (i in a blue circle), this is not an error but a warning
Let's deal with them in turn, starting with 1.
In CDS (as with some other data sources such as SharePoint), lookup fields contain records pointing to the other table and not IDs. In my test the record only had one field, the PrimaryId field. Hence this is what worked for me:

Gallery2.Items = Filter( TestInvoices, CustomerID.PrimaryId = Gallery1.Selected.PrimaryId)
So in your case you need to put a . after ProductCategory in the filter function and see what the autocomplete suggests, probably something like the PrimaryId field.
This worked for me, as you can see in the screenshot below (by the way to see what is selected I set the TemplateFill property of the first gallery to If(ThisItem.IsSelected, LightCyan ,RGBA(0, 0, 0, 0)) )
I also got the warning but I ignored it and the filter still worked fine. But I will deal with the warning in the next post.
In the meanwhile please let me know if this works for you.

Was this reply helpful? Yes No
Verified answer

Meneghino 6,949 on at

Like
a
(1)

Report
Copy link

Link copied!

Now, regarding 2 I cannot make the warning go away. The text box trick worked in other situations but not this one.
In any case the filtering should work (please confirm in your case) and the warning is not shown to users of the app since it only appears in the design view.
I suspect that the warning should not appear and is just left over from when lookup fields did not work properly but now they seem to do so. Maybe a new release will clarify this.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

thank you 🙂

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like
a
(0)

Report
Copy link

Link copied!

hi @Anonymous
are you reposting to see if there are updates on the blue circle warning?
if so, does your app in any case work as it should?

Was this reply helpful? Yes No