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:-
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
A while back I posted about the backup and restore process and recommended using the SharePoint utility in Central Administration. That is still my preference and I recently used this to rebuild my server farm including 3 servers, 2 shared service providers and a dozen PWA instances covering about 10 different languages. I must admit it did take several attempts and my one big take-away is - try, try, try again until it all works in one go. The process isn't very forgiving if one piece fails, so best to remedy the problem and run the whole restore again. The problems I ran into were services that needed starting (Office search in my case, and the Project application) or databases I'd forgotten to delete. Top tip number two is that if you have a complex restore to do and you are changing machine names, URLs, paths to db files, service accounts etc. it can be very tedious to edit these in the UI each time - particularly for all 12 PWA sites! So a short cut is first to take a backup of the spbackup.xml file, and then edit the original and change the various things that need changing in the XML file. It should be easy to work out what needs editing - especially if you have already been through the restore process a couple of times.
So this restore gets everything back how it was, on either the same server or a different configuration.
What if you only have database backups? What can you do and what do you lose? This is a reasonably common scenario - particularly for people with a 2003 background who are used to working with just the WSS content db and the ProjectServer db. But things have changed - and the main challenge is that the PWA site is now held within the content database. This causes some issues as if you want to move the databases to another server you will need to re-provision this PWA site - and you can't do this against the same content db if it already exists. And if you delete it then everything under /PWA is gone! Which may well include any workspaces for your projects as this is the default location for them. So you have a few options, and we will assume here that you are restoring and re-attaching your main content database to the default web site, and restoring your 4 project server databases.
So what do you still lose by going this route? Fidelity. If you have customized your PWA site through site settings you will lose these changes using any method other than the SharePoint backup/restore. As an example the following site was replicated using option 1 and the highlighted changes (and the colour from the theme) are lost in the restored site pictured below. You do however keep any changes made through Server Settings such as menu edits (My Timecard in place of My Timesheets in the screen shots).
I hope this helps understand what is stored where - and the challenges of moving data between servers, either for migration or disaster recovery.
I missed this posting at the tail end of last month - so just calling it out in case some of you missed it too. Scalable Issues and Risks Report!
If you have lots of workspaces then a behavior you may have seen is the timeout of your PWA homepage, caused by the query bringing back (or at least attempting to bring back) the list of active issues and risks for a user. Patrick Conlan has posted a potential workaround for this which is very cool. It uses the reporting database and the SQL Reporting Services web part to give a very elegant alternative to the existing web part. This isn't meant to be a replacement in all situations - and please note the caveat on the posting that this isn't something that will have an migration/upgrade path to future products; should be fully tested in pre-production and will not be fully supported by Microsoft.
This suggested replacement really shows how the reporting database can be the best place to query for this kind of information.
The full article can be found here - also look at some of the recent great postings including details of developer training for Project Server 2007.
These short video clips are just over 10 minutes each and support the SDK articles Walkthrough: Creating a Custom Project Server Web Part and How to: Write and Debug a Project Server Event Handler. Both of these videos show Visual Basic being used - and the articles show C# for both and also VB for the event handler example. They can be found at the following links:-
Creating Custom Web Parts for Project Server 2007
Writing and Debugging Event Handlers for Project Server 2007.
A couple of topics that have come up through support incidents and comments posted here. The first on the use of the filter or xmlFilter parameters available in a few of the web services, and the second talking about event handlers and what happens if you fire an event based on Check-In that then issues a check-in - how do you break the loop?
The SDK details how the xmlFilter parameter can be used to filter the returned dataset and the sample code covers the CustomFields dataset. However there are a couple of limitations that you should be aware of. You can use the PSLibrary.Filter.Fields.Add to control the columns returned in the dataset and PSLibrary.Filter.Criteria to control the rows. The criteria can only be used against the main tables of the dataset and cannot be used to filter rows in the secondary datasets. For example the Resource web service has the ResourceDataSet which contains the ResourcesDataTable as the main datatable and also several others including the ResourceAvailabilityDataTable. Only rows in the ResourcesDataTable can be filtered using criteria. You will get a ResourceFilterInvalid Exception (or CalendarFilterInvalid exception if you try to use criteria on CalendarExceptionsDataTable of the Calendar web service). This should be fairly obvious as the construction for the criteria does not allow for a specific DataTable to be referenced. The other limitation is less obvious and will be corrected in our documentation shortly. The ResourceCustomFieldsDataTable cannot be filtered at all using the filter parameter - you will get a ResourceFilterInvalid exception. In both cases you can of course use filter on the client once you have the full dataset returned. Another option would be to create your own PSIExtension that does the extra filter and keep everything server side.
Recursive Event Handlers
I addressed this question from a comment but might be useful for a full posting. So if as part of your workflow you have an event handler for check-in, and when it fires you need to update something in the project then you will also need to check-in when you have finished, which will then fire the event handler and so on. The best way I could think of to break the circle is to set a specific string as the session description when you use the check-in in your event handler. Then you can check in your code before checking out if the sessions description in the project dataset is the one for your event handler - or a user session description which will be of the format machinename\pwa_account. If it is your setting then no need to continue. If anyone has a better way then please share.