I am working on Project server 2013, I tried to create a new enterprise custom field with a lookup table as a custom attributes with allow multiple values to be selected from lookup table as shown below.
I tried to query “MSP_EpmProject_Userview” to retrieve all custom fields.but I couldn’t find all custom fields that link-able to a lookup table with allow multiple value option.
The Multi value custom fields is not saved in “MSP_EpmProject_Userview” that saved in an independent association view based on its name.
In my scenario, I need to retrieve a multiple value custom field called (Beneficiary) to be displayed as the following.
- Based on the custom field name > find the related association view that start with MSPCFPRJ, In my case it’s
- Create a new custom view [CUS_BeneficiaryLookUp] to join “MSP_EpmProject_Userview” with [MSPCFPRJ_Beneficiary_AssociationView].
SELECT proj.ProjectUID, proj.ProjectName, CONVERT(nvarchar(MAX), lt.MemberFullValue) AS N'Beneficiary'
FROM dbo.MSP_EpmProject_UserView AS proj INNER JOIN
[dbo].[MSPCFPRJ_Beneficiary_AssociationView] AS Benassoc ON proj.ProjectUID = Benassoc .EntityUID INNER JOIN
dbo.MSP_EpmLookupTable AS lt ON Benassoc.LookupMemberUID = lt.MemberUID
- Create another custom view [CUS_BeneficiaryDetails] to show the related Beneficiary to its Project Name in one field delimited with Comma.
select distinct t.[ProjectUID],t.[ProjectName],
STUFF((SELECT distinct ', ' + t1.[Beneficiary]
from [ProjectWebApp].[dbo].CUS_BeneficiaryLookUp t1
where t.[ProjectUID] = t1.[ProjectUID]
FOR XML PATH(''), TYPE
from [ProjectWebApp].[dbo].CUS_BeneficiaryLookUp t;
You can also join the multiple value custom filed with other related custom fields in “MSP_EpmProject_Userview” via the following query
SELECT b.ProjectUID, b.ProjectName, CONVERT(nvarchar(10), b.ProjectStartDate, 103) AS StartDate,
CONVERT(nvarchar(10), b.ProjectFinishDate, 103) AS FinishDate,
FROM dbo.CUS_BeneficiaryDetails INNER JOIN
dbo.MSP_EpmProject_UserView AS b ON dbo.CUS_BeneficiaryDetails.ProjectUID = b.ProjectUID