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

Custom field with Lookup table

In this post, we’re gonna get multiple value lookup table in Project Server 2013 database.

Couldn’t find custom fields with allow multiple lookup table values within MSP_EpmProject_Userview In Project Server 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.

Custom field with Lookup table

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.

Get lookup table custom fields in Project Server Database

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 
Association View in Project Server

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; 
Missing lookup table custom fields in MSP_EpmProject_Userview

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
Have a Question?

If you have any related questions, please don’t hesitate to Ask it at deBUG.to Community.

2 thoughts on “Can’t find multiple value lookup table custom fields in EpmProject Userview”

  1. Marcos Vinicius Fontes

    Great solution to bring all the values of the lookup table assigned to a project on the same row! I implemented using subquery instead of view. Thank you.

Leave a Reply