JavaScript API read/write

A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to many relationship between an item in one list to multiple items in another list…. I had to get creative.

javascriptapi-sharepoint-splash

This is an example where I wanted a person to check one or more values in a list item, using a lookup column. Then, each of the selected items they chose had a value. I needed to get the sum of those values, perhaps have some multipliers on some of them, and get a weight value. It was not happening for me in SharePoint Designer or using calculated columns, but this was something that was simple using JavaScript or C#. What was I to do…  OK well here is what I did, and it worked out pretty well actually…

 

javascriptapi01
First, a custom list called “listings” was made. The purpose of the list was to keep track of real estate listings. We put certain criteria that were important here, such as details of the listing. Some things are one-to-one relationship, so those would be columns in this list such as the price, the square feet, etc. But…

 

javascriptapi02
There are some criteria that there is a one-to-many relationship, where one listing might have several of the features we would want. For this, a separate custom list called “features” was made. This had a list of features, and each feature had a weight assigned which was a numerical value. The higher the weight, the more important it is to us.

 

javascriptapi03
Back to the “listings” custom list. I added a lookup column called “features” with “allow multiple values” enabled. Initially I had also checked the “add a column to show each of these additional fields” also enabled for the “feature weight” number value. The problem here was that…
1) this “features:feature weight” column would show up in the SharePoint list and views, but was not accessible for use in calculated columns.
2) also, this same column was not accessible from within SharePoint Designer 2013 either.

The changes I made to begin addressing this was to just have the “features” column still there, without bothering to have the “feature weight” additional column. Then, a “Score” column with type set as “number” was added. This would be used later on…

 

javascriptapi04
The first part of the solution was to put some .js and .txt files into the Site Assets document library. To be prepared, I put jQuery and MicrosoftAjax .js files, and a new empty updateWeights.txt file.

*One place you can find the MicrosoftAjax.js file is at: http://ajax.aspnetcdn.com/ajax/4.0/1/MicrosoftAjax.js
*You can get jQuery at: https://jquery.com/download/

 

javascriptapi05
On the “listings” custom view page, I added a Content Editor web part to the page.

 

javascriptapi06
I set the Content Editor properties so that the “Content Link” option was set to point to the updateWeights.txt file.

 

javascriptapi07
The updateWeights.txt file starts out with references fo the jQuery and MicrosoftAjax files.

 

javascriptapi08
Then another JavaScript section that has all of the other code. This may not be the best way to accomplish what is probably a simple task, but what I did was just create two JSON arrays to dump the data from “features” and “listings” into. Then let’s say one listing had four or five features tagged in that records. The weights for each of the tagged features would be added up, then that sum value would be sent back up to update the listing record…

 

javascriptapi09
There is a function called GetItemTypeForListName, but I actually didn’t use this because it was giving errors later on when trying to use. For me, I just ended up finding and hard-coding the item type later on.

 

javascriptapi10
I made the first function called getFeatures(), which uses ajax to pull down the list of features into a JSONfeatures JSON array. Then once done, this calls the next function called getListings()…

 

javascriptapi11
getListings() does the same thing as getFeatures(), but to the JSONlistings JSON array. Once done, this calls a calculateWeights() function…

 

javascriptapi12
This function loops through each of the listings, and for each one, it loops through the list of tagged features. For each tagged feature, it loops through the list of features to find the weight for that specific feature. This is added to a temporary variable that sums up how much overall weight the listing has. Once the features are all looped through, the current listing item is updated by a call to a updateListItem() function. Then the process is repeated for the next listing.

 

javascriptapi13
The updateListItem() function is sent the JSON item ID, the website URL, the title of the list, the list item ID to be updated, the “Score” value to merge, and the names of which functions to call upon success or failure. This function puts it all together, then sends it to another function called updateJson() which gets the JSON item ID, the endpoint URI which is basically the URL reformatted to be sent to the API, the payload which is basically the list of properties to be updated, and the success/failure functions again.

 

javascriptapi14
The end result is that the listings view gets the Score column updated with the sum of all features that are tagged from within that listing’s edit page. Phew!

 

I put all my related files that I used in this example up on my newly setup GitHub repository, in case it might be useful for anyone else (and to make it easier for myself to use in the future). The URL for that, which is where you can get the full updateWeights.txt example file, is at:
https://github.com/gregbesso/SharePoint-JavaScriptAPI-Example

 

If anyone finds this useful, let me know. Would like to see if other people run into similar situations like I did. Or if there was an easier way to accomplish this same thing let me know also 😛

 

Also, for the full MSDN reference of the JavaScript API for SharePoint 2013, check out this link:
https://msdn.microsoft.com/en-us/library/office/jj193034.aspx