prefilters per role

Have you tried setting the prefilter settings in the software factory under roles → tables → prefilters? (and of course syncing these settings to IAM)
The prefilters tab under roles is only visible if prefilters are present at the selected table.

Interesting question, we might deal with the same problem in the future. I'd like to hear the final solution.

Due to the fact that the content (both users and warehouse) is variable it's a bit tricky to set prefilters, as warehouse can be added or removed.

What we were thinking of is a table in the end product database linking users and warehouses. If a user exists in this link table (e.g. user_warehouse (usr_id, warehouse_id)), the warehouse could be shown. However this requires user information from IAM, and (a kind of role) management via your end product. 

The (user) information could be extracted via external tables (explained here:

• Cross database scripts on Microsoft Azure Blog

). We already do this for retrieving some company information stored in IAM.

This is a good solution for only setting the visibility.

What I miss in Alban his question is that we would like to force the prefilter to be enabled. In our case we have 2 locations where stock is located. 
We have (several screens and lookups) where we would like to force a filter, e.g. we would like to see a filter on the location screen for location A, where this prefilter is (forced) on for the employee's who work at location A. The same of course counts for B.

I don’t think it’s currently possible to force certain prefilter states based on the user’s role without slightly ‘hacky’ solutions with reading IAM tables.

It is however possible to set access via roles to variants and for each variant, prefilter settings can be set.

so to limit access for specific roles, create a variant for each role/group. Set the prefilters for each variant to limit access to certain data and then manage access to these variants via roles. 

It is however possible to set access via roles to variants and for each variant, prefilter settings can be set.

so to limit access for specific roles, create a variant for each role/group. Set the prefilters for each variant to limit access to certain data and then manage access to these variants via roles. 

Will this also work for lookups? As a task has 1 lookup, and that lookup also needs to be filtered.

It won’t. You can select a variant lookup but then you would have to create a separate task for each variant. This could be done dynamically but it would become a bit complex. In that case I would use the slightly hacky way of creating a prefilter that filters rows based on data from IAM:
 

exists (
    select 1
    from [IAM_database].[dbo].[usr_role]
    where usr_id = dbo.tsf_user()
    and role_id = [your_role]
)

I would upvote an idea to force prefilter states based on role though.

To add to my colleague’s comments: we actually use a single pre-filter query to filter records based on the combination of a specific User Group assigned to the Current User and the Company of that User, both retrieved from IAM tables/views indeed. Probably that Company field (or another field on the usr View from IAM) could be used for the Warehouse location. I presume the same kind of pre-filter could be used on the Lookup field. 

In this solution no Variants are needed.

@J. de Lange I am referring to the Broker User Group on the Contracts table, perhaps you can share some code.

Prefilter:

/*
    Give users access to legal accounts of the company they belong to
*/
(
    select u.company_name /*the company name the user belongs to*/
    from iam_usr u 
    where u.usr_name = dbo.tsf_user() 
) = (
    select a.official_company_name /*The company name this legal account belongs to*/
    from account a
    where a.account_id = t1.broker_account_id
)

Where the iam_usr view uses the following template:

/*
    Get all users from the IAM users table
*/
select usr_id   as usr_name
,u.first_name   as first_name
,u.sur_name     as surname
,u.name         as display_name
,u.gender       as gender
,u.email        as email_address
,u.phone_no     as phone_nr
,c.company_name as company_name
,u.begin_on     as begin_on
,u.end_on       as end_on
from [YOUR_IAM_DATABASE].[dbo].[usr_general] u
left join  [YOUR_IAM_DATABASE].[dbo].[company] c
on u.company_id = c.company_id

In the Software Factory at Roles > Tables > Prefilters, you can alter the value of "Data authorization prefilter” to True. This will force the prefilter to be On (locked) for the role.

Do keep in mind that if you have multiple roles set up like this, that users should not have multiple roles like this. Then the prefilters will be both/all applied likely resulting in no data being shown.

Does this solve the issue at hand? I don't think querying the IAM is needed for this.

So, in other words and as example, let's say you have three warehouses and three prefilters. Then create three roles:

1. Warehouse_A → Set "Data authorization prefilter” to True for prefilter Warehouse_A
2. Warehouse_B → Set "Data authorization prefilter” to True for prefilter Warehouse_B
3. Warehouse_C → Set "Data authorization prefilter” to True ​​​​​​​for prefilter Warehouse_C

You can then uncheck the "Visible” checkbox for example of other prefilters so employees working at warehouse A cannot see the prefilters for warehouse B and C.