Word 2007 Mail Merges with SharePoint Lists

Here's an interesting tip that I was able to contribute to EndUserSharePoint.com. This might be a good tip for developers as well as end users. The post describes how to make use of a SharePoint list in Word by connecting to an Access 2007 database.

ExpGroupBy(obj) Javascript Function

There are times that I am glad to be a SharePoint developer. Today is one of those days! I have been working on a web part that needs to roll-up a lot of data from multiple sub-sites for a dashboard style view. The view needs to group the data by two separate criteria (Unit and Category). Additionally, the desire of the project sponsor is to have all of the items be in a collapsed view with the ability to expand those items on demand. I begin thinking to myself, "Great -- I get to play with Javascript!" Now, repeat that statement with as much sarcasm as you can muster and you have a good idea of my state of mind.

I then remember that I can use SharePoint Designer to create a view similar to my target view. Okay, let's create a list and add several items to it. I now have my "test data" to begin my experiments. Opening up SharePoint Designer, I create a dataview of my list and then specify two levels of grouping. I save the page and then open up the page in IE.

I play with the grouping and find that it is exactly how I want to implement this functionality for my client. So, the fun work of dissecting code begins. During my investigation, I notice that there is a call to a javascript function ExpGroupBy being issued when I click on the handy 'plus' and 'minus' images. After further investigation, I learn that this is a function available in the core.js script file. Hurray! I can use this!

So, I make the necessary modifications to my web site and create my headers and footers for each group, assign unique ID's and then test it in IE... crash! The javascript doesn't work. Here's a sample of the table I was using:

<tr id="group0" style="display: block">
<td class="ms-gb" style="background: #cccccc;" colspan="8">
<a href="javascript:" onclick="javascript:ExpGroupBy(this);return false;">
<img src="/_layouts/images/minus.gif" border="0" alt="expand" name="collapse"></a>
UNIT 10000</td>
</tr>
<tr id="group1" style="display: none;">
<td class="ms-gb" style="background: #cccccc;" colspan="8">
<a href="javascript:" onclick="javascript:ExpGroupBy(this);return false;"><img src="/_layouts/images/minus.gif" border="0" alt="expand" name="collapse"></a>PERSONNEL EXPENSE</td>
</tr>
<tr>
<th class="ms-vh">Cost Center</th>
<th class="ms-vh">Description</th>
<th class="ms-vh">Prev. Budget</th>
<th class="ms-vh">Prev. Actual</th>
<th class="ms-vh">Current Budget</th>
<th class="ms-vh">YTD Actual</th>
<th class="ms-vh">Proposed Budget</th>
<th class="ms-vh">Increase/Descrease</th>
</tr>
<tr class="ms-alternating" style="display: none;">
<td>
10-10000-1000002</td>
<td>
<a href="http://sharepointdev/sites/budgtest/10000/Lists/Cost Centers/EditForm.aspx?ID=3&Source=/sites/budgtest/Shared Documents/Centers.aspx">Sample Cost Center</a></td>
<td align="right">
$25,365.00</td>
<td align="right">
$25,365.00</td>
<td align="right">
$25,365.00</td>
<td align="right">
$4,289.39</td>
<td align="right">
$0.00</td>
<td align="right">
$29,500.00</td>
</tr>

.....


<tr id="group2" style="display: none"><td class="ms-gb" style="background: #cccccc;" colspan="2">
PERSONNEL EXPENSE Totals:
</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$439,370.00</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$439,370.00</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$439,370.00</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$141,865.06</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$0.00</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$29,500.00</td>
</tr>
<tr id="group3" style="display: none"><td class="ms-gb" style="background: #cccccc;" colspan="2">
PERSONNEL EXPENSE Totals:
</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$439,370.00</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$439,370.00</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$439,370.00</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$141,865.06</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$0.00</td>
<td class="ms-gb" style="background: #cccccc;" align="right">
$29,500.00</td>
</tr>

So I loaded this up in IE and to my surprise it ALMOST worked. When I clicked on the unit expand/collapse, the entire table would be collapsed from that place. So if I had multiple units, I would only see a single unit. The same thing would happen if I collapsed a category -- all units and categories below would also collapse.

