I have a situation where I am trying to create a priv set that restricts records to authorized users. I have a db containing sales data, with a priv set created to only give access to the appropriate sales rep. I used to do this using the get(accountName) function - something like .[salesRepName] = get(accountName). That has worked well until recently. I now have a situation where more than one sales rep can access the same record. I was trying to implement something that utilzed the rep territory number rather than the rep name to grant access. I have a table that has the login name and the individual's territory number. My idea was at startup to look up the logged in user's territory number from the table and use it to populate a global variable ($$USR_TERRITORY). The priv set would then read something like .[Territory] = $$USR_TERRITORY). This way in my table I could have multiple users assigned to the same territory number, and it would use the territory number to define access.
The problem: I can't seem to get a global variable to work properly in the priv set formula. Sometimes it works, much of the time it doesn't, particularly when performing a find.
I also tried using a calculated field that ran a SQL function to pull out the territory number from based on get(AccountName). The calculation works just fine. But using the calculation as part of the priv set does not appear to work.
Any ideas for what I could be doing wrong? Or is there another method I should use for giving more than 1 person access to the same record? I would prefer to use the same priv set for all of these users.