SQL filtering by user auth field not working

[T3NN3SS33]

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)

 

 

[DefinitelyNot31337]

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

[MayMusic]

If you want to see a checkbox is checked you can have [@field:YesNoField] = 1 which is checked and [@field :Yes NoField] = 0 which is not checked. @T3NN3SS33 please let us know if the issue is resolved

[T3NN3SS33]

Thanks @DN31337 and @MayMusic. The issue was related to a couple of errors - the single quotes around compared text strings and also the missing "target." in my statement. Thanks for answering this - I had given up on it back in October

Cheers,

T3NN3SS33