Trouble Installing SharePoint 2013

I had a quick SharePoint job to do today, but I didn’t have a SharePoint developer environment set up.

So, I figured I would just spin up a server in Azure and set one up. I ended up with a day of grief!

Firstly I tried installing SP2013 on Windows 2016, but ran into all sorts of problems with the prerequisite installer.

So I gave up on that and tried Windows Server 2012 R2. But, I made the mistake of installing Visual Studio 2015 before SharePoint. That installs .NET framework 4.6 and then SharePoint reports that .NET 4.5 is not there!

The exact error message is “Setup was unable to proceed due to the following errors: This product requires Microsoft .Net Framework 4.5”

problem1

So I tried again with a clean install and was surprised when I got the same error.

It turns out that the Window Server 2012 R2 machine provisioned by Azure already has .NET 4.6 installed. But here’s the kicker – you can’t actually see it installed anywhere.

You have to know that in was installed with KB3012467. WTF!

solved

Anyway – once you uninstall that KB everything works fine.

Select2 JQuery Plugin used in a SharePoint form – JSLink

Continuing on the theme of JSLink in SharePoint 2013, I’ve been trying to get a straight “autocomplete” or “typeahead” plug-in working.  The requirements were pretty straighforward:

1.  Use in place of a Lookup field in SharePoint (multiple values)

2.  Use Ajax to query the lookup source list after the user starts typing at least 3 characters.

Now you think that would be fairly easy to do give my two previous walk-throughs on JSLink:

https://kogzee.wordpress.com/2014/11/11/cascading-dropdowns-on-large-lists-in-sharepoint-using-jslink/

and

https://kogzee.wordpress.com/2014/11/12/cascading-dropdowns-on-large-lists-in-sharepoint-using-jslink-part-2/

But I had all kinds of problems getting this working.  I started with the Chosen JQuery plug in that I used in those articles above, but it didn’t have an easy way to give the AJAX typeahead functionality.  I tried mixing it with JQuery UI autocomplete but it lead me down a few dead-end paths.

 

In the end I got it working with the Select2 plug in.  The example code is shown below.  Sorry for the badly written JavaScript.  I wrote this for a colleague just to get him on the right path and he will tidy it up from here :-). I’ve probably got a few redundant lines in there.

The important part is the “setValues” function.  The loaded flag prevents this from running multiple times on page load.  I’m not sure why this happens sometimes.

 

Another important thing about the Select2 plug in is that it uses an array of objects that look like this:

[{id:”id”, text:”text”}]

where my data coming back from SharePoint looks like this:

