Thursday, April 16, 2009

SharePoint Data Zoom: Handling Complex List Field Types

The free SharePoint Data Zoom Web Part gives you the power and flexibility of creating SharePoint apps from .NET code, but with a much quicker result and without the hassles of coding, compiling and deploying your solution.
As a case and point, let’s take a look at the $SPFields context item. We’ve added $SPFields to provide better support for “hard to access” SharePoint list fields like Look-up, User, URL, Multi-Column, Multi-Choice and Rating Scale fields. To illustrate, let’s look at an example:
 1## Get the list
 2#set( $myLinksList = $web.lists.get_item("DOT Links") )
 4#foreach($myLink in $myLinksList.Items)
 6 ## Get hard to read SharePoint fields
 7 #set( $urlValue = $SPFields.GetUrlValue($myLink.get_item("URL")) )
 8 #set( $stateValue = $SPFields.GetLookupValue($myLink.get_item("State")) )
 9 #set( $ownerValue = $SPFields.GetUserValue($web, $myLink.get_item("Owner")) )
11 #beforeall
12   <table class="ms-listviewtable" cellpadding="3" cellspacing="0" border="0" width="100%">
13     <tr class="ms-viewheadertr">
14       <th class="ms-vh2-nofilter">Title</th>
15       <th class="ms-vh2-nofilter">State</th>
16       <th class="ms-vh2-nofilter">Owner</th>
17     </tr>
18 #odd
19     <tr class="">
20 #even
21     <tr class="ms-alternating">
22 #each
23       <td class="ms-vb2"><a href="$urlValue.URL">$urlValue.Description</a></td>
24       <td class="ms-vb2">$stateValue.LookupValue</td>
25       <td class="ms-vb2">$ownerValue.LookupValue</td>
26 #after
27     </tr>
28 #afterall
29   </table>
30 #nodata
31   No links found

In line 2 two of the example above, we load a variable $myLinksList with the “Dot Links” list. In line 4, we begin a for..each loop to iterate over each item in the list. On line 7 though 9, you’ll notice that we’re using the $SPFields context object to set the variables for three complex fields. $SPFields.GetUrlValue will handle parsing the URL type fields, while $SPfields.GetLookupValue can hand fetching the value of a lookup field from another list! Finally, $SPFields.GetUserValue handles parsing a User type field for you.
If you drop your attention down to lines 23 through 25, you’ll see where the convenience of the $SPFields context item really shines. For the URL field, we simply need to call $urlValue.URL and $urlValue.Description to get at our data. With the lookup and user field types, calling $stateValue.LookupValue and $ownerValue.LookupValue does the trick.
Here’s a screen shot of the simple output:

Convenient and flexible yet powerful! Don’t you agree?


Frode Sørhøy, Sparebanken Vest, Bergen, Norway said...

I'm testing the Data Zoom web part, and it's very promising. Currently checking out the SQL Server extension as well. Do you have an example on how to do paging when displaying large lists/data sets?

- Frode

Jeremy Luerkens said...

You can page SQL records by formatting your T-SQL query. For SQL Server 2005 and above you can format your query like so...

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CompanyName desc) AS rownum, * FROM Customers) AS tmpCustomers WHERE rownum >= 21 AND rownum <= 30

To format the UI to allow users to select the page size and navigate between pages check out the User Directory script.