Custom Fields and Lookup Tables


In Project Server 2007 the structure of the custom fields has changed and this is causing some confusion both for developers and for users.  This isn't helped by our UI for creating and maintaining lookup tables.  In this posting I intend to give some background information about the structure - along with some of the potentially unexpected things that can happen if you don't understand this structure. 

Custom fields can either be free form (of various datatypes) or based on pre-configured lookup tables.  The latter are the replacement for Outline Codes and it is these that can feed through to the cube as dimensions.  Numeric custom fields can go to the cube as measures.  The custom field when applied to a project (or task, or resource) has a value.  This value may be obvious - such as the actual text entered as the custom field for that project, or may be more obscure - such as a GUID that references a specific value from a lookup table.  It is this last variation I will drill into.

Suppose we want to identify a color as our custom field and have a lookup table with a hierarchy something like this:-

Light Red 23a6b3f1-80f3-4e0b-a494-00881571f4c1
  Green f3b5f8fc-1fce-4323-a84d-17f16408396e
  Blue 887c1fa3-d1c2-4b3a-a661-1a417b8b3d1e
Dark Red 4049aa5f-c030-4dda-98cf-1a4d18fa0b94
  Green c3e5a030-925a-41eb-873f-2af26bd0c4a0
  Blue 95986ff0-d3a0-4ccc-bfae-2afc6bfb80e7

So we can have Light, Blue - or Dark, Blue - or any of the other combinations.  In our UI for the lookup table each of the final leaves is identified by a GUID, and when you select Dark, Green this value in my example is c3e5a030-925a-41eb-873f-2af26bd0c4a0.  So if we use this lookup table value as a custom field then behind the scenes we would identify the actual selected value using this GUID.  Now suppose in the PWA UI for managing the lookup tables we made some changes and moved the structure around so it looked like this:-

Light Red 23a6b3f1-80f3-4e0b-a494-00881571f4c1
  Green c3e5a030-925a-41eb-873f-2af26bd0c4a0
  Blue 887c1fa3-d1c2-4b3a-a661-1a417b8b3d1e
Dark Red 4049aa5f-c030-4dda-98cf-1a4d18fa0b94
  Green f3b5f8fc-1fce-4323-a84d-17f16408396e
  Blue 95986ff0-d3a0-4ccc-bfae-2afc6bfb80e7

So what changed?  Well we didn't mean for anything to change - and in the UI it looks exactly the same as when we started (as we don't see the GUIDS) - but we accidentally moved the Green that was under Dark to be under Light - and the Green that was under Light is now under Dark.  The problem is that as far as our project (or task or resource) is concerned it is associated with the GUID c3e5... so it now relates to Light, Green which probably isn't what we intended.

So please take care when maintaining the lookup tables - don't move items between the different parts of the hierarchy unless this is really what you want.

I will follow this up with more detail on setting custom fields through the PSI - but if you can't wait Christophe did a posting similar to this recently on resource custom fields.  I'll elaborate to help you understand the different types of fields and the different values you will need to set.

Technorati Tags: Project Server 2007


Comments (0)

Skip to main content