SharePoint List Validation: How to validate a column based on the value of another field in the list?

=IF([Column1]="SomeValue",IF(ISBLANK([Column2]),FALSE,TRUE),TRUE)

SharePoint Designer: SharePoint workflow content missing or blank when viewed from another machine.

Solution: Install the following updates:

https://support.microsoft.com/en-gb/kb/2837633

Fixed: SharePoint web-part zones not visible on Internet Explore.

All you need to do is to add the site in local intranet site zone like this:

How to add or remove fields in SharePoint list's new item form?

1. Go to advanced list setting.
2. Enable management of content types.
3. Go to the default content type and add or remove the columns.
4. Browse this list in SharePoint designer and create a new item form with the above content type.
5. Set the above form as default form.

Note: The same is applicable on edit and display item form as well.

SharePoint Designer: Embedding Image in Workflow Mailer

1. Create a Pictures Library in SharePoint.
2. Add the required image in the library and copy the image URL.
3. Create workflow in SP Designer.
4. Create a workflow variable with datatype as string.
5. Assign variable following value:

<img src="ImageURL" height="500" />

6. Place the above variable in the workflow mailer where image should be loaded. 

SharePoint List Item: How to programmatically set value to a lookup column?

If you try to assign directly like below then you will get an error with message saying "Invalid data has been used to update the list item. The field you are trying to update may be read only".

newItem["Resource_x0020_ID"] = "john@company.com";

Below is the correct way to assign value to a lookup column:

SPListItem newItem = list.AddItem();

var empID = web.Lists["Resource Master"].GetItemById(Convert.ToInt32(101));

newItem["Resource_x0020_ID"] = new SPFieldLookupValue(empID.ID, empID.Title);

newItem.Update();

SharePoint Application Page: How to export DataTable data to Excel file using ClosedXML?

Installing ClosedXML in your project (VS)

Go to Tools > Library Package Manager > Manage NuGet Packages..

Search for ClosedXML and install it. This will install DocumentFormat.OpenXML as well for you.

Reference to above should be added under References automatically. If not then you can add them manually.

Code:

using DocumentFormat.OpenXml;
using ClosedXML.Excel;
using System.Xml;

using System.IO;

protected void Page_Load(object sender, EventArgs e)
        {
//So that page does not stop responding after generating Excel file (Response.End())
btnExportExcel.OnClientClick = "_spFormOnSubmitCalled = false;_spSuppressFormOnSubmitWrapper=true;";
}


protected void btnExportExcel_Click(object sender, EventArgs e)
        {
            try
            {
                DataSet ds = new DataSet();
                //Call method which returns DataTable
                ds.Tables.Add(GetDataTable());
                ExportToExcel(ds);
            }
            catch (Exception ex)
            {
                lblMessage.Text = "Error: " + ex.Message;
            }
        }


protected void ExportToExcel(DataSet ds)
        {
            XLWorkbook wb = new XLWorkbook();
            DataTable dt = ds.Tables[0];
            wb.Worksheets.Add(dt);

            string fileName = Server.UrlEncode("Report" +  "_" + DateTime.Now.ToShortDateString() + ".xlsx");
            MemoryStream stream = (MemoryStream)GetStream(wb);

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
            Response.ContentType = "application/vnd.ms-excel";
            Response.BinaryWrite(stream.ToArray());
            Response.Flush();
            Response.End();
        }


        public Stream GetStream(XLWorkbook excelWorkbook)
        {
            Stream fs = new MemoryStream();
            excelWorkbook.SaveAs(fs);
            fs.Position = 0;
            return fs;
        }

SpQuery : [Error] Cannot complete this action : How to add three condition in where clause?

Multiple AND nested SPQuery (CAML):

Example 1

<Where>
      <And>
         <And>
            <Eq>
               <FieldRef Name='EmployeeID' />
               <Value Type='Text'>12345</Value>
            </Eq>
            <Eq>
               <FieldRef Name='Status' />
               <Value Type='Text'>Active</Value>
            </Eq>
         </And>
         <Eq>
            <FieldRef Name='Gender' />
            <Value Type='Text'>F</Value>
         </Eq>
      </And>
   </Where>



Example 2

<And>
    <Eq>... condition...</Eq>
    <And>
        <Eq>... condition...</Eq>
        <And>
            <Eq>... condition...</Eq>
            <And>
                <Eq>... condition...</Eq>
                <Eq>... condition...</Eq>
            </And>
        </And>
    </And>
</And>


Reference: https://docs.microsoft.com/en-us/sharepoint/dev/schema/and-element-query

