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:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

Filipe Relvas Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
EpicTriffid 364 on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Filipe Relvas Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No