1

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?

ptrandem
  • 300
  • 5
  • 10

3 Answers3

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

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • I need to do this exact thing and it must be via the PSI. If you have details on using a filtered query I'd appreciate it. My attempts have been unsuccessful. – Mike Aug 19 '15 at 19:52
0

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.

Kit Menke
  • 7,046
  • 1
  • 32
  • 54
  • Hey Kit, thanks for your answer. I am not clear on what a sharepoint list is (total newb to sharepoint and project both, and I've been learning a lot to get even this far). If you can enlighten me, I would appreciate it. – ptrandem Feb 13 '11 at 04:23
  • If it works the same way (I've only used 2007), your PWA site is a SharePoint site. MS Project uses SharePoint to host the PWA end user interface. Within that SharePoint PWA site you can create the normal SharePoint objects: sites, document libraries, lists, calendars, and more. I should also add that I asked a similar question a while ago: http://stackoverflow.com/questions/2610579/use-sharepoint-search-to-crawl-project-server-project-metadata – Kit Menke Feb 13 '11 at 04:39
  • Right on, I see what your saying. I can see how that would work. I was hoping for something more direct to the custom tables so we won't have to explicitly manage keys and values. Currently, to speed things up I've switched to pre-populating a Dictionary for the duration of the processing session, but that is not the best solution either. – ptrandem Feb 15 '11 at 16:24
0

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

Nebojsa Popovic
  • 1
  • The problem with this is that even if you have just the GUID of the projects you still have to make the request for each one. fine if you have 5 projects. A problem if you have > 500 and you need the data for a web application. – QueueHammer Jun 10 '13 at 18:10