Power User Toolbox: JavaScript for SharePoint – Pt5

When I first installed SharePoint, I was amazed with the many ways I could access data (your mileage may vary depending on licensing options): user interface, web services, SharePoint Services API, RSS, email alerts, workflows, Business Data Catalog, Excel Services, Stssync protocol (Outlook Integration), Stsadm command line, T-SQL (unsupported), and RPC/URL protocols (owssvr.dll). Well, add to that list (and your Power User Toolbox) the SharePoint and Office Live JavaScript API.

Darren Johnstone created a JavaScript library that talks to SharePoint using the native web services. Here’s an example that pulls the current user’s name and displays it:

 

<script type="text/javascript" src="/PaulGrenier/js/SPAPI/SPAPI_Core.js"></script>
<script type="text/javascript" src="/PaulGrenier/js/SPAPI/SPAPI_Lists.js"></script>
<div id="username_div"></div>
<script type="text/javascript">
_spBodyOnLoadFunctionNames.push('showUserName');
function showUserName(){
  var userName = getCurrentUserName();
  if (userName != null){
    document.getElementById('username_div').innerHTML = 'Welcome: ' + userName;
  }else{
  document.getElementById('username_div').innerHTML = 'User name not found';
  }
}
function getCurrentUserName(){
  var lists = new SPAPI_Lists('https://endusersharepoint.securespsites.com/PaulGrenier');
  var items = lists.getListItems(
  '2D3F492B-C6E9-4608-82FA-DCAE42B0C57F', //other lists can be called by name
  '',
  '<Query><Where><Eq><FieldRef Name="ID"/><Value Type="Counter">' +
   _spUserId + '</Value></Eq></Where></Query>', // query
  '',
  1, // rowLimit
  '' // queryOptions
  );
  if (items.status == 200){
    var rows = items.responseXML.getElementsByTagName('z:row');
    if (rows.length == 1){
      return rows[0].getAttribute('ows_Title'); //field name of data to return
    }else{
    return null;
    }
  }else{
  return null;
  }
}
</script>

Just like with jQuery, we have to reference some scripts that we uploaded to the server. After that, the code has a little div tag to display the returned information–but that’s the last step, no reading ahead! You’ll miss the cool parts.

The first line of JavaScript uses a built-in SharePoint function you probably noticed in Solution 1 of part 4, _spBodyOnLoadFunctionNames.push. That just tells SharePoint that we want to run this script when the page loads. That calls the first function we wrote, showUserName.

showUserName calls the last function, getCurrentUserName, and stores the returned value in a variable called userName. Within the last function, we have to call Darren’s library functions: SPAPI_Lists and getListItems to establish the list we’re talking to and to grab the returned XML.

With Darren’s libraries, we can normally use the list name (very cool) instead of the Globally Unique Identifier (GUID) as a parameter in the SPAPI_Lists function but I was not able to make that work in this situation. I have an easy way to find the GUID for the User Information List that I’ll show you later.

In the middle of getListItems, we use another built-in SharePoint function, _spUserId, to filter the returned rows by the current user’s ID. To understand the next few steps, it helps to see what the XML the web service returns. To see that, we can use the URL protocol.

This trick works on any site and with most permission levels. Click the People and Groups link and then click All People. Now view the page’s source and search for owssvr.dll. Since the User Information List isn’t a normal SharePoint list, the page uses the URL protocol to retrieve the data.

Notice the value of the List parameter, that’s the User Information List’s GUID. Copy that entire URL after o:webquerysourcehref and paste it into your browser’s address bar. If you don’t see many records, remove the &View=… portion of the URL and try again. This displays an XML file with helpful information. Here’s a sample returned row:

See the z:row element? The script above uses getElementsByTagName(‘z:row’) to get that data. In the next step, we extract the attribute ows_Title but we could easily change that to pull ows_EMail or ows_Name instead.

In the last step, after getting a value for the userName variable, we simply find the empty div we made and insert the value as innerHTML.

EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part VI)

