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

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

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.
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"
)

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,956)