Answered

Sorting gallery using a drop down using custom items

(0) Share

Report

Posted on by powelma

Good day, I am attempting to sort, filter data from Table based on if specific fields in the table are blank using a drop down. My drop-down items are:

["All","Completed","Requiring Prep","Requiring Scan","Requiring Index", "Requiring QC"]

I am able to get only one of the items in the dropdown to work. Current Gallery Items is :

Sort(Filter(Table1_12,Dropdown1.Selected.Value = "Requiring Prep", IsBlank('Prep Completion Time')),'Date & time received',SortOrder.Descending)

Prep Completion Time is the name of one of Columns I want to filter by.

Whenever I attempt to add additional Drop Down Items, it will not display any data for the dropdown Select value.

An example would be:

Sort(Filter(Table1_12,Dropdown1.Selected.Value = "Requiring Prep", IsBlank('Prep Completion Time') Or Dropdown1.Selected.Value = "Requiring Scan", IsBlank('Scan Completion Time')),'Date & time received',SortOrder.Descending)

For the code above, It will filter data where Prep Completion time is Blank but not for Scan Completion Time

I tried replacing the Or in the code above with a , but that does not display any data

Any help would be greatly appreciated.

Categories:

Building Power Apps

All responses (5)

Answers (2)

Sort by

madlad 2,637 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi!

I think here, your best bet is to use an If function. Try something like this:

Sort( If( Dropdown1.Selected.Value = "Requiring Prep", Filter(Table1_12, IsBlank('Prep Completion Time')), Dropdown1.Selected.Value = "Requiring Scan", Filter(Table1_12, IsBlank('Scan Completion Time')) ), 'Date & time received', SortOrder.Descending )

This will use the dropdowns value to choose which filter statement you use. It may need some slight modifications, but something along these lines should work. 🙂

Hope this helps!
powelma 47 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the quick response. I'll give it a try.
Verified answer

powelma 47 on at

Like
a
(0)

Report
Copy link

Link copied!

It worked like a charm. Here is what I created for the fields associated with that drop down.

Sort(
If(
Dropdown1_1.Selected.Value = "Prep Required",
Filter(Table1_12, IsBlank('Prepper Name')),
Dropdown1_1.Selected.Value = "Scan Required",
Filter(Table1_12, IsBlank('Scanner Name')),
Dropdown1_1.Selected.Value = "Index Required",
Filter(Table1_12, IsBlank('Indexer''s Name')),
Dropdown1_1.Selected.Value = "QA Required",
Filter(Table1_12, IsBlank('QCer Name')),
Dropdown1_1.Selected.Value = "All" And Dropdown1.Selected.Value = "All" ,
Filter(Table1_12, true)
),
'Date & time received',
SortOrder.Descending
)
Thank you.

I originally had one drop down and a search field. I am now trying to figure out how to incorporate all three into the Items. My original Item code when I had the one search field and one dropdown was:

Sort(Filter(
Table1_12,StartsWith(Notes,SearchTextInput1.Text) Or StartsWith('Date & time received',SearchTextInput1.Text) Or StartsWith(Department,SearchTextInput1.Text),
If(Dropdown1.Selected.Value = "All", true, Department = Dropdown1.Selected.Value)
),
'Date & time received',
SortOrder.Descending
)

Dropdown1 Items are ["All","Tax","F&B","Appeals"]

I tried incorporating some or all of the other filters into the code you provided but to no avail.
madlad 2,637 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Glad the first part worked!

Just to confirm, you had only one dropdown to filter by before, but now you have two dropdowns and a search field?

The code you provided should be along the right lines - but combining filters like this is a bit particular, especially if you want to avoid delegation. I'm not sure it's the most efficient, but using the blocks of code you just sent, perhaps something like:

