Retrieve user properties and Group Memberships from Profile database of User Profile Service - SharePoint 2010

SQL Queries -  

Use this query to see the one or multiple user’s properties from Profile Database.

SELECT  UserProfile.NTName,  PropList.PropertyName,  UserProfileVal.PropertyVal

 FROM [dbo].[UserProfile_Full] UserProfile WITH(NOLOCK)

JOIN  [dbo].[userProfileValue] UserProfileVal

     ON  UserProfileVal.RecordID =  UserProfile.RecordID

JOIN  [dbo].[PropertyList]     PropList

     ON  PropList.PropertyID =  UserProfileVal.PropertyID

WHERE  UserProfile.NTName IN ('domain\user1', 'domain\user2')

Use this query to find the membership of a user in various AD groups.

SELECT  UserProfile.NTName, UserProfile.PreferredName, MemberGrp.Displayname

FROM [dbo].[UserProfile_Full] UserProfile WITH(NOLOCK)

JOIN  [dbo].UserMemberships UserMembership

    ON  UserProfile.RecordID =  UserMembership.RecordID

JOIN  [dbo].Membergroup MemberGrp 

      ON UserMembership.Membergroupid = MemberGrp.id

where UserProfile.NTName like '%domain\user1%'

SharePoint Form Fields and jQuery

Get or Set values/attributes of SharePoint Form Fields using JQuery.

Question 1: From where to download JQuery Base Library?
Answer 1:  http://code.jquery.com/

Question 2: How to include JQuery Script?
Answer 2: Upload the JQuery base library to the Assets list and include the library using the below syntax
<script type="text/javascript" src="../../Assets/jquery-1.10.2.min.js"></script>

Question 3: What attribute to use for getting the INPUT object?
Answer 3: We need to use the title attribute of the INPUT control

<input name="ctl00$m$g_e2bcfa9c_6e16_4b44_9833_22e44201a72b$ctl00$ctl04$ctl03$ctl00$ctl00$ctl04$ctl00$ctl00$TextField" type="text" maxlength="255" id="ctl00_m_g_e2bcfa9c_6e16_4b44_9833_22e44201a72b_ctl00_ctl04_ctl03_ctl00_ctl00_ctl04_ctl00_ctl00_TextField" title="Email" class="ms-long" />

Question 4: How to write JQuery function?
Answer 4:

<script type="text/javascript" src="../../Assets/jquery-1.10.2.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {

});

</script>

Note: $(document).ready(function ()... is referred as the MAIN() function

Question 5: How to get the value of the INPUT field?
Answer 5: var variablename = $("input[title='title of the input control']").val();

Question 6: How to make the field readonly?
Answer 6: $("input[title='title of the input control']").attr("readonly","true");

Question 7: How to get the value of the Dropdown?
Answer 7: var variablename = $("select[title='title of the dropdown control']").val();

Question 8: How to set the value to the text field?
Answer 8: $("input[title='title of the input control']").val("enter value here");

Question 9: How to remove the readonly of the text field?
Answer 9: $("input[title='title of the input control']").removeAttr("readonly"); 

Question 10: How to set focus to the text field?
Answer 10: $("input[title='title of the input control']").focus(); 

Question 11: How to use JQuery in PreSaveAction or PreSaveItem?
Answer 11: 
<script type="text/javascript" src="../../Assets/jquery-1.10.2.min.js"></script>
<script language = "Javascript">
function PreSaveAction() 
{

var variablename = $("input[title='title of the input control']").val();

}

</script>

Note: do not include $(document).ready(function ()... 

Question 12: How to call JQuery function in Dropdown value change?
Answer 12:
<script type="text/javascript" src="../../Assets/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {

$("select[title='title of the control']").change(function () {
//write logic here
});
});
</script>

Question 13: How to set the width of the Text field?
Answer 13: $("input[title='title of the control']").width(100);

Question 14: How to disable Textfield?
Answer 14: $("input[title='title of the control']").attr('disabled','disabled');
Question 15: How to Remove disable attribute on Textfield?
Answer 15: $("input[title='title of the control']").removeAttr('disabled');

Question 16: How to check numeric value in Text field?
Answer 16: 
var numbervaluefield = $("input[title='title of the control']").val();
var numericheckvaariable = $.isNumeric(numbervaluefield);

Note: The function will return boolean value

Question 17: How to compare date in Jquery?
Answer 17:

