Sorting Gallery by Title within a Letter range of the alphabet

[awalker]

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

[RandyHayes]

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

[awalker]

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]

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

[awalker]

🙂 

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]

@awalker 

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

[awalker]

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

[RandyHayes]

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

[awalker]

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]

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