Issue: By default, project site column doesn’t support hierarchical values in the lookup table.
Workaround: To fix the above issue, we can configure the Project Site list column look up values as per the below snapshot:
1. Create the custom SharePoint list. For example, we have created the SharePoint list name as “DepartmentValueList”
2. Create two custom columns and one calculated column to concatenate the values for two columns. In this example, to make it simple we are taking two columns but in real scenario you may need to concatenate more than two columns based on the hierarchy levels.
3. Define the following formulae to concatenate the values of the list columns – level one and level two
4. Add the dummy values in the DepartmentValue list to validate the formulae. Here, please note that you need to add the manual values with different combination for given level in the hierarchy. Refer screenshot as below:
“CalculatedDepartmentValues” concatenate the above two columns first row values as per below snapshot:
5. Now, create the Look type site column and reference the CalculatedResourceDepartment list field
6. The last steps is to add the site column in the project site list and save as new template.
7. In above step, we have added the Resource Department site column for the issue list. Now, click on the Issue list, you will see the site column added to the list with the Look up values. Bingo!!