cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lorem1960
Resolver III
Resolver III

Filtering a DataTable with a value selected from a Dropdown?

Hi ,

I am fairly new to power apps and I have the following question:

I have a DataTable with multiple columns and I am able to perform a search and sort on all columns using this code:

 

SortByColumns(
Filter(
'Pending Receipts',

(IsBlank(SearchTextBox.Text) || StartsWith(
'PR #',
SearchTextBox.Text
) || StartsWith(
'Req. Office',
SearchTextBox.Text
) || StartsWith(
'Title (Title0)',
SearchTextBox.Text
) || StartsWith(
'Vendor Name',
SearchTextBox.Text
) || StartsWith(
'Receipt Status',
SearchTextBox.Text
) || StartsWith(
'Order Contact',
SearchTextBox.Text
) || StartsWith(
'Order Type',
SearchTextBox.Text
) || StartsWith(
'Receipt Status',
SearchTextBox.Text
) || StartsWith(
'Email Sent'.Value,
SearchTextBox.Text
) || StartsWith(
Response,
SearchTextBox.Text
) || StartsWith(
Status.Value,
SearchTextBox.Text
))
),
varSortColumn,
varSortDirection
)

 

I have been asked to add a dropdown based on the Order Type, so I added a dropdown named drpdwnOrderType, the items on my dropdown is like this: Distinct('Pending Receipts','Order Type') so that the dropdown shows unique values.

 

I then tested this filter for the dropdown like this (items in datatable)

 

Filter('Pending Receipts', 'Order Type'= drpdwnOrderType.Selected.Result) and this works just fine, but now I have no idea where exactly I need to insert this extra filter in the code listed above so that I can also continue to perform my search and sorting on my different columns within my datatable. 

 

Any help is most appreciated.

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@Lorem1960 

Change your Items property on the dropdown to:

 

Ungroup(
    Table(
        {values: Table({Result: "All"})},
        {values: Distinct('Pending Receipts', 'Order Type')}
    ),
    "values"
)

 

Set the Default property of the dropdown to "All"  (Please double check that you are using a dropdown and not a combobox - if there is a DefaultSelectedItems property, it is a combobox).

 

Then change your Items formula to the following:

 

SortByColumns(
    Filter('Pending Receipts',
        (IsBlank(SearchTextBox.Text) || 
         StartsWith('PR #',SearchTextBox.Text) || 
         StartsWith('Req. Office', SearchTextBox.Text) || 
         StartsWith('Title (Title0)', SearchTextBox.Text) || 
         StartsWith('Vendor Name', SearchTextBox.Text) || 
         StartsWith('Receipt Status', SearchTextBox.Text) || 
         StartsWith('Order Contact', SearchTextBox.Text) ||          
         StartsWith('Receipt Status', SearchTextBox.Text) || 
         StartsWith('Email Sent'.Value, SearchTextBox.Text) || 
         StartsWith(Response, SearchTextBox.Text) || 
         StartsWith(Status.Value, SearchTextBox.Text)
        ) &&
        (drpdwnOrderType.Selected.Result = "All" ||
         'Order Type' = drpdwnOrderType.Selected.Result
        )
    ),
    varSortColumn,
    varSortDirection
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Okay it works, needed to correct the syntax - thanks very much!!!

 

Ungroup(
Table(
{values: Table({Result: "All"})},
{values: Distinct('Pending Receipts', 'Order Type')}
),
"values"
)

View solution in original post

8 REPLIES 8
RandyHayes
Super User
Super User

@Lorem1960 

I will assume this means that you will be removing the logic for the StartsWith on the Order Type as you will now be selecting that by the dropdown.

If that is the case, then your formula should be as follows:

SortByColumns(
    Filter('Pending Receipts',
        (IsBlank(SearchTextBox.Text) || 
         StartsWith('PR #',SearchTextBox.Text) || 
         StartsWith('Req. Office', SearchTextBox.Text) || 
         StartsWith('Title (Title0)', SearchTextBox.Text) || 
         StartsWith('Vendor Name', SearchTextBox.Text) || 
         StartsWith('Receipt Status', SearchTextBox.Text) || 
         StartsWith('Order Contact', SearchTextBox.Text) ||          
         StartsWith('Receipt Status', SearchTextBox.Text) || 
         StartsWith('Email Sent'.Value, SearchTextBox.Text) || 
         StartsWith(Response, SearchTextBox.Text) || 
         StartsWith(Status.Value, SearchTextBox.Text)
        ) &&
        'Order Type' = drpdwnOrderType.Selected.Result 
    ),
    varSortColumn,
    varSortDirection
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi, this works fine, but I am wondering what you need to do so that by default it shows all rows and only after you make a selection on the dropdown that the filter is being applied, that would make it just perfect.

RandyHayes
Super User
Super User

@Lorem1960 

That's going to depend on how you have defined your dropdown.  By default a dropdown will always show the first value in the Items collection unless you have the AllowEmptySelection set to true and have the Default set to Blank().

So...is that the case?  How have you set up the dropdown? 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

In the Items for the dropdown I have this filled in:

 

Distinct('Pending Receipts','Order Type')

Would be nice that by default it could show All Order Types.

 

 

RandyHayes
Super User
Super User

@Lorem1960 

Change your Items property on the dropdown to:

 

Ungroup(
    Table(
        {values: Table({Result: "All"})},
        {values: Distinct('Pending Receipts', 'Order Type')}
    ),
    "values"
)

 

Set the Default property of the dropdown to "All"  (Please double check that you are using a dropdown and not a combobox - if there is a DefaultSelectedItems property, it is a combobox).

 

Then change your Items formula to the following:

 

SortByColumns(
    Filter('Pending Receipts',
        (IsBlank(SearchTextBox.Text) || 
         StartsWith('PR #',SearchTextBox.Text) || 
         StartsWith('Req. Office', SearchTextBox.Text) || 
         StartsWith('Title (Title0)', SearchTextBox.Text) || 
         StartsWith('Vendor Name', SearchTextBox.Text) || 
         StartsWith('Receipt Status', SearchTextBox.Text) || 
         StartsWith('Order Contact', SearchTextBox.Text) ||          
         StartsWith('Receipt Status', SearchTextBox.Text) || 
         StartsWith('Email Sent'.Value, SearchTextBox.Text) || 
         StartsWith(Response, SearchTextBox.Text) || 
         StartsWith(Status.Value, SearchTextBox.Text)
        ) &&
        (drpdwnOrderType.Selected.Result = "All" ||
         'Order Type' = drpdwnOrderType.Selected.Result
        )
    ),
    varSortColumn,
    varSortDirection
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi Randy,

 

I checked and I am using indeed a dropdown, but when I am entering the code for the items

 

Lorem1960_1-1634138450965.png

 

I get an error message.

 

BTW, the Default property of the dropdown is set to "All"

RandyHayes
Super User
Super User

@Lorem1960 

Ooops, I had a regular open paren in the formula - should have been a curly open.  Tough to write these formulas by hand on the forum 😉

Formula should be:

Ungroup(
    Table(
        {values: Table({Result: "All"})},
        {values: Distinct('Pending Receipts', 'Order Type')}
    ),
    "values"
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Okay it works, needed to correct the syntax - thanks very much!!!

 

Ungroup(
Table(
{values: Table({Result: "All"})},
{values: Distinct('Pending Receipts', 'Order Type')}
),
"values"
)

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,865)