Sorting Gallery by Title within a Letter range of the alphabet

(0) Share

Report

Posted on by awalker

233

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

Categories:

Building Power Apps

I have the same question (0)

All responses (15)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@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.

Was this reply helpful? Yes No
awalker 233 on at

Like (0)

Report

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
)

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@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.

Was this reply helpful? Yes No
awalker 233 on at

Like (0)

Report

🙂

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.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@awalker

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

Was this reply helpful? Yes No
awalker 233 on at

Like (0)

Report

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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@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.

Was this reply helpful? Yes No
awalker 233 on at

Like (0)

Report

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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@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.

Was this reply helpful? Yes No
awalker 233 on at

Like (0)

Report

I replaced the OnChange code with what you just gave me.

Here is what is happening now -
When I leave the ddRangeStart on "A" and set ddRangeEnd to
"A" the output is "A",
"B" the output is "B;C"
"C" the output is "C;D;E",
"E" the output is "E;F;G;H",
etc

If I leave ddRangeEnd set to "E" but set ddRangeStart to
"B" the output is "E;F;G;H"
"C" the output is "E;F;G"
"D" the output is "E;F"
etc.
It looks like the coding takes the ddRangeEnd selection and adds as many letters following that selection as the number of letters that the ddRangeEnd selection is removed from the ddRangeStart selection.

Was this reply helpful? Yes No