As our company grows and its structure more complex, I am having difficulty getting our Sales team access to all of their data.
Example: Our Sales team hierarchy is organized as: Channel > Territory (this is where the sales rep resides) > Customer Corp ID > Customer ID > Ship To. Each level of the hierarchy is its own dimension.
We have instances where a Sales Rep gets assigned a new territory but will still be responsible for some accounts (residing at the Customer Corp ID dimension) in another territory. We don’t want them to have access to all of their old territory, only the Corp IDs they are responsible for as well as their new territory. If I were to assign a territory and the Corp ID values then only the common customers (which is usually a very small list) are displayed.
I have tried using custom columns to create a value at the customer level to be used in security but abandoned it because there would just be constant maintenance keeping everything aligned.
We have a similar issue for 1 sales person.
Unfortunately there is no option in Phocas to have an OR in the access restrictions, although I think it was mentioned that this may be added in a future update.
Our solution was similar to what you mention except we created a field in the database for the sales person instead of the customer using the SQL for the sync item (I hope you have less sales people than customers).
I suggest you do the same, where each sales person would have a separate field returning a 1 or 0 and the access restrictions would be set once for the sales person where the field must be 1.
While this may seem high maintenance, it is really the same amount of work if not less.
Instead of updating the conditions for each user in the database restrictions, you would update the formula for the field that represents the sales persons access. We are using the SQL for the sync item, which I think adds clarity to it since all restrictions are defined in one text file, but a transformed field will work as well.
Hope this helps
Scott