After further investigation, I learned that the function works by examining the ID of the table row containing the group header and footer. This is how it determines the current level you are viewing to collapse the correct items. So, for my example I needed two group ID's, group0 and group1. Each first level group row header/footer (Unit) was assigned the ID group0 while all second level group rows header/footer (Category) were assigned the ID group1. Once I figured this out, the function began working properly!

Access Denied when using SPSite.AllWebs

As I have been working to develop a web part that our organization will use to present roll-up data from multiple sub-sites to a top-level site, I've come across a n interesting tid bit that was frustrating at first.

It is the use of SPSite.AllWebs property. Check this code:

            ArrayList Lists = new ArrayList();
            SPSite CurrentSite = SPContext.Current.Site;
            SPContentTypeId ContentType = new SPContentTypeId(PARENT_CONTENT_TYPE);
            SPWebCollection UserSites = CurrentSite.AllWebs;

            foreach (SPWeb web in UserSites)
            {
                foreach (SPList list in web.Lists)
                {
                    if (list.ContentTypesEnabled)
                    {
                        try
                        {
                            bool ContainsType = false;
                            SPContentTypeCollection Types = list.ContentTypes;
                            foreach (SPContentType Item in Types)
                            {
                                if (Item.Id.IsChildOf(ContentType))
                                {
                                    ContainsType = true;
                                    break;
                                }
                            }
                            if (ContainsType)
                            {
                                Lists.Add(list);
                            }
                        }
                        catch
                        {
                        }
                    }
                }

When running as an administrator, this code doesn't cause a single problem. However, if you swap to a non-administrative user that doesn't have full control of the site, you'll get the lovely access denied page. The problem is that you must have full control of the site to be able to use the AllWebs property.

The fix for this is to use a property of the SPWeb object. See the corrected code below:

            ArrayList Lists = new ArrayList();
            SPSite CurrentSite = SPContext.Current.Site;
            SPContentTypeId ContentType = new SPContentTypeId(PARENT_CONTENT_TYPE);
            SPWebCollection UserSites = CurrentSite.OpenWeb().GetSubwebsForCurrentUser();

            foreach (SPWeb web in UserSites)
            {
                foreach (SPList list in web.Lists)
                {
                    if (list.ContentTypesEnabled)
                    {
                        try
                        {
                            bool ContainsType = false;
                            SPContentTypeCollection Types = list.ContentTypes;
                            foreach (SPContentType Item in Types)
                            {
                                if (Item.Id.IsChildOf(ContentType))
                                {
                                    ContainsType = true;
                                    break;
                                }
                            }
                            if (ContainsType)
                            {
                                Lists.Add(list);
                            }
                        }
                        catch
                        {
                        }
                    }
                }

By using the GetSubwebsForCurrentUser() method, I'm able to get a list of subsites that the user does have access to view. If the user doesn't have access to any subsites, there will not be the access denied error message and you can display a warning to the user.

VB.NET Translation for Thomas Conté's Code

As a side note, here is the translation of the code to VB.NET from Thomas Conté's post.

        Dim PageFile As SPFile = _ManagedWeb.GetFile("default.aspx")
        Dim WebPartManager As WebPartPages.SPLimitedWebPartManager = PageFile.GetLimitedWebPartManager(Web.UI.WebControls.WebParts.PersonalizationScope.Shared)
        Dim TargetList As SPList = Me.GetList(ListName)
        Dim TargetListWebPart As WebPartPages.ListViewWebPart = New WebPartPages.ListViewWebPart()
        TargetListWebPart.ListName = TargetList.ID.ToString("B").ToUpper()
        TargetListWebPart.ViewGuid = TargetList.DefaultView.ID.ToString("B").ToUpper()
        TargetListWebPart.ChromeType = Web.UI.WebControls.WebParts.PartChromeType.TitleOnly
        WebPartManager.AddWebPart(TargetListWebPart, Zone, Ordinal)
        PageFile.Update()

Programmatically Adding Web Parts to Pages

Today I was working to make modifications to a site that was being provisioned via automation code. The code creates a batch of sites and then uploads various data to the site for review by members of the site. The goal of the automation is to insure a consistent user experience with web parts to containing lists and libararies in the same place. However, I couldn't see exactly where this would be accomplished in the object model of SharePoint, so I pulled up Google and began searching. I came across this post by Thomas Conté that was very helpful.