Jump to content
  • 0
T3NN3SS33

SQL filtering by user auth field not working

Question

I am attempting to apply record level security to several calculation fields and I am receiving an error telling me the column from my user table is invalid. I have given some details below, hopefully someone will quickly see my error and get me back on track!

 

This query works well but is not authenticated by user…….

IsNull((SELECT COUNT([@field:specimen_ID])

FROM MDx_tbl_specimen

WHERE [@field:spec_received] = 'false'

AND [@field:spec_released] = 'false'

AND [@field:spec_batch_deact_2] = 'true'

),0)

 

This query works but is limited to one user. I only tested this one because I wanted to see where things break down.

IsNull((SELECT COUNT([@field:specimen_ID])

FROM MDx_tbl_specimen

WHERE [@field:spec_received] = 'false'

AND [@field:spec_released] = 'false'

AND [@field:spec_batch_deact_2] = 'true'

AND [@field:MDx_tbl_specimen_spec_acct_ID] = '1234'

),0)

 

This is the query I need that gives me RLS based on matching ACCT ID field from both user and records of interest – Caspio says it is invalid and gives an error (Invalid Column Name '@authfield:MDx_tbl_users_user_account_ID' . 

IsNull((SELECT COUNT([@field:specimen_ID])

FROM MDx_tbl_specimen

WHERE [@field:spec_received] = 'false'

AND [@field:spec_released] = 'false'

AND [@field:spec_batch_deact_2] = 'true'

AND [@field:MDx_tbl_specimen_spec_acct_ID] = [@authfield:MDx_tbl_users_user_account_ID]

),0)

 

 

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Hi @T3NN3SS33,

 

I haven't gone throughout your syntax but it seems that you've been missing the target keyword.

 

If my assumption that you're doing this in a Calculated Field in a Report is correct, you might want to use the target keyword to refer to the field of the current row.

I've encountered this issue countless times before. Good thing, I came across this article: https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/ (Scroll over to Subqueries in Calculated Fields)

 

Additionally, I think Caspio has an annoying thing on Virtual Fields. Not sure if this applies for other DataTypes as well.

If you're comparing Text strings, you may want to try putting them inside a single quote (e.x. 'target.[@field:blablabla]', '[@authfield:userid]').

 

Hope one of these things ultimately resolve your issue.

 

Cheers,

DN31337

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×