cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter gallery items based on a lookup field :(

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Meneghino
Level 10

Re: Filter gallery items based on a lookup filter :(

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

 

There are two separate issues:

  1. The filtering does not work
  2. 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.

 

CaptureCDSlookup.JPG

View solution in original post

Meneghino
Level 10

Re: Filter gallery items based on a lookup filter :(

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.

View solution in original post

19 REPLIES 19
Meneghino
Level 10

Re: Filter gallery items based on a lookup filter :(

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

Anonymous
Not applicable

Re: Filter gallery items based on a lookup filter :(

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

Meneghino
Level 10

Re: Filter gallery items based on a lookup filter :(

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

Anonymous
Not applicable

Re: Filter gallery items based on a lookup filter :(

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

 

Meneghino
Level 10

Re: Filter gallery items based on a lookup filter :(

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

 

Anonymous
Not applicable

Re: Filter gallery items based on a lookup filter :(

Exactly

Meneghino
Level 10

Re: Filter gallery items based on a lookup filter :(

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

 

There are two separate issues:

  1. The filtering does not work
  2. 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.

 

CaptureCDSlookup.JPG

View solution in original post

Meneghino
Level 10

Re: Filter gallery items based on a lookup filter :(

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.

View solution in original post

Anonymous
Not applicable

Re: Filter gallery items based on a lookup filter :(

thank you 🙂

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 237 members 5,195 guests
Please welcome our newest community members: