Filter a calendar view based on user profile property

I got this request from Carel Schotborgh:

Using WSS 3.0

Not have been able to find it anywhere so, since it is possible to filter a list items (in all event/task/etc view) based on membership in SharePoint group (By Alexander). I would like to request the possibility to filter an Agenda View on a single line text, based on a field of the current user information field (for instance department). I import data into SharePoint list what contains names of people. So users do not create the list items and there are multiple columns with names like Judge1 Judge2 Lawyer etc. As you all know you can’t use [ME] value in a single line text. This can only be used with columns: Created by, Modified by, Assigned to and People picking data. Problem with agenda view is also that it only displays 1 field (in general description of appointment for instance).

What do I want?
Filter (a) specific column(s) on the basis of the current user that is logged on (department field for example) in an agenda view.

The agenda should only show items that match the same text as entered in the department field of the user information field of the user that is currently logged on.

In my case IF Judge1 = Mr. J. Jenkins display it OR IF Judge2 = Mr. J. Jenkins display it. The OR statement is very important since SharePoint also filters with AND statement. So I would like to filter multiple columns with department field (any other also ok, as long I can fill in a value that is connected to the current user).

I already managed to create something that filters all event/tasks/etc list view, searching all the columns based upon the current user department field with or statement idea, but not specific columns. Also in agenda view my code and Alexanders code will not work.

If it is not possible to do this on each specific column it is also ok to search all of the columns since the names in my list are unique for each column. Other columns do not contain their names in such a way anyway. I’m already using my script for the not agenda view and works perfectly except user with slow internet will see the entire list my filter starts working.

Excuse me for long text, but it is complex. Much appreciated any help, suggestion or solution.

Here is one suggestion on how this can be done. It pulls the “Department” from the user profile and searches for a match in one of the fields specified in the script (in the variable “arrOfFieldInternalNames”).

This solution does:

  • Filters a calendar view based on a text retrieved from the current users profile (from People and Groups)
  • Filters with an “OR” statement looking for a match in multiple text fields
  • Handles “single day items” and items spanning over multiple days.
  • Hide the calendar view until it has been filtered

This solution doesn’t:

  • If multiple elements are located in one day, the standard calendar view will display a “x more items” link to expand all items that day. My filter solution will filter the items, but may display a link saying there are “3 more items”, but when clicking the link there may no items to display.

The solution
To keep a nice even background when removing items spanning over multiple days, i had to change the background for the “not in this month items” to white.

Add this code to a CEWP below the calendar view:

<style type="text/css">
.ms-cal-nodataMid,.ms-cal-nodataBtm2{
	background-color:#FFFFFF;
}
</style>
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/interaction.js"></script>
<script type="text/javascript" src="/test/English/Javascript/stringBuffer.js"></script>
<script type="text/javascript">

// Set variables
listGuid = 'FilterCalendarView'; // Use list Name or List Guid
listBaseUrl = L_Menu_BaseUrl;
userListGuid = "{570D772F-0EAB-45A8-8C54-9CCD4EC6A0AF}"; // The "People and Groups" list
userListBaseUrl = ''; // Modify if on a managed path
// The FieldInternalNames to search for information matching the selected user property
arrOfFieldInternalNames = ['Judge1','Judge2'];

// Hide calendar view until it is filtered
$(".ms-cal-gempty").hide();

// Get the UserInfo
var userInfoObj = getUserInfo();
var dep = userInfoObj.Department; // User profile property "Department"

// Find all "relevant" items
var myItems = getMyItemsID(dep,arrOfFieldInternalNames);
// Filter view
filterCalendarView(myItems);

function getMyItemsID(find,findInArr){
	wsBaseUrl = listBaseUrl + '/_vti_bin/';
	var query = "";
	$.each(findInArr,function(i,fin){
		query += "<Eq><FieldRef Name='"+fin+"' /><Value Type='Text'>"+find+"</Value></Eq>";
		if(i>0)query = "<Or>" + query + "</Or>";
	});
	query = "<Where>" + query + "</Where>";
	var arrToReturn = [];
	res = queryItems(listGuid,query,['ID']);
	$.each(res.items,function(i,item){
		arrToReturn.push(item.ID);
	});
	return arrToReturn;
}

function filterCalendarView(arrOfIDs){
	$(".ms-cal-gempty a[href*='DispForm.aspx?ID=']").each(function(){
		var currID = $(this).attr('href').match(/ID=(d+)/);
		if($.inArray(currID[1],arrOfIDs)==-1){
			// remove
			var thisParentTd = $(this).parents('table:first').parents('td:first');
			var colspan = thisParentTd.attr('colspan');
			if(colspan>1){		
				for(i=1;i<colspan;i++){				
					thisParentTd.after("<td class='ms-cal-workitem'> </td>");
				}
				thisParentTd.replaceWith("<td class='ms-cal-workitem'> </td>");
			}else{
				thisParentTd.html(' ');			
			}		
		}
	});
// Show calendar view after it has been filtered
$(".ms-cal-gempty").show();
}

function getUserInfo(UserId){
wsBaseUrl = userListBaseUrl + '/_vti_bin/';
var uiObj = {};

if(typeof(UserId)=="undefined" || UserId=='')UserId = _spUserId;

var arrOfFields = ['ID', 'Name', 'Title', 'EMail', 'Department', 'JobTitle', 'Notes', 'Picture',
'IsSiteAdmin', 'Created', 'Author', 'Modified', 'Editor', 'SipAddress', 'Deleted'];

var item = getItemById(userListGuid,UserId,arrOfFields);
    if(item != null){
	    for(i=0;i<arrOfFields.length;i++){
	    	if(item[arrOfFields[i]]!=null){
	    		uiObj[arrOfFields[i]] = item[arrOfFields[i]];
	    	}else{
	    		uiObj[arrOfFields[i]] = '';
	    	}
	    }
       	return uiObj;
    }else{
        for(i=0;i<arrOfFields.length;i++){
    		uiObj[arrOfFields[i]] = "User with id " + UserId + " not found.";
    	}
		return uiObj;
	}
}	
</script>

Modify the parameters:

  • listGuid: The list name or list Guid of the list to filter.
  • listBaseUrl: The base URL of the list to filter.
  • userListGuid: The list Guid of the user list (People and Groups).
  • userListBaseUrl: The user list base URL – Most likely an empty string “”. If the site resides on a manage path this must be reflected.
  • arrOfFieldInternalNames: Array of the FieldInternalNames to search for a match on the search string-

The jQuery-library is found here. The sourcecode refers to jquery-1.3.2.min.js. If you use another version, please update the reference in the code.

The scripts “interaction.js” and “stringBuffer.js” is created by Erucy and published on CodePlex.

Read here how to add a CEWP to NewForm or EditForm, and how to get the guid for a list.

I do not think this is a “production environment solution” at this stage, but please test it and post back any comments or suggestions and i will try to adapt the script.

Regards
Alexander

