Reading / writing / updating custom enterprise fields using PSI

[Stephan Steiner]

Hi

While the SDK contains documentation on creating a new lookup EF
field, it unfortunately doesn't touch the most common process dealing
with EFs: reading and writing them.

I'm currently porting over a PDS app to PSI and I'm trying to
replicate the process of writing project enterprise fields (and to
start off just read them from a project).

I managed to get a list of all EFs by using the CustomField service
and calling ReadLookupTables then iterating through the values.. that
gets me the field IDs (the 188.. numbers) and names, and if I see that
a CF uses a lookup table, I managed to list all possible values using
the LookupTable service (with a lot of help from the source code of
ProjTool).

So far so good.. now I'm trying to work with the
ProjectDataSet.ProjectCustomFieldsDataTable..

For starters.. what is the relationship between a row of that table
and a row of CustomFieldDataSet? Which properties should match?
I figure
ProjectDataSet.ProjectCustomFieldsDataTable.CustomFieldsRow.MD_PROP_ID
matches CustomFieldsDataSet.CustomFieldsRow.MD_PROP_ID and the same
goes for MD_PROP_UID in both rows). Is there any other match or
correlation?

Then I figured I'd iterate through all rows in the
ProjectDataSet.ProjectCustomFieldsDataTable.. get the MD_PROP_ID, look
up the corresponding row in the CustomFieldsDataSet, and from the
latter, get the name.

Then comes reading the value.. frmo the row in CustomFieldsDataSet I
can identify the type by looking at
CustomFieldsDataSet.CustomFieldsRow.MD_PROP_TYPE_ENUM which is of type
PSILibrary.CustomField.Type. And knowing that, I can then read out the
appropriate property from
ProjectDataSet.ProjectCustomFieldsDataTable.CustomFieldsRow.

However, it seems that for my test project, I get rows from the
ProjectDataSet.ProjectCustomFieldsDataTable whose MD_PROP_UID I don't
find in CustomFieldsDataSet..

While a ProjectCustomFieldsRow also has a FIELD_TYPE_ENUM, it appears
that it is of type PSDataType and offers a lot more options than
CustomField.Type.. many of which have no corresponding EF type (e.g. I
ran into type COST, or YESNO).. so what do you do with those?

I figured those data sets must have some match to the database and
being very familiar with the 2003 database I went to search for a data
dictionary but it seems it is only published for the reporting DB and
OLAP cube since you're not supposed to use the DB directly anymore..
but now I'm kinda stuck here.

Is there some documentation out there that goes into excruciating
details on the subject of EFs? Specifically going from one of the
datasets I can get from the project service I need to be able to
figure out the name of the ef, as well as it's value, and conversely
write to the appropriate field.

Regards
Stephan

[Stephen Sanderlin]

Check out Brian Smith’s article on Custom Fields: http://blogs.msdn.com/brismith/archive/2007/12/06/setting-custom-field-values-using-the-psi.aspx. It should provide some clarity around custom fields, though probably not as extensive as you’d like.

 

