cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DouglasStamper
Frequent Visitor

Filtering Sub-Gallery by DISTINCT values

Hi all, 

 

I'm so stuck on this I hope someone can nudge me in the right direction. 

 

I have a gallery & a sub-gallery: 

DouglasStamper_0-1649760588181.png

 

When the user selects the value of the top gallery, in this case ""Alloa WWTW influent flow", the sub-gallery drops down & the user should be able to select the particular Signal ID row which subsequently opens the form component on the right hand side so they can make changes. 

 

DouglasStamper_1-1649761686585.png

 

However, I have loads of duplicates showing, where as I really need just one unique record, based on Signal ID to show. 

 

DouglasStamper_2-1649761800649.png

 

My current formula for the top gallery is: 

 

Search(
    Sort(
        RenameColumns(
            Distinct(
                'FLOWRETURNS.MANUAL_UPDATE',
                SW_Unique_Measurement_Point
            ),
            "Result",
            "SW_Unique_Measurement_Point"
        ),
        SW_Unique_Measurement_Point
    ),
    txtSearch_1.Text,
    "SW_Unique_Measurement_Point"
)

 

This works fine - no issues with this. 

 

My sub-gallery's Item property is set to: 

 

Filter('FLOWRETURNS.MANUAL_UPDATE', SW_Unique_Measurement_Point = ThisItem.SW_Unique_Measurement_Point)

 

I know this won't achieve my needs but I have tried multiple formula's, including: 

 

Distinct(Filter('FLOWRETURNS.MANUAL_UPDATE',SW_Unique_Measurement_Point=ThisItem.SW_Unique_Measurement_Point),Signal_ID)

 

And: 

 

ForAll(Distinct('FLOWRETURNS.MANUAL_UPDATE',SW_Unique_Measurement_Point),LookUp('FLOWRETURNS.MANUAL_UPDATE',Signal_ID=ThisRecord.Signal_ID))

 

However, nothing has worked. Maybe my understanding of filtering/distinct on a sub-gallery isn't perfect but I am at a loss on how to achieve this. 

 

Can anyone provide any clues?

 

I am using SQL as my data source. 

 

Many thanks, 

 

Doug

1 ACCEPTED SOLUTION

Accepted Solutions

@DouglasStamper ,

When GroupBy is used, the last reference needs to be the table field that all the fields matching the grouped column/s are stored. I probably could have used "Data", but has already done so in the Parent GroupBy, so used something different to avoid possible ambiguity. You can actuully call it whatever you want (HomerSimpson would work . . .)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

View solution in original post

6 REPLIES 6
WarrenBelz
Super User
Super User

Hi @DouglasStamper ,

The issue you have is that a Distinct filter only returns one column (Result) and therefore Signal_ID will not be in the data - tTry this on the Items of the Parent gallery

Search(
   Sort(
      GroupBy(
         'FLOWRETURNS.MANUAL_UPDATE',
         "SW_Unique_Measurement_Point",
         "Data"
      ),
      SW_Unique_Measurement_Point
   ),     
    txtSearch_1.Text,
    "SW_Unique_Measurement_Point"
)

and then the Items of the nested gallery

Distinct(
   ThisItem.Data,
   Signal_ID
).Result

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Hi @WarrenBelz

 

Many thanks for the reply!

 

I implemented your solution, however I'm not getting any data now at all: 

 

DouglasStamper_0-1649764608653.png

 

Just to confirm. - Parent Gallery Item property is: 

 

earch(
   Sort(
      GroupBy(
         'FLOWRETURNS.MANUAL_UPDATE',
         "SW_Unique_Measurement_Point",
         "Data"
      ),
      SW_Unique_Measurement_Point
   ),     
    txtSearch_1.Text,
    "SW_Unique_Measurement_Point"
)

 

And child gallery is: 

 

Distinct(
    ThisItem.Data,
    Signal_ID
).Result

 

I also see a 'semi' error message saying the formula uses scope that which isn't supported for evaluation. 

 

Many thanks!

 

Doug

@DouglasStamper ,

Firstly, the parent gallery code is valid providing your field values are (I just tested it here to check). In nested one unfortunately you seem to want to see a lot of other fields - both GroupBy and Distinct will only return one field - it you want others you need to do something like this

AddColumns(
   GroupBy(
      ThisItem.Data,
      "Signal_ID",
      "Data2"
   ),
    "Field1",
    First(Data2).Field1Name,
   "Field2",
    First(Data2).Field2Name',
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Hi @WarrenBelz ,

 

Again, thank you - I feel like I am getting closer!

 

One quick question - what do you mean by "Data2" in your solution? Apologies for my poor understanding. 

 

I've currently built it up like this:

 

AddColumns(
   GroupBy(
      ThisItem.Data,
      "Signal_ID",
      "SW_Unique_Measurement_Point"     
   ),
    "Signal Name",
    First('FLOWRETURNS.MANUAL_UPDATE').Signal_Name,
   "Signal ID",
    First('FLOWRETURNS.MANUAL_UPDATE').Signal_ID,
    "SW Point Description",
    First('FLOWRETURNS.MANUAL_UPDATE').SW_Unique_Measurement_Point,
    "Process Scientist",
    First('FLOWRETURNS.MANUAL_UPDATE').Process_Scientist
)

 

I know its not correct, placing one of my fields in place of "Data2" but I can see now how it is supposed to be structure, just a little unclear on the "Data2". 

 

If it makes any difference, my data source is a SQL view, does that make any difference?

 

Thanks, 

 

Doug

@DouglasStamper ,

When GroupBy is used, the last reference needs to be the table field that all the fields matching the grouped column/s are stored. I probably could have used "Data", but has already done so in the Parent GroupBy, so used something different to avoid possible ambiguity. You can actuully call it whatever you want (HomerSimpson would work . . .)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Thank you @WarrenBelz - Life Saver!

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (5,056)