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.
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…
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.
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…
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/
On the “listings” custom view page, I added a Content Editor web part to the page.
I set the Content Editor properties so that the “Content Link” option was set to point to the updateWeights.txt file.
The updateWeights.txt file starts out with references fo the jQuery and MicrosoftAjax files.
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.
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()…
getListings() does the same thing as getFeatures(), but to the JSONlistings JSON array. Once done, this calls a calculateWeights() function…
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.
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.
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:
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 😛