Can’t find multiple value lookup table custom fields in EpmProject Userview

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

[dbo].[MSPCFPRJ_Beneficiary_AssociationView] 

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 

---

Applies To

• Project Server 2013

Conclusion

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.