Friday, May 25, 2012

SharePoint 2010 LVWP Dynamic Date Filtering

Situation: You used SharePoint Designer 2010 to insert what you *thought* was a data view onto a web part page and it is actually a List View Web Part (LVWP) and you want to filter the data related to [Today]. When you click the 'Filter' button in the top-left of SPD2010, it will only let you filter to '[CurrentDate]' and doesn't give you the option to say something like '[Today]-10'.

Resolution: There are two ways to alter the core query here:
1 - No looking at the dreaded 'code' or 'CAML': Save the page in SharePoint Designer, open the page directly in your browser, modify the web part by clicking its little dropdown and clicking 'Edit Web Part' and clicking 'Edit Current View' underneath the Current View dropdown (and then fix like you would any other view...sometimes this may actually give you an error if your LVWP was being evil like mine).
2 - Must look at 'code' and 'CAML': When you look at the evil 'code' behind your data view, you will notice a section that looks like this near the top of your LVWP:
<View name="{123jk123-j123-12j1-j1k2l;3l123j}" Mobile View="TRUE" Type="HTML"....
     <Query>
          <OrderBy />
          <GroupBy />
     </Query>

If you have already filtered to '[CurrentDate]' then it will have put a section inside the <Query> that looks similar to this:
<Where>
     <Eq>
          <FieldRef Name="YourDateField"/>
          <Value Type="DateTime">
               <Today/>
          </Value>
     </Eq>
</Where>

What to fix: that lovely <Today/> piece. If you want to say something like 'Show me everything that is greater than (aka after) the last 10 days' then you would need to:
1) Add an 'OffsetDays' attribute to the Today so that it looks like this: <Today OffsetDays="-10"/>
2) Make sure that where I have "Eq", you put "Gt" (short for Greater Than) -- You could do the reverse by setting it to Lt for "Less Than" and making the OffsetDays a positive number to make it something like [Today]+7.

Tuesday, May 15, 2012

SharePoint 2007 and 2010 - Prepopulate or Prefill list fields via Query String aka browser address