22 Comments on “Filter a calendar view based on user profile property”

    1. Yes it works good. Had same issue as Carel, the name FilterCalendarView for list guid mixed me up. But it works great now.

      If it is not too much asked, is it possible to keep calendar always expanded?

  2. Hey great post. Thank you very much for the trouble.

    Uploaded js files other folder and chaged path in cwep code

    Since I’m not really an expert on this I have some issues applying this. The scripts “interaction.js” and “stringBuffer.js are in some word document with chinese/jap text or something. I googled for it and found alternatives sites with the code (i think it might be the right one but not shure).

    listGuid = ‘Testcalendar’
    listBaseUrl = /test1/;
    For the userListGuid I went to people and groups –> all people –> right click –> view source –> ctx.listName = “{595C4025-364D-4337-ACF6-2810D7608479}”;
    arrOfFieldInternalNames = [‘Judge1′,’Judge2’];

    Department field of current user (me in this case as admin). I filled in J. Jenkins.

    I applied everything and I get a blank calendarview.

    1. Hi,
      The CodePlex page has for some reason the documentation as default download, click “View all downloads” and pick “SPJsLib.zip”.

      Alexander

    3. Hi thx for speedy reply again.

      Blank calendar gone now. Yet now I get a calendar view with no items. Allong with a link on one date saying 2 more items. When expand or collapse all there are no items visible

      Department field of current user (me in this case as admin). I filled in J. Jenkins. BTW in my other list filter code I used departmentfield 2 and i Noticed I had to remove a non-breaking space at the end of the word that is in departmentfield I didnt notice you doing this in the code. Perhaps that is it?

      // Set variables
      listGuid = ‘Testcalendar’; // Use list Name or List Guid
      listBaseUrl = ‘/test1’;
      userListGuid = “{595C4025-364D-4337-ACF6-2810D7608479}”; // The “People and Groups” list
      userListBaseUrl = ”; // Modify if on a managed path
      // The FieldInternalNames to search for information matching the selected user property
      arrOfFieldInternalNames = [‘Judge1′,’Judge2’];

    4. I made it simpler to just test it. Instead of using Department var I replaced it with a static var. Would mean its not user department field causing my issue.

      I get same result: a calendar view with empty content (there is content though ofcourse).

      .ms-cal-nodataMid,.ms-cal-nodataBtm2{
      background-color:#FFFFFF;
      }

      // Set variables
      listGuid = ‘Testcalendar’; // Use list Name or List Guid
      listBaseUrl = ‘/test1’;
      userListGuid = “{595C4025-364D-4337-ACF6-2810D7608479}”; // The “People and Groups” list
      userListBaseUrl = ”; // Modify if on a managed path
      // The FieldInternalNames to search for information matching the selected user property
      arrOfFieldInternalNames = [‘Judge1′,’Judge2’];

      // Hide calendar view until it is filtered
      $(“.ms-cal-gempty”).hide();

      var dep = “J. Jenkins”

      // Find all “relevant” items
      var myItems = getMyItemsID(dep,arrOfFieldInternalNames);
      // Filter view
      filterCalendarView(myItems);

      function getMyItemsID(find,findInArr){
      wsBaseUrl = listBaseUrl + ‘/_vti_bin/’;
      var query = “”;
      $.each(findInArr,function(i,fin){
      query += “”+find+””;
      if(i>0)query = “” + query + “”;
      });
      query = “” + query + “”;
      var arrToReturn = [];
      res = queryItems(listGuid,query,[‘ID’]);
      $.each(res.items,function(i,item){
      arrToReturn.push(item.ID);
      });
      return arrToReturn;
      }

      function filterCalendarView(arrOfIDs){
      $(“.ms-cal-gempty a[href*=’DispForm.aspx?ID=’]”).each(function(){
      var currID = $(this).attr(‘href’).match(/ID=(d+)/);
      if($.inArray(currID[1],arrOfIDs)==-1){
      // remove
      var thisParentTd = $(this).parents(‘table:first’).parents(‘td:first’);
      var colspan = thisParentTd.attr(‘colspan’);
      if(colspan>1){
      for(i=1;i<colspan;i++){
      thisParentTd.after(" “);
      }
      thisParentTd.replaceWith(” “);
      }else{
      thisParentTd.html(‘ ‘);
      }
      }
      });
      // Show calendar view after it has been filtered
      $(“.ms-cal-gempty”).show();
      }

    5. Hi,
      Try to insert this alert between line 38 and 39:
      alert(query);

      Also insert this alert between line 40 and 41:
      alert(res.count);

      Post the result from this back to me (in Firefox you can copy from the dialog box).

      Try to use the SharePoint variable L_Menu_BaseUrl for the listBaseUrl like this:
      listBaseUrl = L_Menu_BaseUrl;

      Alexander

    2. The spam filter caught them…
      Your problem is the res.count -1.

      This is due to wrong FieldInternalName, wrong ListGuid (or name) or wrong baseUrl.

      Please double check these values.

      Alexander

    3. Can’t seem to fix this issue…FieldInternalName is correct. ListGuid, i assume I can read (even created another view) tried the guid 2. Only don’t really understand what you mean with wrong baseurl…

    4. If your list is located at the URL:
      http://www.contoso.com/MySubsite/Lists/MyList/AllItems.aspx

      The baseUrl for the list “MyList” is:
      listBaseUrl = “/Mysubsite”;

      If the list is located on the root site at http://www.contoso.com/Lists/MyList/AllItems.aspx, the baseUrl is like this:
      listBaseUrl = “”;

      If your Sitecollection is located on a managed path like this: http://www.contoso.com/MyManagedPath/Lists/MyList/AllItems.aspx, the listBaseUrl is like this:
      listBaseUrl = “/MyManagedPath”;

      Alexander

    5. mine is like this:

      http://dcscsrv04/Lists/Test1/SharePoint.aspx

      I created now a new calendarview in the same list named SharePoint just to be shure

      Perhaps bc I have SharePoint wss 3.0?

      Here:

      listGuid = ‘SharePoint’; // Use list Name or List Guid
      listBaseUrl = “”;
      userListGuid = “{595C4025-364D-4337-ACF6-2810D7608479}”; // The “People and Groups” list
      userListBaseUrl = ”; // Modify if on a managed path
      // The FieldInternalNames to search for information matching the selected user property
      arrOfFieldInternalNames = [‘Judge1′,’Judge2’];

      Still everything is filtered..

    6. How does it know what the name of the list is by the way?

      I have Test1 but you would have MyList….Could be different with any person.

    7. Finally! I got it…. my last question answered my own question. not the name of the view needs to be there but the name of the LIST needs to be there.

      Fantastic it works…

      Thank you very much!

      You should consider a donation button on your site for your excellent work.

  4. I assume I can only have 1 name in the field written of a mutline field.

    So like for instance

    Party1
    G. Henkins
    E. Richards
    N. Mils

    Not possible. It will filter it even if the name is displayed.

    1. This should work if you change the fieldType to multi line text (preferably plain text). Then change line 35 like this (switch Eq with Contains):

      query += "<Contains><FieldRef Name='"+fin+"' /><Value Type='Text'>"+find+"</Value></Contains>";

      Alexander

    1. Hi,
      I have no real good workaround, other than this one that refreshes the page upon visit to expand all:

      if($("img[src$='expandbttn.gif']").length>0){
	$(document).ready(function(){
		GetMonthView('1111111');		
	});
}
$("img[src$='collapsebttn.gif']").parent().remove();

      Embed it in the code in the CEWP – for example in line 10.

      Alexander

  6. Alexander,

    I’ve been desperately searching for a method to filter much like what you’re doing here. Just curious if you know if this solution will work for 2010 as well. Basically what I’m trying to do is to be able to filter a master calendar by a users department from UPS or even a BCS connection. Do you think the same methodology would apply?

    Thanks in advance

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.