cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mgr
Level: Powered On

Gallery filter with subquery (Dynamics embedded)

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?

4 REPLIES 4
Community Support Team
Community Support Team

Re: Gallery filter with subquery (Dynamics embedded)

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:

Filter function

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
mgr
Level: Powered On

Re: Gallery filter with subquery (Dynamics embedded)

Hi thanks, for the help. Sadly I still don't get it to work.

 

2 Problems:

 

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:

 

Filter(
'Product Inventory',
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?

mgr
Level: Powered On

Re: Gallery filter with subquery (Dynamics embedded)

I found out that the table in question can be queried fro Dynamics Connector instead of CDS Connector. Also I found another way to do my query. Thanks anyway.

Community Support Team
Community Support Team

Re: Gallery filter with subquery (Dynamics embedded)

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:Test.gif

 

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.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.