SharePoint Lookup Column Based on a Calculated Column Woes

I’ve been working on trying to improve the performance of some DVWPs I wrote about a year ago as part of an application that I inherrited from others. (Was that tactful enough?) One of the things that I’ve been trying to do is to filter on the LookupId of a Lookup column, but I couldn’t for the life of me get it to work. I’ve done this dozens of times, but in this case I was getting nowhere.

I finally figured it out. The Lookup column is based on a Calculated column, which is itself built out of two strings which are concatenated. Here’s a simple example.  If the reference list looks like this:

ID	ColumnA	ColumnB	CalcColumn
1	Blue	Cow	Blue – Cow
2	Red	Sheep	Red – Sheep

where CalcColumn is calculated as =[ColumnA]&” – “&[ColumnB], and you create a Lookup column in another list which uses CalcColumn for its values, you’ll see this in the values:

string;#Blue – Cow
string;#Red – Sheep

where you would expect:

1;#Blue – Cow
2;#Red – Sheep

Therefore, you can’t get at the LookupId of the Lookup column because it isn’t available.  (See my post on Using the ID Value for a Lookup Column for how this *ought* to work.)

Moral of the story: Don’t use a calculated column for your Lookup column source.  Now on to some list rebuilding…

Redirect to Another Page from NewForm.aspx with the New Item’s ID: Redux

UPDATE 2009-10-19: I solved this for real in the jQuery Library for SharePoint Web Services function called $().SPServices.SPRedirectWithID.  Feel free to read this post, but this is the solution you’re looking for.

The most visited post on my blog, hands down, is the one on Redirect to Another Page from NewForm.aspx with the New Item’s ID.  (Oddly enough, the one about Harvey Balls for Office Documents is right up there, too.)

I never liked the solution that much, as it seems sort of messy, but it works.  Over the last few days, I’ve been trying to figure out a way to take advantage of the SharePoint Web Services which we’ve “wrapped” in the jQuery Library for SharePoint Web Services to come up with a cleaner approach.  Unfortunately, I’m still stumped.

The basic issue is this: until you commit the item which you are creating, there is no ID for it.  There’s nothing in NewForm.aspx to hook into to grab that new ID because it doesn’t exist yet.  This makes chaining pages together in a wizard-like way sort of kludgy, at least from NewForm.aspx to the next page in the chain.

Right now, I have a jQuery function for the library called $().SPServices.SPGetLastItemId in the lab which returns the latest ID for a given user on a given list.  This is a bit cleaner than the DVWP approach, as you can just drop a call into your interstitial page like this:

var thisID = $().SPServices.SPGetLastItemId({
    webURL: "/Demos",
    listName: "Sales Opportunities"
});

The function uses two Web Services calls to get the latest item’s ID.  First, it calls the GetUserInfo operation of the Users and Groups Web Service to find out the user’s ID based on the user’s account (either specified in the options or as returned by the $().SPServices.SPGetCurrentUser() function).  Next it calls the GetListItems operation of the Lists Web Service to find the latest item created for that user.

But you still need to have that interstitial page, which is what I don’t like in the first place!  Before I make this function a part of the library, I want to think some more about how we can improve upon this method.

If you have any ideas on how to get this to work better, drop me a line.  I’d love to offer up a better solution!

List Filtering: Coding vs. Native Functionality Considerations

Over in the MSDN SharePoint – Design and Customization Forum, I was answering some questions about how to best provide filtering capabilities on a List View Web Part.   McGeeky wanted to put filtering dropdowns at the top of the page to "improve the user experience".

My stock answer to this is to user Data View Web Parts (DVWPs):

As for the linking, I would forego Web Part Connections, as I’ve seen too many people have issues with them.  Simply add a "Go" (or "Submit" or whatever) button which gathers up the filter values and passes them back to the same page on the Query String.  Then have your "main" DVWP filter based on those values.  You may need to write a little XSL to make the filtering work correctly.  Once you dive in and see how much you can do with DVWPs, you may want to, anyway!  ;=)
I understand on the usability question for the column headers.  Of course, you’re trading training for coding.  That and the fact that everywhere else there is a list displayed in SharePoint, the filtering is available on the headers (if it is enabled in that instance).  This means that the users won’t know about this great capability unless they stumble on it.  It might be worth explaining this to your customer a little.

And that second paragraph is what I want to expand on a little in this post. SharePoint provides a wonderfully consistent user interface.  In that interface, most List View Web Parts have filtering and sorting capabilities exposed in their headers, if enabled.  My point above was that, just because the "customer" wanted it to work differently, it wasn’t necessarily a good idea.  The coding vs. training trade-off can actually be very expensive in the long run if your users don’t understand the native, pervasive functionality of SharePoint.

If you read the whole thread, it turned out that this was for an outwardly-facing customer site, so creating more obvious filtering mechanisms makes total sense.  But when you have a "customer", whether internal or external who wants to develop fancier functionality on top of or to replace the SharePoint native functionality, it’s a good idea to have the coding vs. training discussion early.

