PowerApps Gallery Items property with lookup columns in sharepoint

[MicaBassi]

Hi all,

I'm trying to achieve something in powerapps that seems to be complex.

I have a gallery, with an Items property that shows the values stored on my "Programs" sharepoint list, like this "Items: Programs".
I also have a combo box named "Customers" with the following Items property: "Items: Sort(CustomerTable, Title)".

Below I'm sharing an example of what my Data looks like for each sharepoint list:

Programs

Title	Customers*
Program1	cust1, cust2, cust3
Program2	cust2
Program3	cust3

*Customers column here, is a lookup column from my CustomerTable sharepoint list, that allows multiple selection.

CustomerTable

Title	Option
cust1	option2
cust2	option4
cust3	option2

I want to filter Programs and show the items where the Option of the customers in CustomerTable column match Option of the customer selected from the combo box. 

So If I select cust1 in the combobox, the Option would be option2. I need to filter Programs by those that have at least one customer with option2 as Option.

In this case, according to my example data, the gallery would display those that have cust1 and cust3 in the customers column (Because those customers have option2 as Option) meaning the gallery would display only Program1 and Program3. 

I tried storing the value of the customer selected from the combobox on the OnChange like this:

Set(
selectedOption,
CustomerCombobox.Selected.Option.Value
);

Then tried to get the programs with a "ForAll" but didn't work:

Set(filteredPrograms,

Filter(

Programs,

ForAll(

Customers,

LookUp(

CustomerTable,

Title = Result,

Option) = selectedOption

)

)

);

The idea was to then use filteredPrograms on the gallery items property.

It's even hard to put in words what I'm trying to do, so if further clarification is required on this request, let me know.

Hope there's a way to make this work!

Thanks,

Mica

[Amik]

@MicaBassi - as I understand it, you want to use a multi-choice ComboBox to Filter a Gallery control which contains a multi-choice SharePoint LookUp field (a many-to-many filter).

Try:

If(
    CountRows(CustomerCombobox.SelectedItems) = 0 || IsBlank(CustomerCombobox.SelectedItems),
    Programs,
    Sort(
        Filter(
            ForAll(
                Distinct(
                    Ungroup(//create a table per number of matches, then ungroup all tables
                        ForAll(
                            CustomerCombobox.SelectedItems As _selecteditem,
                            Filter(
                                Programs,
                                _selecteditem.Value in Customers.Value
                            )
                        ),
                        "Value"
                    ),
                    ThisRecord//get each distinct record from the ungrouped tables
                ),
                Value
            ),
            Len(ID) > 0  //exclude blank rows from appearing in the Gallery for any selected choice which has not yet been entered in the data source
        ),
        ID,
        SortOrder.Ascending
    )
)

Notes:

1. The above assumes the Items property of the "CustomerCombobox" contains:

Choices([@Programs].Customers)

2. ".Value" on "Customers.Value" will not be automatically picked up by intellisense. You will need to enter ".Value" manually (e.g. Customers.Value)

[PolyAlvarez]

Hi @Amik , let me give you more details:
* ProgramTable has a column "customers" that allows multiple selection, yes, you got it right. (it also has a 2nd column with "future customer" which has same properties than "customer" but different values)
* CustomerTable has a column "Option" which is a choice field single selection.
* The selection must be done from a combo box which values are unique from CustomerTable.
The aim is to show a gallery with the list of Programs which have customers with the same "option" than the customer picked from the combo box.
According to the data below, the gallery should show "Program1", "Program3" & "Program5", all have customers in any of the customer's field a customer with "option2".

ProgramTable

Title	Customers	FutureCustomers
Program1	cust1, cust2, cust3	cust4
Program2	cust2	cust5
Program3	cust3
Program4	cust2	cust5
Program5	cust4

CustomersTable

Title	Option
cust1	option2
cust2	option4
cust3	option2
cust4	option2
cust5	option1

I've attached a picture to see which is the expected outcome.
Thanks!

[Amik]

@PolyAlvarez - sorry I did not realise you had responded. I have read your response a few times and unfortunately, I cannot understand what you're trying to do. I would suggest raising a new post with the same question - hopefully another Community User will be able to understand it.