Edit 1/9/2013 - Updated the time function to round any supplied time to the nearest 5 minutes so that it will properly set the time dropdown.
Edit 10/24/2012 - Updated one line where an errant forward slash existed and may be the cause of some users being unable to use the 2010 script.
Situation: You want to have users fill out a standard list form but you want to auto-fill some of the fields for them (to make it just easier to fill or else to hide those fields so the user doesn't know they've been filled for them...think customer service feedback and a field specifying which person you spoke to).
Resolution:
  1. Place the jQuery library (http://docs.jquery.com/Downloading_jQuery download the minified version) in a document library everyone has access to in your SharePoint
  2. If you are using SharePoint 2010, open your list, click the 'List' tab, click the edit 'Default New Form' menu option, and skip to step 6
  3. Go to your list, click the 'New' menu and click 'Item' (or just click 'New')
  4. Notice the URL has your web address and ends in /newform.aspx?BLAHBLAH...delete everything after the question mark and replace it with the following: PageView=Shared&ToolPaneView=2
  5. Your link should now look like http://sharepointserver/site/lists/yourlist/newform.aspx?PageView=Shared&ToolPaneView=2 and you just hit enter to visit this new address
  6. Huzzah! You are in edit mode for the page! Just click 'Add a Web Part' and add a Content Editor web part
  7. Once you add the web part, move it to the bottom of the page and edit its properties
  8. Click 'Edit Source' or, if you are using 2010, click in the webpart so you could type and when the menu tab at the top switches to the 'Format Text' tab, click the 'HTML' dropdown option and choose 'Edit HTML Source'
  9. If you are using SharePoint 2007, grab the script from nothingbutsharepoint.com (https://www.nothingbutsharepoint.com/sites/eusp/Pages/jquery-for-everyone-pre-populate-form-fields.aspx) and paste it into your source. If you are using SharePoint 2010, copy and paste my script below these instructions.
  10. Key thing to notice: in the beginning of his script, there's a part referencing Google's jQuery library, what you want to do is replace what is typed there for a URL for your own URL to the document library where your jQuery is
  11. Click ok, save the page, you are now done.
Here is the script for SharePoint 2010 (If you want the non-commented/more condensed version, I've added it to the very bottom of this post):
<script type="text/javascript">
 if(typeof jQuery=="undefined"){
  var jQPath="http://YOURSHAREPOINT/YOURLIBRARY/jquery.min.js";
  document.write("<script src='",jQPath,"' type='text/javascript'><\/script>");
 }
</script>
<script type="text/javascript">
/*
 * Prepopulate form fields in SharePoint
 * Copyright (c) 2008 Paul Grenier (endusersharepoint.com now nothingbutsharepoint.com)
 * Licensed under the MIT (MIT-LICENSE.txt)
 * Updated for 2010 by Brendan Horner for nothingbutsharepoint.com
 */
(function(){
 var params = window.location.search.substring(1).split("&"),
  kv = {},
  opts,
  sp=/%20|\+/g,
  datetime=/([1-9]|0[1-9]|1[012])[\-\/.]([1-9]|0[1-9]|[12][0-9]|3[01])[\-\/.](19|20)\d\d\s([0-1][0-2]|[0-9]):([0-9]{2})\s(A|P)M/i,
  date=/([1-9]|0[1-9]|1[012])[\-\/.]([1-9]|0[1-9]|[12][0-9]|3[01])[\-\/.](19|20)\d\d/,
  clean = function(str){
   return str.replace(sp," ");
  },
  getKv = function(){
   $.each(params,function(i,e){
    var p=e.split("=");
    kv[p[0]]=decodeURIComponent(p[1]);
   });
   return kv;
  };
 jQuery.prepop = function(){
  $.each(getKv(),function(k,v){ 
   k=clean(k);
   v=clean(v);
   var f=$("[title='"+k+"']"),
    job;
   if (f.length>0){
    if (f[0].type=="text"){job=10;} //text
    if (f[0].type=="checkbox"){job=20;} //checkbox
    if (f[0].type=="select-one"&&f[0].tagName=="SELECT"){job=10;} //choice dropdown and non-IE lookup
    if (f[0].tagName=="TEXTAREA"){job=10;} //Multi-lines of text
    if (f[0].type=="text"&&f[0].opt=="_Select"){job=70;} //IE lookup with evil img and hidden input
    if (v.match(date)){job=40;} //date
    if (v.match(datetime)){job=50;} //datetime
   }
   if (f.length===0){ 
    var elm = $("nobr:contains('"+k+"')");
    if (elm.length>0){
     elm = elm.closest("td").next()[0];
     var s1 = $(elm).find("select:first"),
      s2 = $(elm).find("select:last"),
      p1 = $(elm).find("textarea[title='People Picker']"),
      p2 = $(elm).find("div[title='People Picker']"),
      r1 = $(elm).find("span[title='"+v+"']"),
      vals = v.split(","),
      r2 = $(elm).find("span[title='"+vals[0]+"']");
     if (s1.length>0){job=80;} //multi-select
     if (p1.length>0){job=90;} //people picker
     if (r1.length>0||r2.length>0){job=30;} //radio button single select or checkbox list
    }
   }
   switch (job){
   case 10:
    if (v.substring(0,1)=="@"){
     opts = f[0].options;
     $.each(opts,function(i,e){
      if (opts[i].value==v.substring(1)){f[0].selectedIndex=i;}
     });
    }else{
     f.val(v);
    }
    break;
   case 20:
    if (v.toUpperCase()=="TRUE"||v=="1"){f[0].checked=true;}
    if (v.toUpperCase()=="FALSE"||v=="0"){f[0].checked=false;}
    break;
   case 30:
    $.each(vals, function(i,e){
     var V=TrimSpaces(e); //TrimSpaces is a function in core.js of SharePoint 2010
     $.each($(elm).find("span.ms-RadioText").find("label"),function(i,e){
      if($(e).text()==V){
       $(e).prev().attr('checked',true);
      }
     });
    });
    break;
   case 40:
    v=v.replace(/[\-\/.]/g,"/");
    f.val(v);
    break;
   case 50:
    var dt=v.split(" "),
     d=dt[0].replace(/[\-\/.]/g,"/"),
     t=dt[1],
     hm=t.split(":"),
     hh=hm[0].replace(/^0/,""),
     mm=hm[1],
     ap=dt[2].toUpperCase();
    f.val(d);
    mm=5*Math.round(mm/5);
    f.parent("td").siblings("td.ms-dttimeinput")
     .find("select:first").val(hh+" "+ap)
     .parent("td").find("select:last").val(mm);  
    break;
   case 70:
    fArr = f.attr('choices').split('|'); 
    if (v.substring(0,1)=="@"){
     for (i=1;i<fArr.length;i=i+2){
      if(fArr[i] == v.substring(1)){ 
       f.val(fArr[i-1]);
       $('input[id="'+f.attr("optHid")+'"]').val(fArr[i]);
      }
     }
     f.blur();
    }else{
     f.val(v);
     for (i=0;i<fArr.length;i=i+2){
      if(fArr[i] == v){ 
      $('input[id="'+f.attr("optHid")+'"]').val(fArr[i+1]);
      }
     }
     f.blur();
    }
    break;
   case 80:
    opts = s1[0].options;
    var s1hiddenInput = s1.parents('span').find('input[type="hidden"]').first();
    var s1hiddenVal="";
    $.each(vals,function(i,e){
     var V=e;
     $.each(opts,function(i,e){
      if (opts[i].text==V){
       s2.append("<option value='"+opts[i].value+"'>"+V+"</option>");
       s1hiddenVal+=opts[i].value+"|t"+V+"|t";
      }
      if (V.substring(0,1)=="@"){
       if (opts[i].value==V.substring(1)){
        s2.append("<option value='"+V+"'>"+opts[i].text+"</option>");
        s1hiddenVal+=opts[i].value+"|t"+V+"|t";
       }
      }
     });
    });
    s1hiddenInput.attr('value',s1hiddenVal);
    break;
   case 90:
    var p=vals.join(";");
    p1.val(p);
    p2.html(p);
    break;
   }
  });
 };
})();
$(window).load(function(){
 $.prepop();
});
</script>
 
Here's the slightly more condensed version:
 
<script type="text/javascript"> if(typeof jQuery=="undefined"){ var jQPath="http://YOURSHAREPOINT/YOURLIBRARY/jquery.min.js"; document.write("<script src='",jQPath,"' type='text/javascript'><\/script>"); } </script> <script type="text/javascript"> /* * Prepopulate form fields in SharePoint * Copyright (c) 2008 Paul Grenier (endusersharepoint.com now nothingbutsharepoint.com) * Licensed under the MIT (MIT-LICENSE.txt) * Updated for 2010 by Brendan Horner for nothingbutsharepoint.com */ (function(){ var params = window.location.search.substring(1).split("&"), kv = {}, opts, sp=/%20|\+/g, datetime=/([1-9]|0[1-9]|1[012])[\-\/.]([1-9]|0[1-9]|[12][0-9]|3[01])[\-\/.](19|20)\d\d\s([0-1][0-2]|[0-9]):([0-9]{2})\s(A|P)M/i, date=/([1-9]|0[1-9]|1[012])[\-\/.]([1-9]|0[1-9]|[12][0-9]|3[01])[\-\/.](19|20)\d\d/, clean = function(str){ return str.replace(sp," "); }, getKv = function(){ $.each(params,function(i,e){ var p=e.split("="); kv[p[0]]=decodeURIComponent(p[1]); }); return kv; }; jQuery.prepop = function(){ $.each(getKv(),function(k,v){ k=clean(k); v=clean(v); var f=$("[title='"+k+"']"), job; if (f.length>0){ if (f[0].type=="text"){job=10;} if (f[0].type=="checkbox"){job=20;} if (f[0].type=="select-one"&&f[0].tagName=="SELECT"){job=10;} if (f[0].tagName=="TEXTAREA"){job=10;} if (f[0].type=="text"&&f[0].opt=="_Select"){job=70;} if (v.match(date)){job=40;} if (v.match(datetime)){job=50;} } if (f.length===0){ var elm = $("nobr:contains('"+k+"')"); if (elm.length>0){ elm = elm.closest("td").next()[0]; var s1 = $(elm).find("select:first"), s2 = $(elm).find("select:last"), p1 = $(elm).find("textarea[title='People Picker']"), p2 = $(elm).find("div[title='People Picker']"), r1 = $(elm).find("span[title='"+v+"']"), vals = v.split(","), r2 = $(elm).find("span[title='"+vals[0]+"']"); if (s1.length>0){job=80;} if (p1.length>0){job=90;} if (r1.length>0||r2.length>0){job=30;} } } switch (job){ case 10: if (v.substring(0,1)=="@"){ opts = f[0].options; $.each(opts,function(i,e){ if (opts[i].value==v.substring(1)){f[0].selectedIndex=i;} }); }else{ f.val(v); } break; case 20: if (v.toUpperCase()=="TRUE"||v=="1"){f[0].checked=true;} if (v.toUpperCase()=="FALSE"||v=="0"){f[0].checked=false;} break; case 30: $.each(vals, function(i,e){ var V=TrimSpaces(e); $.each($(elm).find("span.ms-RadioText").find("label"),function(i,e){ if($(e).text()==V){ $(e).prev().attr('checked',true); } }); }); break; case 40: v=v.replace(/[\-\/.]/g,"/"); f.val(v); break; case 50: var dt=v.split(" "), d=dt[0].replace(/[\-\/.]/g,"/"), t=dt[1], hm=t.split(":"), hh=hm[0].replace(/^0/,""), mm=hm[1], ap=dt[2].toUpperCase(); f.val(d); mm=5*Math.round(mm/5); f.parent("td").siblings("td.ms-dttimeinput") .find("select:first").val(hh+" "+ap) .parent("td").find("select:last").val(mm); break; case 70: fArr = f.attr('choices').split('|'); if (v.substring(0,1)=="@"){ for (i=1;i<fArr.length;i=i+2){ if(fArr[i] == v.substring(1)){ f.val(fArr[i-1]); $('input[id="'+f.attr("optHid")+'"]').val(fArr[i]); } } f.blur(); }else{ f.val(v); for (i=0;i<fArr.length;i=i+2){ if(fArr[i] == v){ $('input[id="'+f.attr("optHid")+'"]').val(fArr[i+1]); } } f.blur(); } break; case 80: opts = s1[0].options; var s1hiddenInput = s1.parents('span').find('input[type="hidden"]').first(); var s1hiddenVal=""; $.each(vals,function(i,e){ var V=e; $.each(opts,function(i,e){ if (opts[i].text==V){ s2.append("<option value='"+opts[i].value+"'>"+V+"</option>"); s1hiddenVal+=opts[i].value+"|t"+V+"|t"; } if (V.substring(0,1)=="@"){ if (opts[i].value==V.substring(1)){ s2.append("<option value='"+V+"'>"+opts[i].text+"</option>"); s1hiddenVal+=opts[i].value+"|t"+V+"|t"; } } }); }); s1hiddenInput.attr('value',s1hiddenVal); break; case 90: var p=vals.join(";"); p1.val(p); p2.html(p); break; } }); }; })(); $(window).load(function(){ $.prepop(); }); </script>