Navneet Chanalia's SharePoint Blog

Tuesday, 29 January 2019

SP Services: How to populate a dropdown box with unique values from a SharePoint list column?

</script>

<body>
<select class="opselector" onchange="getOP();"></select>

</body>

Tuesday, 4 December 2018

How to redirect the current user to the existing item from the new-item page?

Add the following to a web-part on the new-item page:

<script type="text/javascript" language="javascript">
var masterDataList;

ExecuteOrDelayUntilScriptLoaded(getWebUserData, "sp.js");

function getWebUserData() {

clientContext = new SP.ClientContext.get_current();
web = clientContext.get_web();
currentUser = web.get_currentUser();
currentUser.retrieve();
clientContext.load(web);
clientContext.executeQueryAsync(onSuccessMethodGetUser, onFailureGetUser);
}

function onSuccessMethodGetUser(sender, args) {
var userObject = web.get_currentUser();
var userName = userObject.get_title(); //get_email();
masterDataList= web.get_lists().getByTitle('List Name');
var camlQuery = new SP.CamlQuery();
var query = "<View><Query><Where><Contains><FieldRef Name='Resource_x0020_Email' /><Value Type='Text'>" + userName + "</Value></Contains></Where></Query></View>";
camlQuery.set_viewXml(query);
masterDataListItem= masterDataList.getItems(camlQuery);
clientContext.load(masterDataListItem);
clientContext.executeQueryAsync(onMasterDataListItemsLoadSucceeded,onMasterDataListItemsLoadFailed);
}

function onFailureGetUser(sender, args)
{
//Do nothing
}

function onMasterDataListItemsLoadSucceeded(sender,args)
{
var itemID;
var itemsEnumerator=masterDataListItem.getEnumerator();
//Fetch latest item
while(itemsEnumerator.moveNext())
{
var currentItem=itemsEnumerator.get_current();
itemID=currentItem.get_item('ID');
}

if(itemID!=undefined)

{
window.location.href="https://server/sites/site_name/Lists/list_name/Item/editifs.aspx?ID="+itemID+"";
}
}

function onMasterDataListItemsLoadFailed(sender,args)
{
//Do nothing
}

</script>

Friday, 16 November 2018

SharePoint Excel Service : How to resolve "the workbook cannot be opened" error?

Run following commands for each web application that is associated with Excel Services:

$w = Get-SPWebApplication -identity http://<WebApplication>
$w.GrantAccessToProcessIdentity("<Domain>\<Username>")

In future, for the additional content databases, you must rerun these cmdlets to ensure that Excel Services has access to the new databases.

Source: https://docs.microsoft.com/en-us/sharepoint/administration/configure-excel-services

Thursday, 15 November 2018

SharePoint Application Page: How to export Gridview data to Excel file using ActiveX?

Note: This feature works only in IE. Also, you would need to enable ActiveX in IE settings and add the URL in trusted sites.

Prerequisite 1: IE Settings -> Internet Options -> Security -> Trusted Sites -> Custom Levels -> ActiveX Settings -> Enable/Prompt for ActiveX not marked as safe.

Prerequisite 2: IE Settings -> Internet Options -> Security -> Trusted Sites -> Add URL

Javascript:

<script language="javascript" type="text/javascript">
function exportTable() {
var x = document.getElementById('table_id').rows;
var xls = new ActiveXObject("Excel.Application");
xls.visible = true
xls.Workbooks.Add
for (i = 0; i < x.length; i++) {
var y = x[i].cells;
for (j = 0; j < y.length; j++) {
xls.cells(i + 1, j + 1).value = y[j].innerText;
}
}
}
</script>

HTML

<input type='button' value='Export To Excel' onclick="exportTable();"/>

Thursday, 11 October 2018

SPQuery: How to filter based on modified date?

<Query>
<Where>
<Leq>
<FieldRef Name="DateColumn" />
<Value Type="DateTime">
<Today OffsetDays="-4" /> // Modified in last 4 days
</Value>
</Leq>
</Where>
</Query>

Monday, 20 August 2018

How to lock a w2ui grid row and column?

To lock a column:

w2ui['grid'].columns[col_index].editable=false;

To lock a row:

w2ui['grid'].records[row_index].w2ui.editable=false;

-or-

w2ui['grid'].records[w2ui['grid'].get($(this).attr("recid"),true)].w2ui.editable=false;

-or-

records: [
recid: $(this).attr("ows_ID"),
'w2ui':{
style: {},
editable:($(this).attr("ows_columnName")=="Some Value")?false:true
}
]

To lock the full grid:

w2ui.grid.lock('Loading...', true); //second parameter is for the optional spinner

To lock a particular cell:

There is no out-of-the-box method to support this. However, you can target some of the HTML attributes of the cell to achieve this. You will have to look deep into w2ui js files to understand the working of editable cells.

Something like this may work:

$(window).load(function (){
disableCells ();
}

function disableCells()
{
var rowArr=w2ui['grid'].records;

$(rowArr).each(function() {
if($(this).attr("Column Name")=="SomeValue")
{
//column index = 5
//column name = Column5
$("#"+"grid_grid_data_"+w2ui['grid'].get($(this).attr("recid"),true)+"_5").html("<span>"+$(this).attr("Column5")+"<span>");
}
});
}

If windows.load fails to lock the cells then use the following:

$(window).on('load', function () {
w2alert('Welcome message/instruction.').done(function () {
disableCells();
});
});

Tuesday, 14 August 2018

[Solved] SharePoint designer workflow lookup for number field is returning 0 for the blank fields.

1. One of the ways is to have a workflow variable and assign it to the lookup value. And then comparing it to 0.

2. Other way is to create a calculated text field in the list and use it instead of the number field in the workflow lookup.

Calculated text field =IF([ColumnName]=0,"-",TEXT([ColumnName],0))

=TEXT([Column],<format>) is used to avoid unwanted zeros after the decimal point. Without this, the final value for the calculated text field would look something like 123.000000000

<format> is a number format in text format. e.g. 0; 0.0; 0.00; etc.