Don’t get me wrong: I love doing this type of stuff with DVWPs.  Sometimes, though, the cheaper up-front answer (not coding something new) may be the right long term answer as well.

Odd SharePoint Designer Custom Form Behavior

@workerthread tweeted this today:

Really interested to hear if you can reproduce weird #SharePoint Designer custom form behaviour detailed here http://bit.ly/60Tko

I ran through the steps Derek outlines, and I do indeed see the odd behavior that he reports.  I’ve never seen this before because this isn’t the usual sequence I go through to create my custom forms.  My usual method is this:

1. Create a copy of the default form
2. Open the copy and either delete the original LFWP or convert it to XSLT.  I usually delete it.
3. If I haven’t converted to XSLT, I add a new Data View: Data View / Insert Data View from the top menu

While everyone seems to say that you MUST leave the default LFWP on the page and hide it, I never do this, and I’ve never seen any adverse effects.  (This is one for the SharePoint Myths series.)

Filtering in SharePoint DVWPs with Multiple Lists in the Data Source

Another question from my contact form:

Hi Marc.

My problem in short is to get a filters to work on columns that holds values from linked tables. For instance I have a column with

xsl:value-of select="../../../Products/Rows/Row[@Title=current()/@ProductID]/@ProductGroup"

Am at a loss for a way to do it at this point. A hint would be greatly appreciated.

And my response…

I’d build the filtering mechanisms separately from the DVWP which displays the items. You can use any controls you like, but I usually use DVWPs. In those DVWPs, post back to the same page based on the user selections with the filter values on the Query String, something like this:

http://servername/sitepath/default.aspx?ProductID=X&ProductGroup=Y

Then in your DVWP below, add filtering logic into your row selects, like this:

<xsl:variable name="Rows" select="/dsQueryResponse/primaryList/Rows/Row[
    (@ProductID = $ProductID or string-length($ProductID) = 0) and
    (@ProductGroup = $ProductGroup or string-length($ProductGroup) = 0)
  ]"/>

At this point, you’re doing all of the filtering in your XSL yourself. One of the problems with the mumbo jumbo code that SharePoint Designer creates for filtering on column headers is that it is pretty difficult to alter. Keep in mind that SPD is essentially just a code generator and it has to leave "hooks" in for the types of things the you *might* ask it to do later. One caveat: once you start writing your own XSL, don’t ask SPD to do things through the dialogs, as it may well break things on you. (Not all dialogs will cause problems, but until you are very familiar with what each choice generates in the XSL, this is the best rule to follow.)

jQuery Library for SharePoint Web Services v0.2.10 Released

Unfortunately quickly followed by 0.2.10a to fix a quick bug.  But at least it was a quick fix, and driven by someone called mwhite01 trying to actually use SPDisplayRelatedInfo to solve a business requirement.

In this release, the main new things are a new function called $().SPServices.SPDebugXMLHttpResult and a slew of new Web Service operations.  SPDebugXMLHttpResult is a function you can call to help with your debugging.  It will output in a [hopefully] organized manner the details of the XMLHttpRequest which is returned from any Web Service call.  We needed something like this for our debugging and testing, and it seemed to make sense to make it a part of the library.  You could even use it for debugging as you work with other Web Services which we don’t support.

The majority of the new operations are User and Groups operations.  I know that in a lot of the Data View Web Parts (DVWPs) that I have built, it would have been useful to know what groups someone is in, what site permissions look like, etc.

We also did some reorganizing of the documentation.  No software is worth anything without good documentation, and we’d like ours to be as simple, clear, and understandable as possible.  Let us know where we’ve missed the mark!

Once again, for posterity, here are the release notes:

Release Notes

NOTE: jquery.SPServices-0.2.10a.js contains a fix for a bug in SPDisplayRelatedInfo which occurs if columnName contains a space. Thanks to mwhite01 for spotting the bug. And sorry to mwhite01 for publishing the bug in the first place.

New Functions

$().SPServices.SPDebugXMLHttpResult
This function displays the XMLHttpResult from an Ajax call formatted for easy debugging. You can call it manually as part of your completefunc].

New Operations

See $().SPServices for the list of new operations. They are marked with version 0.2.10 in the Introduced column.

 

Bug Fixes

Cascade dropdown won’t work with & symbol

Roll Up Blog Posts in a Site Collection

I got a quick question today from a client asking how to roll up blog posts.  He wanted to display the latest blog posts from all of the blogs which executives have in the Site Collection, which is used as the organization’s Intranet.

He had the idea right, using a Data View Web Part (DVWP) with the DataSourceMode=”Crosslist” and the Webs Scope=”SiteCollection”.  However, he got trapped by a cut and paste gotcha.

Since he wanted to only show the latest blog posts, he needed to set the correct value for ServerTemplate.  The CAML he had copied had <Lists ServerTemplate=’104′></Lists>, which specifies lists which are based on the ‘Announcements list’ template.  The value for the ‘Blog posts’ template is <Lists ServerTemplate=’301′></Lists>.