As for what properties to match, the documentation for ProjectDataSet.ProjectCustomFieldsRow.MD_PROP_UID Property (http://msdn.microsoft.com/en-us/library/websvcproject.projectdataset.projectcustomfieldsrow.md_prop_uid.aspx) offers some advice.

 

When figuring out FIELD_TYPE_ENUM, the documentation for Microsoft.Office.Project.Server.Library.CustomField.Type discusses this (http://msdn.microsoft.com/en-us/library/microsoft.office.project.server.library.customfield.type.aspx). Used in conjunction with the documentation for Microsoft.Office.Project.Server.Library.PSDataType (http://msdn.microsoft.com/en-us/library/microsoft.office.project.server.library.psdatatype.aspx), you can match them up. Matching types between the two enumerations have matching values.

 

I would recommend that you retrieve the information concerning the custom field you want to update from the CustomFields web service and then use that information to access the Project data, rather than the other way around, as you seem to be doing. Store the names of the fields in your application settings, and then use an implementation of this code (http://www.epmfaq.com/ssanderlin/project-server-2007/retrieve-the-guid-of-a-custom-field-using-its-name) to retrieve its GUID and other information. Then, use this information to read/update the project.

 

Remember that when determining what to put in the CUSTOM_FIELD_UID field, you need to make sure that a ProjectCustomFieldsRow for the same MD_PROP_UID does not already exist in updateDs.ProjectCustomFields. If a ProjectCustomFieldsRow for that MD_PROP_UID already exists in projectDs.ProjectCustomFields, you'll want to make a copy of projectDs, update that row, and send the updated copy back through QueueUpdate. If it does not, you can add a new row with a new GUID in CUSTOM_FIELD_UID to updateDs using AddProjectCustomFieldsRow() and send it back through QueueUpdate.  

 

-- 

Stephen Sanderlin

Principal Consultant

MSProjectExperts

 

For Project Server Consulting: http://www.msprojectexperts.com

For Project Server Training: http://www.projectservertraining.com

 

Read my blog at: http://www.projectserverhelp.com/

Join the community at: http://forums.epmfaq.com

[Stephan Steiner]

Stephen

 

Thanks for the links and the explanation (I missed that I can match the field types). I already had the link to Brian Smith's article.. in fact it along with ProjTool got me along my task.

 

At this point I'm able to read all my custom fields both on a project level and a task level but it was a reverse engineering task that I don't think should be necessary and there are still fields that I'm unclear about.

 

I'm going to write down what I understand so far.. I guess that makes gaps in my knowlege most visible.

 

Project Custom Fields are contained in the ProjectDataSet.CustomFieldDataSet data set.

 

The definition of the custom fields can be gotten from the CustomFields webservice by calling GetCustomFieldsByEntity where the EntityUid is one of the constants defined in PSLibrary.EntityCollection.Entities.

 

A ProjectCustomFieldsRow and a CustomFieldsRow have certain matching properties which allows to go from one to another:

 

MD_PROP_ID is the good old 188... integer codes we know from Project Server 2003 which uniquely identifies an custom field
MD_PROP_UID is the Guid that uniquely identifies a custom field

 

So, I can go from a ProjectCustomFieldsRow to a CustomFieldsRow by matching MD_PROP_ID or MD_PROP_UID.

 

Looking at CustomFieldsRow .MD_PROP_TYPE_ENUM (or alternatively ProjectCustomFieldsRow.FIELD_TYPE_ENUM)I get which type the field is, and thus can look at the appropriate ProjectCustomFieldsRow.*_VALUE to get the value, and CustomFieldsRow.MD_PROP_NAME to get the field name.

 

If it's a field with a lookup table, then I have CustomFieldsRow.IsMD_LOOKUP_TABLE_UIDNull is false. In that case, I need to use the LookupTable service to get the lookup tables by callingReadLookupTables("", false, 0), then go through the LookupTableTreesRow in LookupTableDataSet and match the CustomFieldsRow.MD_LOOKUP_TABLE_UID to LookupTableTreesRow.LT_UID and ProjectCustomFieldsRow.CODE_VALUE to LookupTableTreesRow.LT_STRUCT_UID... then take the appropriate LookupTableTreesRow.LT_VALUE_* to get the value.

 

So far so good.. so I've basically covered all fields in a ProjectCustomFieldsRow except for CUSTOM_FIELD_UID - I've not been able to match that to anything in my experiments so I'm wondering what it is. According to the documentation it makes me think I should be able to match this to some value in a CustomFieldsRow.. but what does it match to or am I on the wrong track?

 

I'm also wondering about CustomFieldsRow.MD_PROP_UID_SECONDARY and MD_PROP_ID_SECONDARY? Don't we have a MD_PROP_(U)ID match between an AssignmentCustomFieldsRow.MD_PROP_(U)ID and a CustomFieldsRow.MD_PROP_(U)ID to match between the two so I don't really see the point on the secondary field.

 

All thus musing and jumping between the docuemntation and make code makes me wish for a documentation on the undocumented DBs really bad.. I suppose that the data sets would match closely with the DB and thus having a few examples on how things are matched together would make understanding the data sets and rows a lot easier.

 

Regards

Stephan

 

"Stephen Sanderlin" <stephen NS-DOT sanderlin A-NS-T msprojectexperts DOT-NS com> wrote in message news:usNgTIgO...@TK2MSFTNGP02.phx.gbl...

[Stephen Sanderlin]

Stephan,

 

No problem.

 

I’ve found it easier to match with MD_PROP_UID, just because pretty much everything is done with GUIDs now and I like to be consistent.

 

CUSTOM_FIELD_UID is just a unique ID to identify the row.

 

I believe MD_PROP_(U)ID_SECONDARY exists because Assignments don’t really have “their own” custom fields… they inherit their custom fields from the task and resource for which they exist. Therefore, the MD_PROP_(U)ID value for an Assignment custom field corresponds to the MD_PROP_(U)ID_SECONDARY value for a Resource/Task custom field.

 

Hope that helps,

Steve

-- 

Stephen Sanderlin

Principal Consultant

MSProjectExperts

 

For Project Server Consulting: http://www.msprojectexperts.com

For Project Server Training: http://www.projectservertraining.com

 

Read my blog at: http://www.projectserverhelp.com/

Join the community at: http://forums.epmfaq.com

 

 

"Stephan Steiner" <stephan...@nextiraone.ch> wrote in message <news:O2n0CWnO...@TK2MSFTNGP02.phx.gbl>:

[Stephan Steiner]

Stephan

 

Yes this definitely helps. I think between your comments and links and my own tests I now have a rather complete overview of custom fields in Project Server 2007.

 

Regards

Stephan

"Stephen Sanderlin" <stephen NS-DOT sanderlin A-NS-T msprojectexperts DOT-NS com> wrote in message news:e%23HzB%23rOJH...@TK2MSFTNGP03.phx.gbl...

[Stephan Steiner]

Stephen

 

One more thing that came to mind: I'm actually using MD_PROP_UID as well - I use CustomFieldsDataSet.CustomFields.Find_By_PROP_UID rather than looping to get from a ProjectCustomFieldsRow or TaskCustomFieldsRow to the matching CustomFieldsRow.

 

However, I've come across a situation where I have a project custom field but cannot find a match by MD_PROP_UID.... so I have a CF with a value and type, but no name.. isn't that not supposed to happen?

 

Regards

Stephan

"Stephen Sanderlin" <stephen NS-DOT sanderlin A-NS-T msprojectexperts DOT-NS com> wrote in message news:e%23HzB%23rOJH...@TK2MSFTNGP03.phx.gbl...

[Stephen Sanderlin]

This happens when an administrator deletes a custom field definition and the project has it set – I just tested this in my VPC to confirm. If there is no match in MD_PROP_UID between ProjectCustomFieldsRow and CustomFieldsRow, then the custom field is not valid and you can discard it.

-- 

Stephen Sanderlin

Principal Consultant

MSProjectExperts

 

For Project Server Consulting: http://www.msprojectexperts.com

For Project Server Training: http://www.projectservertraining.com

 

Read my blog at: http://www.projectserverhelp.com/

Join the community at: http://forums.epmfaq.com

 

 

"Stephan Steiner" <stephan...@nextiraone.ch> wrote in message <news:OUz0HH1O...@TK2MSFTNGP06.phx.gbl>: