Filtering a DataTable with a value selected from a Dropdown?

(0) Share

Report

Posted on by Lorem1960

379

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.

Categories:

Building Power Apps

All responses (8)

Answers (2)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@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.
Lorem1960 379 on at

Like
a
(0)

Report
Copy link

Link copied!

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 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@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?
Lorem1960 379 on at

Like
a
(0)

Report
Copy link

Link copied!

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

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@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 )
Lorem1960 379 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Randy,

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

I get an error message.

BTW, the Default property of the dropdown is set to "All"
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@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" )
Verified answer

Lorem1960 379 on at

Like
a
(0)

Report
Copy link

Link copied!

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

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