SharePoint Lists-Smarter with JavaScript

SharePoint lists are great for tracking data-easy to create, export to Excel, develop against using JSOM.  Problem is when you have a list sometimes you have documents that are associated with the list item.  There are attachments to the list but better to use a document library right?  Maybe create a folder in the document library for each new list item created and tag the folder for the specific folder associating the item to the folder.

Here is where some simple JavaScript and the SharePoint JSOM come in to save the day…

Step 1: Create your SharePoint list, add your fields, etc.

Step 2: Create a new item, when the newform.aspx opens simply select Edit Page from settings.  Settings-Edit page.  Now you can place a content editor web part referencing your script file.

Screenshot (Standard NewForm.aspx un-customized)

SPLISTFORM

Also, add a document library web part to display the folders we are going to create from the script and filter for your specific list item using the web part connection from the list item web part to the document library web part.

Add any other web parts to the page, calendar, tasks lists etc.  This provides a simple all up view for related content tagged to the list item.

Add a content editor web-part or script editor web part referencing your script file (I typically organize conveniently in the site collections style library)

Example script:
//ajax.aspnetcdn.com/ajax/jQuery/jquery-1.7.2.min.js

//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js

var matterName;
var matterNum;
var folderName;
var collListItem;
var folderUrl ;
var folderpath;

//Execute when the user saves the item create a folder in our document library and tag the folder with relevant data from our list item in order to associate the two
function PreSaveItem()
{

//grab some fields off the newform.aspx to use for tagging the folder we create
matterNum = $(‘input[title=”MatterNum”]’).val();
matterName = $(‘input[title=”Name”]’).val();

//Create the folder

folderName = matterName;
folderUrl = “[SiteUrl]” + “/” + folderName;

createFolder(folderUrl, folderName, matterNum);

return true;
}
function createFolder(folderUrl, fname, matterNumber) {

var clientContext;
var oWebsite;
var oList;
var itemCreateInfo;

clientContext = new SP.ClientContext.get_current();
oWebsite = clientContext.get_web();
oList = oWebsite.get_lists().getByTitle(“Documents”);

itemCreateInfo = new SP.ListItemCreationInformation();
itemCreateInfo.set_underlyingObjectType(SP.FileSystemObjectType.folder);
itemCreateInfo.set_leafName(folderName);
//I am using a specific content type inherited from the folder type
this.oListItem = oList.addItem(itemCreateInfo);
this.oListItem.set_item(“ContentTypeId”, “0x0120003C4F41AA23709B458E3DA2EEFAA5DCAA”);
this.oListItem.set_item(“Title”, fname);
this.oListItem.set_item(“MatterNum”, matterNumber);
this.oListItem.update();
clientContext.load(this.oListItem);
clientContext.executeQueryAsync(
Function.createDelegate(this, successHandler),
Function.createDelegate(this, errorHandler)
);
}
function successHandler() {

//Yeah have a new folder

}

function errorHandler(sender, args) {
alert(‘Record Create failed. ‘ + args.get_message() + ‘\n’ + args.get_stackTrace());
}

//TO DO-This part for next time-build out a complete folder structure based on a pre-built document libary structure
function retrieveFolderStructure() {
var _clientContext1;
_clientContext1 = SP.ClientContext.get_current();
var oList = _clientContext1.get_web().get_lists().getByTitle(‘Matter Documents’);

var camlQuery = new SP.CamlQuery();
camlQuery.set_viewXml(“500”);

this.collListItem = oList.getItems(camlQuery);

_clientContext1.load(collListItem);

_clientContext1.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded2), Function.createDelegate(this, this.onQueryFailed2));

}

 

Summary:

Make your SharePoint lists a little smarter.  Why not?  Of course all this works on SharePoint 2013 and Office 365.  Also can use on editform.aspx, the script to access the form fields slightly different but doable np.

Bonus material:

