Internal name vs display name

Anyone that’s done a reasonable amount of work with the SharePoint object model will soon come across its flaws. The one that I regularly come up against is when working with the SPField object that represents list columns.

There are inconsistencies throughout the object model when attempting to retrieve an instance of an SPField, its value, or perform various other operations. In some cases the display name is required, or the internal name, or both! Even worse, rarely does the SDK document what is required to find your field. Unpredictable behaviour can also result in the cases where it checks for display name and internal name, because what if you have two fields with the same display name?!

Here’s just a few frustrating examples:

  • SPFieldCollection.ContainsField: Internal name or display name
  • SPFieldCollection.Item[string]: Display name
  • SPViewFieldCollection.Exists: Internal name

Tom, another SharePoint developer, has made a list with more examples here. He also details the inconsistencies around when an exception is thrown – yet another place to get caught out.

One of the most important things in an API is consistency so it behaves like you would expect. I’m sure how Microsoft can fix this without introducing breaking changes so maybe we will have to live with this for a long time yet. If in doubt, as always, Reflector is your friend.

Posted in SharePoint | Tagged , | 3 Comments

Linking tasks to issues, risks, documents, and more…

In Project Server 2003 a feature was introduced to link issues, risks and documents to tasks. It’s been carried over to 2007 as well (although the UI for it isn’t as noticeable) and I think it’s one of the coolest yet underrated features in the web interface. Let me take you through it… (Note: you will need the Create Object Link category permission to use this feature.)

Every project workspace out of the box comes with the Project Documents, Risks and Issues lists. On the add and edit pages of these lists, you can find “Link Items”:

Clicking Link Items while creating or editing an issue, for example, allows the issue to be linked to any task in the project. Also, this linking can have several meanings, such as whether the issue simply relates to the task, affects the task, or whether the task resolves the issue. Here is the dialog:

You’ll notice the drop-down at the top that reads Project Tasks. This can be changed so that this issue is linked to any risk, document or other issue as well! (Note there is no additional ‘meaning’ unless linking to a task.)

Upon clicking OK on this dialog box, a nice little summary is displayed of the link options chosen:

Of course, the issues list is just an example here. It is also possible to link items from the other lists previously mentioned. Here are the other dialog boxes shown…

Risk:

Document:

Linking’s cool, but now what?

Obviously whenever you look at a linked object you will be able to see and click on what each object is linked to which helps to describe the relations within your project, not to mention handy as well. Some examples are linking a document to a task for its delivery, or linking a risk item to a risky task! Also very useful is that the link gets propagated to the reporting database when the workspace’s project is next published, so some neat queries can be written to relate your data together.

What’s even better is what you don’t see. This functionality is powered by the Object Link Provider, which is described in some detail within the Project 2007 SDK. With some custom development work, it is possible to link tasks or project workspace items in other lists to any object you can reach with a URL. Imagine the possibilities – linking to an item in the company’s financial system, for example! This can all be done with the ObjectLinkProvider web service (examine the SDK for more details).

A few cautionary words

As you may have already encountered, some functionality in Project Server can break when you start altering project workspaces. This is even though it does nothing to stop you and virtually every customer/user has a requirement to. The Link Items feature is no exception here. If you delete the Links column from a workspace, there is no way I’ve found to put it back (this is probably because it is not a built-in SharePoint field type). Instead, try setting it to Hidden within the content type! In fact, hiding the field in the list’s content type should be done for every workspace field you don’t want to use.

Thanks for reading

Hopefully this provides a deeper look at a feature that doesn’t seem to get mentioned much. If you have good uses for “Link Items”, please add a few comments to this post!

Posted in Project Server | Tagged , | 9 Comments

SharePoint and SQL schemas

I’m just under two weeks away from installing the infrastructure update to a production environment. But of course these things need testing first so I started the install on the development environment. Thing’s went hairy for a while…

First I must admit I’m not running the update as the service account, recommended by Microsoft. This is because I’m working in a locked down environment where trying to get the permission to do this requires a lot of questions, moaning and red tape. However both my account and the service account (there’s only one service account in use here) are local administrators.

The log file pointed to by the SharePoint Configuration Wizard contains this ever so helpful line: “An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown. Additional exception information: Failed to upgrade SharePoint Products and Technologies.” However, with a little more digging there are details located at C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS. (This isn’t actually the folder where I have directed diagnostic logs to, but for the purposes of an upgrade it seems SharePoint sometimes redirects it!) The logs contain errors over the several times I tried to upgrade…

