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:

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:

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 screenshot below:

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:

1. Find the Row where the actual data is being rendering by default in the dvt_1.rowview template.
2. For its TR add an ID attribute and assign its value to "displayRows"
3. 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";
}
}

}

}

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 below Image: "Before").

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 below Image: "After")

--> Before --> After

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 Countries and look up the values from Country 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='Countries'/ ><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='Countries'/ ><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 visiting my blog!

SharePoint Lookup Fields with 20 or more items - Single Click

I recently ran into an issue with SharePoint Lookup Columns containing 20 or more items. Many of you might have already noticed that the with the above criteria the control renders differently (this is the default feature). This is to provide ease to the users to filter the data as and when you enter the data.

With the above criteria the control renders as Input Type followed by an Image. So far we have discussed about the way it renders, now lets get into details of the issue and its solution.

Issue Description:

Whenever, we click on that image it displays us with a drop-down containing all the values, but the funniest part is you can select a particular item either by double click or by selecting it once and clicking it else where on the page or by tabbing system of the keyboard. But I want it work like a normal drop-down selection in other words just with a single click and also the control should be closer to other controls from usability perspective.

Solution:

The above functionality can be achieved by making few changes to the CORE.JS file available in the layouts folder, but being a SharePoint developer I wouldn't recommend doing that for many reasons. We may also want to have this only for few pages and not all. So what are the other options we have ? I would choose to override the function which causes this problem in just one page or the required pages. In order to achieve the above functionality we have to follow few steps:

1) First have "Defer" Tag Set to "False" (it is where you reference the core.js file, it can either be in the master page or the current page itself)

SharePoint:ScriptLink language="javascript" name="core.js" Defer="false" runat="server"/>

Defer = "true" specifies that the page need not wait for the script to be loaded, with this if we have to override the CORE.JS function with ours it may not be possible, so we need to set this to "false".

2)Look for the function "FilterChoice" in CORE.JS file, (which has to be overridden, as the double click functionality is handled in this script). Copy this function and place it in your page where you want this functionality to reflect.

3) After copying the code find "ondblclick" and replace it with "onclick" (marked in Red below) this would suffice our functionality of single click.

4) With this it looks like we are all set to use it, but here comes another problem a small JavaScript error ""id" is null or not an object" on the left hand side of the progress bar. The functionality would still work but this error may quite annoying to few users.

5) This happens because when we push this overridden function (that is FilterChoice) it cannot recognize the parameters passed such as "opt", "ctrl" etc and the function uses the parameters attributes without checking if those are actually defined objects. once we add that condition we are all set to use it without even a hint of error. The condition added is marked in brown below.

I have added the updated function below for reference.

_spBodyOnLoadFunctionNames.push("FilterChoice");

function FilterChoice(opt, ctrl, strVal, filterVal)

{

if(typeof(opt) != "undefined")

{

var i;

var cOpt=0;

var bSelected=false;

var strHtml="";

var strId=opt.id;

var strName=opt.name;

var strMatch="";

var strMatchVal="";

var strOpts=ctrl.choices;

var rgopt=strOpts.split("|");

var x=AbsLeft(ctrl);

var y=AbsTop(ctrl)+ctrl.offsetHeight;

var strHidden=ctrl.optHid;

var iMac=rgopt.length - 1;

var iMatch=-1;

var unlimitedLength=false;

var strSelectedLower="";

if (opt !=null && opt.selectedIndex >=0)

{

bSelected=true;

strSelectedLower=opt.options[opt.selectedIndex].innerText;

}

for (i=0; i < i=""> {

var strOpt=rgopt[i];

while (i < length="="> {

strOpt=strOpt+"|";

i++;

if (i <>

{

strOpt=strOpt+rgopt[i+1];

}

i++;

}

var strValue=rgopt[i+1];

var strLowerOpt=strOpt.toLocaleLowerCase();

var strLowerVal=strVal.toLocaleLowerCase();

if (filterVal.length !=0)

bSelected=true;

if (strLowerOpt.indexOf(strLowerVal)==0)

{

var strLowerFilterVal=filterVal.toLocaleLowerCase();

if ((strLowerFilterVal.length !=0) && (strLowerOpt.indexOf(strLowerFilterVal)==0) && (strMatch.length==0))

bSelected=false;

if (strLowerOpt.length > 20)

{

unlimitedLength=true;

}

if (!bSelected || strLowerOpt==strSelectedLower)

{

strHtml+=""+STSHtmlEncode(strOpt)+"";

bSelected=true;

strMatch=strOpt;

strMatchVal=strValue;

iMatch=i;

}

else

{

strHtml+=""+STSHtmlEncode(strOpt)+"";

}

cOpt++;

}

}

var strHandler=" onclick=\"HandleOptDblClick()\" onkeydown=\"HandleOptKeyDown()\"";

var strOptHtml="";

if (unlimitedLength)

{

strOptHtml=" < tabindex="\" ctrl="\" name="\" id="\">

}

else

{

strOptHtml=" < class="\" tabindex="\" ctrl="\" name="\" id="\">

}

if (cOpt==0)

{

strOptHtml+=" style=\"display:none;position:absolute;z-index:2;left:"+x+ "px;top:"+y+ "px\" onfocusout=\"OptLoseFocus(this)\">";

}

else

{

strOptHtml+=" style=\"position:absolute;z-index:2;left:"+x+ "px;top:"+y+ "px\""+ " size=\""+(cOpt <=8 ? cOpt : 8)+"\""+ (cOpt==1 ? "multiple=\"true\"" : "")+ " onfocusout=\"OptLoseFocus(this)\">"+ strHtml+ "";

}

opt.outerHTML=strOptHtml;

var hid=document.getElementById(strHidden);

if (iMatch !=0 || rgopt[1] !="0" )

hid.value=strMatchVal;

else

hid.value="0";

if (iMatch !=0 || rgopt[1] !="0" )

return strMatch;

else return "";

}

}

Now lets look at the usability part, of replacing that image with something which is closer to other drop-downs. I know the input type box has some broader dimensions, but I am not showing how to change its dimensions (I haven't tried this, but I am pretty sure it can be done using JavaScript and CSS ), all I'll show is to replace the image, next to the input box.

Below is the JavaScript function used, it is very straight forward. However, you can add few more conditions to restrict it to only few images.

_spBodyOnLoadFunctionNames.push("fillDefaultValues");

function fillDefaultValues() {

var vImg = document.getElementsByTagName("img");

if(vImg.length>0)

{

for(i=0; i <>

{

if(vImg[i].alt == "Display lookup values")

{

vImg[i].src = "Give your Image URL here";

}

}

}

That is it, and you are all set to go.. Finally I would like to thank Hari, a friend of mine who actually wanted this functionality to be achieved and I am glad I could help.

Thanks for visiting my blog!!

Note: For some reason this editor doesn't show the proper data, specially the script with Select Tag. I would recommend you to copy the script from CORE.JS and make the necessary modifications as suggested in this blog