Two

Overview

In this article, we’re going to be covering the last 4 functions in the "Text and Data" set of formulas for SharePoint.

The four functions we’ll be discussing are:

DOLLAR – Converts a given number into text with it represented in a "Currency Format" and having it’s decimals rounded to a specified place. The format used in this function to convert the number to the appropriate text convention is "$#,##0.00" (this text format is automatically applied – so you don’t have to specify it directly)

USDOLLAR – Same as the "DOLLAR" function in that it takes a given number and converts it to text (into a "Currency Format" with the decimals rounded), but it differs in that it will use a standard "U.S. Currency" format instead of what your local computer may be set to (international settings).

FIXED – Also similar in how both the "DOLLAR" and "USDOLLAR" functions format a number into text, but differs in that the "FIXED" function simply formats the text result into a decimal format using a period and commas (not necessarily currency – this will just represent the text version of a number, whereas the "DOLLAR" and "USDOLLAR" will pre-pend on a "Currency" symbol to the result – i.e. "$").

VALUE – Converts a text string that represents a number into a number (the first three described above all take a number and convert it to text – this one works the other direction taking text and converting it to a number).

Example of these functions would be:

Text: "12345"
Formula: DOLLAR("12345")
Result: $12,345

The above will return a currency-based result displayed in the currency-specific regional settings you have applied to your local computer (which may be overridden by whatever regional settings applied in your instance of SharePoint).

Text: "12345"
Formula: USDOLLAR("12345")
Result: $12,345

The above will return a currency-based result that uses the standard U.S. Currency convention.

Text: "12345"
Formula: FIXED("12345")
Result: 12,345

The above will return a numerical result in a 3-character-grouped-comma-separated-2-decimal place format (that was a mouthful).

Text: 12345
Formula: VALUE(12345)
Result: 12,345

Although appearing the same as the original input (text), the outputted result of this is a number converted to a standard numerical format which includes a comma ("#,##0.00").

Getting Started

To see how these can work in a SharePoint list, we’ll look at the example of a "Loan Calculator" used to determine monthly payments on an amount of money borrowed.

For this, we’ll create a custom list that will allow us to build out our calculated columns and test how these functions work (we’ll look at each one as we create the columns).

In SharePoint, create a "Custom List" called "Loan Calculator".

Calculated Column

Once created, we’ll create several new columns in order to have the list perform the calculations we need.

First, to have the items that will appear on the list make a bit more sense, simply go into the list settings ("Settings > List settings") and rename the "Title" column to "Loan Title".

Next, we’ll create our columns using the following:

Create a new column called "Years" making it a "Number" type. Give it a description of "Total Number of Years on the Loan" and set its "Number of decimal places" to "0" and uncheck "Add to default view" (we’ll be displaying it in another column).

Create another new column called "Payments per year" making it a "Number" type. Give it a description of "Total payments per year (monthly payment, weekly, etc.)" and set its "Number of decimal places" to "0" and uncheck "Add to default view" (we’ll also be displaying it in another column).

Create another new column called "Annual Rate" making it a "Number" type. Give it a description of "Annual percentage rate on loan" and set its "Number of decimal places" to "4", and check the box to "Show as percentage".

Create another new column called "Loan Amount" making it a "Single line of text" and give it a description of "Total loan amount (in dollars and cents)".

Create another new column called "Total Payments" making it a "Calculated" type. Enter in the following formula and set its return type to "Number" with "0" decimal places:

Formula:

=Years*[Payments per Year]

Calculated Column

This calculation is simply multiplying the number of years of the loan, by the number of payments per year.

Example:

Years = 10
Payments per year = 12
Formula = 10*12
Result = 120

Create another new column called "Payment Rate" making it a "Calculated" type. Enter in the following formula and set its return type to "Number" with "Automatic" for the decimal places (we don’t know how many it will have yet, so this will leave plenty of room for us), and uncheck "Add to default view" (we’ll be referencing this column in another calculated column so it doesn’t need to be displayed on its own):

