Filtering a gallery by numbers

(0) Share

Report

Posted on by EpicTriffid

364

Morning all,

I've got a gallery that can be filtered by starting letter by using a dropdown. See below:

The "All" dropdown is the filter. It contains "All" and every letter of the alphabet. The gallery is set to show those entries with the selected starting letter, or to show all entries if "All" is selected. The code looks like this:

 If(Dropdown1.Selected.Value = "ALL",
 Sort(Filter(
 'Lookup - Units',
 'Academic Year' = 'Admin AER - Dropdown - AY_1'.Selected.'Academic Year',
 Department = 'Admin AER - Dropdown - Dept_1'.Selected.Department),
 'Unit Title',
 Ascending),
 Sort(Filter(
 'Lookup - Units',
 'Academic Year' = 'Admin AER - Dropdown - AY_1'.Selected.'Academic Year',
 Department = 'Admin AER - Dropdown - Dept_1'.Selected.Department,
 StartsWith('Unit Title',Dropdown1.Selected.Value)),
 'Unit Title',
 Ascending))

However, a few entries begin with numbers, and I would like to be able to filter on those, using a "#" sign. I can easily add this into the dropdown, but can't make the the gallery filter appropriately.

In short, I need the gallery to detect if the first letter is a number and filter by that when "#" is selected from the dropdown. I would imagine it would look something like this, but I can't get it to work:

If(Dropdown1.Selected.Value = "ALL",
 Sort(Filter(
 'Lookup - AHUnits',
 'Academic Year' = 'Admin AER - Dropdown - AY_1'.Selected.'Academic Year',
 Department = 'Admin AER - Dropdown - Dept_1'.Selected.Department),
 'Unit Title',
 Ascending),
 If(Dropdown1.Selected.Value = "#",
 Sort(Filter(
 'Lookup - AHUnits',
 'Academic Year' = 'Admin AER - Dropdown - AY_1'.Selected.'Academic Year',
 Department = 'Admin AER - Dropdown - Dept_1'.Selected.Department,
 IsNumeric(Left('Unit Title',1))),
 'Unit Title',
 Ascending),
 Sort(Filter(
 'Lookup - AHUnits',
 'Academic Year' = 'Admin AER - Dropdown - AY_1'.Selected.'Academic Year',
 Department = 'Admin AER - Dropdown - Dept_1'.Selected.Department,
 StartsWith('Unit Title',Dropdown1.Selected.Value)),
 'Unit Title',
 Ascending))))

Cheers!

Categories:

General Questions

Connecting to Data

All responses (3)

Answers (1)

Filipe Relvas on at

Like (0)

Report

Re: Filtering a gallery by numbers

I'm to glad to read that @EpicTriffid and happy I could help!

However it is odd that changing the order had an impact on the outcome since the Filter() formulas results are all combined with an And. This means that for the result to be true all of them need to be true which is completely order independent.

It probably is a limitation on the data source and how the conditions are evaluated because the syntax is certainly correct.
Either way if you encounter any other related issues feel free to follow-up on this thread!

Best regards,
Filipe Relvas
EpicTriffid 364 on at

Like (0)

Report

Re: Filtering a gallery by numbers

Thank you for the reply @FilipeRelvas !

So it turns out that having two filters above it were somehow blocking this from working, as you said. I simply moved the numeric check to the top and it solved the problem!
Verified answer

Filipe Relvas on at

Like (0)

Report
Re: Filtering a gallery by numbers

Hi,

I tested a similar scenario against the account entity with the following formula (based on yours):
Filter(Accounts, IsNumeric(Left('Account Name',1)))

I was able to filter a Gallery and then Sort it based on that formula alone (i.e. Accounts that started with numbers were the only ones being retrieved) so I don't think that's the problem.
What is your output exactly? Are you getting any results or it doesn't retrieve any?

It's possible the remaining Formulas you have might be excluding some records that you were expecting to have.
Try to simplify the Formula and the Filter function as much as possible and start adding everything one by one until you get conflicting results.

Keep us posted! 🙂

Filipe Relvas