In this post, we’re gonna get multiple value lookup table in Project Server 2013 database.
You may also like Project Server 2016: Query Enterprise Custom Fields
Missing lookup table custom fields in MSP_EpmProject_Userview
I am working on Project server 2013, I tried to create a new enterprise custom field with a lookup table as custom attributes with allow multiple values to be selected from the 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 options.
Multivalue lookup table in Project Server Database
The Multivalue lookup table custom fields are not saved in “MSP_EpmProject_Userview“, it’s saved in an independent association view based on its name.
Get Multivalue lookup table custom fields in Project Server Database
In our scenario, we need to retrieve a multiple value custom field called (Beneficiary) to be displayed like the following.
Based on the custom field name > find the related association view that starts 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).value('.', 'NVARCHAR(MAX)'), 1, 2 , '') [Beneficiary] 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, dbo.cus_beneficiarydetails.beneficiary FROM dbo.cus_beneficiarydetails INNER JOIN dbo.msp_epmproject_userview AS b ON dbo.cus_beneficiarydetails.projectuid = b.projectuid
- Project Server 2013
In conclusion, we have explained how to query multiple value lookup table from Project Server 2013.
You may also like
- Project Server 2016: Query Enterprise Custom Fields.
- Project Server 2016: Invalid object name ‘pjrep.MSP_EpmProject_UserView’
- Migrate to Project Server 2016 Step By Step
Have a Question?
If you have any related questions, please don’t hesitate to Ask it at deBUG.to Community.