Upgrade.log:

  • SqlException: The default schema does not exist.
  • SqlException: Cannot find the object ‘MFN_Epm_GetAllCustomFieldsInformation’, because it does not exist or you do not have permission.
  • SqlException: Cannot drop the function ‘dbo.MFN_Epm_GetAllCustomFieldsInformation’, because it does not exist or you do not have permission.

Then in the ULS logs:

  • The schema version (3.1.4.0) of the database SharePoint_AdminContent on server_x is not consistent with the expected database schema version (3.1.5.0) on server_x. Connections to this database from this server have been blocked to avoid data loss. Upgrade the web front end or the content database to ensure that these versions match.
  • SqlException: Cannot find the user ‘DOMAIN\myusername’, because it does not exist or you do not have permission.
  • Queue unable to interact with SQL. Queue type (Project Queue, Timesheet Queue etc): ProjectQ Exception: Microsoft.Office.Project.Server.BusinessLayer.Queue.QueueSqlException: PeriodicTasks failed —> System.Data.SqlClient.SqlException: Invalid object name ‘MSP_QUEUE_RENDEZVOUS’.

It appears that I don’t have permission to the databases. However, my account is db_owner on every database The service account permissions haven’t been changed. What’s going on?!! It turns out the root of the cause was described this exception: The default schema does not exist.

I don’t know a great deal about the schema feature introduced with SQL 2005, however with every user added to a SQL 2005 box, an accompanying schema with the same name is also created. A colleague of mine who was doing some spring cleaning on our databases removed the schema for our service account, however the service account’s SQL user was still trying to use it as its default schema! The solution was to change the schema to dbo.

Unfortunately the upgrade still didn’t quite complete since I was part of the way through the upgrade when it fell over… I also had to create a fake MFN_Epm_GetAllCustomFieldsInformation function in the Reporting database so the upgrade could find and drop it (fortunately replaced with the new version in the upgrade).

It was a big relief that this ended up working. The downside is that my user account and a schema for it have been created on the database server. It can’t be removed right now because the new database objects that come with the update have my schema set as their owner! Now I have to change them all…

Posted in Project Server, SharePoint | Tagged , , , | 4 Comments

Project Server ItemSwapper control

ItemSwapper hasn’t been documented by Microsoft so its behaviour may change between service packs – use at your own risk!

With the 2007 version, Project Server is now a SharePoint application. One of the best things about this is the nifty stuff you get out of the box as part of SP. Custom controls are a highlight, of which there are many that can be used in your custom Project Server solutions. Even better is that Project Server 2007 comes with some cool controls of its own which you can use in any solution running within Project Server. My latest discovery is the handy ItemSwapper.

ItemSwapper

ItemSwapper

This might look familiar to you from popular administration pages such as “Manage Queue” or “Manage Groups”. Just by popping one of these on your page, you get a pretty flawless interface to selecting projects. It’s also speedy and has some great features both client and server side. It’s great to use in a web part used for selecting report parameters, so that’s what we’re going to build in this post!

First of all, let’s have a look at two other custom controls used by ItemSwapper. There is the ItemSwapperLabel (extended from System.Web.UI.WebControls.Label) which renders the text above each list, and ItemSwapperSelect (extended from System.Web.UI.HtmlControls.HtmlSelect) which renders the list itself. Data binding is done with ItemSwapperSelect and the ‘parent’ ItemSwapper control renders all of these controls for you.

The ItemSwapper control refers to the two select lists as the ‘alpha’ and ‘beta’ lists (originally designed for Greek Project Server?). In our example the beta list will be blank for us to add projects into, so we only want to initalise the alpha list. Looking at the code, our CreateChildControls method contains:
[sourcecode language='csharp']protected override void CreateChildControls()
{
alphaSelect = new ItemSwapperSelect();
alphaSelect.Multiple = true;

projectSwapper = new ItemSwapper();
projectSwapper.ID = “projectSwapper”;
projectSwapper.TrackBetaListChanges = true;
projectSwapper.AlphaSelect = alphaSelect;
projectSwapper.LblAlphaList.Text = “Available Projects”;
projectSwapper.LblBetaList.Text = “Selected Projects”;
projectSwapper.DisplaySelectedItemText = false;
projectSwapper.ShowBetaUpDownButtons = true;
projectSwapper.ShowRestoreAllButton = true;
projectSwapper.ShowRemoveAllButton = true;
this.Controls.Add(projectSwapper);
}[/sourcecode]

