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

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.

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 Smiley Sad 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

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.

Anonymous
Not applicable

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

thank you Smiley Happy

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 141 members 3,885 guests
Please welcome our newest community members: