cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omi18
Post Patron
Post Patron

Filter Gallery Based on Last Name Starts with

Hello, 

 

I have a gallery and I'm trying to use the Starts with function. 

 

omi18_0-1626789007992.png

 

My Current formulla: If(Button20.Pressed,Sort(Filter(Search('[dbo].[Profile]',ListSearchBox.Text,"ID","First","Last"),Department=ListDeptBox_1.Selected.Result,StartsWith(LastName,"A","B","C","D","E","F","G","H")),LastName,Ascending))

Any suggestions where I am going wrong with Start With function? 

 

Thanks,

Omi

 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

  • If we use the button then we need to use some collection to store the result
  • And assign the collection to Gallary

    Example 1 (Gallery Items property) - No button - Direct binding

    StalinPonnusamy_1-1626791491554.png

     

    Example 2: Using the button "OnSelect" event

    • Variable FilterApplied tells which data source we need to bind

 

 

UpdateContext({FilteredApplied: true});
    ClearCollect(
    ColWithFilter,
    SortByColumns(
    Filter(
        [@'Issue tracker'],
        StartsWith(
            Title,
            "L"
        ) || StartsWith(
            Title,
            "M"
        )
    ),
    "Title",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)
);Gallary Item propertyIf(FilteredApplied, ColFilteredData, MainDataSource)   

 

 

StalinPonnusamy_0-1626791094855.png

View solution in original post

5 REPLIES 5
Drrickryp
Super User II
Super User II

@omi18 

I would use each button to create a sorted, filtered collection for the gallery and then filter the resulting collection in the gallery with  the dropdown and textinput controls.

 

 

Button20 OnSelect:
ClearCollect(cProfile,Sort(
                            Filter(
                                    '[dbo].[Profile]', StartsWith(LastName, "A"||
                                                  StartsWith(LastName,"B")||
                                                  StartsWith(LastName,"C")||
						  StartsWith(LastName,"D"||
						  StartsWith(LastName,"E"||
						  StartsWith(LastName,"F"||
						  StartsWith(LastName,"G"||
						  StartsWith(LastName,"H")
                             )
                        ), LastName, Ascending
)
Gallery Items property:
   Search(
          cProfile,ListSearchBox.Text,"ID","First","Last"
    ) && Department  =ListDeptBox_1.Selected.Result
)

 

 

You would use the same collection, cProfile for example for each button but change the letter in the StartsWith()'s.

StalinPonnusamy
Community Champion
Community Champion

Hi @omi18 

Multiple Startswith should have OR || condition like below

 

SortByColumns(
    Filter(
        '[dbo].[Profile]',
        StartsWith(
            LastName,
            "A"
        ) || StartsWith(
            Title,
            "B"
        )
    ),
    "Title",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

 

Please do not forget to give kudos if you find the suggestion helpful or Accept it as a solution if works fine to help other users to find it useful.

 

Hi @StalinPonnusamy ,

 

I have below formulla: 

 

If(
Button20.Pressed,
Sort(
Filter(
Search(
'[dbo].[Profile]',
ListSearchBox.Text,
"ID",
"FirstName",
"LastName"
),
Department = ListDeptBox_1.Selected.Result,
StartsWith(
LastName,
"A"
) || StartsWith(
LastName,
"B"
) || StartsWith(
LastName,
"C"
)
),
LastName,
Ascending
),
If(
Button20_1.Pressed,
Sort(
Filter(
Search(
'[dbo].[Profile]',
ListSearchBox.Text,
"ID",
"FirstName",
"LastName"
),
Department = ListDeptBox_1.Selected.Result,
StartsWith(
LastName,
"D"
) || StartsWith(
LastName,
"E"
) || StartsWith(
LastName,
"F"
)
),
LastName,
Ascending
),
Sort(
Filter(
Search(
'[dbo].[Profile]',
ListSearchBox.Text,
"ID",
"FirstName",
"LastName"
),
Department = ListDeptBox_1.Selected.Result
),
LastName,
Ascending
)
)
)


But Gallery is still showing all the records after clicking any of the two burrons. 

Omi

StalinPonnusamy
Community Champion
Community Champion

  • If we use the button then we need to use some collection to store the result
  • And assign the collection to Gallary

    Example 1 (Gallery Items property) - No button - Direct binding

    StalinPonnusamy_1-1626791491554.png

     

    Example 2: Using the button "OnSelect" event

    • Variable FilterApplied tells which data source we need to bind

 

 

UpdateContext({FilteredApplied: true});
    ClearCollect(
    ColWithFilter,
    SortByColumns(
    Filter(
        [@'Issue tracker'],
        StartsWith(
            Title,
            "L"
        ) || StartsWith(
            Title,
            "M"
        )
    ),
    "Title",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)
);Gallary Item propertyIf(FilteredApplied, ColFilteredData, MainDataSource)   

 

 

StalinPonnusamy_0-1626791094855.png

View solution in original post

omi18
Post Patron
Post Patron

Hi @StalinPonnusamy ,

 

Thanks for the help on this. 

 

-Best,

Omi

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,110)