Thursday, December 6, 2012

SharePoint 2010 Foundation User Profile Sync

Problem:

SharePoint 2010 Foundation does not provide a user profile synchronizing service between active directory and SharePoint. Sure, whenever a user logs into SharePoint it adds that user but it only copies the name and email address and only to the site collection in question. If there are changes to the name of the user, this can sometimes get stuck and not sync over correctly. Additionally, information like the manager, phone number, and other directory information does not get pushed to the user profiles.

Why does this matter?

When you use a Person/Group column in SharePoint 2007/2010, it is really just a lookup column to that site collection's User Information List and thus you can return any column from the user information list that you like (Name, email, etc.) even though the input is always the username and click the little Check Names box. This can allow for specialized lookups in things like InfoPath or special forms where you want to automatically know who the person's manager is whenever they add an item to a list.

Enter Powershell scripting

Oh no, NO NO NO: the word "scripting" made it into a blog about 'out-of-the-box' solutions!? HYPOCRITE. Anyway, Powershell is like the command prompt from windows, something geeks have been using for years to do things that would otherwise require lots of clicks. Powershell is ONLY for system administrators, not regular SharePoint people. But this type of problem is really more of an admin problem anyway.

Resolution:

I combined two blog entries: here and here to make a script that does the following:
  1. Creates a request to pull from active directory via LDAP (lightweight directory access protocol I think). This asks Active Directory (where all usernames/passwords/etc are stored for your company) to find all users they have. Now, if your company has a bajillion users, this would be bad, but it asks for their account name and just a few properties so it's not too terrible (and, hey, I have like 80 users to deal with it).
  2. Gets all the site collections from SharePoint
  3. For each site, try to run a simple "SyncFromAd" command to make sure that the user's automatic info is up-to-date
  4. Go the user information list and update the columns in there with my information
  5. Forgot to mention - I added a couple of custom columns to the user information list for my site collection...that may be a no-no, not totally sure ^_^, but I SET THEM ANYWAY. Bam.
So, without further ado, here's the script that does the work (and this WILL take updating on your end if you want to use it to specify the domain for your company and whether or not you are adding columns to your user information lists like I did):

$objDomain = New-Object System.DirectoryServices.DirectoryEntry("LDAP://OU=YOUR_USERS_OU,dc=YOUR_DOMAIN_NAME,dc=YOUR_DOMAIN_ENDING_LIKE_COM")
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.PageSize = 1000
$objSearcher.Filter = $strFilter
$objSearcher.SearchScope = "Subtree"
$varDomain = "YOUR_DOMAIN_NAME"

$colProplist = "samaccountname","title","department","ipphone","mobile","name","distinguishedname","manager"
foreach ($i in $colPropList) {
 $objSearcher.PropertiesToLoad.Add($i)
}

$colResults = $objSearcher.FindAll()
$manResults = $objSearcher.FindAll()
$sites = Get-SPSite -Limit "ALL"

foreach ($objResult in $colResults) {
 $objItem = $objResult.Properties
 write-host 
 $userID = $varDomain+"\"+[string]$objItem.samaccountname
 foreach ($site in $sites) {
  $web=$site.RootWeb
  set-spuser -Identity $userID -SyncFromAD -web $site.url -ErrorAction SilentlyContinue
  if(!$error[0]) {
   write-host $site.url " - " $userID
   $list = $web.Lists["User Information List"]
   $query = New-Object Microsoft.SharePoint.SPQuery
   $query.Query = "<Where><Eq><FieldRef Name='Name' /><Value Type='Text'>$userID</Value></Eq></Where>"
   foreach ($item in $list.GetItems($query)) {
    $item["JobTitle"] = [string]$objItem.title
    $item["Department"] = [string]$objItem.department
    $item["IPPhone"] = [string]$objItem.ipphone
    $item["MobilePhone"] = [string]$objItem.mobile
    $item["Title"]= [string]$objItem.name
    $item["Username"] = [string]$objItem.samaccountname
    $manager = $manResults | Where-Object {$_.Properties.distinguishedname -eq $objItem.manager}
    $managerClean = $varDomain+"\"+[string]$manager.Properties.samaccountname
    $spManager = Get-SPUser -Identity $managerClean -web $site.url
    $item["Manager"] = $spManager
    $item.SystemUpdate()
   }
  }
  else {
   #write-host $site.url " - " $error[0]
  }
  $error.clear()
  $web.Dispose()
  $site.Dispose()
 }
}

