Answered

Filtering a Table

(0) Share

Report

Posted on by R45

Hi all

I am trying to filter a table using the selected option from a combo box but on selection the table does not filter. Instead it shows no data to display. My code under the items property shows no errors:

Sort(Filter(Hours, Name_Column2.Text = DisplayName_1.Selected.Name), Date, SortOrder.Descending)

The combo box is single select and its options are from an employee table on SQL Server. The Tables data is from SQL Server also.

Any help in filtering the table using the combo box would be great.

Thanks

Categories:

Building Power Apps

All responses (13)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @R45 ,

If DisplayName_1 is the Combo box, please try below formula:

Sort(Filter(Hours, Name_Column2 = DisplayName_1.Selected.Name), Date, SortOrder.Descending)

Best regards,
R45 94 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni

When I run your code I get a compatibility error as shown:

Any other suggestions?

Thanks for your response.
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @R45 ,

You need to use a Column Name instead of Control Name (Name_Column2). What's the column name in the SQL table?

Best regards,
R45 94 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-jefferni Thank you! I have now changed the name and the filter works although now it only shows the filtered columns as shown:

I only changed the column name to "name" as in SQL so idk why it would ignore data in other columns;.
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @R45 ,

You may have to use a ShowColumns function to tell PowerApps which columns do you want to retrieve from SQL table, due to some limitations of SQL:

Sort(Filter(ShowColumns(Hours,"Name","Date","Week",...), Name= DisplayName_1.Selected.Name), Date, SortOrder.Descending)

You will be able to select columns from the Dropdown under formula bar when writing in ShowColumn function.

Best regards,
R45 94 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-jefferni

I have entered the show columns as you suggested and no no data is displaying again.

The code has no errors. Any suggestions?

Cheers
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
You didn't select any in the Combo box.

If you want to show all entries in the Data Table control, change the formula to:

Sort(Filter(ShowColumns(Hours,"Name","Date","Week",...), IsBlank(DisplayName_1.Selected.Name)||Name= DisplayName_1.Selected.Name), Date, SortOrder.Descending)

Adding IsBlank(DisplayName_1.Selected.Name) in the formula would solve the issue that no item is displayed when nothing is selected in the Combo box.

BR,
R45 94 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-jefferni

I made sure to try that before asking you but that didn't show data either.

Kind regards
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @R45 ,

What if remove Sort and Filter from the formula? Will it display all items in the data table?

//Sort(Filter(ShowColumns(Hours,"Name","Date","Week",...), IsBlank(DisplayName_1.Selected.Name)||Name= DisplayName_1.Selected.Name), Date, SortOrder.Descending) ShowColumns(Hours,"Name","Date","Week",...)

Best regards,
R45 94 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-jefferni

Yes, the data is then displayed.

Kind Regards