We have a BS1192 based datasource, the below is how its filters volume codes to roles / disciplines using the description of the role folder.
"SELECT pw_code, pw_desc FROM env_lkp_volumes WHERE pw_sub1 = '$EDIT#FI_ROLE_CODE$' ORDER BY pw_code"
This works with no problems, I now need to develop it to allow the volume codes it manages to be client specific, as such I've added a "client" attribute at the project level and was planning on using a second filter.
So the query will read the role value as existing but will then perform a secondary filter to the client value set at the project level. I thought something like the below would work but I'm missing something basic I suspect.
SELECT pw_code, pw_desc FROM env_lkp_volumes WHERE pw_sub1 = '$EDIT#FI_ROLE_CODE$' and pw_sub2 = '$PROJECT#CLIENT$' ORDER BY pw_code
Any guidance on where I'm going wrong appreciated.
Probably not the issue, but I like using () in WHERE clauses in case the order of operations doesn’t work like I think:
WHERE ((pw_sub1 = '$EDIT#FI_ROLE_CODE$') and (hpw_sub2 = '$PROJECT#CLIENT$'))
hmm, also, I think the fields in the project properties get prefixed with PROJECT_ so maybe:
WHERE ((pw_sub1 = '$EDIT#FI_ROLE_CODE$') and (hpw_sub2 = '$PROJECT#PROJECT_CLIENT$'))
Back to this one, findng it difficult, the suggestion above looks good to me but no joy, I have a feeling it's the referemce to the project field I'm not getting right.
Have you added 'Client' values for 'pw_sub2' property against various volumes in env_lkp_volumes folder?
I have and if i put a temporary filter for those directly it works as expected.