Formula:

=[Annual Rate]/[Payments per Year]

Calculated Column

This formula will take our "Annual Rate" and divide it by the total payments per year to give us the total of the rate that will be applied each month.

Example:

Annual Rate = 5.5%
Payments per year = 12
Formula = .055/12
Result = .004583333

Create our final column called "Payment Amount" making it a "Calculated" type. Enter in the following formula and set its return type to "Single line of text":

Formula:

=FIXED(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&[Years]&" years)"

Calculated Column

Note – since we’re working with money in this exercise, we’re also taking advantage of one of the available functions in the "Financial" set of formulas for SharePoint called "PMT". I’ll be discussing this in future articles when I delve into this set of functions, but since we’re using it here, the "PMT" function is used to calculate the payments for a fixed-rate loan using regular or "Constant" payments and a constant interest rate.

The function has the form of:

PMT(rate,nper,pv,fv,type)


Rate – interest rate for the loan
Nper – total number of payments for the loan
Pv – principal (present value, or total amount that a series of future payments is worth now)
Fv – future value, or cash balance you want to attain after the last payment is made. This is an optional field in the function and will be presumed to be "0" if omitted.
Type – either a "0" or "1" indicating when payments are due (0 = end of the payment period, 1 = beginning of the payment period. Presumed to be "0" if omitted since this value is also optional in the function)

Dissecting the Formula

In our formula for the "Payment Amount" column, we can look at it as:

=FIXED(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&[Years]&" years)"

The "PMT" function is in the format of: PMT(rate,nper,pv,[fv],[type])

Note – items in brackets [] are optional (we’re not using them in this case).

Stepping through the calculation, we first calculate the current payment by the following:

(Original Principal)*((1 + interest per period)^(number of periods)*(interest per period)/((1+interest per period)^(number of periods)-1)

Example:

(P)Original Principal = 250,000
(I)Interest per period = (Annual rate)/(payments per year) = .055/12 = .00458333
(N)Number of periods = 360 (based on 30 year loan with 12 payments per year: 30*12=360)

=(P*((1+ I)^N)*I)/((1+ I)^N-1)
=(250000*((1 + .004583333) ^360)* .004583333) /((1 + .004583333) ^360 -1)
Result = -1419.472441

Since this number (-1419.472441) needs to be reflective of a dollar-type number, we take it and pass it to the "FIXED" function to set it as a number with only two decimal places and a comma.

=FIXED(Result) = -1,419.47

Also, since this specific calculation is returning a negative number, we use the "FIXED" function with a minus (-) in front of the data it needs to format:

=FIXED(-Result) = 1,419.47

After we have our value, we then "CONCATENATE" a little more information onto the final result in order to make it more easily understood (optional, but can assist in readability):

=FIXED(-Result) ]))&" ("&[Payments per year]&" times per year for "&[Years]&" years)"

This produces a final result of:  "1,419.47 (12 times per year for 30 years)"

Testing the Solution

Now that we have our columns setup, let’s create a new item on our list and see what happens.

On the list, create a new item filling in each of the fields with data:

Loan Title = Home Loan
Years = 30
Payments per year = 12
Annual Rate = 5.5
Loan Amount = 250000

Calculated Column

Upon saving the item, we’ll see the calculations have taken place and show the results:

  • "Annual Rate" is displayed as a percentage.
  • "Total Payments" shows how many payments it will take to pay off the loan.
  • "Payment Amount" displays how much is due each payment with frequency.

Calculated Column

This works, and does give us the accurate calculations of the loan and payments, but notice how the "Loan Amount" and "Payment Amount" values are represented a numbers, but not in a "Currency" format? Let’s go back to our column settings and make a few changes to make the display of these values more relevant to the task (this is money we’re working with, so it should look like money!)

In the list settings page, click on the "Loan Amount" column to edit its properties. Change its type to "Currency" and leave the rest as default. Once saved (click OK on the prompt warning you about the possible loss of data – we wont be losing anything), go back to the list and view the item again.

