cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vladimir84
Advocate IV
Advocate IV

[BUG] - Filter SQL, column name starts with a number, invalid operation

Hello everyone,

Please take a look and let me know if I should raise a ticket:

It's a Filter(SQL, '1_step'=true) on a SQL view (if that's relevant), and as you can see the name of the column is a bit weird, but it was spelled correctly in between the single quotes.

 

vladimir84_0-1611067820026.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User III
Super User III

Hi @vladimir84 

This is very interesting and I've been able to recreate this issue.

timl_0-1611073830649.png

 

What I think is happening is that Power Apps isn't handling column names that don't adhere to the regular identify rules in SQL.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-se...

 

timl_1-1611073940942.png

As it's possible to create a view with a column that starts with a number, I would certainly consider raising this as a ticket

In the meantime, you could rename the column in your view so that it doesn't start with a number. This resolves the problem for me.

CREATE VIEW MyView 
AS
SELECT YourColumns, 1_Step AS [OneStep]
FROM
YourTable

As this shows, the problem now goes away.

timl_2-1611074304538.png

 

View solution in original post

5 REPLIES 5
eka24
Super User III
Super User III

Consider this:

Filter(SQL, '1_step'="Yes") 

or

Filter(SQL, '1_step'=1) 

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

tried that, nope. I jumped the gun a bit. The "Yes" is a text value, I assume you meant true, I tried with =true.

when I try filtering on a column that does not require ' ' in its name, it works

eka24
Super User III
Super User III

Did you try:

Filter(SQL, '1_step'=1) 

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

timl
Super User III
Super User III

Hi @vladimir84 

This is very interesting and I've been able to recreate this issue.

timl_0-1611073830649.png

 

What I think is happening is that Power Apps isn't handling column names that don't adhere to the regular identify rules in SQL.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-se...

 

timl_1-1611073940942.png

As it's possible to create a view with a column that starts with a number, I would certainly consider raising this as a ticket

In the meantime, you could rename the column in your view so that it doesn't start with a number. This resolves the problem for me.

CREATE VIEW MyView 
AS
SELECT YourColumns, 1_Step AS [OneStep]
FROM
YourTable

As this shows, the problem now goes away.

timl_2-1611074304538.png

 

View solution in original post

Yes, but I already designed everything and put it on paper...I'm 3/4 into development, damnit! 

I'll just do the filtering on the views, as I need the numbers to keep track of the steps.

Fortunately, Patch is still working with those columns.

 

Thanks for the answer. Hopefully, someone in the development will take note.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (20,380)