Sort( If( Dropdown1.Selected.Value = "All", If( Dropdown1_1.Selected.Value = "Prep Required", Filter(Table1_12, IsBlank('Prepper Name')), Dropdown1_1.Selected.Value = "Scan Required", Filter(Table1_12, IsBlank('Scanner Name')), Dropdown1_1.Selected.Value = "Index Required", Filter(Table1_12, IsBlank('Indexer''s Name')), Dropdown1_1.Selected.Value = "QA Required", Filter(Table1_12, IsBlank('QCer Name')), Dropdown1_1.Selected.Value = "All", Table1_12 ), If( Dropdown1_1.Selected.Value = "Prep Required", Filter(Table1_12, IsBlank('Prepper Name') && Department = Dropdown1.Selected.Value), Dropdown1_1.Selected.Value = "Scan Required", Filter(Table1_12, IsBlank('Scanner Name') && Department = Dropdown1.Selected.Value), Dropdown1_1.Selected.Value = "Index Required", Filter(Table1_12, IsBlank('Indexer''s Name') && Department = Dropdown1.Selected.Value), Dropdown1_1.Selected.Value = "QA Required", Filter(Table1_12, IsBlank('QCer Name') && Department = Dropdown1.Selected.Value), Dropdown1_1.Selected.Value = "All", Filter(Table1_12, Department = Dropdown1.Selected.Value) ) ), 'Date & time received', SortOrder.Descending )

This formula only accounts for your two dropdowns, and not the search - however that can easily be added similarly to how I added the second dropdown. You can move the If functions into the filter statements and save a lot of space, but that's not always delegable, if that's a concern. It's worth noting, that depending on your data source, you may be able to use the search function as opposed to startswith, which would be substantially easier. Let me know what your datasource is and if you need specifics for searching.

Also as a side note, if your problem is solved if you could mark the solution so others can find it easier 🙂
Verified answer

powelma 47 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks again. I was able to add to what you provided and included search bar functionality. Works like a charm. My data source is just a excel spreadsheet in One Drive. Will be moving it to a SharePoint list eventually. I am sure there is a more efficient way but I'm good with what I have, because it works.

Sort(
If(
Dropdown1.Selected.Value = "All",

If(
Dropdown1_1.Selected.Value = "Prep Required",
Filter(Table1_12, IsBlank('Prepper Name') && SearchTextInput1.Text in 'Date & time received'),
Dropdown1_1.Selected.Value = "Scan Required",
Filter(Table1_12, IsBlank('Scanner Name') && SearchTextInput1.Text in 'Date & time received'),
Dropdown1_1.Selected.Value = "Index Required",
Filter(Table1_12, IsBlank('Indexer''s Name') && SearchTextInput1.Text in 'Date & time received'),
Dropdown1_1.Selected.Value = "QC Required",
Filter(Table1_12, IsBlank('QCer Name') && SearchTextInput1.Text in 'Date & time received'),
Dropdown1.Selected.Value = "All",
Filter(Table1_12, SearchTextInput1.Text in 'Date & time received'),
Dropdown1_1.Selected.Value = "All",Table1_12
),

If(

Dropdown1_1.Selected.Value = "Prep Required",
Filter(Table1_12, IsBlank('Prepper Name') &&
Department = Dropdown1.Selected.Value &&
SearchTextInput1.Text in 'Date & time received'),

Dropdown1_1.Selected.Value = "Scan Required",
Filter(Table1_12, IsBlank('Scanner Name') &&
Department = Dropdown1.Selected.Value &&
SearchTextInput1.Text in 'Date & time received'),
Dropdown1_1.Selected.Value = "Index Required",
Filter(Table1_12, IsBlank('Indexer''s Name') &&
Department = Dropdown1.Selected.Value &&
SearchTextInput1.Text in 'Date & time received'),
Dropdown1_1.Selected.Value = "QC Required",
Filter(Table1_12, IsBlank('QCer Name') &&
Department = Dropdown1.Selected.Value &&
SearchTextInput1.Text in 'Date & time received'),
Dropdown1_1.Selected.Value = "All",
Filter(Table1_12, Department = Dropdown1.Selected.Value &&
SearchTextInput1.Text in 'Date & time received')

)
),
'Date & time received',
SortOrder.Descending
)