cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
el
Level: Powered On

SQL data source filter delegation statement

Anyone have an idea why my following data source filter shows blue lined non-deleglable?  One of three 'in' operators and both 'Or' operators.  SQL delegation web page seems to indicate these are all deleglable. 

 

I know dates are problems and I will deal with that, but even with no date clause in filter it still shows blue flag on 'in' and 'Or's.

 

Data source is on premise SQL.

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: SQL data source filter delegation statement

Hi @el 

 

It is the Trim function

not delegable

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

6 REPLIES 6
Dual Super User
Dual Super User

Re: SQL data source filter delegation statement

Hi @el 

 

It is the Trim function

not delegable

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

el
Level: Powered On

Re: SQL data source filter delegation statement

Thanks.  I should have seen that.

However, a wierd thing is that unless I use a Trim() on at least one of the 'Or' clauses, I get no records returned.  Any thoughts on that?

Dual Super User
Dual Super User

Re: SQL data source filter delegation statement

Hi @el 

 

The trim is being used on your db column value

 

So "MECH" in Trade_No

is there any Trade_No value that has the word MECH in it (check in SQL)

Just try with this query first and see if u get any results

 

 

 

el
Level: Powered On

Re: SQL data source filter delegation statement

"MECH" is in SQL database as are "OP" and "LOC".

query updated to : 

SortByColumns(Filter('[dbo].[schedule_trans]',"OP" in trade_no Or "MECH" in trade_no Or "LOC" in trade_no, date=galScheduleDates.Selected.date),"trade_no",Descending,"sup",Ascending,"employee_name",Ascending)

 

If I use Trim() on any one of the 'trade_no' elements, I get records.  If I do not use Trim on any, it is deleglable, but no records are returned.

???

Dual Super User
Dual Super User

Re: SQL data source filter delegation statement

Hi @el 

 

Can you give one example of data in sql column

Trim removes leading and trailing spaces

It must have to do with how your data is stored

lknudson
Level: Powered On

Re: SQL data source filter delegation statement

Existing values in column 'tradeno':

'MECH '
'OP   '
'DRVR '
'LOC  '

Column is varchar(5), allows nulls (but no nulls values exist in column)

 

I have tried putting in padded values (e.g., "LOC  ") and non-padded values (e.g., "LOC") in filter clauses:

"OP   " in trade_no Or "MECH " in trade_no Or "LOC  " in trade_no          - no records

"OP" in trade_no Or "MECH" in trade_no Or "LOC" in trade_no                - no records

 

But when I put Trim on any of the three 'trade_no' clauses:

"OP" in Trim(trade_no) Or "MECH" in trade_no Or "LOC" in trade_no       - all records including OP/MECH/LOC trades

 

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (4,131)