web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Apps Filter on Datasource (...

Power Apps

Answered

Filter on Datasource (canvas app)

(0) Share

Report

Report

Posted on by Lee-Martin

581

I'm trying to filter a datasource from a SQL Server view

I have a data Connection.

I can bring back an unfiltered list perfectly using

- ClearCollect(testCollectAll, MySQLServerDataSourceOnView,);

However at no stage do I need an unfiltered list (the number of results is way to large)

I am using this Power FX code to filter

- ClearCollect(testCollectOne, Filter(MySQLServerDataSourceOnView, Site_Code = "ABC"));

It returns the correct number of rows filtered correctly, however there is no data in any of the columns other than Site_Code

Also this also changes my already proven to be working unflitered list to only return results from the Site_Code column

How can i bring back data in all columns on this filtered list?

Thanks
L

(i have no choice on the datasource being SQL Server so please do not direct me to use something else as a solution)

(i cannot show any images of the data due to confidentiality rules where I work)

Categories:

Building Power Apps

All responses Img

All responses (3)

Answers Img

Answers (1)

Sort by

Pstork1 69,661 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
If the filter works for you then you don't need to put it in a collection. You can just use the filter portion of your expression in a Gallery. That will show you the first 100 records and continue showing you the rest of the records as you page down through the gallery. From the Gallery you can select any of the records you want to work on. Just use this in the Item property of the gallery

Filter(MySQLServerDataSourceOnView, Site_Code = "ABC")
Verified answer

BCLS776 8,994 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

In your case, it sounds like the explicit column selection setting is acting to show only columns the platform thinks will be used. Start by turning this setting off (in the app settings) and see if it resolves the problem.

If explicit column selection is the issue (it is a good feature for performance). Turn it back on and use ShowColumns() around your view name to specify each column you wish to have included in your collection. You will need to name each one.

Hope this helps,

Bryan
Lee-Martin 581 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @BCLS776 turning off explicit column selection has worked

Join the conversation