I have a Dynamics CE form for Purchase Order Product (POP) that contains two lookup fields: POP.Warehouse and POP.Product.
I have added an embedded Canvas App on that POP form.
I now need to show (=filter) in a Gallery all Product Inventory (PI) records where the PI.Warehouse equals the POP.Warehouse and additionally the PI.Product equals one of the Products which are related with a Product Relationship (PR) record of the type "Substitute" (Optionset) with the POP.product. (The Product Relationship record has two separate Product Lookups).
select PI where PI.Warehouse=POP.Warehouse and PI.Product in (select PR.ProductA where PR.ProductB=POP.Product and PR.type=substitute)
So (how) could we implement this as a gallery filter?
HI @mgr ,
Do you want to achieve the Filter functionality within your embedded app?
Based on the issue that you mentioned, I have made a test on my side, please take a try with the following workaround:
Set the Items property of the Gallery to following formula:
Filter( 'Product Inventory', Warehouse = 'POP.Warehouse', Product in Filter('Product Relationship', ProductB = 'POP.Product' && type = substitute).ProductA )
Note: The 'POP.Warehouse' and 'POP.Product' represents the values you passed from your POP form to our embedded app.
Please take a try with above solution, then check if the issue is solved.
More details about Filter function, please check the following article:
Hi thanks, for the help. Sadly I still don't get it to work.
1. I recognized only now that the entity "Product Relationship" [productsubstitute] is not available in PowerApps with the CDS Connector, although I can find it in web.powerapps.com=>Data=>Entities, when I remove the list filters from there. So is there a possibility to get it into the data sources in PowerApp Studio?
2. To test the filter formula in the PowerApp (from previous post) I now created a custom entity "AlternativeProduct". But the formula for the filter doesn't work.
My formula looks like this:
Warehouse = First(ModelDrivenFormIntegration.Data).msdyn_AssociateToWarehouse,
Product in Filter(AlternativeProducts, ProductA = First(ModelDrivenFormIntegration.Data).msdyn_Product).ProductB
As result, the Gallery is not loaded on the form. So what could be wrong? I've already tried multiple combinations of adressing the columns without success. Is there maybe another way of how to cover this filter requirement?
Hi @mgr ,
For youe first question, you could consider take a try to add your "Product Relationship" [productsubstitute] Entity within your app manually.
More details about adding another data source within a canvas app, please check the following GIF screenshot:
For your second question, is there some error occurs with your Filter formula? Based on the formula that you mentioned, I could not find any syntax error with it.
On your side, please check if there are some corresponding reocrds existed within your "AlternativeProduct" Entity. You could consider take a try to add a Label control within your embedded app, set the Text property to following:
CountRows( Filter( 'Product Inventory', Warehouse = First(ModelDrivenFormIntegration.Data).msdyn_AssociateToWarehouse, Product in Filter(AlternativeProducts, ProductA = First(ModelDrivenFormIntegration.Data).msdyn_Product).ProductB ) )
If your Filter formula match corresponding records, it would display corresponding count number within the Label.