This should be pretty self-explanatory (we will discuss the ID and TrackBetaListChanges properties later in this post). Now let’s perform a data bind in our OnLoad method:
[sourcecode language='csharp']protected override void OnLoad(EventArgs e)
{
if (!Page.IsPostBack)
{
DataTable projects = DataLayer.GetProjects();
projectSwapper.DataSource = projects;
projectSwapper.DataTextField = “Name”;
projectSwapper.DataValueField = “Uid”;
}
}[/sourcecode]

This will now render fine, but the finishing touch is a workaround to what I would call a bug. If you set ShowRestoreAllButton to true, this will display the “Add All” button. However it is important to know that this button is greyed out unless the user has selected an item first, meaning that will need two clicks to add all projects to the other list. I don’t know why Microsoft would have done this, but to get around it, add the following code to our OnPreRender method:
[sourcecode language='csharp']protected override void OnPreRender(EventArgs e)
{
alphaSelect.SelectedIndex = 0;
}[/sourcecode]
Have a look at the work so far as the ItemSwapper should now render!

Now let’s make use of the items chosen in the lists. First of all we’ll need a button in our web part for users to click on and start the report generation. We’ll also need at least one hidden field that will contain the selected items for us to use on the server side. Note that there is quite a bit of data we can get from the ItemSwapper JavaScript object, such as a count of items for both lists, text labels of chosen items for both lists, and values (e.g. project GUIDs) of chosen items for both lists!

To be able to do retrieve the data on the client side there are two key properties we need to set when creating the ItemSwapper which I touched upon earlier: ID and TrackBetaListChanges (or if you care about the alpha list, TrackAlphaListChanges). We need to use JavaScript to get a reference to the ItemSwapper object and setting ID lets us use document.getElementByID() to do this. (Surprisingly and fortunately, ID doesn’t get mangled in usual SharePoint fashion when rendered.) Setting TrackBetaListChanges sets a property on the JavaScript object to, well, track beta list changes.

So going back to CreateChildControls(), let’s add the following code and connect our server code to the client:
[sourcecode language='csharp']chosenProjectValues = new HiddenField();
this.Controls.Add(chosenProjectValues);

createButton = new Button();
createButton.Text = “Create”;
createButton.Click += CreateButton_Click; // server-side processing done in this method
createButton.OnClientClick = String.Format(“SaveItemSwapperValues(‘{0}’, ‘{1}’);”, projectSwapper.ID, chosenProjectValues.ClientID);
this.Controls.Add(createButton);[/sourcecode]

Everything’s ready now on the server side. We just need to create the SaveItemSwapperValues JavaScript function. Put this JavaScript wherever is standard for your projects.
[sourcecode language='jscript']function SaveItemSwapperValues(itemSwapperClientID, chosenValuesClientID)
{
var chosenValuesField = document.getElementById(chosenValuesClientID);
var itemSwapper = document.getElementById(itemSwapperClientID);
chosenValuesField.value = itemSwapper.GetBetaListValuesAsString();
}[/sourcecode]

That’s it! Now when your server-code hits CreateButton_Click(), you should find a nice comma-separated list of project GUIDs in the Value property of your hidden field that can be used to generate reports!

But it’s nice to take things a bit further… Our Create button is always active, even when no projects are in the chosen list. Well then, behold! The ItemSwapper contains JavaScript event handling functionality! Add script that runs on startup such as this:
[sourcecode language='jscript']var itemSwapper = document.getElementById(‘projectSwapper’); // ‘projectSwapper’ should be passed as a parameter and not hard-coded
itemSwapper.OnAlphaListChange = ChosenProjectsChanged;
itemSwapper.OnBetaListChange = ChosenProjectsChanged;[/sourcecode]

Then add the following with the SaveItemSwapperValues function:
[sourcecode language='jscript']function ChosenProjectsChanged()
{
var itemSwapper = document.getElementById(‘projectSwapper’);
var createButton = /* how you get this reference depends on your environment */
if (itemSwapper.betaListItemCount == 0)
{
createButton.disabled = true;
}
else
{
createButton.disabled = false;
}
}[/sourcecode]

Now we’re talkin’ – a smart ItemSwapper. And that’s not all, there is heaps of handy JavaScript goodness you can hook on to if you’re able to navigate to this file and have a look: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\PWA\LIBRARY\ItemSwapper.htc.

Last but not least, none of the ItemSwapper controls are sealed so you are free to extend them with your own functionality!

Posted in Project Server | Tagged , , | 4 Comments

Intro

Hi there! I’m Alex, working on SharePoint and Project Server and based in London. I’ve been working with SharePoint and Project Server since the 2003 versions.

This blog is intended to provide discoveries and thoughts from working with these technologies. I hope you find it useful.

Posted in Uncategorized | Leave a comment