Copy and Paste CRM Picklist Values - Part I

The web based GUI for MS CRM 4.0 offers essentially the same user interface to edit picklist attribute values as version 3. There's a tidy web form with all the buttons you'd expect in order to manage individual values (add, edit, delete, etc.), but if you want to add a range of values, say copy and paste them from an Excel spreadsheet, you'll be forced either to add them one-at-a-time, or to use one of the tools out there to generate xml to paste into the right place in your entity definition.

There is a very nice web based tool to help you do just that, but unfortunately anyone creating new entities or customizing existing ones will have to be comfortable manually editing those xml entity files - not something everyone may be happy to do.

The screen capture below demonstrates an alternative: customizing the edit attribute form to add an "edit all" button that allows multiple picklist value / label pairs to be edited and copied at once. In this example, I'm setting up an entity to manage NAICS codes. Rather than having to enter the 20 economic sector codes one-by-one, I can simply copy and paste the value / labels pairs from a spreadsheet.

In follow up posts to this one, I'll go over the details of how to set this up. This will involve creating a new aspx page and writing code to do the behind the scenes work.

Get Distinct Lookup Values

Here's another code example using HubKey's Client-side API for SharePoint. In this case 2 lists are created, a document library and a generic list. A lookup field (Subject) is added to the document library which looks up values in the Title column on the second list. The example also demonstrates getting distinct rows (in this case lookup values) by using the DataView.ToTable() method.

using System;

using System.Collections.Generic;

using System.Text;

// using HubKey's Remote SharePoint API instead of the native object model so we can update a remote server

using HubKey.Web.Services.SharePoint;

//using Microsoft.SharePoint;

using System.Data;

namespace DevHoleDemo

{

    class Program

    {

        static void Main(string[] args)

        {

            //always use a test server and site with this beta API

            SPWeb web = new SPSite("http://remoteserver/testsite001/").OpenWeb();

            Guid subjectsListId = web.Lists.Add("HubKey Subjects", "A temporary list.", SPListTemplateType.GenericList);

            SPList subjectsList = web.Lists[subjectsListId];

            // note that using the SPList.Items property here with the HubKey API returns all the items in the list

            // which can be expensive if the list contains many items already. See the following code example for a 

            // better way to add items to a list specific to the HubKey API:

            // http://www.hubkey.com/Documents/HubKeySPAPI/html/60759825-9963-20e4-c31e-ae93ea608330.htm

            SPListItemCollection subjects = subjectsList.Items;

            SPListItem item1 = subjects.Add();

            item1["Title"] = "SharePoint";

            item1.Update();

            SPListItem item2 = subjects.Add();

            item2["Title"] = "Microsoft CRM";

            item2.Update();

            SPListItem item3 = subjects.Add();

            item3["Title"] = "Microsoft CRM";

            item3.Update();

            Guid booksDocLibId = web.Lists.Add("HubKey Books", "A temporary document library.", SPListTemplateType.DocumentLibrary);

            SPList booksDocLib = web.Lists[booksDocLibId];

            booksDocLib.Fields.AddLookup("Subject", subjectsListId, false);

            booksDocLib.Update();

            SPFieldLookup subjectLookup = new SPFieldLookup(booksDocLib.Fields, "Subject");

            subjectLookup.LookupField = "Title";

            subjectLookup.Update();

            // build the query to get a list of subjects

            // (pretend we are starting from scratch)

            SPFieldLookup subjectLookup2 = (SPFieldLookup)booksDocLib.Fields["Subject"];

            SPList subjectsList2 = web.Lists[new Guid(subjectLookup2.LookupList)];

            SPQuery query = new SPQuery();

            query.ViewFields = "<FieldRef Name='" + subjectLookup2.LookupField + "'/>";

            // here we use a data table so that we can get distinct titles (subjects)

            // if you're not woried about distinct values you could ignore this, but it's

            // also a convenient way to query the list items (e.g. see the RowFilter below)

            DataTable subjectsTable = subjectsList2.GetItems(query).GetDataTable();

            DataView dataView = new DataView(subjectsTable);

            DataTable groupedSubjectsTable = dataView.ToTable(true, "Title");

            SPFieldLookupValue lookupValue = null;

            // here we're looping through 2 distinct title rows ('SharePoint' and 'Microsoft CRM')

            foreach (DataRow row in groupedSubjectsTable.Rows)

            {

                string title = (string)row[0];

                // get the ID of the first row that matches the title

                dataView.RowFilter = "Title = '" + title + "'";

                int firstId = (int)dataView.ToTable(true, "ID").Rows[0][0];

                // create a new lookup value (we'll use the 2nd row's lookupValue below)

                lookupValue = new SPFieldLookupValue(string.Format("{0};#{1}", firstId, title));

            }

            SPFile file = booksDocLib.RootFolder.Files.Add("test.txt", Encoding.ASCII.GetBytes("A test document."));

            // update the subject with the 2nd lookup value;

            file.Item["Subject"] = lookupValue;

            file.Item.Update();

            // clean up - pause here if you want to view the results on the server.

            subjectsList.Delete();

            booksDocLib.Delete();

        }

    }

}

SharePoint API: A Client-side Object Model

One common issue (see question #2) that developers using Microsoft's SharePoint platform are faced with is that the SharePoint object model can only be used when the application is run on a server in the SharePoint farm.

The following is code that demonstrates using HubKey's Client-side API for SharePoint. This API wraps many of SharePoint Server 2007's Web Services (Lists Web Service, Webs Web Service, etc.) to provide a familiar object model that can be used on remote client machines without having to process XML requests and responses. In this case the code from the MSDN article How to: Upload a File to a SharePoint Site from a Local Folder was copied verbatim, and it compiles and runs successfully on a client computer, even though the SharePoint DLLs are not referenced or installed.

Update: You can download a demo copy of the API - click here for information.

Update II: The API now includes a demo application - see this post for details.

using System;

using System.IO;

// Using HubKey.Web.Services.SharePoint in place of Microsoft.SharePoint

using HubKey.Web.Services.SharePoint;

namespace HubKey.DevelopmentHole

{

    class Program

    {

        static void Main(string[] args)

        {

            Program program = new Program();

            program.RunDemo();

        }

        public void RunDemo()

        {

            string webUrl = "http://remoteserver";

            SPSite site = new SPSite(webUrl);

            SPWeb web = site.OpenWeb();

            //create a new document library

            Guid id = web.Lists.Add("Temp Doc Lib", "A temp document library.", SPListTemplateType.DocumentLibrary);

            SPList list = web.Lists[id];

            //add a field

            list.Fields.Add("Upload Source", SPFieldType.Text, false);

            list.Update();

            string fileUrl = webUrl + "/Temp Doc Lib/New Folder/test.txt";

            string localFileName = @"D:\Temp\test.txt";

            string localFileContents = "A temp file uploaded by HubKey's SharePoint client-side API.";

            File.WriteAllText(localFileName, localFileContents);

            //Upload the file using the API code from 'How to: Upload a File to a SharePoint Site from a Local Folder'

            //see http://msdn.microsoft.com/en-us/library/ms454491.aspx

            UploadFile(localFileName, fileUrl);

            //Check out

            SPFile file = web.GetFile(fileUrl);

            file.CheckOut();

            //update the title and new upload source field by using the file's SPListItem property...

            file.Item["Title"] = "A temp title";

            file.Item["Upload Source"] = "HubKey's SharePoint client-side API.";

            file.Item.Update();

            //Check in

            file.CheckIn("Checked in by HubKey's SharePoint client-side API");

            //Get the file contents

            string serverFileContents = web.GetFileAsString(fileUrl);

            System.Diagnostics.Debug.Assert(string.Equals(localFileContents, serverFileContents));

            //Tidy up

            list.Delete();

        }

        public void UploadFile(string srcUrl, string destUrl)

        {

            if (!File.Exists(srcUrl))

            {

                throw new ArgumentException(String.Format("{0} does not exist",

                    srcUrl), "srcUrl");

            }

            SPWeb site = new SPSite(destUrl).OpenWeb();

            FileStream fStream = File.OpenRead(srcUrl);

            byte[] contents = new byte[fStream.Length];

            fStream.Read(contents, 0, (int)fStream.Length);

            fStream.Close();

            EnsureParentFolder(site, destUrl);

            SPFile file = site.Files.Add(destUrl, contents);

            SPFolder folder = file.ParentFolder;

        }

        public string EnsureParentFolder(SPWeb parentSite, string destinUrl)

        {

            destinUrl = parentSite.GetFile(destinUrl).Url;

            int index = destinUrl.LastIndexOf("/");

            string parentFolderUrl = string.Empty;

            if (index > -1)

            {

                parentFolderUrl = destinUrl.Substring(0, index);

                SPFolder parentFolder

                    = parentSite.GetFolder(parentFolderUrl);

                if (!parentFolder.Exists)

                {

                    SPFolder currentFolder = parentSite.RootFolder;

                    foreach (string folder in parentFolderUrl.Split('/'))

                    {

                        currentFolder = currentFolder.SubFolders.Add(folder);

                    }

                }

            }

            return parentFolderUrl;

        }

    }

}

Get a Valid SharePoint File Or Folder Name

If you're programatically uploading files to a SharePoint document library or creating folders, the file or folder name must not contain certain invalid characters. If it does, you'll get an error, for example:

The file or folder name ".." contains invalid characters. Please use a different name. Invalid characters include the following: ~ " # % & * : < > ? / \ { | }. The name cannot begin or end with dot and cannot contains consecutive dots.

File or folder names must also be less than or equal to 128 characters in length. In addition, the total unescaped URL length (less the length of the URI authority e.g. http://localhost:2008/) must not exceed 260 characters.

The following code uses a regular expression to validate a file or folder name before it is uploaded or created (without checking the total URL length):

using System;

using System.Web;

using System.Text.RegularExpressions;

namespace DevHoleDemo

{

    class Program

    {

        static Regex illegalPathChars = new Regex(@"^\.|[\x00-\x1F,\x7B-\x9F,"",#,%,&,*,/,:,<,>,?,\\]+|(\.\.)+|\.$", RegexOptions.Compiled);

        static void Main(string[] args)

        {

            string fileName = "/new%20file.txt";

            string validFileName = fileName;

            if (GetValidFileOrFolderName(ref validFileName))

                Console.WriteLine("A valid file or folder name for '{0}' is '{1}'.", fileName, validFileName);

            else

                Console.WriteLine("Could not get a valid file or folder name.");

        }

        public static bool GetValidFileOrFolderName(ref string path)

        {

            return GetValidFileOrFolderName(ref path, '_');

        }

        public static bool GetValidFileOrFolderName(ref string path, char replacementChar)

        {

            if (path == null)

                return false;

            path = illegalPathChars.Replace(HttpUtility.UrlDecode(path.Trim()), replacementChar.ToString());

            if (path.Length > 128)

            {

                path = path.Substring(0, 128);

                return GetValidFileOrFolderName(ref path, replacementChar);

            }

            return path.Length > 0;

        }

    }

}

Programatically Manage Remote Documents with the FrontPage RPC Library

We've rolled up some of the ideas in previous posts about FrontPage Server Extensions RPC methods into a free CC licensed c# code library.

You can download the source code along with a demo copy of HubKey's SharePoint Client API from HubKey's website - click here for details.

Documentation about the members exposed through the HubKey.Net.FrontPageRPC.WebClient class can be found online here.

Features of the class include:

• Upload or download many documents in one web request using the 'get documents' and 'put documents' methods.


• True file streaming (without pre-loading to memory) for faster uploads.


• Asynchronous methods including an asynchronous MoveDocuments method.


• Auto detection of FrontPage dll paths and versions.

The following FrontPage Server Extensions RPC Methods are included in the library:

• checkin document method - remotely check-in a document to source control.


• checkout document method - remotely check-out a document from source control.


• getDocsMetaInfo method - download document metadata to a client machine.


• get document method - remotely download a document with metadata from a web folder or SharePoint document library. Includes an asynchronous method.


• get documents method - programatically download documents with metadata from a web folder or SharePoint document library in one web request. Includes an asynchronous method.


• list documents method - obtains a list of files and folders on the web or SharePoint server. Sorts the results into a tree structure to allow for use in a TreeView control.


• list versions method - retrieves metadata for all the versions of the specified document.


• move document method - programatically move (rename) or copy a file or folder. Allows for moves between webs or even servers by downloading and then uploading the file.


• open service method - list service meta information.


• put document method - programatically upload a file to a SharePoint document library or web server with meta info. Includes an asynchronous method.


• put documents method - remotely upload files to a SharePoint document library or web server with meta info. Includes a method to asynchronously upload large files. Allows true streaming (without loading into memory first) for large file uploads.


• remove documents method - delete files or folders from a SharePoint document library or web server.


• setDocsMetaInfo method - update document meta data from a client computer.


• uncheckout document method - revert a document to the version before it was checked out.


• url to web url method - break a url into its web and web relative components.

A very brief code example follows - we'll cover an in depth sample application in a future post:

using System;

using System.Collections.Generic;

using System.Text;

namespace HubKey.Net.FrontPageRPC

{

    class Program

    {

        static void Main(string[] args)

        {

            WebClient webClient = new WebClient();

            //upload a document and check it in

            MetaInfoCollection meta = new MetaInfoCollection();

            meta.Add("vti_title", "My Title");

            UploadDocumentResponse uploadDocumentResponse = webClient.UploadDocument("https://localhost/Docs1/test.txt", @"C:\test.txt", meta);

            if (uploadDocumentResponse.Document.IsCheckedOut)

                webClient.CheckInDocument(uploadDocumentResponse.Document, "uploaded via RPC");

            //move a document between servers

            MoveDocumentResponse moveDocumentResponse = webClient.MoveDocument("http://localhost/Docs2/test.txt", "https://remoteserver/Docs2/test.txt");

        }

    }

}

Logging Workflow Errors in the Workflow History List

By overriding the HandleFault method and using the SPWorkflowActivationProperties.Workflow.CreateHistoryEvent method, it's possible to write unhandled exceptions (without much coding overhead) to the history log to assist in debugging long running workflows.

The code at the bottom of this post demonstrates the idea. A potential issue is that the description field of the workflow history view displayed at the bottom of the workflow status page will be truncated if it's longer that 255 characters - making it not ideal to show long exception messages and/or stack traces. One solution is to modify the code the WrkStat.aspx page to display a link to the workflow history list item, for example by adding the following code to override the ViewFields used by the idHistoryView ListViewByQuery (near the very bottom of the page), so that a link to the item (in this case a DocIcon field) is displayed. Alternatively, you could code a url into the description, but that would require information about the workflow history item (ID, etc) that's not available by using the CreateHistoryEvent method.

<%
if (this.idHistoryView.Visible == true) {
    this.idHistoryView.Query.ViewFields = "<FieldRef Name=\"Occurred\" /><FieldRef Name=\"Event\" /><FieldRef Name=\"User\" /><FieldRef Name=\"DocIcon\" /><FieldRef Name=\"Description\" /><FieldRef Name=\"Outcome\" />";
}
%>
<SharePoint:ListViewByQuery
runat="server" DisableSort="true" DisableFilter="true" ID="idHistoryView" />

...

using System;

using System.Collections.Generic;

using System.Text;

using System.Text.RegularExpressions;

using System.Workflow.Activities;

using System.Workflow.ComponentModel;

using Microsoft.SharePoint.Workflow;

namespace HubKey.DevelopmentHole

{

    public sealed partial class MyWorkflow: SequentialWorkflowActivity

    {

        //assign workflowProperties in the OnWorkflowActivated activity

        public SPWorkflowActivationProperties workflowProperties = new SPWorkflowActivationProperties();

        protected override ActivityExecutionStatus HandleFault(ActivityExecutionContext executionContext, Exception exception)

        {

            try

            {

                workflowProperties.Workflow.CreateHistoryEvent(

                    (int)SPWorkflowHistoryEventType.WorkflowError,

                    0,

                    workflowProperties.OriginatorUser,

                    "Nothing good",

                    string.Format("An exception occured during activity {0}. Check the item data for more information.", executionContext.Activity.Name),

                    Misc.FormatException(exception));

            }

            catch { }

            return base.HandleFault(executionContext, exception);

        }

    }

    public static class Misc

    {

        static Regex stackRegex = new Regex(@"\r\n   at ", RegexOptions.Compiled);

        public static string FormatException(Exception ex)

        {

            return FormatException(ex, new StringBuilder(), 0);

        }

        private static string FormatException(Exception ex, StringBuilder sb, int tab)

        {

            if (ex == null)

                return sb.ToString();

            string sTab = new string('\t', tab);

            sb.AppendFormat("{0}Source: {1}\r\n", sTab, ex.Source);

            sb.AppendFormat("{0}Message: {1}\r\n", sTab, ex.Message);

            sb.AppendFormat("{0}StackTrace: {1}\r\n", sTab, FormatStackTrace(ex, sTab));

            sb.AppendLine();

            return FormatException(ex.InnerException, sb, ++tab);

        }

        private static string FormatStackTrace(Exception ex, string sTab)

        {

            string stack = ex.StackTrace;

            if (stack == null)

                return null;

            stack = stackRegex.Replace(ex.StackTrace, delegate(Match m)

            {

                return string.Format("\r\n{0}   at ", sTab);

            });

            return stack;

        }

    }

}

Provide Status Updates for Long Running Operation Jobs - Part II

Following on from Part I that showed a simple way to give users some feedback on the progress status of long running jobs - here is the same code, this time running in an .aspx page. To run ASP.NET server side code you'll need to add a PageParserPath node to your web.config as covered here.

As before, make sure that your site collection includes a list named 'Long Running Operation Status' at the root web. This list is created when you install the Office SharePoint Server Publishing Infrastructure feature at the Site Collection Features level.

<%@ Page language="C#" MasterPageFile="~masterurl/default.master"    Inherits="Microsoft.SharePoint.WebPartPages.WebPartPage,Microsoft.SharePoint,Version=12.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c" meta:progid="SharePoint.WebPartPage.Document" %>
<%@ Assembly Name="Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"%>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Import Namespace="Microsoft.SharePoint.Publishing" %>
<%@ Import Namespace="Microsoft.SharePoint.Publishing.Internal" %>
<%@ Import Namespace="Microsoft.SharePoint.Utilities" %>
<script runat="server">
    const string PROGRESS_PAGE_URL = "/_layouts/LongRunningOperationProgress.aspx";

    public void StartJob(object sender, System.EventArgs e)
    {
        SPWeb web = SPContext.GetContext(this.Context).Web;
        LongRunningJob longRunningJob = new LongRunningJob();
        longRunningJob.Title = "Demo Long Running Job";
        longRunningJob.TotalOperationsToBePerformed = 15;
        longRunningJob.RedirectWhenFinished = true;
        longRunningJob.NavigateWhenDoneUrl = SPContext.GetContext(this.Context).List.RootFolder.ServerRelativeUrl;
        longRunningJob.Start(web);
        string url = string.Format("{0}{1}?JobId={2}", web.Url, PROGRESS_PAGE_URL, longRunningJob.JobId);
        SPUtility.Redirect(url, SPRedirectFlags.Default, this.Context);
    }
    
    class LongRunningJob : LongRunningOperationJob
    {
        public override void DoWork()
        {
            for (this.OperationsPerformed = 0; this.OperationsPerformed < this.TotalOperationsToBePerformed; this.OperationsPerformed++)
            {
             //Do your work here
                this.StatusDescription = string.Format("im in ur long running job, doing ur work {0} of {1}...", this.OperationsPerformed, this.TotalOperationsToBePerformed);
                this.UpdateStatus();
                System.Threading.Thread.Sleep(1000);
            }
        }
    }
</script>
<asp:Content ID="Content1" ContentPlaceHolderId="PlaceHolderMain" runat="server">
<asp:Button runat="server" OnClick="StartJob" Text="Start Job" id="Button1"/>

</asp:Content>