My career in technology

This is part six of my exploration of a solution I recently completed, using an event handler to programmatically create and configure a site based on form data provided by users and a site template created and managed by a SharePoint power user.

And now, the third, (the third and an half,) and fourth items on the To Do list:   Adding an item to a list, activating a third party feature, and riding writing some CAML!

(Links to the other parts in the series are at the bottom of the post!)

        private void DoAllSiteCreationSteps()
            //Make sure this runs with appropriate privileges to actually run the commands
                //ToDo #1: Create the site
                //make sure the site has finished baking before continuing
                for (int i = 0; i < 30; i++)
                //ToDo #2:  Create the site user groups
                //ToDo #3:  Provision the site with the data about the project type for correct display
                //ToDo #4:  Set the filters for the Eval Factors List in the new site

Inside this particular site template I’m working with, there is a “Project Info” list with a couple of bits of information about the project for use in some content query web parts scattered throughout the site.

        private void DoAddControlEntries()
                //List "Project Info" - add new, name/title, and Project Type
                SPList ListToUpdate = BuildWeb.Lists["Project Info"];
                SPListItem ListItemToAdd = ListToUpdate.Items.Add();
                ListItemToAdd["Title"] = siteName;
                ListItemToAdd["Project Name"] = siteName;
                ListItemToAdd["Project Type"] = properties.ListItem["Proposal Type"].ToString();
            catch (Exception ex)
                LogTheError("EventHandlerName", "Error in DoAddControlEntries(): " + ex.Message + " Stack Trace: " + ex.StackTrace);

The code retrieves the Project Info list from the site by name, then adds a new SPListItem to the list, using data submitted by the requesting user in the initial request to populate the columns.

        private void DoActivateFeature_3rdPartyFeature()
                //activate the third-party feature
                Guid featureGUID = new Guid(theFeaturesGuid);
                SPFeatureCollection BuildWebFeatureCollection = BuildWeb.Features;
            catch (Exception ex)
                LogTheError("EventHandlerName", "Error in DoActivateFeature_3rdPartyFeature(): " + ex.Message + " Stack Trace: " + ex.StackTrace);

The next thing to do was to activate a particular site-scoped third-party feature on the site. Where I have “theFeaturesGuid”, you would put the guid of the feature, without the braces, as a string variable or in quotes.

Both adding a List Item to a SharePoint list and activating a Feature on a site are short and sweet. Using the object model, each of these tasks is pretty straightforward.

Now we get into the fun stuff. Time to take the CAML for a ride!

There is a lot going on in the next section of code. In the site template, there is a custom list with tasks, goals, things that would need to be done in the course of projects. The list is called “Evaluation Factors”, which, in aggregate, show the relative health or progress on the project. These are used for the Key Process Indicator (KPI) dashboard, a set of red/yellow/green light icons that show at a glance how the project is (or is not) progressing. Not every factor is applicable to every project, and each is applicable to a particular phase of the project. In addition to the simple boolean “Completed” column, the factors have links to templates where applicable, the person responsible (by role) for completion, and other relevant data.

What needs to be updated are the views of this list. Each phase of the project (as defined by the requirements for the KPIs, they are “Project Plan”, “Project Kickoff”, “Project Deliverables”, and “Detailed Project Review”) has a view of the list, and each view is defined to show only those items for that phase. That works great straight from the template.

But as I said, not every factor is applicable to every kind of project. Since the KPIs are defined as a percentage complete of all of the items in a group, having non-relevant items in the group skews the percentage complete (either by having all of the non-relevant items listed as complete, which makes it look like you have made more progress than you have, or by having them all show as incomplete, which makes it look like you aren’t doing yor job!) and makes the KPIs useless to determining if the project is on track.

So the group of factors for each phase needs to be further filtered by the type of project. But since that isn’t known until the request is made, it can’t be added to the template (without having close to 40 views to pick from). So the queries for each of the filters needs to be modified by the code. The filters are CAML Queries. (Note: The linked site was not used as a reference for creation of the code, but it is a pretty good basic reference on CAML.)

        private void DoChangeFactorFilters()
            string OriginalQuery;
            ModifyQuery = "<Contains>";
            ModifyQuery += "<FieldRef Name=\"Show_In\"/>";
            ModifyQuery += "<Value Type=\"String\">" + properties.ListItem["Project Type"].ToString();
            ModifyQuery += "</Value></Contains>";
            ModifyQuery += "</And></Where>";

            OriginalQuery = "<Where><Eq><FieldRef Name=\"Completion_x0020_Phase\" /><Value Type=\"Text\">Prepare Project Plan</Value></Eq></Where>";
            DoChangeQuery("Evaluation Factors", "Project Plan", OriginalQuery);
            OriginalQuery = "<Where><Eq><FieldRef Name=\"Completion_x0020_Phase\" /><Value Type=\"Text\">Conduct Project Kickoff</Value></Eq></Where>";
            DoChangeQuery("Evaluation Factors", "Project Kickoff", OriginalQuery);
            OriginalQuery = "<Where><Eq><FieldRef Name=\"Completion_x0020_Phase\" /><Value Type=\"Text\">Project Deliverables</Value></Eq></Where>";
            DoChangeQuery("Evaluation Factors", "Project Deliverables", OriginalQuery);
            OriginalQuery = "<Where><Eq><FieldRef Name=\"Completion_x0020_Phase\" /><Value Type=\"Text\">Detailed Project Review</Value></Eq></Where>";
            DoChangeQuery("Evaluation Factors", "Detailed Project Review", OriginalQuery);

ModifyQuery, a class-level string variable, is given query operators and parameters that specify that the data in the list column field named “Show_In” must contain a string equal to the data entered in the “Project Type” field in the request form. The “Show_In” field is multi-select with options for each project type.

As shown above, DoChangeQuery() takes the parameters ListName, ViewName, and OriginalQuery. As you will see in DoChangeQuery(), OriginalQuery actually doesn’t need to be in DoChangeFactorFilters() at all, or as a parameter of DoChangeQuery().

I defined OriginalQuery in the code here during testing so I could run it against the same list multiple times and not get really messed-up queries, and kept it in to show what the original queries for each filter looked like. But in the production code, those are commented out, parameter removed from DoChangeQuery(), and line 614 is uncommented…

        private void DoChangeQuery(string ListName, string ViewName, string OriginalQuery)
                SPView ViewToModify = BuildWeb.Lists[ListName].Views[ViewName];
                //OriginalQuery = ViewToModify.Query;
                ViewToModify.Query = "<Where><And>" + OriginalQuery.Substring(7, (OriginalQuery.Length - 15)) + ModifyQuery;
            catch (Exception ex)
                LogTheError("EventHandlerName", "Error changing " + ViewName + " in DoChangeQuery(): " + ex.Message + " Stack Trace: " + ex.StackTrace);

The list is retrieved by its name (passed as a parameter), and the filter is retrieved by its name (passed as a parameter). As shown, the OriginalQuery is passed in from DoChangeFactorFilters(), but if you uncomment line 614, the OriginalQuery is pulled dynamically from the existing view. After a little string wrangling, the modified query is assigned to the view.

The modified query looks like this:

<Eq><FieldRef Name=\"Completion_x0020_Phase\" /><Value Type=\"Text\">Prepare Project Plan</Value></Eq>
<Contains><FieldRef Name=\"Show_In\"/><Value Type=\"String\">"Project Type"</Value></Contains></And></Where>

I’ve broken the string into three lines for explanation. The first line is the re-written beginning of the query that comes from line 615. The second line is the substring of the original query, with the beginning and end of the query trimmed off (remember that after you trim off the first seven characters, the end of your string is seven characters earlier than the original end of the string!). The third line is from ModifyQuery.

Here it is again, this time formatted to show how a CAML query is actually structured:

            <FieldRef Name=\"Completion_x0020_Phase\" />
            <Value Type=\"Text\">Prepare Project Plan</Value>
            <FieldRef Name=\"Show_In\"/>
            <Value Type=\"String\">"Project Type"</Value>

While this CAML is a bit tricky, it won’t spit at you! (At least, not if you handle it right….)

How did I find the CAML code? I saved the list as a template, downloaded the template from the template library on the SharePoint server (http://SharePoint/_catalogs/lt/Forms/AllItems.aspx). Change the .stp file extension to .cab (.stp files are really just .cab files), open it up, and then start reading through the manifest.xml file inside. What you are looking for is the <query> tag. This will give you the original CAML query. By doing the same to a properly filtered view with the Project Type (Show_In) configured, I found what the query was supposed to look like. In fact, anytime you aren’t sure of what is going on behind the scenes in a web part, list, view, site, or any other SharePoint component, there is very likely an oportunity to save it off as a template or part of a template, which then you can open up and pick apart.

And so we move on to the fifth and sixth items on our To Do list:

                //ToDo #5:  When a site is created from the template, the KPI List items point to the template.  Reset them to point to the current site.
                //ToDo #6:  Fix the top nav so it matches parent site

That’s coming up in Part Seven!

I’d love your feedback. Is there something you think I could be doing better? Are there questions about the code I haven’t answered? Am I full of it? Let me know! Feel free to use these code samples in accordance with my usage policy.

Check out Part One here (The Setup).
Check out Part Two here (The Decision).
Check out Part Three here (If You Built It…).
Check out Part Four here (…They Will Come).
Check out Part Five here (Bring ‘Em All In).
You are reading Part Six.
Check out Part Seven here (Pointing The Way).
Check out Part Eight here (Taking Part!).
Check out Part Nine here (Deconfigured).
Part Ten (The Log Blog) is coming!

More posts about SharePoint.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: