Points-of-Sharing

When using the (now infamous) "Today" column trick, in calculated columns, you'll no doubt notice that the dates resulting from this calculation don't dynamically update.  This has become a major point of contention with the use of this technique in formulas because in most (if not all) cases, the whole point of using this in a formula is to track information that does actually need to be updated (daily, in most cases).

I've lost count on the number of discussion threads pointing out this flaw (several of which I've participated in myself), so in an attempt to come up with a solution, I'm going to list out a couple options you can take that can (could) be workable to get around this limitation.

Note – these are "coded" solutions, but are simple to deploy (modify the following code to meet the needs of your specific environment and best practices).

Option 1 – Console application added as a "Scheduled Task" in Windows

Performs a "SPListItem.SystemUpdate()" of all items on the target list at 12:01 a.m. each morning (as discussed here in a thread I participated in awhile back on the "SharePointU" forums).

This program uses "SPListItem.SystemUpdate()" in order to not modify any of the tasks visible details, but since it is an actual update, it will in fact force a re-calculation of any formula using the "Today" reference (the alternative of the "SPListItem.Update()" method will change the "Modified" date property, which in this case we don't want because it'd be preferable to preserve the date it was last modified by an actual person instead of the system).

Steps to create the application (using the object model – haven't tried with web services):

1. In Visual Studio, create a new "Console Application" project (named something like "UpdateSPList").
2. Add in references for "SharePoint" and "System.Configuration" (the latter is optional, but will allow you to use the appropriate "ConfigurationManager" call instead of "ConfigurationSettings").
3. Add in an "Application Configuration File" (will house the name of the site and list – contained in this configuration file so you can make changes later).
4. Add in two "key's" to hold the name of the site and list:

   (Example)
   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
     <appSettings>
       <add key="Site" value="http://My_Portal/Sites/TheSite"/>
       <add key="List" value="Tasks"/>
     </appSettings>
   </configuration>
   

5. Change the default "Program.cs" code to be:

   using Microsoft.SharePoint;
   using System.Configuration;
   namespace UpdateSPList
   {
        class Program
        {
             static void Main(string[] args)
             {
                  SPSite site = new SPSite(ConfigurationManager.AppSettings["Site"]);
                  SPWeb web = site.OpenWeb();
                  SPList list = web.Lists[ConfigurationManager.AppSettings["List"]];
                  web.AllowUnsafeUpdates = true;
                  foreach (SPListItem item in list.Items)
          {
                      item.SystemUpdate();
          }
                 web.AllowUnsafeUpdates = false;
             }
        }
   }

6. Build (compile) the program.
7. Copy the "exe" and "config" files from the "debug" folder into the server location that you'll use for production ("UpdateSPList.exe" and "UpdateSPList.exe.config").
8. Create a new "Scheduled Task" in Windows scheduler that uses "UpdateSPList.exe" and have it scheduled to run after midnight of each day (I use 12:01 a.m., setup as applicable for you).

When ran, the application will connect into the site, find the list, open each item on the list and perform an update on it that will force the recalculation of all formulas which will update any dates based off the "Today" reference.

Option 2 – Add code to the page in SPD to update the contents each time the page is viewed.

This approach comes with a warning, if you choose to enable the ability to run server-side code in your pages, anyone who can upload pages, can access system pages and/or use SPD to connect to and modify pages, will be able to run their own code (use this approach at your own risk).

To make this approach work, we need to do two things – modify the "web.config" file to allow us to run code, and then add in the code.

1. Modify the "web.config" file:
1. Using an editor of your choice (notepad, Visual Studio, etc.), open the web configuration file for your site (generally located at "C:InetpubwwwrootwssVirtualDirectories80web.config" if using the "default" site for your instance).
2. Modify it as follows (you'll be adding in the "PageParserPath" node):

   <SafeMode MaxControls="200" CallStack="false" DirectFileDependencies="10" TotalFileDependencies="50" AllowPageLevelTrace="false">
   <PageParserPaths>
   <PageParserPath VirtualPath="/*" CompilationMode="Always" AllowServerSideScript="true" IncludeSubFolders="true"/>
   </PageParserPaths>
   </SafeMode>
   

This will tell the system the path that contains the pages we want to run code on ("/*" will allow it on all pages), and whether or not to actually allow the code (again – use with caution).

Once we have enabled the ability to run code, we need to add the code into the page.

2. Using the default "Tasks" list as our example:
1. Open SPD and connect to your site.
2. Once connected, open the "AllItems.aspx" page for the "Tasks" list (Root of site > Lists > Tasks > AllItems.aspx).
3. In the "Code" view of the site, locate the section "<SharePoint:RssLink runat="server"/>" (used as an example – you could place the code wherever you see fit) and add in the following just below it:

   <script runat="server">
   protected void Page_Load(object sender, EventArgs e)

   {   SPSite site = new SPSite("<Your_Site_URL>");  
       SPWeb web = site.OpenWeb();  
       SPList list = web.Lists["Tasks"];  
       web.AllowUnsafeUpdates = true;  
       foreach (SPListItem item in list.Items)  
       {      
           item.SystemUpdate();  
       }
       web.AllowUnsafeUpdates = false; 
   }

   </script>
   

(Notice it's the exact same code as used in the "Console Application" except for the site and list are specified in the code rather than in a configuration file for this example)

3. Save the page (ignoring any errors or "squiggly" lines you may see in the code view).

Since we've told the system to allow us to run code in the page (via the "web.config" file), once we now visit the page, all items on the list should be updated without throwing any errors (the update of items will occur each time the page is visited).

———————————————

Both of these solutions will work, but depending on your environment may, or may not, be doable (especially if you don't have access to the server running SharePoint or access via SPD – I generally don't develop for web services since I do have the access I need, but you may be able to work up a similar application that accesses SharePoint via its web services as another option). 

Additionally, the above code should be used as a reference for how to create the "Update" functionality and can (should) be written in a better fashion (disposing objects etc.) to follow good programming practices…this is just an example – modify it as you see fit.

Aside from these two methods, you may also be able to use a workflow to update the list that fires off each time an item is updated.  Although this approach does work as well, I don't like the idea of creating an endless loop and I believe there's also an issue with how many times a self-fired SPD workflow will run (it appears to stop working after a time). 

There may be other methods as well to get the calculated column's formula to dynamically update each day, but both of the methods I've listed above seem to do the trick with minimal effort (and are easy to disable/update when needed).

I'm still looking for other approaches to tackle this, especially since I've been blogging recently on Mark Miller's site (EndUserSharePoint.com) regarding calculated columns (with a bunch of examples using the "Today" column trick).  So if anyone has any other suggestions/approaches (not just coded, any other ideas that might work better for end-users, not just programmers) please share them.

Hopefully these ideas will help, they're not perfect (perfect would be the system doing what we want without these types of hacks), but at least as a work-around they'll do the job.

Till next time…

- Dink

Took me long enough to get this post up, but it's finally done (hopefully – please let me know if you come across any errors in the walkthrough).

Refer to the previous two articles in this series for additional customizations necessary to make this template function correctly.
Employee Training and Scheduling Template – a couple fixes Part 1
Employee Training and Scheduling Template – a couple fixes Part 2

In this walkthrough, we'll be covering how to set permissions and make additional customizations to the "Employee Training Scheduling and Materials" template that allows users to register (and unregister) for courses without the (perceived) ability of being able to create their own "Courses" or "muck-up" anything else in site that we wouldn't want them to.

One of the major problems with how this template was designed is that in order to make it so a user can register and unregister from a course, they must have what's roughly equal to "Contribute" permissions on each of the lists associated with the "Course".  This, of course, opens up the possibility of users being able to do more than they should, and could lead to the entire process breaking down at some point if an errant user were to add in their own Courses on the calendar, or remove existing ones (not that anyone at your company would ever do such a thing).

So, to get the environment setup better to limit what a user can do (or more realistically, the "perception" of what they can do), the steps are as follows:

Step 1: Create the site:

Once created, break inheritance on the site.  It's important to begin with a unique site that doesn't have any fall-through from permissions at its parent (we're creating a new "custom" permission level to use for the users of this site).

Step 2: Modify site to make it custom (part I):

1. Go into the permissions area of the site and break inheritance on the permission levels (the "definition" of these levels will now exist locally instead of being defined at the parent). 
2. Once broken, remove any and all permissions except for defaults ("Full Control", "Design", "Contribute", "Read", "Limited" – this resets the permissions-available back to an "Out of the box" list if you had other custom definitions).
3. Create a new custom permission level by starting with the definition of the default "Contribute" level.
   1. To do this, click on "Contribute" to edit, scroll to the bottom of the permissions page after it opens and click "Copy Permission Level" (this creates a new unnamed level that has all the options the "Contribute" level has as a starting point).
   2. Name the new level "Employee Regs" (or similar that fits your environment), add in a simple description to remind you that this level is for employee registrants, then modify the list of permissions to be set as follows:
      1. List Permissions
         1. Add Items
         2. Edit Items
         3. Delete Items
         4. View Items
         5. Open Items
      2. Site Permissions
         1. Browse Directories
         2. View Pages
         3. Open
      3. Personal Permissions
         1. Uncheck all

These are the minimum permissions needed in order for users to be able to register for a course and unregister if needed.  The problem again, is that with these permissions, a user could in theory, create a new "Course", delete an existing "Course", delete another user who is registered for a course, or modify anything existing.  None of this is really acceptable, so what we need to do is now go through the site and first limit their access to areas not associated with the workflows for registration, then limit the manner in which they can gain direct access to areas they do have permission to.

First though, let's finish up the permissions part by creating a new group that this custom level will be applied to.

4. Go to "People and Groups" and create a new group and name it in a manner that fits in with your naming convention for the rest of the site, but for good practice make sure to name it in a manner that makes it indicative of being for "Registrants" (e.g. your site is named "ACME Training" – an appropriate name for this group might be "ACME Employee Registrants" or similar). 
5. Once created, set this new group's permission level to the new custom permission level we previously created (in my case, "Employee Regs").

At this point since the group has been created, you can add in your users that will be registering (or wait until all the customizations are completed – either way, don't forget to add in your users).

Now we need to start limiting what our users can see (the idea is that "If we hide it, they won't find it!!").

Modify site through SharePoint Designer (Part I)

1. In SharePoint Designer, connect to your site.
2. If you haven't done this already, modify the site as per (…a couple fixes Part 2)
3. Modify web parts visibility
   1. Open default.master
   2. In "Design" view, locate "View All Site Content" on the left-hand-site navigation bar and click on it.
   3. Go to "Code" view. You will see the following code highlighted:
      <Sharepoint:SPSecurityTrimmedControl runat="server" PermissionsString="ViewFormPages"><div class="ms-quicklaunchheader"><SharePoint:SPLinkButton id="idNavLinkViewAll" runat="server" NavigateUrl="~site/_layouts/viewlsts.aspx" Text="<%$Resources:wss,quiklnch_allcontent%>" AccessKey="<%$Resources:wss,quiklnch_allcontent_AK%>"/></div></SharePoint:SPSecurityTrimmedControl>
   4. Change the "PermissionString" attribute value of the "Sharepoint:SPSecurityTrimmedControl" element from "ViewFormPages" to "DeleteVersions".
   5. Save the default.master.

If you login as a member of the "ACME Employee Registrants" group, you will not see the "View All Site Content" link (this is important because it will not let unauthorized users see the listing of site "lists"). However, when authenticated as a member of the site owners or contributors group, you will see this option.

NOTE: The "DeleteVersions" permission is common to both the "Members" and "Owners" group permission levels, but is not set for the "Limited Access" (anonymous users), "Read", or "Employee Regs" permission levels.

4. Modify dispform.aspx to hide "Course Registration List" (lists/courses/dispform.aspx)
   1. Add in new "<tr><td></td></tr>" tags after "Main" web part zone table row closes, and add in: <Sharepoint:SPSecurityTrimmedControl PermissionsString="DeleteVersions" runat="server"><WebPartPages:WebPartZone runat="server" FrameType="None" ID="Bottom" Title="loc:Bottom"><ZoneTemplate></ZoneTemplate></WebPartPages:WebPartZone></Sharepoint:SPSecurityTrimmedControl>
      (this adds a second zone under top one)
   2. Save and close file (ignoring any errors displayed in the designer).

Step 4: Modify site to make it custom (part II):

1. In the browser, go to the site homepage
2. In edit mode (Site Actions > Edit Page) remove each of the following webparts:
   1. Courses I have taught
   2. Courses I am teaching
   3. Courses I have attended
   4. Content Editor Web Part
   5. Links
3. On left zone add Courses web part (and drag to bottom of zone)
4. Click "View all site content
   1. Go to "Course Materials"
      1. Edit permissions
         1. Change permissions of "ACME Employee Registrants" group to "read"
   2. Go to "Announcements"
      1. Delete "Announcements" list (the guy that built this template for Microsoft left some errant information in the list which will cause some errors if you use it so we need to recreate it)
      2. Go to the "Create" screen and select "Announcements"
         1. name it "Announcements"
         2. leave rest as default and click create
      3. Go back into the settings for the "Announcements" list and edit Permissions
         1. Change permissions of "ACME Employee Registrants" group to "read"
   3. Delete "Course Surveys"
   4. Courses
      1. Leave Permissions as is
      2. Modify webpart toolbar to "Summary Toolbar"
   5. Links
      1. Edit Permissions
         1. Change permissions of "ACME Employee Registrants" group to "read"
   6. Past Registrations
      1. Leave permissions as is
   7. Registrations
      1. Leave permissions as is
      2. Modify webpart toolbar to "No toolbar"
   8. Tasks
      1. Leave all as is
5. Quick launch
   1. Delete Add new course
   2. Delete upload materials
   3. Delete Course Surveys (this will also delete "add new feedback")
   4. Delete Announcements (will add back later)
6. Go back to home page
7. Quick launch
   1. Go to announcements list
      1. Edit Permissions
         1. Change permissions of "ACME Employee Registrants" group to "read"
8. Go back to home page
   1. In edit mode, add the Announcements webpart to the right zone.
   2. Change view of "Courses" webpart to "calendar"
   3. Exit edit mode
9. Quick Launch
   1. Copy URL from "Announcements" listing under list heading
   2. Replace URL on lists with Announcements URL
   3. Rename lists to "Announcements"
   4. Delete announcements sub-heading (list item under main heading)
10. Modify "Courses I am attending" webpart XSL to function correctly when un-registering (see Previous post)
    1. On home page, go into edit mode and open the "XSL Editor" for the webpart
       1. Find:
          "You are not scheduled to attend any courses."
          and:
          "Choose &quot;Upcoming courses&quot; from the Quick Launch bar to select an available course to register for."
       2. Replace with:
          "You are not scheduled to attend any Sessions."
          and:
          "Choose &quot;Upcoming Sessions&quot; from the Quick Launch bar to select an available Session to register for."
       3. Find:
          <td class="ms-vb"><a href="Lists/Registrations/Unregister.aspx?ID={../../../Registrations/Rows/Row/@ID}" mce_href="Lists/Registrations/Unregister.aspx?ID={../../../Registrations/Rows/Row/@ID}">Remove</a></td>
       4. Replace with:
          <td class="ms-vb"><xsl:variable name="CourseID" select="@ID"/><a href="Lists/Registrations/Unregister.aspx?ID={../../../Registrations/Rows/Row[@Course_x0020_ID=$CourseID and contains(@Author, $UserID)]/@ID}">Remove</a></td

Step 5: Modify site through SP Designer (Part II)

1. Modify default.aspx
   1. Open default.aspx in SP Designer (at root of site)
   2. Click to select the "Left" webpart zone.
   3. Scroll to the bottom of the webpart zone in the html view
   4. Immediately after "</ZoneTemplate></WebPartPages:WebPartZone>"
      add in:
      <Sharepoint:SPSecurityTrimmedControl PermissionsString="DeleteVersions" runat="server"><WebPartPages:WebPartZone runat="server" FrameType="TitleBarOnly" ID="Bottom" Title="loc:Bottom"><ZoneTemplate></ZoneTemplate></WebPartPages:WebPartZone></Sharepoint:SPSecurityTrimmedControl>
   5. Save page

Step 6: Modify site to make custom (Part III)

1. In the site (on any event details page), go into "edit page" mode and drag the "Course Registration List" web part to the new bottom zone from the top zone.
   When a user visits the page, the list of currently signed up users will now only display to the members of the site owners and contributors groups (registered employees will not be able to see the list).
   
   (8/7/08) EDIT – ADDED STEP I FORGOT ABOUT IN ORIGINAL POST (Thanks to Sara for pointing this out in a comment)
   1. While in "Edit" mode, open the toolpane for the "Courses" list (edit > Modify Shared Web Part).
      1. Open the "XSL" editor and locate the following:
         <SharePoint:FormToolBar runat="server" ControlMode="Display"/>
         Comment out this line to remove the toolbar from display
         (add in <!– –> around the tag to hide it)
         This is neccessary in order to prevent users from being able to create/edit/delete/etc., the items on the "Courses" list.
2. Back on Home Page
   1. Add a content editor webpart to the new "bottom" zone
      1. Add in the following html:
         <A href="/<the new site URL>/Lists/Courses/AllItems.aspx">Click here to add new Calendar Events</A>
      2. This will give access to the screen where admins of the site can enter in new calendar events.

Conclusion:

Using the above, you can definitely see that it can take quite a bit to customize this template to make it a "workable" platform for managing training sessions and user registrations.

To give you an example of what my organization has gotten out of this template:

We have a number of "Assessment Sessions" scheduled each week that we use for placement into various classes (part of a "Student" entry system to make sure they can succeed in the classes they sign up for later).  The problem we had in the past is that in order to register for one of the sessions, a user had to submit the request by email, phone, or physically come to the campus and fill out a paper registration form (add their name to the list for the day).  This worked, but human-nature reared its ugly head numerous times causing emails to get overlooked, phone messages not collected correctly, and registration forms to simply go missing.

Enter the "ETSM" template.

After playing around with the template and working up the above steps to customize it, we were finally able to get a system up and running that could not only give users the ability to register themselves, but from a single place (online).

There's still obviously some extra functionality that needs to be added in to make things work better (ability for an administrator to register someone else, for one), but over time I think the community that has taken a shine to this template will definitely figure out ways to accommodate these "wish-list" items and make the template "fully-functional".

Till next time…

- Dink

For more information on this template, how is was created [and by who], and a general view at why it does what it does, check out the series of posts by the "Microsoft SharePoint Designer" team on the MSDN blog site:

http://blogs.msdn.com/sharepointdesigner/archive/2007/03/10/training-site-template-part-1-introduction.aspx
http://blogs.msdn.com/sharepointdesigner/archive/2007/03/23/training-site-template-part-2-workflows.aspx
http://blogs.msdn.com/sharepointdesigner/archive/2008/07/04/training-site-template-part-3-custom-views-and-forms.aspx

I saw the question come up over on Mark Miller's site about how to save a custom View for use in another document library, so I decided to play around with it some to see what I could come up with for a solution to this.

Basically, what I've found is that there appears to be two ways to approach this:

First, you can do the “Save library as a template” option which will copy over any custom views you have…which sounds simple enough for creating any new libraries, but what about if you have an existing library?  How do we get the view transferred over?

To do this we'll have to use the second option of using SharePoint Designer to add in a new “view page” to an existing site's document library.

For this example, I'll be using two sites.  The first site (where I'll be creating the “master” view) is called “Test site 1″ (URL is "http://portal/ts1“) which has the default document library “Shared Documents”.  The second site will be called “Test site 2″ (URL is "http://portal/ts2") and also has a default document library called “Shared Documents”.
The view I'll be creating is called “MyView” (and “MyNewView”) and will display the following columns:

• “Name (linked to document with edit menu)”
• “Created”
• “Version”

This is a simple test, but will illustrate how to apply the custom view to another library.

First, in the “master” document library (“Shared Documents” in the “Test site 1″ site), create the view using the columns listed above and name it “Test”.  Once you have the view created, open the site (“Test site 1″) in SPD (SharePoint Designer), and navigate to the “Shared Documents > Forms” folder, and open the “Test.aspx” file (the view we just created).

Next, open another instance of SPD and open “Test site 2″.  Once it's open, navigate to the “Shared Documents > Forms” folder.  Once there, “right-click” on the “Forms” folder and choose “New > ASPX” to create a new “view” file (name it “MyView” for this test), then “double-click” the “MyView.aspx” file to open it.

Now comes the fun part!!

Switch back to the “Test.aspx” file and copy its entire html (in the html view, “right-click” and select all – “ctrl+a” – whichever method you like), then switch back to the “MyView.aspx” file and replace its entire html with what you just copied (basically, replace the entire html for the “MyView.aspx” file with the html from the “Test.aspx” file).

Next, we need to change the “guid” that is being referenced in the “old” html (ID pointer to the document library) to that of the new document library.  The easiest way to do this is to open the site in a browser, go to the document library, then go to “Settings > Document Library Settings”.  In the address bar, the URL will have the guid as the last part of the address.

Example:
http://Portal/Test1/_layouts/listedit.aspx?List=%7B35A23714%2D25E3%2D42A5%2DB4BA%2D868F31906FA2%7D

Guid would be the last part of the URL:
%7B35A23714%2D25E3%2D42A5%2DB4BA%2D868F31906FA2%7D

Modify the guid to be in the following format:
{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}

This means replacing the html equivalents to the real characters as such:
{35A23714-25E3-42A5-B4BA-868F31906FA2}

• %7B becomes {
• %2D becomes -,
• %7D becomes }

Do this for both document libraries then switch back to the “MyView.aspx” file and perform a simple “find-and-replace” on the old guid (replace the guid associated with the document library from the “Test site 1″ library to the guid for the document library in the “Test site 2″ site).  There should only be one replace (approximately on line #53).  Also, (two lines down from the guid – should be approx. line#55) you need to update the “Url” attribute to be the correct site (“ts2″ instead of “ts1″ – you can also use the “find-and-replace” method to change this), and on the same line change the “Display Name” attribute to be what you want displayed in the dropdown for the name of the view (optional – you can leave as is if you want).

Once you've made the changes, save the file.  You'll see a popup informing you that “The URL `Shared Documents/Forms/MyView.aspx' is invalid.  It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web”.  I believe this is because there may still be a reference to the old library (or site) somewhere in the page (which I wasn't able to find), but the easy workaround is to just save the page with a different name (for this I chose “MyNewView”).  Once it is saved as the new name, you can delete the “MyView.aspx” page since we won't be using it.

Once you have the above completed, switch back to the “Test site 2″ site and go to the document library.  Refresh the page and look at the available views (you'll notice a new listing with the name you gave it during the "find-and-replace" part, which is now available).

From here you can simply apply the view and use it as normal.

So far, I've ran through this about 6 or so times and haven't found any immediate problems, but with anything custom you do to modify the site (especially through SPD) there may be something that does come up, so if you run into any problems, post a comment here and we'll look and see if we can find out what's happening.

- Dink

– “How do you move (or copy) documents from one library to another while keeping version history intact?” –

This is one of those tasks that, although it sounds pretty straightforward and simple is actually not one of the easiest things to take care of.

From what I've been able to find, there's really only a few different approaches to this that most folks take:

• Explorer view (or Network place) copy/paste
• Save site as a template – including content (*only works if library size is less than 10MB)
• Or the new "Manage Content Structure" page offered in MOSS.

The first option, as proved numerous times by many users, does indeed copy the file(s) to the new library, but it doesn't preserve any version history (note – apparently for some users, doing a "move" rather than a "copy" has successfully brought over the version history, although I have never gotten his to work and realistically, I don't prefer this approach because I don't care for the idea of losing the original source document in this scenario).

Option two is also not a reliable approach due to the size limit's on how big the content can be that you're saving (*default limit only – size can be modified through STSADM – mentioned here: http://blogs.microsoft.co.il/blogs/meronf/archive/2006/08/22/2617.aspx), and it's kind of a “kludgy” approach in the first place because its really just a work-around to move documents one time (not a true “archiving” method since you cant just make updates based on the original documents and apply those changes to the “templated” library – obviously this isn't the point of this method, but some users may want this functionality and wont have it if following this approach).

Option three relies on your having MOSS installed. 
Ok, fine…but what about those of us working with an instance of WSS only and not MOSS?

After researching all the possible approaches (minus paying someone an outrageous amount of money for a program that promised to do this), I decided to investigate if I can perform this programmatically through the SharePoint object model…which I'm happy to report was not quite as difficult as I had thought (although I ran into a few interesting logic problems along the way, but did manage to get past them).

 
– Version history?  What the Heck??? –

The first thing we need to understand is just how SharePoint deals with versions.  Once you turn on versioning on a document library, you're enabling the use of a new “virtual” directory set aside for the sole purpose of providing a “web” interface to access previous versions of a document that are all stored in the content database.  This new directory is called “_vti_history“, and includes a number in each document's URL that signifies it's actual version – it's also important to note that all the documents accessed in the virtual folder are previous versions of the document only, not the current version that is displayed in the document library itself (this will be important to remember later when programming on versions).

An example of these URL's for document versions would be something similar to:

• http://www.mydomain.com/_vti_history/1/Shared%20Documents/Test.doc
• http://www.mydomain.com/_vti_history/2/Shared%20Documents/Test.doc
• http://www.mydomain.com/_vti_history/3/Shared%20Documents/Test.doc
• http://www.mydomain.com/_vti_history/512/Shared%20Documents/Test.doc
• http://www.mydomain.com/_vti_history/1024/Shared%20Documents/Test.doc
• http://www.mydomain.com/_vti_history/1025/Shared%20Documents/Test.doc
• http://www.mydomain.com/Shared%20Documents/Test.doc

(The last URL listed does not contain a number or the “_vti_history” path because it is the current version of the document.)

You'll notice in the URL's the number immediately following the “_vti_history/” part of the address.  This number specifies exactly what the version number is for the document.

• URL number “1” = version “0.1“
• URL number “2” = version “0.2“
• URL number “3” = version “0.3“
• URL number “512” = version “1.0“
• URL number “1024” = version “2.0“
• URL number “1025” = version “2.1“

So, by looking at these numbers, we can start to see a pattern forming (which again will become very important later when we begin coding).  You'll notice that all the minor versions (numbers to the right of the decimal point) are all based on a single number counting system, whereas the major versions (numbers on the left of the decimal point) are based on a “512″ increment system (I like to call this a “base-512” counting system). 

For example, let's say we have a document that is version “14.7″.  Following the pattern and the base-512 counting system, we'd come up with a number of “7175″ (512 * 14 + 7) making the URL http://www.mydomain.com/_vti_history/7175/Shared%20Documents/Test.doc.

Now, I do have to state that I absolutely despise mathematics.  I hate it with a passion.  It always has been, and will continue to be, my worst subject and is the constant source of many-a-migraine   This particular base-512 system threw me for a bit of a loop when attempting some of the coding for this, but in the end I was able to tame it somewhat and come up with a workable solution for the logic it was confusing me with.

  
– Logic?  More of a fad if you ask me. –

So, now that we know how SharePoint deals with document versions, let's take a quick look at the logic that will be involved in copying the contents of one library to another (then we'll jump into the code and get this post over withJ).

First and foremost, since we'll be programming against SharePoint, we'll need to make sure we can get access to the objects available in its object model so make sure in your web project that you add in a reference to the SharePoint.dll (located in the ISAPI folder of the 12 hive).

The steps the program will take are this:

1.      Enumerate all sites in the current web collection and populate the “source site” and “target site” dropdown lists.

a.       For this, I'm adding in a parsed version of the site's URL into the text field of the list that has the domain portion of the URL removed only (makes it easier to read).

b.      In the “value” attribute I'm adding in the full URL.

2.      On selection of the source or target site, the corresponding source or target “library” dropdowns get populated with a list of all the document libraries in the selected site.

a.       Allows us to build the connection for the document library to be copied and its destination.

b.      Additionally, I'm trimming the list of available libraries down to (standard) user-accessible libraries only (removing libraries such as the “Master Page Gallery”, “Workflows”, etc.)

3.      Next, after selection of the source and target have been established, on the button click event, we begin to format the URL's we'll need, setup our connections, then begin to enumerate the documents in the source library and process them.

a.       During the document processing, there's a specific approach that had to be taken due to how the documents are stored and accessed as follows:

b.      For each document in the library, check to see if it has any versions and if it does, grab it's URL and parse out the version number (the “base-512″ number mentioned earlier) and add it to a new SortedList object as a key with no value (we'll use this is a comparison object later) making sure to first convert it to an integer.

                                                               i.      Converting to an integer is actually a rather important step during this process that threw for awhile until I figured out what was happening.  The SortedList object in .NET does perform automatic sorting on its keys (part of the appeal of the object), but it does treat the keys different depending on the type being entered.  Since it's essentially a combination between an Array and a HashTable, it will take any object type you want to add, but adding in the parsed URL as a string has an inherent problem. 

If we add in for example, the following values: “1″, “2″, “512″, “1024″, they will actually be sorted in the list as “1″, “1024″, 2″, “512″.  This is not good!  Since we're working with versions, they must be in a specific order, and as described earlier in this post, the numbers follow a set pattern.  Having them sorted based on the string sorting will make it so our copied versions will go to the new library in the wrong order (very badL).  So, in order to have them sorted in the correct “numerical” order – convert the string number to an integer and all will be fine (finding this out took almost as long as writing the entire program itself).

c.       Next, for each of the keys we just added, we'll again loop through all the versions, again parse out the version number from the URL and find the one that matches the key.

d.      Once we have a match, we'll send the parsed out number (converted to an integer again) to a custom “version checking” method that looks to see if it is a minor or major version and adds it into the target document library as the appropriate version.

                                                               i.      In this method, a couple actions take place.

1.      In order to have an adequate checking system for versions that would allow me to be able to pass in any version number regardless of how large, I had to process the integer through a simple base-512 check ( I say “simple” only because after the time it took me to figure out how to do it, it wound up only being three small lines of code to process the number and then a simple “if/else” to check if it was a major or minor.did I mention earlier how much I hate Math?!?! L).

2.      Once the number has been processed and deemed either major or minor, it is then added to the target document library with the appropriate version.  (Since versioning will be turned on in the target library, as I add in each successive version, the number will increment accordingly.)

e.       Once all the versions have been added, the last step is to then add the current version of the document.

                                                               i.      Since we already have the context of the current document during this entire process, this part is simply running a check to see if the current version is major or minor version then adding it to the target library and publishing it if it's a major version.

So, that's it.  Not too complicated, just a logical step by step process to grab each document, process it for a few checks, the copy it to a target library.  Here's the code to do all this:

(Note – There are of course, ways in which the code can be streamlined and made more efficient, but for the sake of this exercise, it should serve as the foundation for what you can build on to suit your needs.)

Html for “Default.aspx”:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Copy document library contents</title>
</head>
<body>
  <form id="form1" runat="server">
    <table id="tblMain" runat="server">
      <tr>
        <td>
          <asp:Label ID="lblSourceSite" runat="server" Text="Source Site:" Width="115px"></asp:Label>
        </td>
        <td>
          <asp:Label ID="lblSourceLib" runat="server" Text="Source Library:" Width="115px"></asp:Label>
        </td>
      </tr>
      <tr>
        <td>
          <asp:DropDownList ID="ddlSourceSite" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlSourceSite_SelectedIndexChanged">
            <asp:ListItem>– Choose Site –</asp:ListItem>
          </asp:DropDownList>
        </td>
        <td>
          <asp:DropDownList ID="ddlSourceLib" runat="server" AutoPostBack="True"></asp:DropDownList>
        </td>
      </tr>
      <tr>
        <td>
          <asp:Label ID="lblTargSite" runat="server" Text="Target Site:" Width="115px"></asp:Label>
        </td>
        <td>
          <asp:Label ID="lblTargLib" runat="server" Text="Target Library:" Width="115px"></asp:Label>
        </td>
      </tr>
      <tr>
        <td>
          <asp:DropDownList ID="ddlTargSite" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlTargSite_SelectedIndexChanged">
            <asp:ListItem>– Choose Site –</asp:ListItem>
          </asp:DropDownList>
        </td>
        <td>
          <asp:DropDownList ID="ddlTargLib" runat="server" AutoPostBack="True"></asp:DropDownList>
        </td>
      </tr>
      <tr>
        <td>
          <asp:Button ID="btnStart" runat="server" OnClick="btnStart_Click" Text="Copy Files" />
        </td>
        <td>
          <asp:Button ID="btnReset" runat="server" Text="Reset Fields" OnClick="btnReset_Click" />
        </td>
      </tr>
    </table>
  </form>
</body>
</html> 

The corresponding “Default.aspx.cs” file will be as follows:

using System;
using System.Collections;
using System.Text.RegularExpressions;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.WebControls;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page
{
     #region GlobalVars
     string sourceFolder; //source site
     string sourceDocLib; //source library
     string destFolder; //target site
     string destDocLib; //target library
     string destURL = ""; //target site + library + filename
     SPSite siteCollection;
     SPFolder srcFolder;
     SPFileCollection destFiles; 
     SPWebApplication webApp; 
     byte[] verFile; //document to be copied
     #endregion          /// <summary>
     /// Page load event that calls method to populate dropdownlists
     /// </summary>
     /// <param name="sender"></param>
     /// <param name="e"></param>
     protected void Page_Load(object sender, EventArgs e) 
     {
          GetSites();
     }      

     /// <summary>
     /// Populates source and target (site) dropdownlists
     /// </summary>
     public void GetSites() 
     {
          SPSite mySite = SPContext.Current.Site; 
          SPWebCollection subSites = mySite.AllWebs;
          foreach (SPWeb site in subSites) 
          {
               //regex to strip out the domain name from the URL – makes it display nice in the dropdownlist
               //also, make sure to use your own domain name escape the "/" from the end of the domain
               ddlSourceSite.Items.Add(new ListItem(Regex.Replace(site.Url, "yourdomain.com\/", ""), site.Url));
               ddlTargSite.Items.Add(new ListItem(Regex.Replace site.Url, "your domain.com\/", ""), site.Url)); 
          }
     }      /// <summary>
     /// Populates list of source document libraries and filters out "admin" libraries
     /// </summary>
     /// <param name="sender"></param>
     /// <param name="e"></param>
     protected void ddlSourceSite_SelectedIndexChanged(object sender, EventArgs e) 
     {
          ddlSourceLib.Items.Clear();

          using (SPSite curSite = new SPSite(ddlSourceSite.SelectedItem.Value)) 
          {
               using (SPWeb curWeb = curSite.OpenWeb()) 
               {
                    foreach (SPList list in curWeb.Lists) 
                    {
                         if (list.Title == "List Template Gallery" || list.Title == "Master Page Gallery" || list.Title == "Site Template Gallery" || list.Title == "Web Part Gallery" || list.Title == "Workflows")
                         { 
                         }
                         else if (list.GetType().ToString() == "Microsoft.SharePoint.SPDocumentLibrary") 
                         {
                              ddlSourceLib.Items.Add(new ListItem(list.Title)); 
                         }
                    }
               }
          }
     } 

     /// <summary>
     /// Populates list of target document libraries and filters out "admin" libraries
     /// </summary>
     /// <param name="sender"></param>
     /// <param name="e"></param>
     protected void ddlTargSite_SelectedIndexChanged(object sender, EventArgs e) 
     {
          ddlTargLib.Items.Clear();

          using (SPSite curSite = new SPSite(ddlTargSite.SelectedItem.Value)) 
          {
               using (SPWeb curWeb = curSite.OpenWeb()) 
               {
                    foreach (SPList list in curWeb.Lists) 
                    {
                         if (list.Title == "List Template Gallery" || list.Title == "Master Page Gallery" || list.Title == "Site Template Gallery" || list.Title == "Web Part Gallery" || list.Title == "Workflows") 
                         {
                         }
                         else if (list.GetType().ToString() == "Microsoft.SharePoint.SPDocumentLibrary") 
                         {
                              ddlTargLib.Items.Add(new ListItem(list.Title)); 
                         }
                    }
               }
          }
     } 

     /// <summary>
     /// Gathers selections from dropdownlists and fires off copy process
     /// </summary>
     /// <param name="sender"></param>
     /// <param name="e"></param>
     protected void btnStart_Click(object sender, EventArgs e) 
     {
          //set paths – parses out site name from url in dropdownlists
          string sf = ddlSourceSite.SelectedItem.Text; 
          int index0 = sf.LastIndexOf("/");
          string newSf = sf.Substring(index0 + 1); 
          sourceFolder = newSf;

          string sd = ddlSourceLib.SelectedItem.Text; 
          int index1 = sd.LastIndexOf("/");
          string newSd = sd.Substring(index1 + 1); 
          sourceDocLib = newSd;

          string df = ddlTargSite.SelectedItem.Text; 
          int index2 = df.LastIndexOf("/");
          string newDf = df.Substring(index2 + 1); 
          destFolder = newDf;

          string dd = ddlTargLib.SelectedItem.Text; 
          int index3 = dd.LastIndexOf("/");
          string newDd = dd.Substring(index3 + 1); 
          destDocLib = newDd;

          //connection info and context
          siteCollection = SPControl.GetContextSite(Context); 

          //source folder (document library)
          srcFolder = siteCollection.AllWebs[sourceFolder].Folders[sourceDocLib];

          //destination folder (document library)
          destFiles = siteCollection.AllWebs[destFolder].Folders[destDocLib].Files;

          //Admin web application object
          webApp = siteCollection.WebApplication;

          //temporarily disables "security validation" to get around the
          //"The security validation for this page is invalid." error message
          webApp.FormDigestSettings.Enabled = false; 

          //enumerate source library
          foreach (SPFile file in srcFolder.Files) 
          {
               //standard check
               if (file.Exists) 
               {
                    //custom sorted list used to reorder versions
                    SortedList myList = new SortedList(); 
                    ICollection items = myList.Keys; 

                    //destination URL – path that will be used for copied file to target library including filename
                    destURL = destFiles.Folder.Url + "/" + file.Name; 

                    //checks to see if file has versions
                    if (file.Versions.Count != 0) 
                    {
                         //enumerate versions
                         foreach (SPFileVersion ver in file.Versions) 
                         {
                              string tempKey = ""; 

                              //parses version number from previous versions URL
                              tempKey = Regex.Replace(ver.Url, "_vti_history/", ""); 
                              tempKey = Regex.Replace(tempKey, "/" + sourceDocLib, "");
                              tempKey = Regex.Replace(tempKey, "/" + file.Name, ""); 

                              //converts string number to int in order to be sorted correctly
                              //adds to Sorted list as a new key
                              myList.Add(int.Parse(tempKey), ""); 
                         }
                    }
                    
                    //since items in sorted list are now actually sorted correctly
                    //we start with this list in order to process the versions
                    //to copy them in the correct order
                    foreach (object key in items) 
                    {
                         //as we iterate the keys in the sorted list (the version numbers)
                         //we then run a comparison on the actual versions to find which one matches
                         //the key so we can process each one in order
                         foreach (SPFileVersion newVer in file.Versions) 
                         {
                              string temp = ""; 

                              //parses version number from previous versions URL again 
                              //in order to compare it to key stored in SortedList.
                              temp = Regex.Replace(newVer.Url, "_vti_history/", ""); 
                              temp = Regex.Replace(temp, "/" + sourceDocLib, "");
                              temp = Regex.Replace(temp, "/" + file.Name, ""); 

                              //checks to see if version matches key 
                              if (temp == key.ToString()) 
                              {
                                   //opens file for processing and calls method to determine major/minor status
                                   verFile = newVer.OpenBinary();
                                   SetVersion(int.Parse(temp)); 
                              }
                         }
                    }

                    //Last step which copies current version
                    byte[] binFile;
                    SPFile copyFile; 
                    binFile = file.OpenBinary();

                    //checks if current version is major/minor version and publishes accordingly
                    if (file.Level.ToString() == "Published") 
                    {
                         copyFile = destFiles.Add(destURL, binFile, true);
                         copyFile.Publish(""); 
                    }
                    else
                    {
                         copyFile = destFiles.Add(destURL, binFile, true); 
                    }
               }
          }

          //re-enables "security validation"
          webApp.FormDigestSettings.Enabled = true; 
     }      /// <summary>
     /// method to determine major/minor status
     /// of version and publish accordingly
     /// </summary>
     /// <param name="num">parsed version number from file's URL</param>
     public void SetVersion(int num) 
     {
          int baseNum = 512; 
          decimal d = num / baseNum;
          int i = (int)Math.Floor(d) * 512; 

          //major publish (eg 1.0, 2.0, 3.0)
          if (num == i) 
          {
               SPFile copFileVers = destFiles.Add(destURL, verFile, true);
               copFileVers.Publish(""); 
          }
          //minor (eg 0.1, 1.1, 2.3)
          else
          {
               SPFile copFileVers = destFiles.Add(destURL, verFile, true); 
          }
     }      /// <summary>
     /// Resets dropdowns
     /// </summary>
     /// <param name="sender"></param>
     /// <param name="e"></param>
     protected void btnReset_Click(object sender, EventArgs e) 
     {
          Response.Redirect("default.aspx"); 
     }
}

 
– Conclusion –

In order to get this to work:

• Modify the “GetSites” method to strip out your domain instead of the “yourdomain.com” sample listed.
• Build and deploy using your preferred method ("layouts" directory, IIS site and view with page viewer, etc.)
• Run the app and enjoy J

Due to the length of this post it is also very possible that I may have inadvertently skipped something, or possibly even typo'd in some area that may be rather important.  If you see something obvious, please add a comment and I'll make sure and fix it as soon as I can.  Also, if anyone has any issue with getting the code to work, again please post a comment to that effect and we'll work through it to see if we can get things going.

Until next time,

- Dink

On the last post regarding this template (see Part 1), I gave a walkthrough on how to fix the "Available Seats" variable when a user unregisters from a course so that the variable would increment.

This time I'm going to give a walkthrough on the simple fix to make it so when a user has registered for multiple courses, when they attempt to unregister from an event through the "Courses I am attending" webpart, they actually get removed from the correct course

There is an error in the XSL for the web part that makes it so when the "Remove" link is clicked, it choose whatever course is listed first and not the specific one that lines up with the "remove" link.

For example:  if you had three courses on the list and you clicked "remove" on the second one…it would actually remove you from the first course that was listed (because it was displayed first on the list) instead.

The walkthrough for this is really pretty simple to do, so I'm not going to use screenshots for this one…I'll just walk you through.

Open the "XSL Editor" for the "Courses I am attending" webpart.
Copy/paste the contents into NotePad (or editor of your choice).
Search for the "remove" link (it's in a "td" tag a little past halfway down in the document).
Replace the contents of the "td" with the following:

Change from:

<td class="ms-vb">
    <a href="Lists/Registrations/Unregister.aspx?ID={../../../Registrations/Rows/Row/@ID}" mce_href="Lists/Registrations/Unregister.aspx?ID={../../../Registrations/Rows/Row/@ID}">Remove</a>
</td> 

To:

<td class="ms-vb">
    <xsl:variable name="CourseID" select="@ID"/>
    <a href="Lists/Registrations/Unregister.aspx?ID={../../../Registrations/Rows/Row[@Course_x0020_ID=$CourseID and contains(@Author, $UserID)]/@ID}">Remove</a>
</td>

This adds a section that will match up the "Remove" link to the ID of the actual listing and make the removal work correctly.

****Thanks to Yannis on MSDN Blogs to pointing out this fix.****

The following is a walkthrough on how to manually adjust the default set of workflows built into the "Employee Training Scheduling and Materials" template to set the "Attendee Unregistration" workflow to work as intended.  By default, the workflow does not increment the "Available Seats" variable when a user un-registers from a course, which can lead to issues in how many users can actually register if several "un-registrations" take place.

Walkthrough:

In SharePoint Designer:
File > Open Site
Open to the Employee Training Site
Expand WorkFlows, expand Attendee Unregistration and double click "Attendee Unregistration.xoml"
The Workflow Designer for "Unregister from course" should display.
And the Delete item in Registration flow should display. Click on Actions > More Actions
Double Click the "Do Calculations" to add it to the workflow.

Open the drop down menu for this item and move it up twice, so it's the first item under "Delete Item in Registration"
Click the first "value" hyperlink and click the "fx" button next to it.
For the pop-ups, enter the information as follows:

Click the "fx" and fill out the following then click ok

Click on the "plus" hyperlink and change it to "minus"
Click on the 2^nd "value" hyperlink and enter the number one (1).
Click on the "Variable:calc" hyperlink and select "create new variable" and enter a new name, make it a "Number" Type and click "OK"

The Workflow should now look something like this:

Click on the Actions button > More Actions and add the "Upldate List Item" action.
Move this action up so it is under the action you just created above.
Click the "this list" hyperlink
Change List to : Courses
Click Add to fill out the Value Assignment

Click "fx" on the "Value Assignment" to get the "Define Workflow Lookup"

Value Assignment should look like this:

Ok out of Define Workflow Lookup and Ok out of Value Assignment
For the bottom half, choose Courses:ID for the field and then click "fx" to lookup the Value:

The Update List Item should look like this:

Ok out of the screen.
The updated Attendee Unregistration Workflow should look like this:

Finish to complete. The workflow should build.

****Special thanks to milayen from SharePointU.com for providing me this walkthrough when I needed it most, and allowing me to reproduce it here for you.****

I ran across an interesting situation recently with an application I have running on one of my WSS 3.0 boxes that performs a backup of our MCMS site.  It had been a few months since I had a need to run the app, but when I attempted it this time I came across the following error:

This control does not allow connection strings with the following keywords: `Integrated Security', `Trusted_Connection'

"Ok", I thought…this is strange.  Up until then, the application had been running just fine with no errors.  We use "mixed" authentication on our SQL boxes (standard practice), so this error didn't really make sense to me since I've been using "Integrated Security" on it the entire time and have never had any problems. 

After Googling this (with very little success I might add), I came across a few solutions that lead me to a work-around.  It turns out that when installing Windows SharePoint Services 3.0 on the server, the web.config on the default site (where WSS was installed and where the web app was running) gets modified to include multiple lines of configuration settings to help get WSS running.  No big deal…I knew this already, but apparently this can mess with how (some, not all) web applications connect to SQL by limits set on security (which are defined in the "Security Policy", and "Trust Level" sections of the web.config).

In an attempt to fix this error, I ran through each of the possible options for making a connection to SQL (including passing the username and password as clear text – bad bad bad!!)…the only option that seemed to work was to use SQL authentication instead of windows authentication.  This, of course, was not going to work for us since it's always our practice to use windows authentication for security and compatibility with all of our web applications.

I then tried removing WSS from the box (it's just a dev box to no big issue there), and removing all references in the default site web.config to SharePoint…but still no success.

I then tried creating a new Website in IIS (using a separate port 8080) and copied the app to this. 

Lo and behold – the web app now worked!! 

So, even though I basically had reset the web.config back to its original state prior to the installation of WSS (or at least to where I thought it should be), there may still have been something possibly in the IIS Meta data that was screwing with the web app (my guess anyways).

Why none of the other numerous web applications (that all have similar SQL connections by the way) running on the box didn't fail…I have no clue.  But in my case, the fix apparently seemed to be to simply move the web application to a different site to make it work.  My next step will probably be to redo IIS on the dev box and see if I can fix whatever's wrong, but for now I can at least access the web app in question with a port:8080 workaround.

For reference, here's a few links to some of the sites that lead me to this fix:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1918708&SiteID=1
http://www.eggheadcafe.com/software/aspnet/30460993/this-control-does-not-all.aspx
http://robotmindsofrobotslaves.net/?p=39

Since this is my first post, I decided that the easiest way to get things rolling was to list out some of the basic information concerning the templates available in WSS 3.0, including the "fabulous 40".

I believe that with the inclusion of the optionally available "GroupBoard Workspace" template, the total list of available templates reaches around 51…and granted, most of the following information can be found on any one of numerous other posts and forums, but my goal here is to simply provide a reference of descriptions for the templates that may provide some usefullness when deciding which ones to install and use as the base for building your site(s).

All the "40" templates can be downloaded here, and the "GroupBoard Workspace" template can be downloaded here.
For a preview of each of the templates, you can download some sample screenshots created by Todd Baginski here, or view some sample sites here.