sharepointproject-serverpsi

MS Project 2010 PSI: Is there a way to get custom field values without loading a project?

I am trying to locate a particular project by a custom field value. So far, this is the best way I've found to do it:

var targetProjectId = "some_external_reference_ID";
var projIdCustomFieldUid = GetCustomFieldUidByName("ExternalProjectId");
var projectList = base.ProjectClient.ReadProjectList();

foreach (ProjectDataSet.ProjectRow projRow in projectList.Project.Rows)
{
    var fullProj = base.ProjectClient.ReadProject(projRow.PROJ_UID, DataStoreEnum.WorkingStore);
        if (fullProj != null)
        {
            var cf = fullProj.ProjectCustomFields.Where(x => x.MD_PROP_UID == projIdCustomFieldUid && x.TEXT_VALUE == targetProjectId ).FirstOrDefault();
            if (cf != null)
            {
                return fullProj;
            }
        }
    }
    return null;
}

As you can imagine, looping through all the projects and loading each one to check the custom field value is horribly slow and ugly. I need to identify a PROJ_UID by custom field value as fast as possible, thus:

Is there a way to get at custom field values without loading a whole project?

Solution

  • If you only need published projects then I would use a SQL query on the ProjectServer_Reporting database, probably the MSP_EPMProject_UserView view. This view includes columns for most types of custom fields.

    SELECT
  ProjectUid
  ,ProjectName
FROM
  MSP_EPMProject_UserView mepuv
WHERE
  mepuv.[My Custom Field] = 'the value I care about'

    If you really need to call this through the PSI, then iirc, there is a filtered query you can issue to get just the projects you need, but I don't have the syntax in front of me. Let me know if you really want to use PSI for this instead of the SQL method, and I'll look around for this.

    Hope this helps... James Fraser