cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
awalker
Helper IV
Helper IV

Sorting Gallery by Title within a Letter range of the alphabet

Hi there

 

I'm trying to sort my gallery much like the produce search function at a grocery store that allows you to pick a letter and returns all produce that begins with that letter.

Except in my gallery, I'd like to be able to set a letter range. I have two dropdowns with the "Items" set to the alphabet. 

How do I write the filter function of the gallery to return all items who's title begins with a letter in that range?

 

Any help is appreciated

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@awalker 

<*smack to head*>  Sorry about that...flying too fast today!

Should be based on the start:

 UpdateContext({range: ForAll(Sequence(ddRangeEnd.Selected.Value - ddRangeStart.Selected.Value +1), Char(ddRangeStart.Selected.Value + Value-1))})

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

15 REPLIES 15
RandyHayes
Super User
Super User

@awalker 

Please consider changing your Logic to the following:

 

Two dropdowns - start and end range dropdowns.  ddRangeStart and ddRangeEnd

    Set the Items property of both to:  ForAll(Sequence(26, 0), {Char:Char(65+Value), Value:65+Value})

 

Set the OnChange action of both to:

  UpdateContext({range: ForAll(Sequence(ddRangeEnd.Selected.Value - ddRangeStart.Selected.Value +1), Char(Self.Selected.Value + Value-1))})

 

For the Items property of your Gallery, add to your existing formula this:

Filter(
    AddColumns(
        <yourExistingItemsPropertyFormulaHere>,
        "Char", Left(Title, 1)
    ),
    IsBlank(range) ||
    Char in range
)

 

This should give you what you are looking for.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
awalker
Helper IV
Helper IV

Thank you for the quick response and help.

 

"Please consider changing your Logic to the following:" Best line ever! I'm gonna use that next time I have an argument with my wife :-).

 

The coding seems to work somewhat, but it doesn't sort consistently.

The titles of the items are multi word, like "Power Supply - DC" or "Fuse FNM-1 35A Time Delay".

I would need the filtering to only look at the first letter of the first word in the title only.

 

Here is my code for the gallery:

 

Filter(
AddColumns(
colTruckStock,
"Char", Left(Title, 1)
),
IsBlank(range) ||
Char in range
)

 

 

 

RandyHayes
Super User
Super User

@awalker 

It's a great line for so many people - I'd be cautious with the wife though 😂

 

So, the filtering and sorting would be two different things.  The Filter should do you fine. But, what is the issue with the sorting?  

You mention you only need to filter on the first letter of the first word...that is what the formula I provided you will do.  

The character used is the first letter of title -  Left(Title, 1)  

That is then used on the Filter to determine if that letter is in the range table.

 

So, tell me more on the sorting issue then.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

🙂 

 

I apologize - I used the wrong term - I meant to say filter not sort. If ddRangeStart is set to "A" and ddRangeEnd is set to "C" for eg., I get only titles that begin with D. If I set ddRangeEnd to "D" and leave ddRangeStart at "A", I get titles that begin with "F", "I", and "H".

My gallery formula is probably not right. The collection is colTruckStock and the title column is what we are filtering for. The gallery also contains fields for date, quantity, etc. that have to populated. 

 

RandyHayes
Super User
Super User

@awalker 

That should not be the case.  Did you set up your dropdown range controls as I mentioned?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Yes, just double-checked again. Items property and OnChange property have your code pasted in. 

RandyHayes
Super User
Super User

@awalker 

So other than the need for a collection, the filter formula should be giving you what you want.

 

For testing, place a label on the screen and set the Text property to:

   Concat(range, Value & ";")

Make sure it is showing the correct range.

 

Also, verify the it is Title that you are displaying in your gallery.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

So this is what the label returns:

 

F;G;H;I;J;K

 

ddRangeStart is set to "A" and ddRangeEnd is set to "F"

I'm using dropdown boxes for these.

 

My gallery label with the title is referencing ThisItem.Title

RandyHayes
Super User
Super User

@awalker 

This is my bad.  I must have given you the wrong formula.  I was trying to "abbreviate" my response and consolidate the formula for you and I made a typo.

  UpdateContext({range: ForAll(Sequence(ddRangeEnd.Selected.Value - ddRangeStart.Selected.Value +1), Char(ddRangeEnd.Selected.Value + Value-1))})

 

Put that one in on your OnChange actions.  It should fix it.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,657)