Monday, September 10, 2012

Sending emails between SharePoint lists

(Also thought of as SharePoint Workflows emailing other SharePoint Lists.)

Situation: Wanted to have a single list at the root site where users can submit ideas and problems that should go to IT for review. We figured we would place a simple list there, use the masterpage to create a link at the bottom, and have this list workflow email our own IT Tickets announcements list (different site). One problem: MS built SharePoint so that it will not recognize emails sent to it from itself (security feature for possible infinite loops- KB970818).

Resolution: There is a resolution out there that involves a VB script placed on the SharePoint server to strip out the SharePoint-y part of the email....I thought that (since I've got Exchange in my environment) I could just use what is known as a "transport rule" to strip out the same stuff so that I don't have to worry about configuring SharePoint all the time. See, here's the deal - when SharePoint sends emails it adds a special header that you don't normally see (in Outlook 2010, you have to open an email and hit File -> Properties to see the message headers). This special header is titled "X-Mailer" and acts like a little field and SharePoint puts inside this field "Microsoft SharePoint" something or another. So I had our exchange administrator setup a transport rule that says "If an email has an X-Mailer header with the word "SharePoint" in it and is being sent to an address that ends in @sharepoint.mydomain.com then remove the X-Mailer header". That's it!

Just to clarify - this would allow me to have two lists email each other in an infinite loop...so your SharePoint admin may not be a fan if you have a large environment and people could setup this sort of thing...only use if you have a structure in-place that would prevent that from happening :)

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>

Thursday, January 5, 2012

Hide 'Submitted By' in InfoPath Browser Form

If you have had to deal with browser forms a lot, you may have run across the ability to send an email with the active view (aka page you are last looking at) as the body of an email to someone. The annoying thing is: it always adds 'Submitted by: BLAH' at the top of the email. I found a way to hide it IF you are sending the email to a recipient that processes <style> elements located in the <head> of the html in an email like Microsoft Exchange aka Outlook or Hotmail (GMail is one of the few that do not). DISCLAIMER: this just hides it and it only hides it ON THE FIRST EMAIL, it doesn't actually remove it from the email so someone *could* go find whoever submitted it if they wanted or see it if their computer is set to NOT process html emails. This also means that when they hit 'reply' or 'forward' it will appear (at least I haven't figured out a workaround for that yet). I have not tested it in a 2007 browser form or using InfoPath 2007 but the theory should work the same.

Steps for InfoPath 2010:
  1. Publish like normal and run your form to send you an email
  2. Open the email and view its source (in Outlook, you have to double-click and open the actual email and THEN you can right-click and then click 'View Source')
  3. Scroll to the bottom and you will see something like this:

    <div style="word-wrap:break-word;color:windowtext;background:window;font-size:10.0pt;font-family:Tahoma" class="GOBBLEDEEGOOK">[Submitted by USERNAME@DOMAIN.COM] <br><hr></div>

  4. Note the 'class=' there...grab your GOBBLEDEEGOOK and copy it (without the quotations)
  5. Go back to your infopath form and publish as source files into a folder on your desktop
  6. Close InfoPath
  7. Open up the folder on your desktop and right-click on the InfoPath view that you are emailing (it will be the name of your view.xsl) in notepad or, preferably, in notepad++
  8. Add the following directly before the "</head>":

    <style>.GOBBLEDEEGOOK{DISPLAY:none !important;VISIBILITY:hidden !important}</style>

    (Just be sure you replace GOBBLEDEEGOOK with whatever your GOBBLEDEEGOOK was). Mine looked like this:
    <style>.D38B7128-85A9-4481-A264-D05E46BC1B50{ DISPLAY:none !important; VISIBILITY:hidden !important;}</style>

  9. Close notepad/notepad++
  10. Go back into the folder on your desktop and right-click on manifest.xsf and click 'Design'
  11. Publish your form like normal
  12. Run a test to see if it hides that dumb 'submitted by' on that particular view/email.