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

Question

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?

Answer 1

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

Answer 2

Using the 2007 version of the PSI, we came across this same problem. We decided to sync all of the custom metadata properties to a SharePoint list. This makes it very easy to query and consume. However, the sync is quite a bit of work to maintain.

I think an alternative would be to query the Reporting database, but I haven't been able to find a good source of information for this.

Answer 3

To load custom fields withouth loading whole project you can use ReadProjectEntities like this ReadProjectEntities(..., 32, ...) where 32 identifies CustomField entities