Combining GroupBy, Filter, and Collection in a gallery

[cmnflom]

I have a large dataset (8500 records) in a SharePoint list that I have partitioned into several collections in order to bring in all of the rows into a gallery.
In my On Start, I have the following:

ClearCollect(AllDataCollection,ds1, ds2, ds3, ds4);

 I have a search bar above my gallery called SearchInput_2 and in my gallery, I have the following in the Items section:

GroupBy(
    Filter(
    AllDataCollection,
    SearchInput_2.Text in 'Part Group' || SearchInput_2.Text in 'Part Name'
),
"Part Group",
"Part Name",
"Manufacturer",
"Part Type",
"PartsGroup")

In this gallery, users are to select a Manufacturer from a dropdown list and also select a Parts Type from a dropdown.

My issue is that instead of the dropdown showing all the potential manufacturers or part types in the Items section, it is only pulling in one value.
I have tried both of the following in the Items section of the Manufacturer and Parts Type dropdowns, but they only bring in a single value instead of all potential values for that column:

{Value: ThisItem.Manufacturer}

Distinct(ThisItem.'RM Group', 'Manufacturer')

Can someone please help point out what I am doing wrong? There should be a list of values within each of these dropdowns, but I am only seeing one.

ThisItem.RMGroup.

 If I start to type the above code, I do not see 'Manufacturer' appear as one of the options despite including it in the GroupBy.

Any help would greatly be appreciated!

[v-xiaochen-msft]

Hi @cmnflom ,

Could you tell me:

1. What's RM Group?  It does not appear in the Items of the gallery control. (I think the problem is here)
2. What is your ultimate goal? 
3. Do you want to display the list of the "Manufacturer" column after deduplication?
4. Do you want to display the list of the "Parts Type" column after deduplication?

So, maybe you could try the formula in the Items property of dropdown control.

Distinct(GroupBy(
    Filter(
    AllDataCollection,
    SearchInput_2.Text in 'Part Group' || SearchInput_2.Text in 'Part Name'
),
"Part Group",
"Part Name",
"Manufacturer",
"Part Type",
"PartsGroup"), 'Manufacturer')

Distinct(GroupBy(
    Filter(
    AllDataCollection,
    SearchInput_2.Text in 'Part Group' || SearchInput_2.Text in 'Part Name'
),
"Part Group",
"Part Name",
"Manufacturer",
"Part Type",
"PartsGroup"), 'Part Type')

If this still does not solve your problem, please provide more information.

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[cmnflom]

Thank you for the reply! It looks like I had a typo in my code - it should read ThisItem.PartsGroup and not ThisItem.RMGroup.

I've corrected this in my formula but I am still having the same issue. You are correct in that I want to see the list of 'Manufacturer' and the list of 'Parts Type' in each dropdown. When I apply the code above, I now see the list of all potential 'Manufacturer' and 'Part Type' options, but it is not correctly coinciding with the data that I have.

For example, the "Manufacturer" and "Part Type" options that display in the dropdown should vary depending on which "Part Group" or "Part Name" is displayed in that specific gallery item. As it is now, I see a list of 10 values in the dropdown (which is all possible options), when there should only be 2-3 specific 'Manufacturers' and

Part Types' for a gallery item, and they vary depending on the item. Do you know of any ways to address this issue?

Thank you again for your help!

[v-xiaochen-msft]

Hi @cmnflom ,

Now i understand what you mean.

However, I think there is only one value in the 'Manufacturers' column and the value in the 'Part Types' column of each Item in the gallery.

Because you are grouping based on the 'Manufacturers' column and the 'Part Types' column.

So, you could try to build a collection as shown below:

ClearCollect(ColTest,
GroupBy(
    Filter(
    AllDataCollection,
    SearchInput_2.Text in 'Part Group' || SearchInput_2.Text in 'Part Name'
),
"Part Group",
"Part Name",
"Manufacturer",
"Part Type",
"PartsGroup")
)

It may be different from the table structure you want.

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.