The possible values for ServerTemplate can be found in this MSDN support article, and here they are listed for ease of use:

• 100   Generic list (this means any Custom List)
• 101   Document library
• 102   Survey
• 103   Links list
• 104   Announcements list
• 105   Contacts list
• 106   Events list
• 107   Tasks list
• 108   Discussion board
• 109   Picture library
• 110   Data sources
• 111   Site template gallery
• 112   User Information list
• 113   Web Part gallery
• 114   List template gallery
• 115   XML Form library
• 116   Master pages gallery
• 117   No-Code Workflows
• 118   Custom Workflow Process
• 119   Wiki Page library
• 120   Custom grid for a list
• 130   Data Connection library
• 140   Workflow History
• 150   Gantt Tasks list
• 200   Meeting Series list
• 201   Meeting Agenda list
• 202   Meeting Attendees list
• 204   Meeting Decisions list
• 207   Meeting Objectives list
• 210   Meeting text box
• 211   Meeting Things To Bring list
• 212   Meeting Workspace Pages list
• 301   Blog Posts list
• 302   Blog Comments list
• 303   Blog Categories list
• 1100   Issue tracking
• 1200   Administrator tasks list

Preserving the Existing Query String When Building a Link in a DVWP

Another question came to me in email today that I thought was worth sharing, along with my answer.  The email referred to my post about Alpha Selection of List Items in a Data View Web Part (DVWP), but it’s relevant to many Middle Tier development tasks.

I hope this email comes to you in good spirits! I was messing around with your alpha selection and it works great except I have 1 more stipulation. I filter a dvwp by URL parameter for instance:

www.mysite.com/site/Pages/thispage.aspx?Type=Type1 OR

www.mysite.com/site/Pages/thispage.aspx?Type=Type2

And then I would like to be able to them choose the A-Z and when it filters it will keep the original URL, but as of right now it will drop the Type=Type1/Type2 and while it filters ‘A’ it filters ‘A’ for both Type1 and Type2. Is there a way that I can allow it to keep the Type parameter then add the Letters parameter to the URL for it to filter further?

Sure you can!  You can just parse out the existing Query String parameters and add them back onto the URL when you build it up.  If you set up a new parameter like so:

<ParameterBinding Name="QUERY_STRING" Location="ServerVariable(QUERY_STRING)" DefaultValue=""/>

then you have the existing Query String parameters available to you.  You’ll just want to be sure to strip out the parameters you want to add if they are already there, something like this:

<a href=”{$URL}?{substring-before($QUERY_STRING, ‘&amp;Letter’)}&amp;Letter={$ThisLetter}”>

(This simple example assumes that the Letter parameter will always be there.)

Two SharePoint Designer DVWP Best Practices

I find myself retyping these two of my DVWP best practices over and over again in some form or another in the MSDN SharePoint – Design and Customization forum, so I thought I’d capture them here for posterity.

SharePoint Designer Generates Errors

It’s not unusual when you are making quite a few changes to a DVWP in SharePoint Designer that Designer itself causes errors.  Usually it’s one of the the dreaded "non-specific errors" which basically tells you that "you just did something wrong" (but nothing else useful).  The key trick here is to use Ctrl-Z to get back to the last working state.

Depending on the number and complexity of changes that you make, SharePoint Designer can "get lost".  It’s basically a code generator, and especially if you’ve made changes to the code yourself, Designer can cause errors.

When things like this happen, you’re often best served by simply editing the code yourself.  My rule is that if I have made a change to the code directly, I give up on using the dialogs altogether, as the odds of using them causing a problem is very high.

Web Part Connections Don’t Do What You Want

Web Parts linked with Web Part Connections, by default, tend to show all items; the filtering occurs once you’ve made a choice.  At least in the forum, almost no one wants this to be the case with their DVWPs.

What I usually do is build my own connections by passing values on the Query String back to the same page and filtering based on those values.  It’s more controllable, especially if the functionality of your DVWP is likely to expand (and when doesn’t it?),  and has the added benefit of allowing the user to bookmark what they are really seeing rather than the default starting point for the page.

Show Numbers with a Different Locale in a SharePoint DVWP

Here’s a great little trick from an MSDN Forum thread I was trying to help out in.  Thanks to Playerkiller for this one.

Suppose you’d like just a single DVWP to display numbers (either currency or regular numbers) in a different locale than the host site.  This trick lets you do just that.

Prerequisites: Numbers are shown with US notation so: 1000.00

1. Open the page you need to customize with Sharepoint Designer. Switch to code view. Perform a search to find the tag xsl:decimal-format. Change the whole line with:

<xsl:decimal-format name=”European” decimal-separator=”,” grouping-separator=”.” minus-sign=”-” NaN=”error” />

2. Now, locate your <xsl:value-of select=”@URENBESTDEZPER”/> and replace it as follows:

<xsl:value-of select=”format-number(@URENBESTDEZPER, ‘€#.###,00;-€#.###,00′, ‘European’)”/>

You should have replaced your view. ;)

(I made a few small edits, but the work was all Playerkiller’s.)