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

Filter on SQL data source no longer working

I have a Power App I've been using for at least a year, maybe two, and it has a button which performs the equivalent of the following.  I've simplified it a bit for the purposes of this forum post, but this simplified version exhibits the same problem:

 

ClearCollect(MyCollection, Filter('[dbo].[Tasks]', ID = 209 )

 

The "[dbo].[Tasks]' table has 3 columns - ID, ID_Client, and Task.  The problem is that only the first two of these are being loaded into my Collection - the "Task" column is being missed out.  If I remove the Filter, and just do:

 

ClearCollect(MyCollection, '[dbo].[Tasks]')

 

...then it works fine - all three columns are returned.  So it looks like something weird to do with the filtering operation.  If I use the PowerApps Monitor tool to see what's going on under the hood, the "x-ms-request-url" is being set to the following (have redacted the server and database names):

 

/apim/sql/23cd4685c08249129ca710527d3ee0df/v2/datasets/servername.database.windows.net,databasename/tables/%255Bdbo%255D.%255BTasks%255D/items?%24filter=ID_Client+eq+209&%24select=ID%2CID_Client&%24top=500

 

The "%24select=ID%2CID_Client" part suggests to me that only the ID and ID_Client columns are being requested from Azure SQL.  As I say, this has been working for well over a year, and it's not exactly complicated!  So I'm wondering why it's not working.  Am I missing something? Is it just plain broken at the Microsoft end?

 

The original Power App used v1 of the SQL connector.  But I get the same problem with v2 of the SQL connector, and a completely new test Power App created from scratch.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Hi @Interlock 

Out of interest, can you confirm if you've disabled 'explicit column setting'?

 

image.png

 

Although I can't explain why it used to work, but is no longer working, if you were to specify the columns with the ShowColumns function, does that make a difference?

ClearCollect(MyCollection, 
             ShowColumns(Filter('[dbo].[Tasks]', ID = 209 ),
                         "ID", "ID_Client", "Task")
)

View solution in original post

3 REPLIES 3
Super User III
Super User III

Hi @Interlock 

Out of interest, can you confirm if you've disabled 'explicit column setting'?

 

image.png

 

Although I can't explain why it used to work, but is no longer working, if you were to specify the columns with the ShowColumns function, does that make a difference?

ClearCollect(MyCollection, 
             ShowColumns(Filter('[dbo].[Tasks]', ID = 209 ),
                         "ID", "ID_Client", "Task")
)

View solution in original post

@timl, truly thou art a superstar!  You have nailed it.

 

  • If I switch off "Explicit Column Selection" and relaunch the app, it works.  
  • If I keep "Explicit Column Selection" on, and wrap my filter in a "ShowColumns", it also works.

 

I see that the description for "Explicit column selection" is "Optimizes load times and reduces memory consumption by only fetching columns used in your app.  Target data source must support this feature".  The thing is, the "Task" column IS used in my app, it's just that it wasn't explicitly specified with ShowColumns in the formula as per your suggestion.  Hoping that this behaviour will change before this feature comes out of Preview, because it seems to me that Power Apps ought to have seen references to this column elsewhere in the app, and requested it from SQL Server.

 

Anyway, I now know what the problem is, and I can make things work.  Thank you so much for saving my sanity.

Thanks @Interlock! I'm glad to have helped. 

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (35,331)