Creating Security Dependent Extended Fields for Resource Pools

If your company uses Resource Planning, Administrators and Process Designers can create list-type extended fields using a query for resource pools such that the items available in the list depend on which items are selected in the pool's security lists.

Create a query-based, list-type extended field whose query contains a code like the QMD query field codes used for list metrics. The code's pattern is {*QMD:(metadata name)*}. The code returns a comma delimited list of the selected security list item IDs for the pool.

There are five codes, one for each of the five potential security lists that might be enabled within Accolade:

• {*QMD:PoolSecurityListID1*}
• {*QMD:PoolSecurityListID2*}
• {*QMD:PoolSecurityListID3*}
• {*QMD:PoolSecurityListID4*}
• {*QMD:PoolSecurityListID5*}

An additional five codes return a comma-delimited list of the security list system names associated with the pool.

• {*QMD:PoolSecurityListSystemName1*}
• {*QMD:PoolSecurityListSystemName2*}
• {*QMD:PoolSecurityListSystemName3*}
• {*QMD:PoolSecurityListSystemName4*}
• {*QMD:PoolSecurityListSystemName5*}

Example Query

Use a query to select the extended field items that are associated with the pool's selected security list items. For example, for a custom view named CV:

SELECT DISTINCT CV.[ColumnName]
FROM CRV_CustomView CV WITH (NOLOCK)
WHERE CV.SecListID IN ({*QMD:PoolSecurityListID1*})

Tables That Associate the Security List Items with the Extended List Items

You also need to create associations between the IDs or system names of the security list items and the IDs or names of the items in the extended list. One way to do this would be to import reference tables that contain the associations. Then create a custom view to select the associated items.

Security lists do not have visible IDs. To find the IDs, either create the lists using reference tables, which allows you to specify the IDs or look in the ACC_SecurityLists table of the Accolade database. Contact Sopheon Customer Support for additional information.

Notes:    Resource Planning is an optional Accolade component that you may not have access to. To implement this solution, contact Sopheon Customer Support .