How to merge duplicate rows in Data Table in c#

           DataTable dt = new DataTable();
           dt.Columns.Add("Name", typeof(string));
           dt.Columns.Add("Result", typeof(string));

           dt.Rows.Add("John", "1,2,3,4,5");
           dt.Rows.Add("Mary ", "5,6,7,8");
           dt.Rows.Add("John", "6,7,8,9");

           DataTable dtRsult = dt.Clone();
           var distinctRows = dt.DefaultView.ToTable(true, "Name").Rows.OfType<datarow>().Select(k => k[0] + "").ToArray();
           foreach (string name in distinctRows)
           {
               var rows = dt.Select("Name = '" + name + "'");
               string value = "";
               foreach (DataRow row in rows)
               {
                   value += row["Result"] + ",";
               }
               value = value.Trim(',');

               dtRsult.Rows.Add(name, value);
               value = "";

           }

     

SPQuery: Cannot complete this action. Please try again?

"Cannot complete this action" = "Error in the CAML syntax".

Zoom in and look for the error!

How to deploy a custom master page from SharePoint farm solution.

Right click on your project > Add New Item > Module

Rename Sample.txt to MyCustomMaster.master

Replace the content of Element.xml with following:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

  <Module Name="CustomMasterPages" Url="_catalogs/masterpage" List="116">

    <File  Url="MyCustomMaster.master" Path="CustomMasterPages\MyCustomMaster.master" Type="GhostableInLibrary" IgnoreIfAlreadyExists="FALSE" >

      <Property Name="UIVersion" Value="15"></Property>
      <Property Name="MasterPageDescription" Value="This is the master page for custom pages." />
      <Property Name="ContentType" Value="$Resources:cmscore,contenttype_masterpage_name;" />
    </File>
  </Module>
</Elements>


Refer it like this from your pages:

MasterPageFile="/_catalogs/masterpage/MyCustomMaster.master"

How to refer a custom master page from a SharePoint Application page?

To refer a custom master page from an application page you should put both the files under the same layout folder in your SharePoint farm solution.

Then you can refer to the master page using the following tag from the application page:

MasterPageFile="MasterPages/MyCustomMaster.master"


Workaround to refer master page gallery from an application page:

protected override void OnPreInit(EventArgs e)
{
base.OnPreInit(e);
SPWeb web= SPControl.GetContextSite(Context).OpenWeb();
string strUrl = web.ServerRelativeUrl + “/_catalogs/masterpage/MyCustomMaster.master”;
this.MasterPageFile = strUrl;
}

SharePoint: Site pages vs Application pages.

Site Pages

These Pages are stored in the Content Database and they are parsed when requested by user. A typical web part page is an example of Site Pages. They can be edited, modified by the Power Users and customized according to their needs.

Application Pages

They are same as ASP.net Pages and stored on the layouts folder of SharePoint front end web server. When user requests , application pages are compiled and they are much faster than Site Pages. Admin Pages like settings.aspx, accessdenied.aspx are an example of Application Pages.

SharePoint Designer: How to add an if condition around an existing workflow statement?

Add the if condition, which you missed earlier, right above the statement you need inside the if block.

Then go to the properties of the statement and select 'move action up'.

How to hide password while entering it on C# console application?

static void Main(string[] args)
        {
            string user;
            Console.Write("Enter your user name: ");
            user = Console.ReadLine();

            string pwd = "";
            Console.Write("Enter your password: ");
            ConsoleKeyInfo key;
            do
            {
                input = Console.ReadKey(true);

                if (input.Key != ConsoleKey.Backspace && input.Key != ConsoleKey.Enter)
                {
                    pwd += input.KeyChar;
                    Console.Write("*");
                }
                else
                {
                    if (input.Key == ConsoleKey.Backspace && pass.Length > 0)
                    {
                        pwd = pwd.Substring(0, (pwd.Length - 1));
                        Console.Write("\b \b");
                    }
                }
            }
            while (input.Key != ConsoleKey.Enter);
            CallYourMethod(user, pwd);
        }

Exception: The collection was modified. Enumeration operation may not execute.

Change

foreach(Object x in ObjectCollection) {...}

to

foreach(Object x in ObjectCollection.ToList()) {...}

Bulk delete SharePoint list items: CSOM

Call this method recursively:

public static void deleteExistingItemFromDestination(string listName)
        {
            try
            {
                ClientContext context = new ClientContext("https://domain.com/sites/siteColl/siteName/");
                string decryptedPwd = "password";
                foreach (char c in decryptedPwd)
                {
                    securePassword.AppendChar(c);
                }
                context.Credentials = new NetworkCredential("user", decryptedPwd, "Domain");
                Web web = context.Web;
                context.Load(web);

                List list = web.Lists.GetByTitle(listName);
                context.Load(list);
                context.ExecuteQuery();
                CamlQuery camlQuery = new CamlQuery();
                camlQuery.ViewXml = "@<View Scope='RecursiveAll'>"
                                        + "<Query>"
                                            + "<OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>"
                                        + "</Query>"
                                        + "<RowLimit Paged='TRUE'>5000</RowLimit>"
                                    + "</View>";
                ListItemCollection lstcoll = list.GetItems(camlQuery);
                Console.WriteLine("Starting at: "+System.DateTime.Now.ToShortTimeString());
                context.Load(lstcoll);
                context.ExecuteQuery();
                Console.WriteLine("\r\n\r\nDeleting...");
                foreach (ListItem eitem in lstcoll)
                {
                    ListItem item = list.GetItemById(Convert.ToInt32(eitem["ID"]));
                    item.DeleteObject();
                    context.ExecuteQuery();
                }
                Console.WriteLine("Completed at: " + System.DateTime.Now.ToShortTimeString());
             
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.Read();
            }
        } 

Show Hide Fields Based On Dropdown Selection Sharepoint 2013

http://www.myclassbook.org/hide-show-fields-in-sharepoint-depending-on-drop-down/

How to get filtered list items using CAML query in CSOM?

Microsoft.SharePoint.Client.CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery();

query.ViewXml = "<View>"
   + "<Query>"
   + "<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>Navneet</Value></Eq></Where>"
   + "</Query>"
   + "</View>";

ListItemCollection listItems = list.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();

Multiple lookup SPD Workflow: How to filter list item based on two columns in SharePoint designer 2013 workflow?

In SharePoint designer you can lookup only one column.

So, create a calculated column in the source list and concatenate the two columns.

Calculated column (KEY) = CONCATENATE([ColumnA],[ColumnB])

Now you can perform lookup on this calculated column.

In SharePoint designer create a variable by concatenating the columns containing the data same as ColumnA and ColumnB.

As SPD doesn't have concatenate function so, you can use string builder to achieve the merged string:

How to set unique permission for list items based on the users present in list columns for each item?

 static void Main(string[] args)
        {

            Console.Write("Enter list name:\r\n");
            string listTitle = Console.ReadLine();
            Console.Write("\r\nEnter column names separated by columns without space:\r\n");
            string listColumns = Console.ReadLine();
            SetItemPermissions(listTitle, listColumns);
       }