Add a calculated column to your SharePoint list that links users to the specific folder:

=”<a class=’docs’ title=’Documents’ href=”&”‘”&”/[Site]/Shared Documents/Forms/AllItems.aspx?RootFolder=/[Site]/Shared Documents/”&Name&”‘”&”>Documents</a>”

*Set your column format to “number” so displays HTML

 

SharePoint 2013-Create User Profiles from BCS External List using PowerShell

Overview:
We have an internal identity management system that stores and manages identities and custom properties for all employees.  As part of a recent project designed a solution to populate our SharePoint user profile store using information stored about each user in our custom indentity management system.

Alternatives:
We evaluated the Active Directory Import and User Profile Synchronization as well.  The Active Directory Import supports LDAP sources for profiles and although can be configured to import from BCS external list does not actually create user profiles based on that data, rather can populate additional properties for AD user profiles that already exist.  In our case those user profiles do not exist since we are not able to use AD import or profile sync in this environment.

Solution steps::

Step 1:
Configure SharePoint 2013 Secure Store Service and Business Connectivity Services

Step 2:
Configure an external content type and external list in SharePoint Designer 2013 with full CRUD operations enabled bound to a custom database table in SQL where identities are stored.

Note: The external list provides full CRUD operations on the external list to the SQL database table.  This is a key additional feature over a standard SharePoint list which was useful in our case having the option of querying and perform updates to the external list directly from our custom admin form via JavaScript and the SharePoint REST API.

This could be interesting for other use cases as well where updating a custom SQL database table from a SharePoint list is usefull i.e. AngularJS or REACT UI user-interface integrated with SharePoint list data via REST.  Exploring more on this front soon.

Step 3:
Develop PowerShell script to query the SharePoint external list, for each item check for existing profiles in SharePoint, and if none exist create a new profle populated with the data in the external list item for each user.

Example PowerShell script:

#Rod Stagg

#Create SharePoint User Profiles based on data in External List (BCS)

[CmdletBinding()]
Param(
[Parameter(Mandatory=$True)]
[System.String]$siteUrl,
[Parameter(Mandatory=$True)]
[System.String]$listSiteUrl,
[Parameter(Mandatory=$True)]
[System.String]$domain,
[Parameter(Mandatory=$True)]
[System.String]$user
)

Remove-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
Add-PSSnapin Microsoft.SharePoint.Powershell

$claimsformat = “i:0#.w|”;
$accountName = “”;
$fullname = “”;
$lastfirst = “”;
$firstname = “”;
$lastname = “”;

$Web = Get-SPWeb $listSiteUrl
$SourceList = $Web.Lists[“External List”]
$SourceItems = $SourceList.Items | where {$_[‘networkid’] -ne “”}
$site = Get-SPSite $siteUrl
$context = Get-SPServiceContext($site)
$profilemanager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
$SourceItems | ForEach-Object {
# Assumes account in domain\account format and networkid field is the unique identifier on external list
$accountName = $claimsformat + $domain + “\” + $_[‘networkid’]
# Assumes username stored as lastname, firstname on external list so split
$lastfirst = $_[‘lastfirst’];
$firstname = $lastfirst -split “,”
Write-Host $firstname[1];
Write-Host $firstname[0];
Write-Host $accountName;
# If user profile doesn’t exist create new one
if ($profilemanager.UserExists($accountName)) {
$userProfile = $profilemanager.GetUserProfile($accountName)
}
else {
$userProfile = $profilemanager.CreateUserProfile($accountName)
}

# Update any necessary user profile properties
$userProfile[“FirstName”].Value = $firstname[1]
$userProfile[“LastName”].Value = $firstname[0]
# Commit changes
$userProfile.Commit()

}

Additional elements of entire solution:

  • SSIS population of SQL database table that is bound to the SharePoint 2013 external list.
  • Windows schedule task to execute PowerShell script on
  • JavaScript to update the external list from our custom form.