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();