public static void SetItemPermissions(string listName, string fields)
        {
            try
            {
                int y = 0;
                ClientContext context = new ClientContext("https://domain.com/sites/siteColl/siteName/");
                string decryptedPwdt = "password";
                foreach (char c in decryptedPwdt)
                {
                    securePassword.AppendChar(c);
                }

                context.Credentials = new NetworkCredential("user", decryptedPwdt, "Domain");
                Web web = context.Web;
                context.Load(web);

                List list = web.Lists.GetByTitle(listName);
                context.Load(list);

                CamlQuery camlQuery = new CamlQuery();
                ListItemCollection itemColl = list.GetItems(camlQuery);
                string[] columnNames = fields.Split(',');

                foreach (string column in columnNames)
                {
                    context.Load(itemColl, includes => includes.Include(i => i[column]));
                }
                context.ExecuteQuery();

                foreach (ListItem item in itemColl)
                {
                    item.BreakRoleInheritance(false, false);

                    foreach (string member in columnNames)
                    {
                        try
                        {
                            if (item[member].GetType().ToString() == "Microsoft.SharePoint.Client.FieldUserValue")
                            {
                                try
                                {
                                    User user = web.EnsureUser((((Microsoft.SharePoint.Client.FieldUserValue)(item[member])).LookupValue).ToString());
                                    RoleDefinitionBindingCollection roleColl = new RoleDefinitionBindingCollection(context);
                                    roleColl.Add(context.Web.RoleDefinitions.GetByName("Custom permission level"));
                                    item.RoleAssignments.Add(user, roleColl);
                                }
                                catch
                                {
                                    User user = web.EnsureUser((((Microsoft.SharePoint.Client.FieldUserValue)(item[member])).Email).ToString());
                                    RoleDefinitionBindingCollection roleColl = new RoleDefinitionBindingCollection(context);
                                    roleColl.Add(context.Web.RoleDefinitions.GetByName("Custom permission level"));
                                    item.RoleAssignments.Add(user, roleColl);
                                }
                            }
                            else
                            {
                                User user = web.EnsureUser(item[member].ToString());
                                RoleDefinitionBindingCollection roleColl = new RoleDefinitionBindingCollection(context);
                                roleColl.Add(context.Web.RoleDefinitions.GetByName("Custom permission level"));
                                item.RoleAssignments.Add(user, roleColl);
                            }
                        }
                        catch { }
                    }

                    try
                    {

                        Group groupMember = web.SiteGroups.GetByName("Group1");
                        Group groupOwner = web.SiteGroups.GetByName("Group2");
                        Group groupTL = web.SiteGroups.GetByName("Group3");



                        RoleDefinitionBindingCollection roleCollEdit = new RoleDefinitionBindingCollection(context);
                        roleCollEdit.Add(context.Web.RoleDefinitions.GetByName("Edit"));

                        RoleDefinitionBindingCollection roleCollFull = new RoleDefinitionBindingCollection(context);

                        roleCollFull.Add(context.Web.RoleDefinitions.GetByName("Full Control"));
                        item.RoleAssignments.Add(groupMember, roleCollEdit);
                        item.RoleAssignments.Add(groupOwner, roleCollFull);
                        item.RoleAssignments.Add(groupTL, roleCollEdit);


                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }

                    try
                    {
                        item.Update();
                        context.ExecuteQuery();
                    }
                    catch (Exception ex)
                    {
                        if (ex.HResult == -2146233088)
                        {
                            Group groupMember = web.SiteGroups.GetByName("Group1");
                            Group groupOwner = web.SiteGroups.GetByName("Group2");
                            Group groupTL = web.SiteGroups.GetByName("Group3");



                            RoleDefinitionBindingCollection roleCollEdit = new RoleDefinitionBindingCollection(context);
                            roleCollEdit.Add(context.Web.RoleDefinitions.GetByName("Edit"));

                            RoleDefinitionBindingCollection roleCollFull = new RoleDefinitionBindingCollection(context);

                            roleCollFull.Add(context.Web.RoleDefinitions.GetByName("Full Control"));
                            item.RoleAssignments.Add(groupMember, roleCollEdit);
                            item.RoleAssignments.Add(groupOwner, roleCollFull);
                            item.RoleAssignments.Add(groupTL, roleCollEdit);
                            item.Update();
                            try
                            {
                                context.ExecuteQuery();
                            }
                            catch (Exception ex2)
                            {
                                Console.WriteLine(ex2.Message);
                            }
                        }
                    }

                    Console.Write(".");
                    y++;
                    if (y == 1) Console.WriteLine("\r\n1 item updated.");
                    if (y == 100) Console.WriteLine("\r\n100 items updated.");
                    if (y == 500) Console.WriteLine("\r\n500 items updated.");
                    if (y == 800) Console.WriteLine("\r\n800 items updated.");
                    if (y == 1000) Console.WriteLine("\r\n1000 items updated.");
                    if (y == 2000) Console.WriteLine("\r\n2000 items updated.");
                    if (y == 3000) Console.WriteLine("\r\n3000 items updated.");
                    if (y == 4000) Console.WriteLine("\r\n4000 items updated.");
                }
                Console.WriteLine("\r\nDone!");
                Console.Read();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.Read();
            }

        }

How to reset SharePoint list items permission from unique permission to inheriting from parent?

public static void ResetItemPermissions(string listName)
        {
            ClientContext context = new ClientContext("https://domain.com/sites/siteColl/siteName/");
            string decryptedPwdt = "password";
            foreach (char c in decryptedPwdt)
            {
                securePassword.AppendChar(c);
            }
            context.Credentials = new NetworkCredential("user", decryptedPwdt, "Domain");
            Web web = context.Web;
            context.Load(web);

            List list = web.Lists.GetByTitle(listName);
            context.Load(list);

            CamlQuery camlQuery = new CamlQuery();
            ListItemCollection itemColl = list.GetItems(camlQuery);
            context.Load(itemColl);
            context.ExecuteQuery();

            foreach (ListItem item in itemColl)
            {
                item.ResetRoleInheritance();
                item.Update();
                context.ExecuteQuery();

                Console.Write(".");
           
            }
            Console.WriteLine("\r\nDone!");
            Console.Read();
        }

How to get User object using CSOM from SharePoint online list item?

newItem["Created_x0020_By_x0020_History"] = web.EnsureUser((((Microsoft.SharePoint.Client.FieldUserValue)(item["Author"])).Email).ToString());


                            newItem["Modified_x0020_By_x0020_History"] = web.EnsureUser((((Microsoft.SharePoint.Client.FieldUserValue)(item["Editor"])).Email).ToString());

How to remove multiple SharePoint user permission without getting following error message: "You have chosen to delete too many groups at once. Select fewer groups and try again".

SharePoint doesn't allow to delete all 1000s of users at once. But, we can delete 400 at a time. That again is quite tedious - selecting 400 hundred check-boxes manually.

We can reduce the effort for selecting checkboxes manually by using the browser's developer tool.

Press F12 on your browser to open the developer tool.

Go to console and execute the following to select only 400 users at a time:

(function() {
    var aa = document.querySelectorAll("input[type=checkbox]");
    for (var i = 1; i < 400; i++){
        aa[i].checked = true;
    }
})()



Note: Loop is executed from the 2nd index to avoid 'select all' checkbox.


-----------------------------------------OR---------------------------------------------------

Use the following CSOM code:

public static void RemoveSitePermissions()
        {
            ClientContext ctxSource = new ClientContext("https://domain.com/sites/siteColl/siteName/");
            string decryptedPwd = "password";
            foreach (char c in decryptedPwd)
            {
                securePassword.AppendChar(c);
            }
            ctxSource.Credentials = new SharePointOnlineCredentials("user@domain.com", securePassword);
            Web webSource = ctxSource.Web;
            ctxSource.Load(webSource);
            UserCollection userColl = webSource.SiteUsers;
            ctxSource.Load(userColl);
            ctxSource.ExecuteQuery();

            foreach (User user in userColl.ToList())
            {
                try
                {
                    userColl.Remove(user);
                    ctxSource.ExecuteQuery();
                    Console.Write(".");
                 
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

How to hide a part of navigation for a set of users on SharePoint 2013?

First organize all the site users into different groups,

Then go to Site Settings > Navigation and edit the part of navigation you want to hide and set the target audience. 

How to hide left navigation on a particular page in SharePoint 2013?

Add a Script Editor web-part on the page where you need to hide the left navigation and add the following:

<style> 
#sideNavBox
{ 
display: none; 
}
</style>

To re-use this on multiple pages, in case of Publishing site, add the above on Page Layout.

How to prepare basic variables for SharePoint hosted App?

    //Global variables

    var context;

    var hostweburl;

    var appweburl;

    var appContextSite;

    var list;

    var web;

    //Code included inside $( document ).ready() will only run once the page

    //Document Object Model (DOM) is ready for JavaScript code to execute

    $(document).ready(function () {

        //Ensures that the specified file that contains the specified function is loaded and

        //then runs the specified callback function.

        //SP.SOD.executeFunc('file name','function name',methos to call post execution);

        SP.SOD.executeFunc('sp.js', 'SP.ClientContext', getUrl);

    });

    function getUrl() {

        //Fetch query string parameters

        hostweburl = getQueryStringParameter("SPHostUrl");

        appweburl = getQueryStringParameter("SPAppWebUrl");

        //Decode

        hostweburl = decodeURIComponent(hostweburl);

        appweburl = decodeURIComponent(appweburl);

        //Build absolute path to the layouts root with the spHostUrl

        var scriptbase = hostweburl + "/_layouts/15/"; 

        //Before you get SP.js, you have to get SP.Runtime.js

        //Once SP.js can be retrieved, it makes a call to the execOperation method.

        //SP.ProxyWebRequestExecutorFactory is located in sp.requestexecutor.js,

        //which also provides objects, methods, and properties for

        //managing Web request executors for client requests.

        $.getScript(scriptbase + "SP.Runtime.js",

            function () {

                $.getScript(scriptbase + "SP.js",

                function () { $.getScript(scriptbase + "SP.RequestExecutor.js", execCrossDomainRequest); }

                );

            }

        );

            

    }

    function execCrossDomainRequest() {

        context = new SP.ClientContext(appweburl);

        var factory = new SP.ProxyWebRequestExecutorFactory(appweburl);

        context.set_webRequestExecutorFactory(factory);

        appContextSite = new SP.AppContextSite(context, hostweburl);

        web = appContextSite.get_web();

        context.load(web);

        list = web.get_lists().getByTitle("Shared Documents");

        context.load(list);

        context.executeQueryAsync(onSuccess, onFail);

        }

        function onSuccess() {

              alert("List loaded Successfully");   

        }

        // This function is executed if the above call fails

        function onFail(sender, args) {

               alert( args.get_message());

        }

        //Generic function to fetch query sting parameters   

        function getQueryStringParameter(paramToRetrieve)

        {

            var params =

                document.URL.split("?")[1].split("&");

            for (var i = 0; i < params.length; i = i + 1)

            {

                var singleParam = params[i].split("=");

                if (singleParam[0] == paramToRetrieve)

                    return singleParam[1];

            }

        }