Filtering Sub-Gallery by DISTINCT values

[DouglasStamper]

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: 

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. 

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

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

[WarrenBelz]

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

[DouglasStamper]

Hi @WarrenBelz, 

Many thanks for the reply!

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

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

[WarrenBelz]

@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

[DouglasStamper]

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

[WarrenBelz · Accepted solution]

@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

[DouglasStamper]

Thank you @WarrenBelz - Life Saver!