Enterprise Custom Fields from VBA
I got an interesting question the other day. The poster wanted to access enterprise custom fields from VBA, not the PSI. After my recovery from someone not wanting to use the PSI and that I needed to write some VB (just kidding) I started to look for a solution.
First thing to think about is that enterprise custom fields are now GUID based, no more pjTaskEnterpriseProjectText21. When I wanted to get custom field information before I would use the GetField method, and pass in the appropriate constant. I looked at GetField, it’s still there, but the signature hasn’t changed??? I was perplexed. I went through the new calls available in the API and nothing really rang a bell for displaying custom fields. I asked a few people who said they thought you couldn’t access enterprise custom fields from VBA, you’d be required to go to PSI. Well that was half true.
In an earlier post I mentioned that the custom fields had been moved to the server. No more opening the enterprise global and adding new enterprise fields. That was the half true portion, no more changing custom field definitions from the client. The half false portion is that you can access the custom field values from the client (and therefore VBA). I actually needed to ask the product PM for confirmation, and he gave me the clue I needed (thanks Luke). The key is a new method on the application object called FieldNameToFieldConstant, the signature is as follows:
expression.FieldNameToFieldConstant(FieldName, FieldType)
Return Value
PjField
expression A variable that represents an Application object.
Parameters
|
Name |
Required/Optional |
Data Type |
Description |
|
FieldName |
Required |
String |
The name of the field whose constant is required. |
|
FieldType |
Optional |
Long. |
The type of field. Can be one of the following PjFieldType constants: pjResource or pjTask. The default value is pjTask. |
The call returns a pjfield value that can be used to specify the enterprise field you are looking for. Say I wanted to get a project enterprise custom field called “Project Lifecycle” The code in VBA would look like the following:
LifeCycleName = ActiveProject.ProjectSummaryTask.SetField(FieldNameToFieldConstant("Project Lifecycle"))
Since ProjectSummaryTask is technically a task, you don’t need to specify a field type, pjTask is the default. How about if we wanted to get a resource enterprise custom field called “Team Name”. The code in VBA would look like the following:
TeamName = ActiveProject.Resources(3).SetField(FieldNameToFieldConstant("Team Name", pjResource))
Notice now we have to specify the FieldType of pjResource. We have gotten the values of custom fields, how do we change them? Same as before using the SetFields method, but apply the FieldNameToFieldConstant trick. Changing the “Project Lifecycle” would give us:
Call ActiveProject.ProjectSummaryTask.SetField(FieldNameToFieldConstant("Project Lifecycle"), "Project Lifecycle.Product Development")
Remember if you have lookup tables associated with the custom fields to pay close attention to the spelling and dot notation, or you’ll get a failure that can be hard to track down. Also pjProject has been added to the pjFieldTypes enumeration. I’m not quite sure where it’s used, but it doesn’t seem applicable here.
