Search for a project by enterprise custom field with PSI

[Mike G.]

I need to get a project guid given a value for an enterprise custom field.

For example: let's say I have an enterprise custom field for phone number. I
would need to find the project that has a phone number value of xxx-xxx-xxxx.

I assume both fields are stored in a database table somewhere, and so a
lookup against the table should be very fast.

Using PSI, I can get the project guid for the given phone number with the
following:
1. read the list of custom fields
2. loop through the above list to get the guid of the field for the phone
number field
3. read the list of projects
4. for each project on the list of projects:
a. call the readproject method to access info about each project
b. loop through the ProjectCustomFields, find the phone number field, and
get the value
c. if the phone numbers match, get the project uid and break out of the
loop.

The above works, but is SLOW (especially considering I could have thousands
of phone numbers to match). Certainly I'm overlooking a better way?

 

[Chris Boyd]

Hey,

Take a look at:
http://blogs.msdn.com/project_progr...s-based-off-a-project-custom-field-value.aspx

This app does exactly what you want to do.

--
Chris Boyd
MS Project
Program Manager

Blog: http://blogs.msdn.com/project_programmability/

 

[Mike G.]

Thanks Chris,

Actually, I had already found that project (and stole a little code from
it). My thought is that if I access a database table, I could accomplish my
goal with the following query:

select guidfield from table where phone='555-555-5555'

I would be completely unconcerned about running that query against a table
with 50000 rows. 50000 rows is nothing to a database.

But programatically looping through all projects and calling a web service
method that returns a dataset for each one is considerably slower than the
above sql query.

I'll have ~20000 - 40000 projects. So, I'll have up to 40000(or more) web
service calls to find the project that has a particular phone number (and, I
won't be searching for just one phone number).

Which is why I ask: is there a better way?

Mike

 

[Chris Boyd]

For all the projects that you need to query, are the published? If they are,
then I would query the RDB.

--
Chris Boyd
MS Project
Program Manager

Blog: http://blogs.msdn.com/project_programmability/

 

[Rod Gill]

Hi Mike,

Read from the reporting db the VIEW MSP_EpmProject_Userview

This View not only has all project data, but has the custom fields added to
the end so

SELECT ProjectUID, ProjectName, PhoneNumber FROM MSP_EpmProject_Userview
WHERE PhoneNumber='999999999'

should work a treat!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Mike G.]

Chris and Rod,

Thanks for the suggestion. Unfortunately, in this case the projects could be
in any state, and I can't assume they will be published.

I'll be sure to remember the Reporting Database for future projects, thanks.

Mike