In one of our TW applications we have a large stock table and we created prefilters to filter for specific warehouses. We would like to use roles to assign which prefilters are visible and used.
So pickers form warehouse A do not see the warehouse B entries and vice versa. And some people need to be able to see all..
We couldn't find a way to do it.
Best answer by Peter Kruis
View original
+4
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:
). We already do this for retrieving some company information stored in IAM.
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.
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.
+4
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.
+4
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.
+4
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.
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
+23
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:
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.
Hi Mark,
Thanks for the suggestion. However, in that case when a person has been authorized for more than 1 warehouse, data will not be shown.
For now, we have done the following. We have created Ppefilters for each warehouse, and we also created another one for warehouses_allowed, which is invisible and on by default. This checks if the warehouse is in the allowed sections. I am afraid if the data will grow, this will have some impact on the performance.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.