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

Gallery filtering. Do I use the function FIRST?

Hi all,

I am struggling to find an answer on how to filter my gallery.
I have a SP list that consist of columns: WorkInstructionNumberClient and Required.
I would like to filter my gallery whereas it only shows items if the column Required contains “Y” (this I figured out) and only the FIRST WorkInstructionNumber of each WorkInstructionNumber.

WorkInstructionNumberClientRequired
1628341AN
1628341BY
1628341CY
1626720AY
1626720BN
1626720CY


Is this possible?

 

Much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions

I've been testing and seems like I have found the correct formula. 


ForAll(Distinct('Live updates', WorkInstructionNumber),
LookUp(SortByColumns(Filter([@'Live updates'], LiveUpdatesRequired = "Y", StartsWith(WorkInstructionNumber,TextSearchBox114_1.Text)), "Title",If(SortDescending1,Ascending,Descending)),WorkInstructionNumber = Result))

@theapurva Thanks for pushing me in the right direction. 🙂

 

View solution in original post

8 REPLIES 8
theapurva
Super User
Super User

You can refer the solution given by @v-bofeng-msft  on this url:
Your formula would look like below:

ForAll(
    Distinct(
        YourDataSource,
        WorkstationNumber
    ),
    LookUp(
        YourDataSource,
        WorkstationNumber= Result, Required="Y"
    )
)

 

 

----------------------------------------------------------------------------------------------------------------------
Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Hello, 

 

Can you please elaborate more on this part?: 


LookUp(
YourDataSource,
WorkstationNumber= Result, Required="Y"
)
)

 

theapurva
Super User
Super User

If you could put your gallery 'Items' formula,
I can give you exact formula

----------------------------------------------------------------------------------------------------------------------
Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Also please note that as you are using Distinct in this formula, which is not delegable to SharePoint, this Distinct function will stop working correctly once your list has over 2000 items (or whatever your App's delegable limit is set to).

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Sure, 

 

So far I have this figured out, but as mentioned I need the first WorkInstructionNumber of each WorkInstructionNumber to display in the gallery

SortByColumns(Filter([@'Live updates'], Required = "Y", StartsWith(WorkInstructionNumber,TextSearchBox114_1.Text)), "Title",If(SortDescending1,Ascending,Descending))

theapurva
Super User
Super User

I believe this formula should work:

 

SortByColumns(ForAll(
    Distinct(
        Filter([@'Live updates'], //instead of [@'Live updates'], use 'Live updates' if errored
StartsWith(WorkInstructionNumber,TextSearchBox114_1.Text)),
        WorkstationNumber
    ),
    LookUp(
        YourDataSource,
        WorkstationNumber= Result, Required="Y"
    )
),
"Title",If(SortDescending1,Ascending,Descending))

 

----------------------------------------------------------------------------------------------------------------------
Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Hi, 

 

Getting this issue:

MashaOvchar_2-1655718899820.png

 

MashaOvchar_3-1655718920362.png
Strange because in my previous formula it recognizes the column "Title".

 

 

 

I've been testing and seems like I have found the correct formula. 


ForAll(Distinct('Live updates', WorkInstructionNumber),
LookUp(SortByColumns(Filter([@'Live updates'], LiveUpdatesRequired = "Y", StartsWith(WorkInstructionNumber,TextSearchBox114_1.Text)), "Title",If(SortDescending1,Ascending,Descending)),WorkInstructionNumber = Result))

@theapurva Thanks for pushing me in the right direction. 🙂

 

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

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

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,821)