calculated field SQL Filtering with Auth field in where clause not working

[Shuchi]

I am trying to get the count from a table and applying record level security, but in the where clause, when I am comparing it with my auth field, it is saying "Invalid Query"
For instance, my query:

ISNULL((SELECT COUNT(DISTINCT [@field:PropertyKeyId]) FROM UI_FactTrxn_1 WHERE [@field:InvestorUserId] = [@authfield:Users_UserId]),0)

I am getting Invalid error for this.

But when I am trying to pass the string in place of authfield, it is a valid one!

Can anyone please help me on this?

[Shuchi]

Thanks for the help Melody, I figured it out. The syntax was incorrect as my User_Id Field is a string value, I should pass it in ''.

The corrected query is : 

 

SELECT COUNT(DISTINCT PropertyKeyId)

FROM UI_FactTrxn_1 WHERE InvestorUserId = '[@authfield:Users_UserId]'

 

[MayMusic]

If you are using SQL SELECT to read the value you need to use the name of the field form that table not using parameter

 

so you need to have:
 

SELECT COUNT(DISTINCT PropertyKeyId)

FROM UI_FactTrxn_1 WHERE InvestorUserId = [@authfield:Users_UserId]

And you do not have to replace the value with 0 as COUNT returns 0 if there is none. 

[Shuchi]

On 10/29/2018 at 11:41 AM, MayMusic said:

If you are using SQL SELECT to read the value you need to use the name of the field form that table not using parameter

 

so you need to have:
 

SELECT COUNT(DISTINCT PropertyKeyId)

FROM UI_FactTrxn_1 WHERE InvestorUserId = [@authfield:Users_UserId]

And you do not have to replace the value with 0 as COUNT returns 0 if there is none. 

Thanks for the reply Melody, but I am still getting the same error :

even when I used the query you suggested.  

[MayMusic]

Select that value after the equal sign from the picker. 

[Shuchi]

By picker, I hope you mean to say that I should use  button to insert the auth field. I am inserting it from there itself. Is there any other way to do it? Is it a syntax error? Like for using a view we use "_v_" before the name, for referring to fields on data page, we use "target." and then the field name, do we need to append something for authfield as well?

[MayMusic]

Great thanks for updating us

[ChristianM]

On 11/1/2018 at 7:42 PM, Shuchi said:

Thanks for the help Melody, I figured it out. The syntax was incorrect as my User_Id Field is a string value, I should pass it in ''.

The corrected query is : 

 

SELECT COUNT(DISTINCT PropertyKeyId)

FROM UI_FactTrxn_1 WHERE InvestorUserId = '[@authfield:Users_UserId]'

 

So glad I found your post. Finally I can pass on my authfields now that I know they need to be passed in quotations. Thanks for sharing your solution!