var startdate = new Date($("input[title='Start Date']").val()); 
var enddate = new Date($("input[title='End Date']").val());

if(enddate < startdate)
{
 alert("End Date cannot be lesser than Start date.");
 $("input[title='End Date']").focus();
 return false;
}
else
{

 return true;
}

Question 18: How to set default value in Rich Text field?
Answer 18:

<script type="text/javascript" src="../../Assets/jquery-1.10.2.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {

var htmlcontentval = "<table border='1' cellpadding='0' cellspacing='0'><tr><td colspan='3'>Month-Year</td></tr><tr><td>Milestone</td>       <td>Onsite Effort</td><td>Offshore Effort</td></tr><tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr></table>";

$("textarea[title='Milestone Information']").val(htmlcontentval);
});
</script>

Note: Milestone information is the title of the textarea used in the Rich text field

Question 19: How to hide the Sharepoint Enhanced Richtext field?
Answer 19:  $("textarea[title='richtexttitle']").closest("tr").hide();
Question 20: How to unhide the Sharepoint Enhanced Richtext field?
Answer 20:  $("textarea[title='richtexttitle']").closest("tr").show();

Question 21: How to convert string to uppercase?
Answer 21:  $("input[title='titlename']").val().toUpperCase();

Question 22: How to check length of the string?
Answer 22 : $("input[title='titlename']").val().val().length;

Question 23: How to validate Email address?
Answer 23:

var emailReg = /^([\w-\.]+@([\w-]+\.)+[\w-]{2,4})?$/; 
var emailaddressVal = $("input[title='titlename']").val(); 
if(!emailReg.test(emailaddressVal)) 
{
 alert("Please enter valid email address");
}

Question 24: How to prevent free email addresses like yahoo.com, gmail.com ?

Answer 24:

var emailblockReg = /^([\w-\.]+@(?!gmail.com)(?!yahoo.com)(?!hotmail.com)([\w-]+\.)+[\w-]{2,4})?$/;

var emailaddressVal = $("input[title='titlename']").val(); 
if(!emailblockReg.test(emailaddressVal)) 
{
 alert("Free Email addresses are not allowed");
}

Question 25: How to get the lookup text value (Dropdown text value)?
Answer 25: $("option:selected", $("select[title='titlename']")).text();

List and Library templates missing from SharePoint sites

Recently i came across an issue where users don't have default Library and list templates to choose from when they want to create a list or library.

Issue -
When user selects 'More options' from site actions menu and select filter by:
Library - It shows only three templates Asset, Slide and wiki page Library.
List - It shows only one template 'Import Spreadsheet'.

Solution -
Team collaboration Lists feature provides team collaboration capabilities for a site by making standard lists, such as document libraries and issues, available. so if you encounter such issue just make sure Team collaboration Lists feature is activated on the site.

Go to Site settings -> Manage site features -> find 'Team Collaboration Lists' feature -> Activate

Target Audience - non-existent membership Group

While working with User Profiles Service and Target Audience I noticed that members are not showing up in Audiences. There are the possible areas to look for reasons -

- Check group is present in AD.
- Look for Group with same name (duplicate group).
- Check service account permission on Active Directory (OU).
- Check the User Profile Synchronization connection. Make sure Sync connection includes the OU's where groups are located.
 UPApp > Syncronization> Configure Synchronization Settings > Option "Users & groups" is selected, run a Full Sync
- If Full sync is not working then Check User Profile Synchronization Service. (Stop and Start with correct Credential to fix any FIM issues - IISreset required after User Profile Synchronization Service started)
- Compile the audiences after Full Sync.
- Check if Audience shows Users in view membership.
- Check Audience Rules if Audience rule throws error.
- Check Groups are present in UPA "Profile DB" post Full Sync.

Run the following queries against Profile DB of the to see if group is present or not

Select * from MemberGroup (nolock) where AllWebsSynchID is NULL -- It should fetch all AD groups
Select * from MemberGroup (nolock) where AllWebsSynchID is NULL and DisplayName like '%group name%'

Also if you are uploading audiences using PowerShell Script then make sure you are using Active Directory Group's full path in Value option in reverse hierarchy not just Group Name.

<Rule Property='DL' Operator='Member of' Value='CN=GroupName,OU=Groups,OU=ABC,DC=Domain,DC=com' />

SharePoint Development Tools