Calculated Column

The "Loan Amount" column now reflects a currency format for its value (we could’ve just used this format when we originally created the column, but I wanted to show that in many cases you can change it after the fact without any consequences).

Next, we need to look at the "Payment Amount" column and modify it so the value displayed will also be in a currency format.

Looking at our formula again:

=FIXED(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&[Years]&" years)"

All of our numerical formatting is being applied based on the first function "FIXED". Since that function formats the text into a standard numerical format with commas and a fixed decimal length of two places, it’s actually using the "TEXT" format of "#,##0.00" (Seem familiar? Look back at the beginning of this article in the description for "DOLLAR").

As a test to see if this is accurate, modify the formula in the "Payment Amount" column to be the following:

=TEXT(-PMT([Payment Rate],[Total Payments],[Loan Amount]),"#,##0.00")&" ("&[Payments per year]&" times per year for "&Years&" years)"

Notice any difference in the values being displayed for the item? You shouldn’t because the formatting will be the same.

Modify it again, but this time, add in the dollar sign "$" in the formatting:

=TEXT(-PMT([Payment Rate],[Total Payments],[Loan Amount]),"$#,##0.00")&" ("&[Payments per year]&" times per year for "&Years&" years)"

Now, when looking at the results, we’ll see the value displayed in a currency format:

Calculated Column

Again, this does work, and is a manner in which you can have complete control over the formatting of the results, but since we’re just working with the "Currency" format, we can skip this method and instead use the built-in shortcut functions that deal with currency directly. These are of course, "DOLLAR" and "USDOLLAR".

In my case, since I do work in the States, and my international settings for both my PC and my instance of SharePoint are also set for U.S., using either function on my machine will both produce the exact same result. The idea though, is that if you are on a machine that has International settings other than those for the U.S., using "DOLLAR" will display the result in the local currency format (that which is specified on your local machine), whereas using the "USDOLLAR" function will always produce results formatted to meet U.S. Currency.

To use these, modify your formula accordingly, depending on which you want:

=DOLLAR(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&Years&" years)"

=USDOLLAR(-PMT([Payment Rate],[Total Payments],[Loan Amount]))&" ("&[Payments per year]&" times per year for "&Years&" years)"

Calculated Column

So, as I’ve stated in several articles, there a many different ways of accomplishing the same task within SharePoint, it just depends on your needs, the amount of control you want to have, and what the result should be. Each of these approaches will all do the job, but may differ in their results somewhat depending on regional location, personal customizations, and how you approach the formulas, but they do all work, and I’d encourage you to work with each of them to see what other differences you may find.

The last function I want to mention is the "VALUE" function. This function is used to convert text that represents a number, into an actual number. It works with each of the three standard numerical formats (constant number, date, time), and will return an error ("#VALUE") if the text is not in one of these.

Examples:

Text = "12345"
Formula = VALUE(Text)
Result = 12,345

The above returns the formatted number 12,345 since the inputted text represented a number. Also, notice how the result comes through formatted with a comma? Once again, we find another function using the same formatting type of "#,##0.00".

To prove this, look at the following examples:

Text = "12345.99"
Formula = VALUE(Text)
Result = 12,345.99

Text = ".0599"
Formula = VALUE(Text)
Result = 0.0599

Notice how each result gets formatted (note the added leading zero on the second example)?

Summary

Part of the reason I grouped these four functions into the same article is because of this commonality they each have in their formatting of the results. Each uses as it’s base format, "#,##0.00", but can differ somewhat depending on the situation (regional settings).

Granted, we did actually cover a fifth function "PMT" in considerable detail (which we will be looking at more when I get to the series that covers that set of functions), but it seemed necessary since we were working with a set of formulas involving money, so you got one for free!!

This is the last in the series dedicated to the "Text and Data" set of functions as we’ve now covered each function in the set, but we will be revisiting many of these in future articles since this particular set is probably the most frequently accessed (and questioned about).

Till next time…

- Dessie

Suggestions for future posts on calculated columns are always welcome, and in fact are encouraged.

Some of the best scenarios to illustrate are the "real-world" problems that we each face day to day, so if you have an example, an idea you want to explore, or a "Can this be done with a Calculated Column?" question that I can use as the topic of a future post, please submit it as a comment below and I’ll see what I can do to work up a post covering it.

Dessie LunsfordDessie Lunsford
Points-of-Sharing

Fairy Tales in SharePoint Land: The Mean Old Project Sponsor

A Mean Old Project SponsorOnce upon a time, in the Land of Unrealistic Expectations, there lived a mean old Project Sponsor. This Project Sponsor was known throughout all the land as being the meanest, most pedantic project owner that ever lived….a fact made all the more difficult given that this particular Project Sponsor was the Queen of the Land.

She huffed and puffed about standard document templates and content formatting standards between documents for everything created for her projects. The people lived in fear of incurring her wrath and spent many hours ensuring that documents presented to her were of the highest quality in form and function. In fact, they spent so many hours on document management and preparation that their PM duties suffered and their families often ate dinner without them.

Then, one day, a Knight approached the Land on a steed named ‘MOSS’. He was strong and brave and not afraid to take on the unstructured data elements commonly created to manage Projects. This Knight saw the fear of the people and called a meeting at the local watering hole to discuss Content Types and how they might aid in satisfying the mean Queen .

"Don’t you know that you can build SharePoint Site Columns that will offer you standardized information across your entire SharePoint site?", he asked, amazed that they had not.

A Knight on a Steed Named 'MOSS'He continued, "The creation of site columns provides you and, most importantly, your users, the ability to associate meaningful meta data to your information. This is one of the most magical components of the SharePoint platform." He saw bewilderment fill their faces at this new and mystical technology.

One of the meekest of the townspeople stepped forward and said, "Sorry to interrupt you, Sir Knight….that sounds interesting and all, but the Queen expects us to update her whenever any new document or task is posted to our SharePoint site and she expects us to provide her with documents that adhere to strict rules regarding format and function. She’s quite the witch, she is…".

"Well, using content types with site columns is only the beginning. You can also associate a specific Word, Excel or PowerPoint document template with a content type, giving you the ability to click on the ‘New’ button in a document library and use those document templates to create your status reports, presentations or financial models on where a projects sits regarding budget or hours consumed."

The meek townsperson responded, saying, "You mean to tell me that everyone will create the same document just by click on the ‘New’ button in a Document library? This I simply must see with my own eyes.".

"Absolutely", responded the Knight. "But you say that she requires you to notify her immediately regarding any new information that is added to your SharePoint Project Portal? Let me see…that’s a difficult one."

A Meek Town Person"We forgot to add, Sir Knight, that each of the document types that we create must be sent to different people for update or approval", said another meek townsperson." The Queen requires to see project status reports immediately while the CIO must approve change requests to a project’s technology while the CFO must approve capital budget requests and project overages."

The Knight pondered this, carefully considering the options available to him. "Well then…", he pondered, "…we’ll just have to build workflows specific to each _type_ of document that your project managers create and associate them with the Content Type we create in SharePoint."

"Horray!", yelled the townspeople. "We never knew life could be so grand!!"

The Knight continued, "But content types don’t start and stop at the document libraries you have. You can apply a content type to tasks, to-do items, calendar events and more. You just need to know how to do it."

"You mean to tell me that we have the capability to do this right now?!?!", said a particularly old and beaten-down PM. "Why couldn’t we have done this sooner and saved ourselves so much heart-ache and grief!", he wailed.

"Because you haven’t yet attended the EndUserSharePoint.Com training on the Basics of Content Types", responded the Knight. "Go forth and sign up at http://eusp-20081125.eventbrite.com/and see the magic unfold in front of you."

The town full of Project Managers lived happily ever after……until the Queen began asking about content rollups!

The Spector of Content RollupsStory Author: Lee Reed
ThoughtBridge, Atlanta, GA

Lee Reed is an expert in collaboration and user adoption on the Microsoft SharePoint 2007 platform. His consulting with companies large and small throughout the East Coast has resulted in many successful collaboration environments and increased user adoption.

Lee is currently the Director of Business Process and SharePoint Education for Thoughtbridge, a Microsoft Gold Partner focused exclusively on the Microsoft SharePoint 2007 platform.

Final call for Basics of Content Types online workshop

I’m in the process of setting up Tuesday’s workshop, The Basics of Content Types. If you are going to attend, please register now so I can get your sandbox setup for tomorrow.

Thanks. — Mark

Workshop Description

The Basics of Content Types in SharePoint 2007 is a live, online, hands-on workshop showing how to utilize Content Types for creating a robust and maintainable structure in SharePoint.

‘Content Type’ is a critical feature in SharePoint that allows the central management of metadata across an entire site collection. This beginner level workshop will cover the creation and implementation of Content Types to manage multiple libraries and lists from one, central location.

Each workshop participant will be given their own SharePoint site to experiment and follow along with the presenter. Resources and a recording of the session will be available at the end of the session

Testimonials from previous sessions:

"I love these sessions They get me to thinking what I can (or might) do with SharePoint! I never thought of using Content Types for things other than a library." — Shannon [Create a Master Calendar in SharePoint]

"This is an excellent workshop. Mark answered every question and moderated with humor and patience through the usual communication hiccups with an audience that is international (Argentina, Ireland, all over the US)." — Betsy [SharePoint Dashboards, Online]

"This workshop was absolutely worth the time and money. I learned alot and will be able to easily transfer it to my daily work. I can’t wait to impress my coworkers." — Tammy [SharePoint Dashboards, Online]

"Was a great lab. Nice interraction between the host and the audience. Was great information and well presented with loads of reference materials which helps when retrying things on your own." — Shaune [Create a Master Calendar in SharePoint]

"Don’t waste anymore time reading this to check out if it was worth it – stop reading and book your session!" — Mick [SharePoint Dashboards, Online]

"This was one of the best training sessions I have attended. It delivered a lot of powerful information yet was simple to follow and participate in. I can’t wait for the next class!" — Heidi [The Fundamentals of SharePoint Lists and Libraries, Online]

"…very professional work. The workshop format with the hands-on lab made the 3 hour webconferencing event a valuable experience for me." — Urs [The Fundamentals of SharePoint Lists and Libraries, Online]

Bedtime Stories with Content Types

How do you create a building without a plan? Do you create each individual room, one by one?Telling stories is as old as fire. That’s how things get passed down through generations. Entire cultural histories exist through spoken language stories.

Have you ever heard of the Iliad and the Odessy? Yep, I thought so. Pretty good story, I’d say. There was another story about a Trojan Horse that got me pretty excited as a kid. They both started from a story, a poem, that was recited through generations until someone wrote it down.

Every good presentation has stories too, whether down in the trenches gut busters, good analogies related through context or just plain "Boy, I screwed that one up" tales.

As I’m setting up for the "Basics of Content Types" online workshop, I’m developing a set of stories on how Content Types can make a difference in managing your information and its structure. For Tuesday, I’ve come up with some stories for Finance Departments, Law Firms and Building Architects.

Talk is cheap. Examples are priceless, to paraphrase American Express. If you had to write a story about Content Types, what would it be? What can you hand down to the next generation of SharePoint users as part of the oral tradition?

Leave a comment to add to the epic poem of SharePoint.

jQuery and SharePoint: Take Two

In addition to the articles on jQuery and SharePoint that Waldek and I have worked on, Jan Tielens is also working on some fun stuff. If you have a little bit of developer’s blood in you, Jan’s article "Integrating SharePoint 2007 and jQuery[Part One]" takes you through implementing jQuery at the server level.

There’s a lot of material to be covered on jQuery, so keep an eye on us and Jan to see some pretty cool stuff.

Resources from this article: