Change MS Office file properties Programmatically (Colligo issues with iPad – SharePoint)

“Colligo briefcase” is a product used for synching/accessing SharePoint documents using iPad. All works well, as long as we use Out-of-the-box libraries. I recently came across a portal which showed a completely different display in iPad. The document Name looks completely different from its web view. After some research we realized that the problem occurs whenever there is a custom implementation (with custom I mean it has Custom List definition, which was used to create a Library instance). But how does the document name change? Or from where is this name picked from?

The answer for this question is: it picks up the document’s “Title” property to display the name in iPad. I am not sure, why Colligo shows it differently for different definition of libraries. I have no idea what makes Colligo decide to take the title property for the display.

Document’s Title property can be viewed by clicking on File Tab (or right click on the document and click on properties , visit Details tab to view the properties):

How do we solve this problem. Below are 2 options which maybe used until Colligo releases an upgrade version with resolution to this problem.

1. Do not create List definition if we do not really need it. Instead create library instances from the default Library template (we can do this programmatically as well).
2. Create an event handler to update the “Title” property of the document after upload. We can have this event handler associated with content type so that we need not add it to every single library. Associating and dissociating of the event handler can be done on feature activation and deactivation respectively. This would enable us to remove the event handler without having to deploy the code if Colligo comes up with a solution for this problem.

Below is the code block (console application) to be used to update the “Title” and “Subject”

static void Main(string[] args)         {              SPSite site = new SPSite(“http://YourSiteURL”);             SPWeb web = site.OpenWeb();             string filePath = “path of the file to be updated”;             SPFile file = web.GetFile(filePath);             string itemTitle = file.Title;             Stream streamItemDoc = file.OpenBinaryStream();             Package packageDoc = Package.Open(streamItemDoc, FileMode.Open, FileAccess.ReadWrite);             packageDoc.PackageProperties.Title = file.Name.Substring(0, file.Name.LastIndexOf(‘.’));             packageDoc.PackageProperties.Subject = “Sample Subject”;              PackagePartCollection packageCollection = packageDoc.GetParts();             PackagePart part = packageCollection.FirstOrDefault();             Uri uriProps = part.Uri;              XmlDocument xmlDocPP = null;             PackagePart curPP = packageDoc.GetPart(uriProps);             using (Stream streamProps = curPP.GetStream())             {                 xmlDocPP = new XmlDocument();                 xmlDocPP.Load(streamProps);             }              xmlDocPP.Save(packageDoc.GetPart(uriProps).GetStream(FileMode.Create, FileAccess.Write));             packageDoc.Flush();             file.SaveBinary(streamItemDoc);             file.Properties[“vti_title”] = itemTitle;             file.Update();          }

Points to remember:

1. Whenever, we update the package Title property it updates the List Item Title as well and therefore in the penultimate line, the title is updated back to what it was originally.
2. Windows.Base is the reference required to be added (maybe required only for client projects such as Console Application, Windows Application)

Advertisement

Programmatically assigning nested Terms to “Taxonomy” column – Managed Metadata

According to me, Managed Metadata is one of the greatest additions to SharePoint. There are many advantages of using Managed Metadata Service and needless to mention the flexibility and the ease of maintenance it provides to maintain enterprise data. However, these details are out of scope as far as this blog is concerned.

Creating Taxonomy columns which uses Managed Metadata Service to bind the data through UI is straight forward. However, to do it programmatically would require considering few factors. There are many blogs which speak about this, but the one which stands out highlighting the details (as far as I am considered) is here:

http://www.sharepointconfig.com/2011/03/the-complete-guide-to-provisioning-sharepoint-2010-managed-metadata-fields/

This article speaks about creating a taxonomy column and binding the TermSet for that created column.

So far so good, but now I want to bind the taxonomy columns to terms or nested terms and not the termset. This is something I could not find over web and therefore this blog. Consider the example where:

• “Group Name” is “Vehicles”;
  • “TermSet” are “Cars”, “Trucks” etc
    • “Terms” are “Honda”, “Toyota” etc
      • “Terms inside Honda” are “Civic”, “Accord”, “CRV” etc
      • “Terms inside Toyota” are “Camry”, “Corolla”, “Prius” etc

Now I want to create a taxonomy column which would allow me to select only “Honda” make “Cars”. Here I have nested terms and I cannot bind the column to termset “Cars” or “Trucks” as I want users to select only “Honda” vehicles for one list and another list which would have only “Toyota” vehicles selectable for a different Taxonomy field.

Solution: Below is the code block which would do the expected (observe code assignment for “AnchorId” which was assigned to Guid.Empty for TermSet in the link shared earlier)

public static void AssignTermToTaxonomyField(SPSite site, Guid fieldId, string termGroup, string termSetName, string termName)
{
if (site == null)
{
throw new ArgumentNullException(“site”);
}
if (site.RootWeb.Fields.Contains(fieldId))
{
TaxonomySession session = new TaxonomySession(site);
if (session.DefaultKeywordsTermStore != null)
{
// get the default metadata service application   
TermStore termStore = session.DefaultKeywordsTermStore;
Group group = (from g in termStore.Groups where g.Name.ToLower() == termGroup.ToLower() select g).FirstOrDefault();
if (group != null && !(string.IsNullOrEmpty(termName)))
{
TermSet termSet = group.TermSets[termSetName];
TaxonomyField field = site.RootWeb.Fields[fieldId] as TaxonomyField;
foreach (Term term in termSet.Terms)
{
if (term.Name.ToLower() == termName.ToLower())
{
  // connect the field to the specified term  
field.SspId = term.TermStore.Id;
field.TermSetId = termSet.Id;
field.TargetTemplate = string.Empty;
field.AnchorId = term.Id;
field.Update();
break;
}
}
}
}
else
{
throw new ArgumentException(string.Format(“DefaultKeywordsTermStore not found in site {0}”, site.Url));
}
}
else
{
throw new ArgumentException(string.Format(“Field {0} not found in site {1}”, fieldId, site.Url), “fieldId”);
}
}

So the points to remember are:

• Assign Site Column’s “SspId” to Term’s TermStoreId (Ex: Honda or Toyota)
• Assign Site Column’s “TermSetId” to Term’s TermSetId (nested terms would also be part of the same termsets only)  (Ex: Cars)
• **Assign Site Column’s “AnchorId” to the actual nested term’s Id which we want to map (Ex: Honda or Toyota)

Hope this helps and Thank you for visiting my blog!

SharePoint : Custom Email for Task with Outlook ribbon control “Open this Task”

I came across a very interesting requirement with respect to “Tasks” in SharePoint but before we get into the requirement lets get into few details first.

As most us are aware of the fact that when a task item is created, either directly in the task list or through a Workflow an alert email would be sent saying a task has been assigned to you (provided alerts are enabled). When we look at that email in Outlook we see an additional control in the ribbon “Open this Task” under the group “Open” as shown in the figure

Now let me explain the requirement: We have a custom workflow built using sequential workflow template in Visual studio 2010. The customer is not convinced with the details in the email body generated by SharePoint.

1. we have to customize the body and subject as per their request
2. Retain that Ribbon control “Open this Task” when the email is sent
3. If possible, have the “From Address” in the email to be the person who initiated this workflow

That is all what was requested, looks very reasonable, simple and straight forward. When I started implementing it my first impression was it is not as simple as it looks. Later on when I went through different approaches I felt I wasn’t very wrong about this requirement and eventually it turned out be a very simple solution. As they often say “All things are difficult before they are easy”. So I thought I would post my approaches and the final solution.

• I have used “SPWorkflowTaskProperties” class in the Task Created Event and sent it two properties “SPWorkflowTaskProperties.HasCustomEmailBody = true” and “SPWorkflowTaskProperties.EmailBody= <My Custom HTML Email Body> ” With this I can satisfy requirement 1 & 2 but not 3 (listed above).  Also if I were to create a task and if I want to send an email using Event Handler on item created I may not be able to use this. So for the generic purposes this would not fit in.
• Next I thought of modifying the alert template but then it would have an impact on all task lists which is not a recommended option.
• So, I have decided that I have to disable the alerts for that Task list and use “SmtpClient or SPUtility” class instead to send email which can be used at different requirements such as “event handlers/workflows or any other for that matter. With this class we do have control over all the aspects “From, To, Subject, Email Body, etc”. The only problem with this to achieve the requirement # 2 (Ribbon control in outlook). I always wondered how would outlook recognize that an email is for Task, how is this ribbon control activated as soon as it sees an email.

I believed that the alert email sent has some headers which mark this as Task which is understood by outlook and displays the controls accordingly. So now the question is what are those headers. After some research I figured out the Mail message headers. Below is the code for sending an email which also includes those headers and satisfies all the above mentioned requirements (1, 2 & 3)

/// <summary>
      /// Send Email with a control “Open this Task” in Outlook ribbon
      /// </summary>
      /// <param name=”web”>SharePoint Web Object (SPWeb)</param>
      /// <param name=”HtmlBody”>Body of the Email (string)</param>
      /// <param name=”ToEmailId”>Email Id of the recipient (string)</param>
      /// <param name=”listItem”>SharePoint List item Object (SPListItem)</param>
      /// <param name=”EmailSubject”>Subject of the Email (string)</param>
      /// <returns></returns>
public object[] SendMail(SPWeb web, string HtmlBody, string ToEmailId, SPListItem listItem, string EmailSubject)
{
try
{

SPWebApplication webApp = web.Site.WebApplication;

MailMessage message = new MailMessage();

//Have a dynamic email address if you wish to change the From Address        message.From = new MailAddress(webApp.OutboundMailSenderAddress, web.Title);

message.BodyEncoding = Encoding.UTF8;

message.IsBodyHtml = true;

message.Body = HtmlBody;

message.To.Add(ToEmailId);

message.SubjectEncoding = Encoding.UTF8;

message.Subject = EmailSubject;

string domain = webApp.OutboundMailSenderAddress.Remove(0, webApp.OutboundMailSenderAddress.LastIndexOf(‘@‘));

message.Headers.Add(“Message-Id“, “<3BD50098E401463AA228377848493927” + Guid.NewGuid().ToString(“D“) + domain + “>“);

message.Headers.Add(“X-Sharing-Title“, this.ConvertToBase64String(listItem[“Body“].ToString()));

message.Headers.Add(“X-AlertTitle“, this.ConvertToBase64String(“System“));

message.Headers.Add(“Content-Class“, “MSWorkflowTask“);

message.Headers.Add(“X-AlertWebUrl“, this.ConvertToBase64String(web.Url));
message.Headers.Add(“X-AlertServerType“, “STS“);

message.Headers.Add(“X-AlertWebSoap“, this.ConvertToBase64String(web.Url + “/_vti_bin/alerts.asmx“));
message.Headers.Add(“X-Sharing-Config-Url“, “stssync://sts/?ver=1.1&type=tasks&cmd=add-folder&base-url=” + Uri.EscapeDataString(web.Url) + “&list-url=” + Uri.EscapeDataString(listItem.ParentList.RootFolder.ServerRelativeUrl) + “&guid=” + Uri.EscapeDataString(listItem.ParentList.ID.ToString(“D“)));
message.Headers.Add(“X-Sharing-Remote-Uid“, listItem.ParentList.ID.ToString(“D“));
message.Headers.Add(“X-Sharing-WssBaseUrl“, this.ConvertToBase64String(web.Url));
message.Headers.Add(“X-Sharing-ItemId“, this.ConvertToBase64String(listItem.ID.ToString()));

SmtpClient client = new SmtpClient(webApp.OutboundMailServiceInstance.Server.Address);
client.Credentials = CredentialCache.DefaultNetworkCredentials;
client.Send(message);

return new object[] { true };
}
catch (Exception error)
{

return new object[] { false, error.Message }; }
}

string ConvertToBase64String(string InputString)
{
return “=?utf-8?B?” + System.Convert.ToBase64String(UTF8Encoding.UTF8.GetBytes(InputString)) + “?=“;
}

Note:

• Any of the above mentioned solutions can be used based on the requirement. I have therefore listed out different approaches and I would leave it to your best judgement to decide on the selection of approach.
• Above code is not a completely cleaned up code, you can remove unnecessary headers.

Hope this helps! Thank you for visiting my blog.

Hide Sub Menus in Actions Tab – SharePoint: Part 1

Few months ago, I was asked, “Is it possible to restrict the users (even with sufficient privileges to add, edit, delete items) from Editing, adding, deleting the items using ‘Edit in DataSheet‘ option” because the application had a Custom Form for Add and Edit and those forms had some validations. It is very obvious that with Edit in DataSheet Form all it prompts you for are very few validations such as Required Field, DataType etc, but does not support validation depending on two colums.

I felt this would make a very interesting post. Also, I believe this is a very common requirement many customers would like to have it. My first option as always (as most of them would expect as per my previous posts) was to have a javascript fucntion to implement this.

• Add a Content Editor WebPart to the Lists “All Items.aspx” page, where you would have the option “Edit in DataSheet”, Export to Spread Sheet” etc under Actions Tab.
• In the Content Editor WebPart Click Edit –> Modify Shared WebPart –> Source Editor — Paste the below Script.

<html>
<head>
<script type=”text/javascript” language=”javascript”>
HideDataSheet();
function HideDataSheet()
{
   var vMenu = document.getElementsByTagName(‘menu’);
   for(var k = 0; k < vMenu.length; k++)
   {
     var vMenuItem = vMenu[k].getElementsByTagName(‘ie:menuitem’);
     for(var i = 0; i < vMenuItem.length; i++)
     {
        if(vMenuItem[i].getAttribute(“text”) == “Edit in Datasheet” || vMenuItem[i].getAttribute(“text”) == “Export to Spreadsheet“)
        {
          vMenuItem[i].setAttribute(“hidden”,true);
        }
      }
   }
}
</script>
</head>
<body>
</body>
</html>

• Save the Content and click OK.

Now When you click on Actions Tab you will observe that the two options “Export to Spread Sheet”, “Edit in DataSheet” no longer appear.

This script is tested across different browsers: IE, Firefox, Chrome and it works as expected. However, Please note that you wouldn’t be seeing the Option “Edit in DataSheet” in browsers other than IE even without the above script as there is a default script which checks if the browser is IE or not.

Similarly, this script can be used to hide any other Sub menus. Please make sure that you replace the Title you are looking for with the one marked in Red.

A few more queries and its resolution with respect to this feature in my next Post.

Thank you!

Hiding Form Controls on selection Change in a Custom List Form (Attach Javascript to Form Controls) – No Server Side Code

Here is a very Simple requirement which many users would like to have it. Say for instance when the New Item Form of a SharePoint List is loaded, the user would be displayed with all the controls (Form Controls), among these controls we have a Choice field of type radio button and Titled “Location”. The values in the Choice Field read as “USA” and “Other”.

So if he user selects “USA” we need to display another Choice Field of type “Dropdown” Titled “State” (which contains all states of USA) and display a Multiline Textbox to Key in Country, State when “others” is selected**. The data should be saved back to the List

To do this we first we need to create a Custom List Form (Creating a Custom List Form is out of scope of this post). Place the form in a Table (if you don’t have a Table already) and ID as ’tblDataTable‘. Now add ID’s to the rows which contains the controls to be hidden and shown. In this example I have it as ‘trLocation‘ and ‘trNote‘. Once we are done with it, we need to attach a Javascript fucntion to the Radio Button: The code marked in Brown color below is how we attach the javascript function to the click event:

<script type=”text/javascript” language=”javascript”>
 
 _spBodyOnLoadFunctionNames.push(“hideLocations”);
 
 function hideLocations()
   {
     var vTblDataRow = document.getElementById(‘tblDataTable’);
     document.getElementById(‘trLocation’).style.display = ‘none’;
     document.getElementById(‘trNote’).style.display = ‘none’;
  
     if(typeof(vTblDataRow) != ‘undefined’)
     {
      var vControls = vTblDataRow.getElementsByTagName(‘input’);
      if(vControls.length > 0)
      {
       for(var i=0; i < vControls.length; i++)
       {
        if(vControls[i].type == ‘radio’)
        {
         vControls[i].onclick = function NewFunction()
               {
                  var vlblRadioValue = this.nextSibling;               
                  if(vlblRadioValue.innerText == ‘USA’)
                  {
                       document.getElementById(‘trLocation’).style.display = ‘block’;
                       document.getElementById(‘trNote’).style.display = ‘none’;
                  }
                  else if(vlblRadioValue.innerText == ‘Others’)
                   {
                       document.getElementById(‘trNote’).style.display = ‘block’;
                       document.getElementById(‘trLocation’).style.display = ‘none’;
                    }else{//Add additional functionality if you wish}
                }               
        }
       }
      }
     }
   }
 </script>

Add this Code to the page which hosts the Custom List Form or alternatively you can add a content Editor WebPart which holds this code and you are all set to go.

Note: You may have to add few more additional lines of code to in those if else conditions to satisfy more requirements such as the Dropdown Containing State should not be updated after clicking “Ok” (Submit) Button when “Others” is selected. so in the Conditional braching we need to clear of the selection for State Dropdown. 

Thanks for Visiting my blog!

**This is just an example and the actual requirement can be more realistic.

Pull Unique Values from a List in DataView WebPart

I was recently asked to develop a WebPart which enables searching in a List. This doesn’t sound like a big deal as we can always use the DataView WebPart which takes Query String Parameters as Filter Values to display the search Results.

I am nowhere concerned with the Search Results WebPart as we have multiple ways to implement it. My interest is on the Search Page than anything else. In order to search we should have a Page where the users can select their Parameters and these Parameters should be the Metadata available in the List. Doesn’t sound Complicated? Okay! how about this, When ever a new Record is added to the List an Additional Checkbox with the New Metadata Values should be displayed in the search Page as a parameter. Needless to mention that all the Search Field values present in the Page should be Unique.

We have few ddwrt functions which can help our cause. One such function is “ddwrt:NameChaged” but we have a problem with this function as it always compares with the previous value. Let me illustrate this: Consider you have the data for a Column as below:

Value1, Value1, Value2, Value2, Value2, Value3

In this case you will get the data displayed as Value1,Value2,Value3 which is expected. But, if had the as below then we run into problems:

Value1,Value2,Value1,Value3,Value2 – Here the result would be Value1,Value2,Value1,Value3,Value2 instead of Value1,Value2,Value3.

So for obvious reasons this function is ruled out as we cannot expect the data to be in a specified format and the comparison is always with just the previous value and not all the previous values as a whole.

After a bit more digging into google and other sources found another function which is:

not(@Column1=preceding-sibling::Row/@Column1)

Use this function anywhere you wish to get the Distinct Values. we can use this in the <xsl:template name=”dvt_1″> Template where we have the Rows variable defined

<xsl:variable name=”Rows” select=”/dsQueryResponse/Rows/Row/not(@Column1=preceding-sibling::Row/@Column1)”/>

The above setting works good for a Drop down or search (based on Just one parameter) . But as explained earlier if we wish to have multiple columns with Checkboxes to select multiple values either from the same column or different columns we need to place this at Individual Row level that is an If condition has to be in place, just before displaying the Data.

<xsl:if test=”not(@Column1=preceding-sibling::Row/@Column1)”>

Similarly repeat this for different columns but do remember to change it to proper column name to get expected results.

Working with Versions in SharePoint List programmatically

There are very few posts on working with Versions in SharePoint Lists Programmatically. While browsing through those posts I ran in to a question saying “How to get the Columns which changed in the SharePoint List?”

Problem Description:

How to get only the Updated Columns in a SharePoint List with respect to Versions? or maybe get the columns changed from the previous version with respect to the current Version?

Solution:

ArrayList alUpdatedFields = new ArrayList();

bool bItemChanged = false;

SPListItemCollection objItemColl = objList.GetItems(objQuery);

SPListItem objItem = objItemColl[0];

SPListItemVersionCollection objVersionColl = objItem.Versions;

if (objVersionColl != null && objVersionColl.Count > 0)
{
foreach (SPListItemVersion item in objVersionColl)
{
if (item.VersionLabel.ToString() != objItem[“_UIVersionString”].ToString())
{
foreach (SPField objField in objItem.Fields)
{
if (!objField.ReadOnlyField && IsValueChanged(objField.Type, objItem[objField.InternalName], item[objField.InternalName]))
{
bItemChanged = true;
alUpdatedFields.Add(objField.Title);
}
}
if (bItemChanged)
{
break;
}
}
}
}

In the above code I am looking for the previously modified version compared to the current version and adding those fields to the Array List, instead we can have an HashTable to add the Field and its value as a key value pair.

if (item.VersionLabel.ToString() != objItem[“_UIVersionString”].ToString()) //This condition is used to ignore the comparison of the current version with itself. you can add your own logic to compare it with the actually needed version

Below is the function to check if the value for a particular field has changed compared to other version:

private bool IsValueChanged(SPFieldType type, object FirstValue, object SecondValue)
{
if (string.IsNullOrEmpty(Convert.ToString(FirstValue)) && string.IsNullOrEmpty(Convert.ToString(SecondValue)))
{
return false;
}
else if (string.IsNullOrEmpty(Convert.ToString(FirstValue)))
{
return true;
}
else if (string.IsNullOrEmpty(Convert.ToString(SecondValue)))
{
return true;
}

switch (type)
{
case SPFieldType.DateTime:
return !Convert.ToDateTime(FirstValue).Date.Equals(Convert.ToDateTime(Convert.ToString(SecondValue)).Date);
case SPFieldType.User:
break;
case SPFieldType.Text:
case SPFieldType.Note:
return !Convert.ToString(FirstValue).ToUpper().Equals(Convert.ToString(SecondValue).ToUpper());
case SPFieldType.Boolean:
return !Convert.ToBoolean(FirstValue).Equals(Convert.ToBoolean(SecondValue));
case SPFieldType.Attachments:
break;
default:
return !FirstValue.Equals(SecondValue);
}

return false;
}

*Note: I have executed the code for the first item in the List, instead we can have it as a loop for multiple items.

Resolved: Grouping Issues in SharePoint Data View WebPart – Part2

SharePoint Data View WebPart grouping and Filtering:

In continuation to our previous blog l’ll further demonstrate on how to add a message under each group if no records are found: something of this sort “No records found for this Group”

Issue:

Issue

If we use the default filtering available in the SharePoint Data View WebPart or the XSLT filtering we cannot display this message under each group which has no records satisfying the criteria provided. Say for example under Group1 we have 3 SubGroups and under Group2 we have 5 SubGroups When the parameters passed are say “Group1”, “Group2”, “SubGroup4” and “SubGroup5” then we get the records displayed for “Group2” and nothing would displayed for “Group1” not even the header. Also as per our example in the previous Post (parameters passed as above) we get the Group Header (Group1) but nothing under it. Please refer the image below:

Solution:

1. Find the Group Header Template (dvt_1.groupheader0) in this at the end of the “tr” tag add another “tr” tag with ID as “trNoRecords”
2. Inside that “tr” add the required text: for example “No records found for this Group”
3. Now lets add some JavaScript Code to get the desired results: I have clubbed the whole JavaScript in to one function that is from Previous Blog (Green) and the Current Blog (Red)

_spBodyOnLoadFunctionNames.push(“HideGroup”);
function HideGroup()
{
var vTable = document.getElementById(‘tblRows’);
var vTRelement = vTable.getElementsByTagName(“TR”);
for(i=0;i < vTRelement.length;i++)
{
if(vTRelement[i].id == “group1”)
{
var vNextSibling = vTRelement[i].nextSibling;
if(vNextSibling != null)
{
if(vNextSibling.id != “displayRows”)
{
vTRelement[i].style.display = “none”;
}
}
else
{
vTRelement[i].style.display = “none”;
}
}
else if(vTRelement[i].id == “trNoRecords”)
{
var vNextSibling = vTRelement[i].nextSibling;
var vCount = 0;
while(vNextSibling.id != “group0”)
{
if(vNextSibling != null)
{
if(vNextSibling.id == “displayRows”)
{
vCount++;
break;
}
}
vNextSibling = vNextSibling.nextSibling;
if(vNextSibling == null)
{
break;
}
}
if(vCount>0)
{
vTRelement[i].style.display = “none”;
}
}
}
}

Once this is added we can see the desired result as shown in the below figure:

Solution

Resolved: Grouping Issues in SharePoint Data View WebPart – Part1

SharePoint Data View WebPart grouping and Filtering:

Not always we can use the filtering conditions in a Data View WebPart, say for example we have few parameters (Group, SubGroup, level etc..) passed in Query String and based on these parameters we need to filter our Data View WebPart.

One of the Parameters (say SubGroup) may be sent as “All”, instead of selecting individual items. In this particular scenario we cannot use the default Filters available in Data View WebPart, hence we go for individual Row verification against these parameters. Till this point everything works fine and looks great. Lets now discuss about the problem:

Issue:

If we set grouping to the results we see that the data is filtered properly according to our requirement but then , we can also all other Group Headers, which are does not meet the criteria (though there will not be any data available under these headers). Please refer the adjacent screenshot

Solution:

The first possible solution anyone can think of is to add the same filtering condition where the Group header (in XSLT) is being added and I did the same too and found that it resolved the issue to some extent. It would remove the main Group Headers but the second level grouping (if exists would still be there)Please refer below screenshot for the resultant Data after the modifications.

But applying the same filter to the next Grouping level wouldn’t work instead it would add additional issues, I would not go in to its details. So I opted to get it done with JavaScript:

Steps to be followed:

Find the Row where the actual data is being rendering by default in the dvt_1.rowview template.

For its TR add an ID attribute and assign its value to “displayRows”

Add the below script to you page:

_spBodyOnLoadFunctionNames.push(“HideGroup”);

function HideGroup()

{

var vTable = document.getElementById(‘tblRows’);

var vTRelement = vTable.getElementsByTagName(“TR”);

for(i=0;i < vTRelement.length;i++)

{

if(vTRelement[i].id == “group1”)

{

var vNextSibling = vTRelement[i].nextSibling;

if(vNextSibling != null)

{

if(vNextSibling.id != “displayRows”)

{

vTRelement[i].style.display = “none”;

}

}

else

{

vTRelement[i].style.display = “none”;

}

}

}

}

Before

This would hide all the Second Level Groups which does not have data under its category*. Now this may look like the issue is resolved. Consider say under Main Group (Group1) we have three Sub Groups (SubGroup1, SubGroup2, SubGroup3). This Group1 may have data for SubGroup1, SubGroup3 but not for SubGroup2. In this case SubGroup2 would also be displayed with no data under its section based on the filtering Condition. With the above the SubGroup2 would be hidden and cannot be seen. Here comes one more issue, that is while using the expand collapse button the hidden TR that is “SubGroup2” would be visible again (Refer adjacent Image: “Before”).

Result

Find the Expand Collapse Image for Group Header (that is the main Group) which contains an OnClick function: onclick=”javascript:ExpGroupBy(this);return false;” change this to onclick=”javascript:ExpGroupBy(this);HideGroup();return false;” and now we are all set to see the expected results(Refer adjacent Image: “Result”)

More on this in my Next Blog

*Note: Since its the Second level group its value is “group1” as rendered by the browser, change it to appropriate value wherever necessary (first level “group0”, third level “group2” etc…)

3 level Cascading Dropdowns in SharePoint – Remove Duplicate Records

One of the most demanding requirements in recent times is to have the cascading dropdown columns in SharePoint. There are many useful articles available over Internet to achieve this functionality, each using different methodologies and technologies.

The one I prefer uses Jquery, thanks to the Marc D Anderson for this wonderful blog http://sympmarc.com/2009/07/19/cascading-dropdown-columns-in-a-sharepoint-form-part-2/

This is one of the easiest ways to implement it. However, I need something more than what is explained in the above blog.

Issue Description:

The above blog does explain how to get Cascading dropdowns work. It also highlights the 3 level cascading dropdowns, but what if the third dropdown values are duplicated that is if the first dropdown has Countries, the second has States and the third one holds the Cities. Now consider an instance where different Countries may have identical City Names then we get duplicate records in the Cities dropdown. So in very Simple words, all the dropdowns should contain unique values.

Solution:

This is not a difficult task, we can get this done just by making little modifications to the existing SharePoint List and also to the Jquery provided by Marc Anderson. Lets walk through the steps to achieve this functionality:

1) Follow all the steps as per Marc Anderson’s blog (link provided above).
2) In the Cities List add one more Lookup Column named Country and look up the values from Countries List
3) When we add items to the Cities List along with the States also select Country values.
4) Now, we are done with the SharePoint List modifications, all we are left with is Jquery updates. Open the file “CascadingDropdowns.js” and look for the function “.SPServices.SPCascadeDropdowns” in this file

5) This function accepts many parameters, one of them is the CAML Query parameter. So we may feel that by adding the Query here we can filter based on first dropdown as well. But unfortunately the answer is NO as it doesn’t accept dynamic values in other words the value would be an hard coded value and hence we cannot pass the first dropdown value in this. The updated “CascadingDropdowns.js” file looks as below:

$(document).ready(function() {

$().SPServices.SPCascadeDropdowns({

relationshipList: “States”,

relationshipListParentColumn: “Country”,

relationshipListChildColumn: “Title”,

parentColumn: “Country”,

childColumn: “State”

});

$().SPServices.SPCascadeDropdowns({

relationshipList: “Cities”,

relationshipListParentColumn: “State”,

relationshipListChildColumn: “Title”,

parentColumn: “State”,

childColumn: “City”,

relationshipListSortColumn: “ID”,

CAMLQuery: ” < Eq >< FieldRef Name=’Country’/ ><Value Type=’Lookup’ >” +document.getElementById(‘ControlId‘).options[document.getElementById(‘ControlId‘).selectedIndex].text+ “< /Value >< /Eq >”

});

});

6) So lets modify the code from another file named “jquery.SPServices-0.5.4.min.js“

7) Open the file and find for var R=”<Query><OrderBy> from here we find sequence of steps which builds query based on condition so just before the Where Condition ends we need to add one more criteria as below

if(U.CAMLQuery.length>0){R+= ” < Eq >< FieldRef Name=’Country’/ ><Value Type=’Lookup’ >” +document.getElementById(‘ControlId‘).options[document.getElementById(‘ControlId‘).selectedIndex].text+ “< /Value >< /Eq >”+”< /AND > “}

8) Save this file and we are all set to use the cascading dropdowns, with unique values, most importantly filters the third dropdown (Cities) values not just based on the second control (States) but also the first one (Countries)

9) If you wish to have single click on the lookup columns with more than 20 items then refer to previous blog

Note: This modifications are with respect to the files from Marc Anderson’s blog so its mandate that we have those files before making these changes.

Thank you for visiting my blog!