Get in the dropdown items the list of services some products have in common

[Antonella]

Hi, I have a collection with a list of products like this

Product ID

ProductA

ProductA

ProductB

ProductC

and in a table the services that can be provided for each product

Product ID	Service ID
ProductA	ServiceX
ProductA	ServiceY
ProductA	ServiceZ
ProductB	ServiceY
ProductB	ServiceZ
ProductC	ServiceY
ProductC	ServiceZ
ProductD	ServiceZ

I need to insert in the items of the dropdown only the Services that the products in the collection have in common, in this example I should show ServiceY and serviceZ, how can I do that?
Thank you in advance!

[v-bofeng-msft]

Hi @Antonella ,

I assume there is a table:

ClearCollect(
    servicesTable,
    {'Product ID':"ProductA",'Service ID':"ServiceX"},
    {'Product ID':"ProductA",'Service ID':"ServiceY"},
    {'Product ID':"ProductA",'Service ID':"ServiceZ"},
    {'Product ID':"ProductB",'Service ID':"ServiceY"},
    {'Product ID':"ProductB",'Service ID':"ServiceZ"},
    {'Product ID':"ProductC",'Service ID':"ServiceY"},
    {'Product ID':"ProductC",'Service ID':"ServiceZ"}
);

You could try this formula:

Concat(
Filter(
With(
    {GroupResult:GroupBy(servicesTable,"Product ID","Service ID Group")},
ForAll(
    First(GroupResult).'Service ID Group' As TableA,
    If(
        CountRows(GroupResult)=CountRows(Filter(servicesTable,'Service ID'=TableA[@'Service ID'])),
        {Value:TableA[@'Service ID']}
    )
 )
),!IsBlank(Value)),Value,";")

Best Regards,

Bof

[Antonella]

Hi @v-bofeng-msft ,

thank you for the prompt answer.
Actually I do not get from your formula, how can I filter the data based on the collection Products, that contains only A, B, C and doesn't contain the product D.
The services table is not in a collection and I get it using the sql server connector, but I do not think this is relevant for the purpose of creating the formula, am I right?
Thank you for the support

[Antonella]

Actually I got this delegation warning too

hope you can help.

Best regards

[Antonella]

To check all the products of the first collection and get the corresponding services in another filtered collection (where I will apply the previous formula), I am trying the following, but it is not working.

ForAll(Distinct(ProductsCollection, ProductID) As theProducts,
ForAll(Filter(Services,ProductID = theProducts.Value) As theServices,
Collect(colResult, {PID: theServices.ProductID, SCID: theServices.ServiceID})))

How can I adjust it to make it work?
Thank you for the support

[v-bofeng-msft]

Hi @Antonella ,

The services table is not in a collection and I get it using the sql server connector, but I do not think this is relevant for the purpose of creating the formula, am I right?

Yes, whatever a collection or a SQL Sever table is just a kind of data source that is not matter.

Actually I got this delegation warning too.

Cause the formula is not delegable, but there is no such a delegable way. In addition, If the number of rows is less than 500 , you could ignore the delegation warning . If the number of rows is less than 2000 and greater than 500, you could set the data row limit to 200

To check all the products of the first collection and get the corresponding services in another filtered collection (where I will apply the previous formula), I am trying the following, but it is not working.

According to the information you provided, the formula I provided earlier can already achieve the needs of 'in this example I should show ServiceY and serviceZ'. I'm not sure what you're going to achieve with the formula provided at the end. I prefer to suggest you provide a more detailed description and create a new thread for help.

Best Regards,

Bof

[Antonella]

Hi, unfortunately the number of rows is around 100.000.

[v-bofeng-msft]

Hi @Antonella ,

I'm afraid there is no such a delegable way to achieve your needs. You could try to do it by T-SQL and create a view in SQL sever, and then you could connect to the view in canvas app.

Best Regards,

Bof

[Antonella]

Actually in PowerApps (using a PowerBI interface), the users select the products for which they intend to provide a service. Then, in a dropdown list, I need to show only the possible services they can provide for such products and the users select one of them. For this reason I cannot create a predefined view in advance. I need to get the products selection from the users before showing them what are the possible services they can provide. Is there a way to achieve this?

[Antonella · Accepted solution]

Hi,

I could solve the delegation problem, by passing the products selected by the users to Power Automate. Using a Flow I could query the SQL server and get the related services into a collection. Finally I used your formula, to get only the services in common to all the products (the intersection). I hope this would help someone who is facing the same issue.
Kind regards