Recently I came across with SharePoint Software Factory. It is a Visual Studio Extension helping SharePoint newbies, as well as experienced developers to create, manage and deploy SharePoint solutions without having to know every tiny XML and C# secret.

SPSF provides a huge collection of helpful recipes for development, debugging and deployment of SharePoint standard artifacts and is fully compatible with SharePoint 2007/2010/2013 and Visual Studio 2008/2010/2012.

This extension can cut your development time tremendously. I would highly recommend it for SharePoint developers.

SharePoint Software Factory - http://spsf.codeplex.com/

Install and uninstall SharePoint Apps using PowerShell

Install and uninstall SharePoint Apps using PowerShell

Microsoft introduced apps for Office and apps for SharePoint. You can develop Apps using visual studio office tools and NAPA. You can deploy app from visual studio deploy option or through PowerShell.

Here is how PowerShell can help you deploy Apps to different site collections.

To Install SharePoint App -

Before you can install an app, make sure that App is published to a physical folder using publish option. After publishing you will have a package file with .app extension (like yourApp.app).  

Installation of app is two steps process, first import app to a site collection and then install imported App.

Import-SPAppPackage -Path <Path of .app file> -Site < SiteCollectionURL > -Source < AppSource>

AppSource can be Marketplace, CorporateCatalog, DeveloperSite, ObjectModel, RemoteObjectModel.

Install-SPApp -Web <SPweb ID or URL> -Identity <SPApp>

Example:

$myApp = Import-SPAppPackage -Path "c:\Apps\MySharepointApp.app" -Site http://localhost/apps

 -Source ([microsoft.sharepoint.administration.spappsource]::ObjectModel)

Install-SPApp -Web http://localhost/apps -Identity $myApp

To Uninstall SharePoint App -

To uninstall an app first you need to get the instances of installed Apps then get the app you want to uninstall using app ID or app Title.

To get all installed apps in SharePoint site -

$appInstances = Get-SPAppInstance -Web < SiteCollectionURL >

To get the app details that you want uninstall -

$app = $appInstances | where {$_.Title -eq '< Title or ID of App >'}

To uninstall the app -

Uninstall-SPAppInstance -Identity $app

Example:

$appInstances = Get-SPAppInstance -Web http://localhost/apps

$app = $appInstances | where {$_.Title -eq ' Your SharePoint App'}

Uninstall-SPAppInstance -Identity $app

Deploy / Upgrade SharePoint 2010 Solutions in SharePoint 2013

you can continue to use your SharePoint 2010 VS2010 code and customizations inside SharePoint 2013. Most of them should work just fine in SharePoint 2013 because by default SharePoint 2013 supports both 14 Hive and 15 Hive directory structure.

Most of time SharePoint 2010 solution gets deployed to the 14 hive instead of 15 hive, because of a setting in the manifest.xml file. you need to add ‘SharePointVersion=”15.0″’ attribute in manifest.xml file of solution to force the solution to be installed in 15 Hive. Also _layouts folder reference needs to be updated to “_/layouts/15/” to point in 15 hive.

I would suggest to follow these steps for any SharePoint 2010 project to upgrade and deploy in SharePoint 2013 - 

1. Install Visual Studio 2012 and reopen solution in VS 2012
2. Change .NET Framework version to  v4.5
3. Update existing reference of Microsoft.SharePoint.dll to "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI". (may need to other dlls reference as well)
4. Add a property ‘SharePointVersion =”15.0"’ in <Solution> element in the manifest file. (otherwise it gets installed into SharePoint 14 location)
5. Update _layouts folder reference.
6. Build your solution, deploy and Test.

Hope this helps!

Open all PDF files in a new browser window

This can be achieved via JQuery. 

To open pdf files in new browser window : 

<script type="text/javascript"> 
$(document).ready(function() { 
$("a[href*=.pdf]").click(function(){ 
window.open(this.href); 
return false; 
}); 
}); 
</script>

Or you can use target=”_self” attribute to open new document in the same window : 

<script type="text/javascript"> 
$(document).ready(function() { 
$("a[href*=.pdf]").click(function(){ 
$(this).attr({"target":"_self"}); 
return false; 
});

}); 
</script>

Update:- 

If onclick attribute is present then try this  

$("a[href$='.pdf']").removeAttr('onclick').attr("target","_blank"); 

Hope this helps.