Couldn’t find custom fields with allow multiple lookup table values within MSP_EpmProject Userview In Project Server Database

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.

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 option.

Cause:

The Multi value custom fields is not saved in “MSP_EpmProject_Userview” that saved in an independent association view based on its name.

Solution:

In my scenario, I need to retrieve a multiple value custom field called (Beneficiary) to be displayed as the following.

getinonerow

  • Based on the custom field name > find the related association view that start 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

Output

customviewoutput

  • 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;

getinonerow

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

Enjoy 🙂

Advertisements

2 thoughts on “Couldn’t find custom fields with allow multiple lookup table values within MSP_EpmProject Userview In Project Server Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s