[{ID:”ID”, Title:”Title”}

There is some way to do this mapping in a tidy way, but I couldn’t quite get it working.

In the end I do the mapping with brute force, using code like this:

return (e.id ? e.id : e.ID);
return (e.text ? e.text : e.Title);
var editApplicationName;
var editApplicationNameLabels;
var loaded = false;

(function () {

//link required plugins from CDN
(window.jQuery || document.write('<script src="//ajax.aspnetcdn.com/ajax/jquery/jquery-1.10.2.min.js"><\/script>'));
document.write('<script src="https://cdnjs.cloudflare.com/ajax/libs/select2/3.5.2/select2.js"><\/script>');
document.write('<link href="https://cdnjs.cloudflare.com/ajax/libs/select2/3.5.2/select2.css" rel="stylesheet" type="text\/css"><\/script>');

// Create an object that has the context information about the field whose rendering we want to change
var newContext = {};
newContext.Templates = {};
newContext.Templates.Fields = {
// Apply the new rendering
"ApplicationName": {
"NewForm": ApplicationNameFieldTemplate,
"EditForm": ApplicationNameFieldTemplate
},

}; //end field rendering properties

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(newContext);

_spBodyOnLoadFunctionNames.push("setValues");
})();

// This function provides the rendering logic for the ApplicationName field.

function ApplicationNameFieldTemplate(ctx) {

var formCtx = SPClientTemplates.Utility.GetFormContextForCurrentField(ctx);

var tempApplicationName = ctx.CurrentItem["ApplicationName"].toString().split(";#");
editApplicationName = [];
editApplicationNameLabels = [];
for (var i = 0; i < tempApplicationName.length; i++) {
(i % 2 == 0 ? editApplicationName : editApplicationNameLabels).push(tempApplicationName[i]);
}

// Register a callback just before submit. We need to build up the string like SharePoint expects
//ID1;#Value1;#ID2;#Value2;#ID2;#Value3
formCtx.registerGetValueCallback(formCtx.fieldName, function () {
var ApplicationNameString = "";
var items = $("#ApplicationNameSelectField").select2("val");
$.each(items, function( index, value ) {
//alert( index + ": " + value );
ApplicationNameString += value + ';#' + 'bla' + ';#'; //only the id's are important for a lookup field
});
//strip off the last ;#
var len = ApplicationNameString.length;
if (ApplicationNameString.substr(len-2,2) == ";#") {
ApplicationNameString = ApplicationNameString.substring(0,len-2);
}
return ApplicationNameString;
});

var fieldHtml = '<input type="hidden"';
fieldHtml += 'id="ApplicationNameSelectField">';
fieldHtml += '</input>  ';

return fieldHtml;
}

function setValues() {
if (loaded == false) {

var selectedValues = new Array();
var selectedKeys = new Array();
$.each(editApplicationName, function( index, value ) {
selectedKeys.push(value);
selectedValues.push({id: value, text: editApplicationNameLabels[index]});
});

$("#ApplicationNameSelectField").select2({
placeholder: 'Search for an application...',
id: function(e) {
return (e.id ? e.id : e.ID);
}, //really important! you cannot select an item unless this is set.
multiple: true,
width: '400px',
minimumInputLength: 3,
ajax: {
url: function (params) {
return "http://YOUR_SERVER_URL/_api/web/lists/GetByTitle('Applications')/items?$select=ID,Title&$top=100&$orderby=Title&$filter=startswith(Title,'" + params + "')"
},
dataType: 'json',
transport: function(params){
params.beforeSend = function(request){
request.setRequestHeader("accept", "application/json;odata=verbose;charset=utf-8");
};
return $.ajax(params);
},
quietMillis: 100,

results: function(data, page) {
return {
results: data.d.results
};
}

},
formatResult: function(item) {
return (item.text ? item.text : item.Title);
//return "<div class='select2-user-result'>" + item.Title + "</div>";
},

formatSelection: function(item) {
return (item.text ? item.text : item.Title);
},
initSelection: function (element, callback) {
callback($.map(element.val().split(','), function (id) {
//get the text from the id
var result = $.grep(selectedValues, function(e){ return e.id == id; });
return { id: id, text: result[0].text };
}));
}
});
if (selectedKeys[0] > ""){
$('#ApplicationNameSelectField').select2('val', selectedKeys, true);
}

loaded = true;
}
}

ASP.NET Now Open Source and Cross Platform

Is this a turning point for .NET development?  I hope so.  This means that Microsoft will now fully facilitate running ASP.NET on other operating systems such as Linux and Mac.   I hope in 5 years time that ASP.NET is widely adopted on these other platforms.  I know I have really enjoyed working with ASP.NET in last few years, and would like to see it as a real option vs PHP etc.  It has come a long way from the days of ASPX and ASMX!

http://www.pcworld.com/article/2846892/microsoft-open-sources-net-server-stack.html

Cascading Dropdowns on Large Lists in SharePoint using JSLink Part 2

In Part 1, we saw how to use the JSLink functionality of SharePoint to create nice Cascading Lookups (dropdowns) with JQuery and the Chosen plugin.

We are going to go one step further here now and have work with a large list of cities. (Over 1.4 million entries).

If you tried to use this list with out of the box functionality in SharePoint you won’t get very far. Even third party Field Controls don’t work very well with large lists.

JSLink, however, puts the power back into the hands of the SharePoint developer.

Start by completing Part 1 of the tutorial.

Then we are going to import the Cities table from this Excel file, as we did with the other tables in Part 1.

https://kogzee.files.wordpress.com/2014/11/worldcities2.xlsx

It may appear like Excel is hung, but if you go to the Site Contents list you should see the size of the Cites list grow as you refresh the page:

Cities1

 

Eventually (may take quite some time) it will look like this:

Cities2

And you should be able to open the list. It should look something like this:

Cities3

 

Now we are going to try and add a City Lookup to our Customers list that we created in Part 1.

Then click the Customers icon to configure:

Customers2

Click the List tab

Customers1

 

Then click “Create Column”

CreateColumn

Configure as shown:

CityColumn

 

Now create a new item and you will see our first problem:

Problem

By default SharePoint will only allow queries to return 5000 items. We can increase this if we need to, but one we will options by choosing some Countries, we might be below the limit. So we’ll will start by modifiying our JSLink script to filter the results and use the Chosen plugin.

Here is the completed JavaScript file:

http://1drv.ms/1yx2qWa

So if you link this to the NewForm of the Customers list as described in Part 1, and test, you will get an error in the JavaScript.

“The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”

I used Chrome’s excellent tools to test and debug.

This is an example of one of an api call that is generated by the script.  You can actually test it by pasting it into the address bar of your browser:

http://crspoint01/sites/sandbox/_api/web/lists/GetByTitle(‘Cities’)/items?$select=ID,Title&$top=5000&$orderby=Title&$filter=(CountryName%20eq%20%27Uruguay%27)

You should see the error in the returned XML.

Notice that this is selecting cities in Uruguay. Now this query should not exceed to 5000 item limit currently set, but we get this error anyway:

The reason for this is not immediately clear. But if we have not created an index on the list, then ANY query will cause this error.

So lets see what happens if we try and create an index on the CountryName field on the City list:

If you go to the List Settings of the Cities list you will see a link for Indexed Columns:

IndexedColumns1

IndexedColumns2

Try and create an index on CountryName

IndexedColumns3 

You will probably get an error. This is because the indexing itself requires being able to read the entire list.

So we need to temporarily increase or list view threshold in order to create the index.

We will increase if to 1500000.

See this article for more information:

http://davecoleman146.com/2013/07/17/sharepoint-2013-changing-the-list-view-limit/

After increasing the limit, try and create the index again.

It should be successful, and now you can go back and set the threshold back to something a bit more reasonable. I am going to set mine to 20,000.

And now the form should be working as expected:

It performs quite well considering the amount if items in the City list.

Result

I hope you found this useful.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Cascading Dropdowns on Large Lists in SharePoint using JSLink

A few years ago, I wrote how I was surprised that SharePoint did not provide some sort of cascading lookup functionality out of the box. Years later and there is still nothing out of the box and easy to use. But in the modern era where JavaScript has become the king of the awesome web interface, SharePoint has a feature that lets you hook into this power. It is called JSLink or Client-Side Rendering.

There are plenty of good articles and examples. I found this set of tutorials particularly useful:

So the great thing about this is that it opens up the possibility to customize many elements of the SharePoint user interface using the plethora of free JavaScript plug ins available today, such as JQuery, Bootstrap, Leaflet etc.

The built in lookups within SharePoint are pretty much dysfunctional, especially on large lists. In the past if you want a custom third solution you would buy or write custom Field Controls, such as Kwizcom’s Cascading Lookup:
http://www.kwizcom.com/sharepoint-add-ons/sharepoint-cascading-lookup-plus/overview/

The problem with solutions like this is that they are “server side”. That is you actually need to install a DLL on the SharePoint server. So they can’t be used in hosted scenarios such as Office 365 (SharePoint Online).

Also Kwizcom’s solution is pretty much unusable on large lists and their support in such circumstances will most likely leave you frustrated.

A better solution would be to use a client side approach – JavaScript and JSLink!

So, what I am going to do here is outline a solution that will give you an awesome lookup UI in SharePoint that will:

• Work on large lists (>1,000,000 entries)
• Work on premises as well as on hosted SharePoint
• Uses JSLink and the following JavaScript plugins: JQuery and Chosen

First we need some large lists to work with. You can download a list of the worlds cities here:

http://download.geonames.org/export/dump/cities1000.zip

Countries:

http://download.geonames.org/export/dump/countryInfo.txt

Here is a copy of it in Excel Format:

https://kogzee.files.wordpress.com/2014/11/worldcities2.xlsx

So the first step is to import the lists from this spreadsheet into SharePoint…

You do this by “Adding the Import Spreadsheet app”

AddAnApp

 

ImportSpreadsheet

We are going to start by importing the Continents table from the spreadsheet listed above:

When you click Import, Excel should appear (if you have it installed). You should see three “tables” that are available to import. Start with the Continents table:

Continents

Click Import and you should have a list that looks like this:

Continents2

Repeat the process for the Countries table:

There is also a Cities table in this file. This one will take some time as there are over 1.4 million entries in it! But don’t worry about it for now. I will be dealing with lookups on large lists such as this one in Part 2 of this tutorial.

So now we are ready to create a new list, where we implement the cascading lookups. We are going to run into a few problems on the way, which I will address as we get to them.

So lets go ahead and create a list just using out-of-the-box functionality and see what happens:

So we start by adding a Custom List app:

CustomList

Lets call it Customers.

Then click the Customers icon to configure:

Click the List tab

Customers1

Then click “Create Column”

CreateColumn

Configure as shown:

CreateColumnContinent

When finished, click “Create Column” again to add a Country lookup as shown. This time we will make allow multiple values:

CreateColumnCountry

Now create a new item and view the result:

NewItem1

A couple of problems here: There is no way to filter the Country options based on the Continent choice. Also the Multiselect UI leaves a lot to be desired.

You could use something link SPServices to get cascading lookups, but I’m not sure if you can use other JQuery plugins, such as Chosen with it.  At any rate you could certainly combine it with the JSLink approach listed below.

There are some fancy multiple choice select JavaScript plugins availalble. I’ve been happy with Chosen, which you can find here: http://harvesthq.github.io/chosen/

With JSLink, we can override the templates used by any form element in SharePoint. For more information see this set of tutorials  that I mentioned previously.  The JSLink that I am going to use looks like this.

We need to upload this JS file to a document library within SharePoint, usually the Master Page gallery. But just to be different I am going to upload it to the Style Library:

StyleLibrary

Now the next step is to link or NewForm and EditForm to this JSLink file.

So go to the Customers list and click New Item:

NewItem2

Then click the Cog at the top right and choose “Edit Page”

EditPage

Then select the Customers Web Part and choose Edit Web Part

EditWebPart

And set the JSLink property to point to the location of your uploaded JSLInk file:

~sitecollection/Style Library/CascadingCountryLookupsPart1.js

JSLink

Click OK and Stop Editing the page.
If the moons are aligned, your page should now look like this…Notice how nice the multiselect UX is now! Filtering built in even!

Chosen1

Lets go ahead and select a few items and click Save:

Chosen2And the result should look like this:

Results

Now you go to edit this item you will still see the default form:

Default

But if you edit this page and set the JSLink of the Web Part as before to the same file, it should come alive:

~sitecollection/Style Library/CascadingCountryLookupsPart1.js

Use this technique to bring your SharePoint forms to life! But for those interested in dealing with the issues of forms with lookups to large lists, there’s more! In Part 2 I will create a new City field that is also multiselect. There are 1.4 Million cities in the excel file mentioned above, so this will be a real challenge.