Limit Access to Business Partners using Logged-in User
The goal here is to ensure that when a certain role (in our case:outside sales representatives) log into the system, they can only see their own Business Partners (in our case: defined by the Sales Representative Field on the Customer tab of the Business Partner). Users logged into the system with a different Role should see all customers.
- Log into System as SuperUser
- Open Window Tab and Field and search for the window: Business Partner
- Click on tab
- Choose Business Partner
- Put the following in SQL WHERE clause:
C_BPartner.SalesRep_ID = @#AD_User_ID@ OR @#AD_Role_ID@ != '1000009'
Role: "Outside Sales Rep"= (AD_Role_ID=1000009)
If you now log in as someone using Outside Sales Rep role, you should only have access to business partners with that users name on the customer tab.
- This seems to break Searches: Search queries that should result in 0 results still show all Customers belonging to the Sales Rep.
- Max records queries still warns you of too many results even when you won't see all of them because of the sql WHERE clause
Turns out that the above doesn't work since search parameters are added to the SQLWhere clause. The OR part part of it values everything as true, showing all records.
The solution is to use a condition:
C_BPartner.SalesRep_ID = CASE WHEN @#AD_Role_ID@='1000009' THEN @#AD_User_ID@ ELSE SalesRep_ID END