Navneet Chanalia's SharePoint Blog: March 2018

Saturday, 31 March 2018

CAML Query : How to query for SharePoint list items with special characters in value?

Use CDATA like below:

var camlQuery="<Query><Where><Eq><FieldRef Name='Column Name'/><Value Type='Text'><![CDATA[" + decodeURI(getParameterByName('queryStringParameter',window.location.href))+ "]]></Value></Eq></Where></Query>";

How to get and update SharePoint list using SPServices and w2ui grid table?

Sample Page (SharePoint Designer):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta name="WebPartPageExpansion" content="full" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Page Title</title>
<script type="text/javascript" src="https://domain.com/sites/siteName/SiteAssets/scripts/jquery-2.2.4.min.js"></script>
<script type="text/javascript" src="https://domain.com/sites/siteName/SiteAssets/scripts/jquery.SPServices.min.js"></script>
<script type="text/javascript" src="https://domain.com/sites/siteName/SiteAssets/scripts/w2ui-1.5.rc1.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://domain.com/sites/siteName/SiteAssets/scripts/w2ui-1.5.rc1.min.css" />

<script language="javascript" type="text/javascript">

function getParameterByName(name, url) {
if (!url) url = window.location.href;
name = name.replace(/[\[\]]/g, "\\$&");
var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
results = regex.exec(url);
if (!results) return null;
if (!results[2]) return '';
return decodeURIComponent(results[2].replace(/\+/g, " "));
}

var cmlQuery="<Query><Where><Eq><FieldRef Name='Column Name'/><Value Type='Text'><![CDATA[" + decodeURI(getParameterByName('queryStringParameter',window.location.href))+ "]]></Value></Eq></Where></Query>";

var listData=[];
var counter=0;

$(document).ready(function() {
$().SPServices({
operation: "GetListItems",
async: false,
listName: "List Name",
CAMLViewFields: "<ViewFields Properties='True' />",
CAMLQuery: cmlQuery,
CAMLRowLimit: 0,
completefunc: function (xData, Status) {
$(xData.responseXML).SPFilterNode("z:row").each(function() {
listData.push({recid: $(this).attr("ows_ID"),Boolean: ($(this).attr("ows_Check_x0020_Box")=="1"),Resource: $(this).attr("ows_Enterprise_x0020_ID").split('#')[1]});
});
}
});
});

function UpdateItem(_id,_boolean)
{
$().SPServices({
operation: "UpdateListItems",
listName: "List Name",
ID: _id,
valuepairs: [["Check_x0020_Box", _boolean]],
completefunc: function (xData, Status) {
//alert(Status);
}
});
}

$(function () {
$('#grid').w2grid({
name: 'grid',
show: {
toolbar: true,
footer: true,
toolbarSave: true
},
columns: [
// { field: 'recid', caption: 'ID', size: '50px', sortable: true, resizable: true,show:false },
{ field: 'Boolean', caption: 'Boolean', size: '60px', sortable: true, resizable: true, style: 'text-align: center',
editable: { type: 'checkbox', style: 'text-align: center' }
},
{ field: 'Resource', caption: 'Resource', size: '120px', sortable: true, resizable: true}
],

onSave: function (event) {
var changeArr=w2ui['grid'].getChanges();
$(changeArr).each(function() {
UpdateItem($(this).attr("recid"),$(this).attr("Boolean"));
});
w2alert('Saved!');

},

records:listData
});

if(w2ui['grid'].records.length==0)
{
$("#grid").hide();
w2alert('No pending items. Thanks!');
}

});

</script>

</head>

<body>
<div id="grid" style="width: 100%; height: 400px;"></div>
</body>

</html>

Friday, 30 March 2018

How to add cell borders in SharePoint list view?

Add following in a script editor:

<style>
/* Table Rows*/
/* Set background for every row */
.ms-listviewtable > tbody > tr {
background: white;
}

/* Set border for every row */
.ms-listviewtable > tbody > tr td{
border-bottom: 1px solid #AFAFAF !important;
border-left: 1px solid #AFAFAF !important;
border-right: 1px solid #AFAFAF !important;
}

/* Set background for every alternating row */
.ms-listviewtable > tbody > tr.ms-alternating {
background: #E2E3E5;
}

/* Header Row */

.ms-listviewtable tr.ms-viewheadertr {
background: transparent;
}

/* Modify background color */
tr.ms-viewheadertr > th.ms-vh-icon, /* Input box and attachment icon */
tr.ms-viewheadertr > th.ms-vh, /* Text */
tr.ms-viewheadertr > th.ms-vh2,
tr.ms-viewheadertr > th /* Text */ {
background: #e2e3e5;
}

/* Modify background color on hover */
tr.ms-viewheadertr > th.ms-vh:hover,
tr.ms-viewheadertr > th.ms-vh2:hover,
tr.ms-viewheadertr > th {
background-color: #e2e3e5;
border-color: #e2e3e5;
}

/* Modify font color */
.ms-viewheadertr a,
.ms-viewheadertr div {
color: black;
font-weight: bold;
}

/* Modify font color on hover */
.ms-viewheadertr a:hover,
.ms-viewheadertr div:hover {
color: black;
font-weight: bold;
}
tr.ms-viewheadertr > th.ms-vh-icon:last-child {
display:none;
}

</style>

[Solved] w2ui table rows not displaying data.

Try using jQuery 2.2.4 or below.

w2ui is not compatible with the higher versions of jQuery.

Tuesday, 27 March 2018

SharePoint : How to make a drop down read-only in an InfoPath form?

Add a rule on the drop down of type 'Formatting'.

Add some condition which would be always true.

e.g. 'Expression' = true()

Choose to disable the control

Monday, 26 March 2018

How to export SharePoint survey into an Excel file with date fields?

Go to the survey actions and click on Export to spreadsheet.

If you want to include any of the missing fields then open the 'Overview' list view of the survey in SharePoint designer. Add the missing fields like below in the advanced mode.

<ViewFields>
<FieldRef Name="Created"/>
<FieldRef Name="Author"/>
<FieldRef Name="Sample_x0020_Text"/>
</ViewFields>

Monday, 19 March 2018

How to get item id using GUID in SharePoint 2013 designer workflow?

'Create item in' action in SharePoint 2013 designer workflow returns GUID instead of item ID.

Use the lookup like below to fetch the ID of the newly created item.