VPA by Department

Hoteling VPA by Department

Populate VPA Option 1 and VPA Option 2 Fields for Users

The following can be run daily as an automated SQL task to update the division and department of the employee to their user record. This can also be modified for users who should be able to access more than one division/department.

UPDATE afm_users SET vpa_option1 = (SELECT TOP 1 dp_id FROM em WHERE em.email = afm_users.email)

UPDATE afm_users SET vpa_option2 = (SELECT TOP 1 dv_id FROM em WHERE em.email = afm_users.email)

Apply VPA to Roles

Navigate to “Add or Edit User Roles” and add the following VPA restriction to the roles that need to be restricted. Flush cached roles to apply. If there is an error it will not allow the user to log in and say that the password is incorrect.

Graphical user interface, application

Description automatically generated

Restricts site, building, floor, room based on the division/department assigned to the room record. Restricts division and department lists.

<restrictions> <restriction type=”sql” sql=”dp.dp_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option1’)) AND dp.dv_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option2’))”> <title translatable=”true”>Table-Specific Restriction on dp and dv</title> <field table=”dp”/> </restriction> <restriction type=”sql” sql=”site.site_id IN (SELECT distinct bl.site_id FROM bl WHERE bl.bl_id IN( SELECT distinct rm.bl_id FROM rm WHERE rm.dp_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option1’)) AND rm.dv_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option2’))))”> <title translatable=”true”>Table-Specific Restriction on site</title> <field table=”site”/> </restriction> <restriction type=”sql” sql=”bl.bl_id IN (SELECT distinct rm.bl_id FROM rm WHERE rm.dp_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option1’)) AND rm.dv_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option2’)))”> <title translatable=”true”>Table-Specific Restriction on bl</title> <field table=”bl”/> </restriction> <restriction type=”sql” sql=”fl.bl_id IN (SELECT distinct rm.bl_id FROM rm WHERE rm.dp_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option1’)) AND rm.dv_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option2’))) AND fl.fl_id IN (SELECT distinct rm.fl_id FROM rm WHERE rm.dp_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option1’)) AND rm.dv_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option2’)))”> <title translatable=”true”>Table-Specific Restriction on fl</title> <field table=”fl”/> </restriction> <restriction type=”sql” sql=”dv.dv_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option2’))”> <title translatable=”true”>Table-Specific Restriction on dv</title> <field table=”dv”/> </restriction><restriction type=”sql” sql=”rm.rm_id IN (SELECT distinct rm.rm_id FROM rm WHERE ((rm.dp_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option1’)) AND rm.dv_id IN (#ASQL_GetAfmUserCachedValue(‘vpa_option2’))) OR rm.dp_id IS NULL))”> <title translatable=”true”>Table-Specific Restriction on rm</title> <field table=”rm”/> </restriction></restrictions>

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *