Filter gallery items based on a lookup field :(

[Anonymous]

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

[Meneghino]

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]

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

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]

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]

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]

Exactly

[Meneghino · Accepted solution]

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.

[Meneghino · Accepted solution]

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]

thank you 🙂