Jump to content
  • 0

SQL filtering by user auth field not working


T3NN3SS33

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)

 

 

Link to comment
Share on other sites

3 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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...