A tribute to the humble “leave form” – Part 7

Send to Kindle

[Note: It appears that SharePoint magazine has bitten the dust and with it went my old series on the “tribute to the humble leave form”. I am still getting requests to a) finish it and b) republish it. So I am reposting it to here on cleverworkarounds. If you have not seen this before, bear in mind it was first published in 2008.]Hi all

Welcome to edition 7 of this seemingly never-ending tribute to the humble annual leave (vacation) form. This is getting to be like the “Friday the 13th” series of movies – they never stop!

First up, I want to categorically state that, as a heterosexual Australian male, I have a few stereotypical personality traits . I enjoy beer, kung-fu movies, Burger Rings, Vegemite and Tim-Tams, love the cricket (especially playing India) and most importantly of all, I do not read instructions. Period. I think that ignoring the RTFM rule is a characteristic of all of humanity but Australians in particular seem to celebrate this trait as something endearing.

Therefore, it pains me greatly to have to inform you that this seventh edition of the series is one of those posts where you have to put on the brakes and explain some really boring conceptual stuff to proceed with the rest of the series. Yeah, I know – theory is boring and sucky and you just wanna get into the nuts and bolts of it all. But, as Homer demonstrates below, sometimes we have to defy our genetic programming and actually read the instructions. The alternative is a SharePoint solution equivalent to that of Homers BBQ.


This post will deal with some of the mechanics of form submission and form publishing.

What has gone before…

The first few posts in this series are starting to get hazy – it seems so long ago now. To sum it all up in a nutshell, we introduced the leave form as a means to introduce a lot of key SharePoint features in an easy to understand and relevant way. We set several requirements that we had to achieve.

  • Automatic identification of requestor
  • Reduce data entry
  • Validation of dates, specifically the automatic calculation of days absent (excluding weekends)
  • Mr Burns is not the only approver, we will need the workflow to route the leave approval to the right supervisor
  • We have a central leave calendar to update

At the end of part 6 we were working on validation of dates. We added some validation rules to ensure that you could not return to work before you started your leave of absence. But at the end of that post, I showed how a published form did not obey our data validation rule, where the form was set to prohibit incorrect dates, yet I was able to click save and put the form straight into the document library. What the…?Why did this occur?

Saving vs Submitting – form confusion

Like everything else designed by nerdy engineers, InfoPath and Forms Services can be used in a few different ways. This offers flexibility of course, but has the disadvantage that if the flexibility is not well understood (i.e not reading the instructions :-) ), it is easy to cause user confusion. Homer is a classic case of a person who, when faced with two choices, will always pick the wrong one.

You have to remember that InfoPath started out as a program installed onto your PC, along with other MSOffice applications. Thus, historically, an InfoPath form is just another type of document. In its simplest (and default) scenario, the form is very much like any other MSOffice document in that you create/open the form, edit it and then save it.

But hang on a second! Most of the time, the data collected in the form is part of a larger business process. Also most of the time, business process needs to do “stuff” with the information that the user has entered. In other words, the data usually doesn’t stay in the forms that users fill out.

So the InfoPath designers, wanting to please everybody, designed InfoPath in such a way that saving a form and submitting a form are two different things. Homer Simpson doesn’t care about this nor should he. He just wants to fill out the form so he can go back to dreaming about the land of chocolate.


Here is the key thing you need to know, though. Saving a form is, in effect, performed on the presumption that the form is still a work in progress. Saving the form should be thought about in the same way you would save a word document after performing some edits. Because the form is still a work in progress, data validation should not take place.

But at some point the form stops being a ‘document’ and becomes…well… a form! It is complete and no more edits should be made. This is when you actually submit the form!

Submitting a form triggers the data validation rules that were set up in part 6.

Big deal…When does this get hard, Paul?

Okay, so you may not think that there is really that much of a drama with the whole “save vs submit” thing. But when InfoPath is integrated with SharePoint via Forms Services, it can get complicated. You see, InfoPath allows you to create forms that submit their data to one or more locations. For example, the Springfield Nuclear Power Plant leave form could be set up so that when Homer submits his application, the data is sent to a database, and a copy of each completed form is also sent in an e-mail message to Waylon Smithers and Monty Burns.

For reference, here are the places that InfoPath can submit to;

  • A Microsoft Access or Microsoft SQL Server database
  • A Web service
  • A server running Microsoft Windows SharePoint Services
  • In an e-mail message
  • An application on a Web server
  • A custom application that hosts InfoPath

Now, given that this series of articles is about SharePoint, which option of the above list do you think we are interested in? You guessed it! The third option in the above list is SharePoint.

But here is the root cause of the confusion, when you publish an InfoPath form to SharePoint, all forms that you fill in end up being created in a document library. Back in part 4 of this series, we first published the form and as part of that process, created a form library called “Leave Forms”. Consider the screenshot below. We are about to create a new form in this library.


Below is a screenshot of the new form to fill out. Note the toolbar at the top with a “Save” and “Save As” options. If you then click ‘Save” on the form, it will prompt you to save into the above document library.

image  image

Therefore, if you use the “Save” option, the “In-progress” copies of the form are stored in the document library and no data validation has taken place. You really have not submitted the form at all.

Does the end user realise this? Some might – but come on! This is Homer J Simpson we are talking about here!

So clearly we need to submit this form because in part 6 we added validation rules that need to be checked. But, what the…? – How the hell do I submit?

Setting up form submission

Real programmer disclaimer: Yes I know there are other ways of doing all of this, but remember – this series of posts is for beginners.

Okay, for now the theory bit is over, and now we get to play with InfoPath again. We are going to add the form submission capability to our existing form so that we can actually submit the form. The thing is, I am also going to *remove* the “Save” and “Save As” options from that toolbar, so that the only action you can make is to submit.

Why would I do this? Several reasons.

  • It is a simple form and really, you either fill it in and submit or you don’t. There really isn’t that much of an imperative to save this form, prior to submitting it.
  • When you submit a form, and you are submitting to a SharePoint library, the submitted form is saved. Therefore, I don’t need a “work in progress” copy.
  • The Homer factor. If you supply a “Submit” and a “Save” button, you know full well that Homer will go and click the “Save” button, when he should be clicking “Submit”. As soon as the save dialog pops up, he won’t know what to do. At this point he will lose interest and fall asleep.

Hiding save buttons

So, start InfoPath, open up the leave form in design mode, and choose “Form options” from the “Tools” menu.


From the list of options, choose “Open and Save” and uncheck the “Save and Save As” checkbox as shown below.


Click “OK” and republish this form to SharePoint using the steps described in part 4. Note the before and after screenshots below :-)

image image

Spot the difference? The “Save” and “Save As” buttons are missing, and with that I can confidently say that I have now made this form Homer proof because he can’t inadvertently click “Save” and think he has submitted the form. Of course, it is so Homer proof that he now has no way to submit the form, so now we have to enable the “Submit” button.

Enabling the submit button

Once again, open the leave form in design mode, and choose “Submit Options” from the “Tools” menu. InfoPath will dutifully display a fairly nondescript dialog box that, at first glance, seems rather simple. There is a checkbox that you need to tick to “enable form submission”. Sweet! How easy is that?

image image

Upon checking that box, you now have to choose where you are going to send form data to. The default option is “Email”, but we want to send it to a SharePoint document library, as shown below. Choose SharePoint and then click OK.


Uh oh! Did I say this was going to be easy? Okay I lied :-) .


So, what is this message telling us? “You must select a data connection for submitting to a SharePoint document library”. Hmmm…I hope that something about that message sounds vaguely familiar to you. That is because this is not the first time we have dealt with data connections.

Data Connections revisited


Way back in part 5, you earned your CCLFD certification by creating a data source that automatically identified Homer by talking to SharePoint web services. If you missed that post, then I suggest reading it, as it is a great example of utilising data connections to *receive* data into InfoPath.

In part 5, we set up a data connection to a webservice by following a wizard. To remind you of how it was done, I have copied the screenshots to this post.



Now, take a look at the second screenshot from the above three. It is asking you to create a connection to receive or submit data and at that time we were receiving data. Guess which one we are doing this time? :-)

So, back to our “Submit Options” dialog box. We were not able to click “OK”, because we have not created a data connection to submit the data. Let’s now start this process by clicking the “Add” button. Given that you have already told SharePoint that you want to submit to a document library, InfoPath now wants to know the name of that document library and the name of the file that will be created by the submit.

image image

Now, given that the entire staff of the nuclear power plant will be filling out this form, we don’t want to simply call the submitted form “Form” as suggested by default. If we did that, we would end up with a document library containing a single file called “FORM” due to the fact that all previous “FORM” files would have been overwritten and replaced by the current “FORM”.

Luckily the InfoPath team anticipated this and the filename can be dynamically generated using functions. (We looked at functions early in part 5).

First up though, I will specify the document library to submit completed forms to. It will be in the same document library that we was created when we first published the leave form in part 4,the imaginatively titled “Leave Forms”. Below is the library as it is inside the radiactivenet site. Note the URL is exactly what I entered into the “Document Library” field.



A unique filename

The final step for submission is to create an auto-generated filename that is unique. Although the principle is dead simple, this bit is actually tricky when you do it for the first time.

The file naming convention to be used will be as follows:

“Leave Form – ” + [EmployeeName field] + ” – “, [LeaveType field] + ” – ” + [todays date]

Here are some examples of this convention in action with various staff.


  • “Leave Form – Homer Simpson – Sick – 2007-09-18″
  • “Leave Form – Lenny Leonard – Annual – 2008-11-22″
  • “Leave Form – Carl Carlson – Bereavement – 2008-12-25″

So, how do we achieve this? Let’s get back to the screen where we are prompted to add a file name for a submitted form.


Click the “Fx” button to the right of the “File name” textbox and you will be presented with the (far too small to be user friendly) “Insert Formula” dialog box. We are going to create a formula that constructs the desired file name.


The first thing to note is that we will be using a combination of fixed text, the data entered on the form and built in InfoPath functions. To join all these together in the manner we want is referred to as concatenation. Why do people call it concatenation? I have no idea but it’s obvious that an engineer thought up that name! :-) .

We start by inserting the concatenation function called “concat”. Click “Insert Function” and in the leftmost window choose the “Text” category. The function called “concat” should be the first one listed. Select it and click “OK’ and you will see that a partially completed formula has been created.

image image

The first part of our file name is the text “Leave Form – “. So single click the first occurrence of the “double click to insert field” hyperlink and it will be highlighted. From here, simply type in the string “Leave Form – ” (including the double quotes).

image image

The next part of the file name is the name of the employee. We already have this, because in part 5 we set up a data connection to automagically grab first name and surname and store it in the textbox called “EmployeeName”.

Single click the next occurrence of the “double click to insert field” hyperlink and it will be highlighted. From here, click “Insert Field or Group” and choose “EmployeeName” from the list of fields.

image  image


Okay, so we have the “Leave Form – Homer Simpson” part done. Next we want a hyphen ” – ” and then the LeaveType field. By now you should be getting familiar editing the formula in the dialog box. Rather than bore you with even more screenshots, I’ll talk through the next 2 steps.

  • Single click the next “double click to insert field” link.
  • Type in (note the leading and trailing space) ” – “
  • Type in a comma (minus quotes) “,”
  • Click “Insert Field or Group” button
  • Choose the “LeaveType” field and click OK.

If all has gone to the script, then you will see this in for formula box.


Note that you can use the mouse to place the cursor anywhere in the Formula textbox and modify what is there.

Finally, we need another hyphen and to insert another function. This time the function is called “time” and it returns the current date in a short locale based format.

  • Referring to the above screenshot, place your cursor between the last letter of the underlined LeaveType (which is “e” ) and the closing bracket.
  • Type in (including the quotes and note the leading and trailing space) ” – “
  • Type in a comma (minus quotes) “,”
  • Click “Insert Function” button
  • Choose “Date and Time” from the Categories List in the “Insert Function” dialog box
  • Choose “today” from the list of functions and click “OK”

We are done! The final formula looks like this. Click “OK” and you now should be back at the data connection wizard (the second screenshot below).



Click “NEXT” on the wizard, and we are prompted to give this data connection a name. Since this data connection has been created to submit leave forms to a document library called “Leave Forms”, I figured that a good name would be “Submit to Leave Form Library”. Click “Finish” and you are done!


To recap on what we have completed in this section, each time a form is submitted, its filename will be automatically specified and the end-user does not have to worry about it.

All rightie, then! Let’s republish this form to SharePoint and take a look at the effect.

Testing it out

Assuming everything worked, the effect of what we have done is immediately noticeable. We now have a “Submit” button on the form where the “Save” and “Save As” used to be. Sweet!


So let’s test the data validation rules that we created in part 6. If you recall, this form should not be submitted if the return date is before the start date. For that to really happen you would have to be Dr Who and use the Tardis to arrive before your departure…


Below is the form with deliberately incorrect data (click to enlarge).


If I now click the newly visible “Submit” button, what happens? Ecccccxcelent! It refuses to submit the form.


I don’t know about you, but this is one of those error messages that some people simply will not read. Homer will no doubt call technical support but the point is, crap data has not gotten past the form in the first place.

Now, when we correct the fault and resubmit the form, it accepts!


Click “Close” and we now see the newly submitted form in the document library, saved with our file naming convention! Woohoo!


Conclusion (Are we done yet?)

We now have achieved the goal of ensuring that our form is validated before being submitted. From a skills point of view, you now have created data connections to both send and receive data and you have integrated your form with web services, as well as some nice data validation logic.

Waylon Smithers is still pleased with progress thus far, and Homer hasn’t managed to completely derail things just yet.

But unfortunately for all of us, there is still much work to do. The next post will spend some time pimping up this whole validation and submission process, illustrating a pitfall or two along the way.

Thanks for reading

[Note: I never finished this series for various reasons. I hope that these 7 posts helps you nonetheless]

Paul Culmsee

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

A tribute to the humble “leave form” – Part 6

Send to Kindle

[Note: It appears that SharePoint magazine has bitten the dust and with it went my old series on the “tribute to the humble leave form”. I am still getting requests to a) finish it and b) republish it. So I am reposting it to here on cleverworkarounds. If you have not seen this before, bear in mind it was first published in 2008.]

Hi again

When I started this series of articles, I knew it would be quite a few parts and ambitious in its way. But after reading some of the other great articles on SharePoint Magazine, such as Bjørn Furuknap’s first article on customising the user experience, I’m now feeling like I picked the easiest topic of all!

Seriously, Bjørn, part 1 of your series is the closest experience I can imagine to any man giving birth! I bet you feel relieved getting that one out of your system!

So, do I feel guilty that my subject matter is easier? Am I suffering nerdiness envy?

Hell, no! I’ll stick to my fantasy world of user acceptance testing with Homer Simpson any day!


Given that there are big time gaps in getting these articles out, I’d just like to reiterate that this series is pitched at a very wide audience, so hard-core nerds will probably find it waffly and frustrating. But then again, most regular people find hard core nerds waffly and frustrating :-) .

I have been writing this series from the point of view of a frustrated implementation developer/engineer who has to deal with a clueless business development manager and a difficult customer. This, of course, is completely unfamiliar territory for all implementation developer/engineers because none of you reading this would have ever experienced such a situation, right? … hehe :-)


Part one of this series introduced some background and provided the requirements for the leave application/approval process for the Springfield nuclear power plant. Those requirements were:

  • Automatic identification of requestor
  • Reduce data entry
  • Validation of dates, specifically the automatic calculation of days absent (excluding weekends)
  • Mr Burns is not the only approver, we will need the workflow to route the leave approval to the appropriate supervisor
  • We have a central leave calendar to update

Parts two to four were really the “slick presales demo” parts of this series where the world is promised to the client and, hence, expectations were completely off the planet. Thus, we started out nice and easy in part two and three, where we introduced InfoPath 2007 and went through the exercise of importing the existing Springfield MSWord based form. We then added some data entry controls and tidied up the formatting. In part four, we published this form to SharePoint itself.

All in all, the first four articles covered some pretty straightforward stuff. Now, however, the salesman has moved on and our intrepid engineer is starting to get into the pointy end of things. In part five we delved into the scary world of web services and InfoPath data connections so satisfy the requirement of automatic identification of the requestor. That was a bit of a jump, wasn’t it?

This time around, let’s take care of the “reduce data entry” requirement, and leverage some of InfoPath’s useful features to make our form smarter. For those who found article 5 a bit heavy, this one is actually quite a bit easier (but it’s just a brief lull before we get to coding!). At the end of part 5 I said I’d deal with how to automagically handle the employee number – but I have decided to take care of that a little later.

Preventing a rift in the space-time continuum

Yeah, I know I was using the Simpsons theme for this series, but today I am dropping in a dash of Heroes.

First up, we need to add some logic to the form so that it doesn’t submit crap (that’s the technical term for “invalid”) information. For example, we want to make sure that you cannot return to work on a date *before* you leave – unless you’re Hiro Nakamura, of course.


Generally, for the rest of us who cannot bend time, you do actually come back to work at some point *after* you leave, so let’s make the necessary changes to ensure that our form does not destroy the world in some space-time vortex.

Today we are interested in the three date/time fields as shown below.


If you recall in part three where we created the form controls, we gave them the meaningful names of CommenceDate, CompletionDate, ReturnDate.

For reference, at any time, you can review your field names easily by clicking the “Data Source” link in the InfoPath design pane as shown below.

image image

So, we have these three date fields and the rules are:

  • The completion date cannot be *before* the commencement date.
  • The return to work date cannot be *before* the completion date.

When you think about what we are doing here, we are validating data to make sure that it matches our business rules. Fortunately for us, InfoPath has some nice capabilities here to assist. Even better, they called it “Data Validation”. So, let’s take a closer look…

  • Select the “Completion Date” control, right click and choose “Data Validation”


You are presented with a fairly unexciting text box asking you to add a condition that determines whether the value entered is valid. Clicking the “Add…” button displays the dialog box to add a condition. It is quite common to have multiple conditions that need to be satisfied in order for the data entered to be considered valid.

image image

In our case, we wish to ensure that the CompletionDate field cannot contain a value that is less than the CommenceDate field. Create this condition by specifying “less than” from the middle drop down box, and then picking the “CommenceDate” field for the third dropdown box.

image image

image image

Clicking OK and we have our completed validation condition!


One final OK and we are back at our form and ready to test this. Rather than publish the form to SharePoint now, we can make use of InfoPath’s “Preview” button on the toolbar and test it out. Below I have an example preview where Homer has been identified as per the previous post. Additionally, I have used the date picker to specify the commencement date (October 25 2008) of leave.


What do you now see on the Completion Date field? What’s with that red asterix? Maybe I should hover my mouse over it and take a look, eh?

Aww… How sweet! A tooltip that matches the description I gave for this data validation condition. Well, what do you know? I spelt data instead of date (don’t worry I’ll fix it up later).


So, let’s try and defy time, then. I will enter October 24th as my return date. Doesn’t seem vary happy does it?



But if I change the date to the 25th of October (therefore validating the rule we set up), InfoPath happily accepts the value.


That it? That was easy!

Yeah, it was very easy so what are you complaining about? Easy is good and there should be more of it!

We now need to do the same thing for the “Return to Work” form field. Essentially, we perform the very same steps and thankfully for the both of us, I am not going to smother you with screenshots. This is where you try it yourself and instead I will simply list the steps and one screenshot.

  1. Right click the ReturnDate field and choose Data Validation
  2. Add a new validation condition where the ReturnDate is less than the CompletionDate
  3. Add a tooltip text “Return date cannot be before the completion date”
  4. Click OK twice and test using the preview button


The published experience

Now that we have successfully tested the validation rules for the date fields, let’s publish the form to SharePoint and see what it all looks like. I followed the exact publishing steps that I outlined in part 4, so please refer to that article if you need a recap.

I navigated to the forms library as described in part 4 and created a new form.


I then deliberately entered bad data into the form and sure enough, the data validation rules picked up on the issue. Excellent – Waylon Smithers is very pleased!


Reviewing our progress…

So, since we are about to see off part 6, our Project Manager wants to get a status update on how the Springfield leave form project is progressing. So, after 6 articles, what have we achieved? To remind you, the requirements were:

  • Automatic identification of requestor
  • Reduce data entry
  • Validation of dates, specifically the automatic calculation of days absent (excluding weekends)
  • Mr Burns is not the only approver, we will need the workflow to route the leave approval to the appropriate supervisor
  • We have a central leave calendar to updateIdentification of Homer is now sorted, and we have taken steps both in the areas of reducing data entry and validating the dates (excluding days absent). So, we are tracking nicely and haven’t fallen behind schedule just yet :-) .

    Before we continue with addressing all of these requirements, we will have to take a bit of a detour as I explain below.

    Too good to be true?

    Here’s a little experiment for you to try. On the above form, published in its current form with deliberately invalid dates (highlighted in red), you will see a toolbar that has a “Save”, “Save As”, “Close” and “Print View” on it. I clicked the Save As button and was dutifully prompted for a filename. I called the form “Homer’s Test Form” as shown below and clicked Save


    I then closed the form and checked out the form library on the http://radioactivenet site. What the…! It saved it! What’s the point of data validation if you can just save the form anyway?


    The answer to that question, my friends, is there is a difference between saving a form and submitting a form. But you will have to wait for part 7 of this series for that riveting discussion!

    Thanks for reading

    Paul Culmsee

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

A tribute to the humble “leave form” – Part 5

Send to Kindle

[Note: It appears that SharePoint magazine has bitten the dust and with it went my old series on the “tribute to the humble leave form”. I am still getting requests to a) finish it and b) republish it. So I am reposting it to here on cleverworkarounds. If you have not seen this before, bear in mind it was first published in 2008.]

image_thumb[2]Welcome again students to part 5 of the CleverWorkArounds Institute Body of Knowledge (CIBOK). As you know, the highly prestigious industry certification, the CleverWorkarounds Certified Leave Form Developer (CCLFD) requires candidates to demonstrate proficiency in all aspects of vacation/leave forms. Parts 1-4 of this series covered the introductory material, and now we move into the more advanced topic areas.

Wondering what the hell I am talking about? Perhaps you’d better read part 4.

This post represents a change of tack. the first four articles were written from the point of view of demonstrating InfoPath in a pre-sales capacity. Your “business development managers”, which is a politically correct term for “steak-knife salesmen”, have promised the clients that InfoPath is so good that it can also make your coffee too. Essentially, anything to make the sale and earn their commission. Of course, they don’t have to stick around to actually implement it. They have moved off onto their next victim, and you are left to satisfy the lofty expectations.

Now, we switch into “implementation engineer” mode where a proof of concept trial has been agreed to. At this point we are dealing with three client stakeholders. Monty Burns (project sponsor), Waylon Smithers (process owner) and Homer Simpson (user reference group).

To remind you about parts one to four, we introduced the leave form requirements, and demonstrated how quick and easy it is to create a web based InfoPath form and publish it into SharePoint with no programming whatsoever. Was it a realistic demo? Of course not! But we have sold the client the dream and now we have to turn that dream into reality!

Here are the original requirements.

  • Automatic identification of requestor
  • Reduce data entry
  • Validation of dates, specifically the automatic calculation of days absent (excluding weekends)
  • Mr Burns is not the only approver, we will need the workflow to route the leave approval to the right supervisor
  • We have a central leave calendar to updateSo in this article, we will deal with the first requirement.

Automatic Identification of Homer

Now, some of the staff of the Springfield Nuclear Power aren’t the most computer literate. Technical support staff report that one employee in particular, Homer Simpson, is particularly bad. In between sleeping on the job and chronic body odour, he has been known to hunt in vain for the “any” key on the keyboard. So he was chosen to be the user acceptance test to ensure that the entire process is completely idiot-proof.

The way we will do this is to automatically fill in the full name of the current user, so Homer will not have to type his name in. It would just automagically appear as shown below.


So our process owner, Waylon Smithers, is excited and expectantly asks you, “This is easy to do, right”?

“Sure”, you answer confidently. “It’s built into InfoPath and I do it all the time”.

You then proceed into the properties of the above “Employee Name” textbox and locate the “Default Value” textbox. You then click the magical “fx” button that lets you pick from a bunch of built-in functions as shown below.

image_thumb[7]  image_thumb[9]

Now anybody who has used Excel will be used to the notion of using built-in functions to create dynamically created values. InfoPath provides similar cleverness. There are formulas for mathematical equations, string manipulation, date and time functions as well as a bunch of others.

While I won’t be discussing every built-in function in this article, I encourage you to check out what is available.

Now our intrepid consultant already knows what formula they want to use. The “Insert Function” button is clicked and from the list of functions available (conveniently categorised by type), we choose the function userName as shown below.

image_thumb[11]  image_thumb[13]


So we have now set the default value for the “Employee Name” field to be whatever the data the userName() function decides to give us. So let’s see what our favourite employee Homer Simpson now sees.


“Voila”, you think to yourself. “Homer, please test it.”


Homer: “Herrrrrr Simmmmmpson. Mmmmm. Who is hersimpson”?

Consultant: “Who”?

Homer: “Hersimpson. Is that… Lenny?”

Consultant: “…”

Homer: “Oh, wait! I know! I know! It’s that new sprinkled donut … mmmm donuts….”

Making it Homer Proof

So, it seems we have a problem at the user acceptance testing phase. Apart from drifting off into a donut induced daydream, it is clear that showing the username is not a good idea as Homer has not realised that his userid (hsimpson) represents himself. Clearly it would be more “Homer friendly” if his full name was displayed instead. At this point, we hit our first InfoPath challenge. How do we get the full name? We have no built in function called “fullName()”, so how can we do it?

Hmm, this is a little tougher than everything we have done so far. Perhaps we should get in some additional help from Professor Frink.

image_thumb[20]“Well it’s quite simple, really. We need to create a secondary data source to the SharePoint web service UserProfileService.asmx and call the GetuserprofileByName method, passing it a blank string parameter called AccountName, and then interrogate name value pairs in the response to grab the first and last name and concatenate them into a full name Mm-hai.”

Does anybody want the non-nerd (English) version of the above sentence? :-)

Given that lots of interesting “stuff” lie buried in various applications, databases, files and other “systems”, the designers of InfoPath were well aware that they had to make InfoPath capable of accessing data in these systems. In doing so, electronic forms can reduce duplication, data entry and leverage already entered (and hopefully, sanitised and verified) data.

One of the many different methods of accessing “stuff” is via “web services”. The easiest way to think about web services is to think about Google. When you place a search on Google for say, “teenyboppers”, your browser is making a request to google servers to return “stuff” that relates to your input. In this case, people who actually like Brittany Spears’ songs.

Google is in effect providing a service to you and the protocol that drives the world wide web (HTTP) is the transport mechanism that both parties rely on.

So, a real-life web service is really just a more sophisticated version of this basic idea. One program can chat to another program by “talking” to its web services over HTTP. In this way, two systems can be on the other side of the world, yet be able to communicate with each-other and provide each-other with data and, well … services!

SharePoint is no exception, and happens to have a bunch of web services that allow programs to “talk” to it in many different ways. I am not going to list them all here, but it just so happens that one of those web services, gives us just what we need – the full details of the currently logged-in user. So we are going to get InfoPath to “call” this particular web service and return to us the data we need to make Homer happy.

Okay in theory but…

Now hopefully the basic idea of webservices now makes sense. Actually making the leap to using them does take some learning. Since this is all over HTTP, a web service is simply a website URL.

Real programmers – don’t start getting all anal with me about definitions here. This explanation is for people who speak human!

The URL of the SharePoint web service that we want is this:

http://<address of sharepoint site>/_vti_bin/UserProfileService.asmx



If you point your browser to this URL, you will get a response back, listing all of the various “methods” that this web service provides (click to enlarge). The screenshot below is not exhaustive, but the point is that one web service can actually provide many functions (generally known as methods) to perform all sorts of tasks.


Now, InfoPath likes to make things nice and wizard-based, and all of these above methods operate differently. Some take parameters (I.e. to create a new user account via web service you would have to supply details like name, password and the like). Some do not need parameters but return multiple values while some return a single value. Others still return different values based on parameters that you send to the method.

How can InfoPath possibly know in advance what it needs to send to/receive from our selected method? Fortunately the geeks thought of this and invented an extremely boring language called Web Services Definition Language (or WSDL for short). All you need to know about WSDL is that apart from being a fantastic cure for insomnia if you ever read it, it provides a way for applications, like InfoPath, to find out what it needs to do, to interact with a particular method.

So, using the above web service again, we will actually ask the web service to tell InfoPath all about itself using WSDL by slightly changing the URL as illustrated below


Now, if you try that in a browser you see all sorts of XML crap :-)


But don’t worry, you will never have to actually look at it again – InfoPath digs it – and that’s all you need to know.

InfoPath Data Sources and Data Connections

Now, it is time to actually get InfoPath to talk to this “UserProfileService” web service as described above.

As I said, Web services is one several sources of data that InfoPath can access from. First up, we need to make a connection to a data source (imaginatively called Data Source connections).

From the tools menu, choose “Data Connections” and a dialog box asks you to some information about the sort of data connection that we want to use. We wish to receive Homer’s details from the web service, so we choose to receive data and choose “Web Service” as the source of our data.

image_thumb[23] image_thumb[25] image_thumb[26]

We are next asked for the URL of the web service. We pass it the URL of http://radioactivenet/_vti_bin/UserProfileService.asmx?WSDL


Note: This really should be the exact sub-site where the InfoPath form was published to. So, if say, this form was to be published into a sub site called HR, then the URL would be http://radioactivenet/HR/_vti_bin/UserProfileService.asmx?WSDL.

Clicking NEXT and you are presented with the various methods that this web service provides. The method that we are going to use is “GetUserProfileByName”. We choose it and click “Next”.


At this point, we really start diving deep into talking to web services. InfoPath examines the details of this method by looking at the WSDL information. It determines that this particular method (GetUserProfileByName) expects a parameter to be passed to it called “AccountName”. We are prompted to supply a value for “AccountName”. Fortunately for us, if we do not supply a parameter here, then the web service will use the account name of the currently logged in user! This is exactly what we want. This method will automatically use the username of the current person without us having to manually specify it.

Thus, we can leave this screen as is and click NEXT. The final message asks if we wish to connect to the web service now to retrieve data, but in this case we do not.

image_thumb[32] image_thumb[34]

We have now finished configuration. We are asked to save this connection and give it a name. (As you can see below the name defaults to the method name, so we will leave it unchanged. We also tick the box to connect to the data source as soon as the form is opened).


Using the data connection

We have an InfoPath data connection to this web service. Now what?

Well, let’s go back to our employee name text box and change the default value to something returned by the web service method GetUserProfileByName. As we did earlier, we click the function button, but this time we are not doing to add a function. We will instead use the “Insert Field or Group” button.

image_thumb[38] image_thumb[40]

By default, InfoPath has a “main” Data source, which is the form itself. We now have to tell InfoPath that the default value for the employee name is going to come from a different data source. On the data source drop down, choose secondary data source called “GetUserProfileByName” from the drop down as shown below.

image_thumb[42] image_thumb[44]

Notice that the fields available to choose for the GetUserProfileByName datasource looks very different to the main datasource. This is because the web service returns data in a particular format, and it is now up to us, to interrogate that format to get the information we want.

The GetUserProfileByName method returns a whole bunch of user profile values, way more than the stuff we specifically want. It returns these details in a name/value format as shown below:

Name Value
FirstName Homer
Lastname Simpson
Office Sector 7G
Title Safety Inspector

… and another 42 more name/value pairs like the above!

So we will have to tell InfoPath to examine the full list of “Values”, and find the specific value for “FirstName”.

Note that firstname and lastname are separate items. There is no property called “FullName”. We will deal with this a little later.

Now although telling InfoPath to filter the user profile information is achieved using a wizard, it is not the most intuitive process. Once you have done it a few times, it becomes second nature. But be warned – you may be about to suffer death by screenshot…

Recall in the last screenshot we were looking at the data returned by the web service method called GetUserProfileByName. We need to filter the 46+ possible profile values to the specific one we need. Now you know what that “Filter Data…” button does ;-)

From the list of fields returned by this method, choose “Value” and click “Filter Data”.


We have now told InfoPath that we want one of the “value” fields, but we need to filter it to the specific value that we need. That value is “FirstName”. So in effect we are saying to InfoPath “give me the value for the property where the property called Name = “FirstName”. The sequence of screenshots below shows how I tell Infopath to use the Name field as the filter field.

image_thumb[47] image_thumb[48]

Now we have to set name to equal the property name “FirstName”. The next two screenshots show this.



Click OK and you now have your formula as shown below.


Click a zillion other “OK” buttons and you will be back at your form in design mode. Clicking the “Preview” button and check the Employee name field. Wohoo! It says “Homer!”


But I said “full name” not “first name”…

So, that’s great. Although connecting to a web service and telling it to retrieve the correct data is tricky and requires some training, no custom programming code was written to do this. But alas, poor old Homer still get’s a little confused and we wish to see the full name of the person filling in the form.

Fortunately, this is actually quite easy. We just need to grab the FirstName and the LastName values from the web service and then join them together.

I demonstrate this below (with an adjusted formula – for the sake of article length I’ve not added the screenshots used to create this formula. I am hoping that I gave you enough to figure it out for yourself).

Application developers or Excel people will quickly see that I have retrieved the “LastName” value using the exact same method described in the last section for the “FirstName” value. I then used the built-in function concat (concatenate) to join FirstName and LastName together (with a space in between).


Let’s now preview the form… Wohoo!


So, just to be completely sure, we republish the form, following the steps in part 4 and examine the form in the browser to make sure that it works there as well.


Conclusion (and further notes)

So amazed at your ingenuity, you have managed to get InfoPath to retrieve the details it required so that the user did not have to fill in their name manually. There was certainly a lot more to it than using a built-in InfoPath function, and (for the first time anyway), probably took you a little while. But the main thing is, you have satisfied the first requirement and Waylon Smithers is now happy. He is a little bewildered in all the low-level web service stuff and concerned about how easy it would be for some of his staff to create forms, but regardless, everything was performed via the InfoPath graphical user interface.

So in the next post, we will look at two ways we can deal with the automatic retrieval of the employee number.

Thanks for reading

Paul Culmsee

P.S: Do not attempt to explain the low level details of pulling values from a web service in a presales demo :-) .

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

A tribute to the humble “leave form” – Part 4

Send to Kindle

[Note: It appears that SharePoint magazine has bitten the dust and with it went my old series on the “tribute to the humble leave form”. I am still getting requests to a) finish it and b) republish it. So I am reposting it to here on cleverworkarounds. If you have not seen this before, bear in mind it was first published in 2008.]

Hi again. Welcome to part 4 of my tribute to the “leave form”. It’s probably time to just get straight into the article, as I think the whole leave form joke isn’t overly funny anymore :-)

Crap, there is probably going to be a dozen or so articles and I’m running out of jokes already? But then I suddenly remembered that in this certification obsessed IT world we live in, I could make up my very own certification! I mean how hard can it be? Just add the word “institute” to your name and your legitimate right?


Therefore I am pleased to unveil the first of a series of certifications to be offered by the CleverWorkarounds Institute and its associated body of knowledge (CIBOK). The CCLFD or CleverWorkarounds Certified Leave Form Developer certification, which allows certified practitioners the opportunity to feel vastly superior to the mere mortals they work with. Additionally, it has waaaay more bragging rights than one of those weenie certifications like MSCE or CISSP. Of course, like all acronyms to add to your business card, you are certainly guaranteed a big, fat pay rise.

So what someone wants to validate your credentials, you just flash the badge below and they will all be like…”wow, this dude is a business process god!”

SharePoint Pre-Requisites

Right enough banter and back to business! Now remember, we are in “Business Development Manager” mode in this post and therefore we are trying to impress here. Selling the client the message that the you will solve all organisational process issues (and find the answer to world peace) in InfoPath and Forms Services. So far, we have fairly quickly produced a usable InfoPath form. All in all it has taken maybe 10-15 minutes of demo time. So in the interests of “impress the client at all costs”, we have done enough to publish it straight into SharePoint, so that it is usable via a web browser.

That CCLFD certification is looking good yes? :-)

So first up, we need a SharePoint site to publish the site to. But not just *any* SharePoint server. It needs to be running the enterprise licence if you wish to use InfoPath Forms Services.  So sorry stingy people, WSS and Office SharePoint Server 2007 Standard Edition will not suffice.

Now if this were a demo, I would have already had this set-up in advance to make the client “ohhh”, “aaah”, “wooow” louder. But I think it is worth examining the prerequisites in more detail.

Assuming that the SharePoint farm been built and a web application has been created (called http://radioactivenet), it is time to create a brand new site collection to host this form. The SharePoint farm administrator performs this task in Central Administration -> Application Management. In this example, we are creating a completely blank SharePoint site.


Have we done enough to publish our form to SharePoint? Unfortunately, not quite. In fact if we tried to publish our leave form into the site as it is now, we would have a little warning message that easy to overlook and then cause you no end of wasted time.


But I am getting ahead of myself. Having created this new site collection above, we now need to enable the enterprise feature-set that includes the forms services component that we need. For the purposes of this post, I will not be explaining the SharePoint feature framework, but for the uninitiated, SharePoint is like a fisher-price toy with lots of buttons that have different effects when pushed. (We haven’t yet pushed the right button). Think about SharePoint site templates (e.g Document Workspace, Wiki Site) as automatically pressing the buttons for you.

To activate the enterprise featureset, we log into http://radioactivenet as an administrator or site collection administrator. Choose the “Site Actions” drop down menu below the search box. Choose “Site Settings” and you will be taken to the administrative back-end of a SharePoint site collection. I have highlighted the option to control site collection features. (Click to zoom)

image image

This screen shows you all of the nice buttons to push. The one that we need for this demonstration is the “Office SharePoint Server Enterprise Site Collection Features”. Simply the click “Activate” and you are done!

Finally, let’s publish it already!

To remind you all of where we got to at part 3, we have a functional form looking like this. (Click to expand)


Publishing an InfoPath form into SharePoint is based on a Wizard. We want to impress here, so we will do a straightforward form publication. Start the wizard by choosing “Publish” from the “File menu”. The first wizard screen will ask you *where* you wish to publish to. Obviously we go with the default answer of SharePoint :-)

image  image

Next we are asked to specify which SharePoint site we would like to deploy this new form to. We are using our newly created http://radioactivenet site.


Now we hit the first important step in the whole process. This is the step where InfoPath publishing wizard asks you to how to publish this form. While I am not going to explain each option in detail at this stage, I will warn you now that we are going to come back to this screen in subsequent posts.

Notice how “Enable this form to be filled out by using a browser” is checked? This is because this InfoPath form that we created has been set to be “browser compatible” way back in part 2 when we first created the form template.

The first and default choice is to create the form in a SharePoint Document Library. Thus in the SharePoint world, InfoPath forms are just another type of library storage. therefore you can can use version history, recycle bin and all of the other goodies that come with SharePoint document libraries. Now I should make it clear though, that SharePoint creates a special type of library called a “Form Library”, designed specifically for InfoPath forms. So we leave “Document Library” chosen as the default and click continue on.


Having chosen to use a document library we are now asked to create a new one or choose an existing library. In our case, we have not yet created the library to hold the leave form, so we choose to “Create a new document library” and then enter it’s name and description into the follow-up dialog box. This will create a form library called “Leave Forms” for us as part of the publishing process.

image image

The next step is another important one. But in the interests of our “impress the client at all costs” focus, we are going to leave it until the next post. So we click Next to continue.


That’s it for the publishing process! Clicking the Publish button will commence the process. InfoPath will create the form library and when it finished provide us with a summary of what has been performed as shown below. You can see the name of the library where the form was published (”Leave Forms”), the URL of the SharePoint site (http://radioactivenet/leave forms) and the fast that Infopath Forms services is running on the server.


The new form library

So, are we ready to “wow” the client yet? Almost!

Looking at the http://radioactivenet site, we can now see that we have a new content item in the navigation. Well what do you know? :-P It’s a leave form library!!


If you navigate to this document library, you will see that it pretty much looks like any other SharePoint document library. Clicking the New drop down in the document library though shows that something is subtly different with this library. The document type to create has the InfoPath symbol on it. This library knows that it is here to store InfoPath forms.


But there is one minor catch. By default, SharePoint assumes that if you have InfoPath installed on your PC, then you will always prefer to use InfoPath in preference to a more limited, somewhat “clunkier” web based version. In our case, we want the form to show up in a browser, irrespective or whether InfoPath is installed on you PC. So let’s do that now.

In the new Leave Forms library, we choose “Form Library Settings” from the “Settings” drop down menu as shown below.


Now remember how I said that a forms library is a document library geared especially to host InfoPath forms? Well, now you get to see why. One of the configuration options unique to a forms library is whether to display a form in a browser or using InfoPath. The option controlling this can be found in “Advanced Settings” as highlighted below. The third option under advanced settings controls the behaviour for “browser enabled” documents. Note that the default option is to “Open in the client application”.

image  image


So let’s change this to “Display as a web page” and click OK. Also note the second option, “Document Template”. We will be talking about this a subsequent post as well.

The big test!

So, all is now in place and it is time to demonstrate the functionality to your eager and excited clients. Go back to the home page of http://radioactivenet and choose “Leave Forms” from the navigation bar. In the document library view, choose “New Document” from the “New” drop down.


Wohoo! The form renders in the browser! (and the crowd goes wild! :-) )



So there you go, in something like 20-25 minutes, you have created and published a web based form that started life as a MSWord template. Highlighting how long this world have taken to be custom developed, your client is already writing a cheque and wants you to do this with all of their internal forms right???

Whatsmore, we haven’t even gotten to the unbridled joy that is a workflow yet! Oh man if the client likes forms services, they aint seen nothin’ yet right???

Well, to be honest, this form really isn’t that smart yet is it? Before we even get to workflow considerations, the form still isn’t *that* friendly and we haven’t yet satisfied several of the requirements outlined in part 1. Remember that we had to provide:

  • Automatic identification of requestor
  • Reduce data entry
  • Validation of dates, specifically the automatic calculation of days absent (excluding weekends)
  • Mr Burns is not the only approver, we will need the workflow to route the leave approval to the right supervisor
  • We have a central leave calendar to update

So in the next post, we will make this form a little smarter and see if it is still within the realms of mere mortals (non developers) to do.

Thanks for reading


 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

A tribute to the humble “leave form” – Part 3

Send to Kindle

[Note: It appears that SharePoint magazine has bitten the dust and with it went my old series on the “tribute to the humble leave form”. I am still getting requests to a) finish it and b) republish it. So I am reposting it to here on cleverworkarounds. If you have not seen this before, bear in mind it was first published in 2008.]

Hi and welcome to part 3 of a series of article that are dedicated to raising awareness of the plight of the much-misused leave form. Long has it been the favourite “real world” example of consultants and IT departments worldwide, to demonstrate how SharePoint product features can be used to achieve a utopian dream of reliable, consistent and optimised processes.

The leave form is a phenomena that is enigmatic. It’s simplicity and suitability to demonstrate the concepts of SharePoint cannot be denied. But at the same time, you need to be very careful since it can set expectations that can be difficult to meet when it comes to other business processes that are more complex and representative of more critical business function. In this series I am neither promoting nor dumping on the suitability of the leave form as a real-world example. Instead, I hope that the reader will be in a better position to make up their own mind as we progress through the series and it slowly gets tougher.

To that end, at the end of part 2, we were left with an incomplete InfoPath form for the Springfield nuclear power plant. This form was created by importing an MSWord version of the original form, along with a little graphical manipulation. So let’s pick up from where we left off …

The objective is part 3 is to get the form functional and into a form ready to be published to SharePoint.

InfoPath Next Steps – Adding Controls


Okay so here is where we are at (click to see the large version of the form above). We have finished the graphics in the header of the form, and now we need to turn our attention to where the action happens. InfoPath attempts to automatically create form textboxes, checkboxes and buttons based on what is has gleaned from any MSWord or Excel based form that you have imported. In my Springfield Nuclear Plant form, during the import, it created checkboxes for us for the leave types, but did not create textboxes for the other fields that we require. What you will notice however, is that InfoPath created a table to hold this information as shown below.


So we need to now create those fields (called “Controls”) to allow the user to fill in the form.


In the “Design Tasks” pane, click the “Controls” link, and you will be presented with the interface to add controls to the form (see the screengrab above). I am not going to explain the functionality of each InfoPath control in this article, but what I will say is that we will use three of controls from those available. The “Text Box”, the “Option Button” and the “Date Picker”.

TextBox Controls

First up, let’s deal with the “Employee Name” field. We will make this a text box so that an employee can enter their name.

Click on the “Text Box” control in the “Design Tasks” pane and drag it across to the blank cell next to the text “Employee Name”.

The result will be a fairly nondescript text box in that cell. InfoPath has named this cell “field4″, and if you try this yourself, you will receive a similar name.


Why is this control called “field4″? It is because the original import from MSWord created three checkboxes for the Leave type. Therefore they were named, “field1″, “field2″, and “field3″, respectively.

Now field4 is not a good name for a text box. So we will change its name to something that makes sense. (Why we do this will become apparent in future posts). Right click on the newly created text box and choose “Properties”. In the next dialog box, change the field name from “field4″ to “EmployeeName”.


While we have this dialog box open, it is worth examining some of the other options available here. We can

  • Set a default value for the text box
  • Set some validation properties. For example, ticking the “cannot be blank” box means that the form cannot be completed unless this textbox has been filled in. When you think about it, it doesn’t make a hell of a lot of sense to have a leave form without the employee name being mandatory (and therefore not blank).
  • Set some rules. Rules allow actions to be taken *after* data has been entered into the form. (We will be utilising rules very soon, and will examine this in more detail then)

We have some additional text boxes to create as well. The “Employee Number” and “Comments” fields will also require text box controls to be added to the form.

The steps are identical to what we just performed for the “Employee Name” field. Drag them to the form, and rename them via the properties menu.

Once the textbox controls have been added to the forms and renamed, the form now looks like screenshot below. (The “LeaveComments” textbox is a single line by default and has been resized)


Date Picker Control

Usually by this time in a demonstration situation, clients are starting to get interested, as they can see where we are going with this. Have a guess what the “Date Picker” control does? By jove, it picks dates! Who would have thought?

Click on the “Date Picker” control in the “Design Tasks” pane and drag it across to the blank cell next to the text “Commencement Date”.

Do the same for “Completion Date” and “Return to Work Date”.

As per the textboxes, rename the controls to match the data being entered. So for the Commencement Date field, rather than “field6″, make it “CommencementDate”.

Below shows the result of performing this task on all three date fields.


Option Button Control

With the option boxes for the type of leave being requested, we have a little more work to do. When InfoPath imported the MSWord document, it decided to use checkboxes for the leave type. As it happens, this is not the behaviour that we want because checkboxes allow multiple selections.

Instead we want only one option to be selected. So we will delete the controls created by the import, and add back “option button” controls in their place.

Clicking on each checkbox on the form, we can delete the existing ones with a tap of the delete key.

Now click on the “Option Button” control in the “Design Tasks” pane and drag it across to the the left of the word “Annual”. Unlike the other two controls that we have used, this time we need to supply more information before it gets added to the form. We have three types of leave on this form, and as it happens, InfoPath defaults to three option buttons.


Click OK, and you will see the screen is now slightly messy. We have our three option buttons, but they are all in the one cell, and are all labelled the same (Field11 in my example).

Why are they labelled the same you may ask? The reason is that although there are three option buttons, they are all linked as a single field. So if I right click on any one of the three option buttons and rename it via the properties dialog box, all three option buttons are renamed! If you are wondering why this is, consider that the whole point of using option buttons is because you can only select one option. If you can only select one option, then you only have one value to store.

Right click on the first option button and I will show you what I mean.

Below I have renamed the first option button to “LeaveType”. Look closely and you will see a “Value When Selected” text box.

I have changed the value to “Annual”, to represent annual leave being selected. I have also set it as the default value for the checkbox as most of the time leave is annual (vacation leave).

After cllicking OK, you will see that the other two controls are also now named “LeaveType”.


Now right click on the second option button and examine its properties. You will see that it is named LeaveType. I have changed the “Value when selected” field to “Sick”, to represent sick leave. For the third option button I have labelled it “Bereavement”

image image

The final thing to do is now to move the last two option buttons into the proper cells and tidy it up. This is a straightforward drag and drop operation. While here I have also moved a few of the other cells around and resized them. At any time you can preview your work by clicking the “Preview” button on the InfoPath toolbar.

At this point, a preview looks like this:


Looks nice enough to me (but hey I’m not hired for my design skills!). Note that the Employee Name field is marked with a red asterisk, meaning that it must be filled in. Additionally the date fields now have buttons allowing you to easily pick a day from a calendar view.

Are we done yet? I wanna get to SharePoint…

So here we are with a semi-functional InfoPath form. At this point we haven’t really used SharePoint at all! So in the next article I will cover publishing the form in its current state into SharePoint and viewing the form within a browser.

We still have some significant work to do however, since we haven’t covered all many of the requirements yet. For example, we still haven’t covered data validation in the form. Additionally, we do not yet have a field on the form showing the number of days absent (incorporating the calculation of weekends). The form doesn’t automatically fill in your name when the form is loaded either. Finally, we will also want to add a SUMBIT and CANCEL button to the form.

Until then, thanks for reading

Paul Culmsee

 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

A tribute to the humble “leave form” – Part 2

Send to Kindle

[Note: It appears that SharePoint magazine has bitten the dust and with it went my old series on the “tribute to the humble leave form”. I am still getting requests to a) finish it and b) republish it. So I am reposting it to here on cleverworkarounds. If you have not seen this before, bear in mind it was first published in 2008.]

Hi again and welcome to the next exciting instalment in the series that pays tribute to the consultant “get out of jail free” card that is the organisational “leave form”. My experience of SharePoint implementations may be somewhat skewed by regional and/or cultural bias of course, but many SharePoint installations tend to follow a script of something like

  • IT Manager attends an “information session” from a Microsoft Gold Partner, has one two many chardonnays and is convinced that SharePoint is the *answer*, but isn’t sure of the question just yet…
  • IT Manager calls aforementioned Microsoft Gold Partner and I am sent out to dazzle them with my wit, charm and technical brilliance
  • IT Manager agrees with me that SharePoint has to be sold to (and owned by) “the business”, so chooses the first candidate business process that pops into his head
  • All hail the mighty “leave form

Me sarcastic? never! :-)


Note: I was recently certified as a Microsoft Certified Trainer for SharePoint, and I am using this series as practice for my training material! Thus this entire series of articles is pitched at a very high (end user) level. Readers with some exposure to SharePoint may find this article from the series is particularly easy-going, but rest assured, by the time I am done, I will be delving into web services, code and all sorts of goodies. Writing that stuff for the non developer will be a challenge – so stay with me, I will slowly ramp up the concepts as we go on.

So to recap the introductory article, we have documented the leave application and approval process for the Springfield nuclear power plant, and they have kindly supplied us with their existing leave form that their employees print out and fill in by hand. So our very first job is to see how InfoPath handles importing this MSWord based form to InfoPath.

InfoPath 101

Even though InfoPath is part of MSOffice, many people do not know about it, let alone used it. If you have never seen InfoPath before now, then I suggest you do some reading about it and maybe even download the evaluation.  My one sentence explanation is that it allows you to create electronic forms for data entry or data collection. Among its features is that it can import Word or Excel documents to speed up the form creation process. So let’s do that first up.

Upon starting up InfoPath 2007 we are presented with a “Getting Started” wizard. Our job is to design a new form, so the option to choose is to Design a Form Template as marked in the screen-grab below.

The next screen is to choose various options in relation to designing a form. We are starting from a completely blank form (which is the default option anyway), but this form is going to web browser based, so that people filling it in do not need InfoPath installed on their PC’s. SharePoint enterprise edition provides support for browser based forms via the “Forms Services” feature. InfoPath 2007 has built-in support for forms services, but the form must be marked as “browser compatible”.

This is not a default setting, so be sure to check the “Enable browser-compatible features only” box before clicking the OK button.

image image

Now we are at the main form designer screen within InfoPath. Being part of the MSOffice suite, it has a set of toolbars that looks very much like the other applications in the MSOffice suite. To the right of the InfoPath screen is the “design tasks” tool pane. I’ve shown it below.


You will come to know the “design tasks” pane very well soon enough. From here we can perform all sorts of actions to construct a new electronic form such as create textboxes, buttons, drop down lists and the like. Not only that, but we can instruct InfoPath to connect to “data sources” to populate the values in say, a drop down list box. Imagine for example that all of your clients are listed in your CRM system. InfoPath has the capability to access those client details and display them on the form. Think of the data entry duplication that this will save,  not to mention the  maintenance of data accuracy, eh?

But hey, we are here to impress the clients with our technical wizardry and to show how InfoPath allows non-developers to create sophisticated electronic forms. So what we will do first-up is import the existing MSWord based leave document and see what we get out of the box.

Importing the old form

So, you should be at the main InfoPath screen to perform this action. From the “File” menu, choose “Import Form”. On the next screen, choose “InfoPath importer for Word documents” and click “next”

image   image

You will be prompted to browse to a file to import. In this example, I have chosen the “springfieldleaveform.doc”. The “options” button allows you to control the specifics of the import process. In this example, I will simply import the form with all of the default import settings.

image   image

Click “Finish” and InfoPath will do its thing – viola! We have the beginnings of a leave form!



Ah, but wait, what happened to the graphics? The original MSWord form had two pictures in the header of the document – a 3-eyed fish and a picture of the towers. It turns out it seems, that graphics in the header or footer of an MSWord document do not get imported into InfoPath. Bummer – I really liked that 3-eyed fish!

But fear not, importing graphics is as easy as a cut and paste. If I open my original word version of the form, I can double click near the top of the document, and the header/footer will now be available to edit as shown below.

image   image

Now you can click to highlight both images and they will paste into InfoPath. Not exactly earth shattering, is it?


It’s still not right…

Okay, so it wasn’t a perfect import. In fact I’d give it about a 6 out of 10. But the point is we have our images and our text inside InfoPath. Also to be fair, MSWord files were not designed for the web either, so it is unsurprising that it wasn’t flawless.

So, now we can use the native features of Infopath to tidy up this form. First, we will tidy up the arrangement of the two images, and then we will add some graphical elements like textboxes and option buttons to allow the form to be electronically filled in. We will create a table on the form that looks like this


In the right-hand “design task” bar, we select “Layout” and then choose to create a “custom table”. Choose a 2*2 table and you should see the table as shown in the third image below (look above the fish graphic).

image  image image

The next step is to format the table correctly. Highlight the two rightmost cells, right click on them and choose “merge cells”. The result should be a table looking like the rightmost image below.

image   image
Now drag and drop the three-eye fish to the leftmost cell and the plant picture to the rightmost cell of the top row of the table. Your result should look something like this.


Now highlight the remaining title text, and then cut and paste it into the empty bottom left cell . Aha! We are starting to look better! A bit of resizing of the table cells width and it is looking more like the original.


Are we impressed yet?

Okay, sorry that is very much a rhetorical question, I know. I haven’t exactly written the most technically complex article ever.

But at least we have gotten the InfoPath version of the form to look much more like the MSWord version. If I had been running that as a client demonstration, it all in all would be around 2-3 minutes work. Is the client blown away in amazement and opening the chequebook yet?

Not exactly, but we haven’t gotten to the good stuff yet. In part 3, we will add some textboxes and other graphical elements (called “controls”) to the form, and then we will do some smart things to reduce manual data entry, as well as ensure that the data that we have collected is accurate.

Bye for now

Paul Culmsee


 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

A tribute to the humble “leave form” – Part 1

Send to Kindle

[Note: It appears that SharePoint magazine has bitten the dust and with it went my old series on the “tribute to the humble leave form”. I am still getting requests to a) finish it and b) republish it. So I am reposting it to here on cleverworkarounds. If you have not seen this before, bear in mind it was first published in 2008.]

Hi all. It’s a pleasure to be involved in the launch and first edition of SharePoint Magazine. My name is Paul Culmsee and I’ll be your host for this series of articles. If you have not read my stuff before, then I’ll say that I am an opinionated, underpaid and overworked SharePoint consultant based in Perth, Western Australia. clip_image001

I had previously decided to write an educational series of articles to pay tribute to the humble, good old leave (vacation) form and I think it is perfect fodder for SharePoint Magazine’s wide variety of audience.

Where would SharePoint consultants worth their grain of salt be without the leave form, eh? When all else is lost, there it is to save your ass from the wrath of the CIO who is wondering where his two hundred grand of license fees, hardware and programming went.


Why the leave form?

From a demo perspective the leave form is pure gold. You can knock out an InfoPath form in minutes, publish it to a SharePoint site using Forms Services and top it off with an easy-to-understand SharePoint Designer workflow that creates some tasks for the boss to approve the request and to notify payroll of the approval. All within the space of a 1 hour demo session. Genius! No wonder Microsoft sell all of those licenses!

For a client who is still coming to grips with the possibilities what forms and workflow offer, the leave form is an excellent starting point. It is a simple process and almost universally understood. There really aren’t that many owners/stakeholders involved in the process, and thus even the most extreme anal-retentive “process nazi” can’t really make it too onerous. So turning this process into a “non-programmer” workflow is not that hard.

Being a simple process, you can use SharePoint Designer workflows. Now some developers reading this will probably start protesting, and believe me I know where you are coming from. But let’s face it – you guys are damn expensive!

Thus, SharePoint Designer based workflows are a great *prototyping* tool. Non programmers can develop them, and making modifications and changes do not require a lot of time or cost. For an organisation unused to workflows and the inevitable “process debates” that arise as a result, delving straight into Visual Studio and expensive developers I do not recommend. Workflows tend to evolve fairly quickly at first as people learn more about them. Additionally, whatever you *think* you want in the first phase has a very high likelihood of being ripped out or seriously modified once it starts to get real-world use.

So in using the leave form, we are using a process that is well suited to a SharePoint Designer based workflow. Once the process is mature and you have enough SharePoint experience to appreciate the governance costs, then you can rewrite it as a “proper” Visual Studio based workflow template.

Why not the leave form?

The leave form unfortunately is not representative of the sort of process where automation or improvement justifies a SharePoint investment. If your company is suffering a cash-flow bleed because you can’t get your leave forms done, then I can say with some confidence that you are *seriously* screwed and SharePoint isn’t going to solve your issues.

The point is, the leave form is not going to have too much real business relevance in terms of tangible return on investment. In fact the leave form is *too easy*. As a demo, it can mislead an organisation into thinking that the answer to life, the universe and everything is contained within the everyday world of mere mortals armed with nothing more than InfoPath and SharePoint Designer.

The real life of organisational process and workflow is completely different. Most workflows tend to be more complex because they involve more teams and team members. Because they involve more teams, they have a tendency to be unoptimised, undocumented, inconsistently followed and over-complex, due in part to to past screwups, lack of co-operation and organisational mistrust and politics. This is a reflection of much bigger issues than SharePoint of course, but to entertain the notion that SharePoint is going to miraculously change cultural issues is about as ludicrous as suggesting that Guns N’ Roses will actually ever release their “Chinese Democracy” album anytime soon.


Believe it or not, the image above is a real workflow. Check out this story behind it here – it’s funny in a very scary kind of way. Whilst this may be an extreme example, it should hopefully make it clear that despite best intentions, your first few attempts at trying to improve something like this via SharePoint aren’t likely to go all that well if your process is crappy to begin with clip_image004

Why use the leave form an example then?

That’s easy! I actually want to finish this series of articles in a reasonable time!

Additionally, it still suffices to demonstrate fairly convincingly how it doesn’t take very long at all before we need to delve deeper into the potential no-mans-land of custom development. So the outcome of this series of articles is two-fold.

  1. Readers will get a good understanding of the tools and SharePoint features that combine to produce an automated version of the leave form process
  2. (But more importantly) They get a glimpse behind the virtual green door of InfoPath and SharePoint’s dirty little secrets.

Is it humanly possible to write a series articles for normal people *and* technical geeks? We shall soon find out!

A recent real-world engagement clip_image004[1]

The leave process that I have outlined here is going to have a little more depth to it than the sort that would be demonstrated in a pre-sales demo, but it is still not industrial strength. I’ve put enough in there to assist the reader to really understand just what it takes to implement a semi-real world case.

I hope that readers have watched the Simpsons!

Conveniently for all of us, my company Seven Sigma’s office in Springfield recently completed a SharePoint engagement for the local Nuclear Power Plant. After some initial requirements gathering, we ascertained that like most companies, the leave process for the plant was problematic. It was an MSWord file that employees have to open, print out, fill in and then hand to their boss. The boss (some old-dude named Burns) was an old-fashioned, unpredictable kind of guy and he tended to forget the names of particular employees. Thus sometimes applications went unprocessed, misfiled or inconsistently handled.

Below is the leave form in its original MSWord format.


After gathering requirements and running some workshops, we were able to determine what the client wanted with their automated leave form workflow…

The Requirements

Requestor Approver Payroll
image image image

The leave workflow steps to be implemented are as follows.


  1. Hardworking and dedicated employee (Requestor) completes an online form to apply for hard earned leave. The form automatically identifies the requestor (a good thing because spelling your own name can be hard). Additionally, the type of leave (sick, annual, bereavement, etc), start date, end date and the return to work date are all entered into the form. Importantly, the number of days absent from work are automatically calculated to exclude weekends.
  2. The evil overlord boss (Approver), receives a task notification to approve an application for leave. The approver reviews the leave details and approves or rejects the leave application. If the leave is approved, proceed to the next step, otherwise the leave is rejected and proceed to step 8
  3. Evil overload boss curses industrial relations laws allowing employee leave in the first place, but belatedly marks the leave request as approved.
  4. Requestor is emailed a confirmation that his application has been approved
  5. The leave is added to the corporate leave calendar
  6. Blatant brown nosing suck-ups (Payroll) are notified by email of the approved leave and adjusts leave remaining in the HR system
  7. End of workflow
  8. The evil overlord boss (Approver) has had a call from the Nuclear safety watchdog and all safety inspectors need to be on-hand to hide the evidence. Thus the approver rejects the leave application
  9. Requestor is emailed a confirmation that his application has been rejected with the reason why
  10. End of workflow

From the above process, a number of key requirements are apparent and some more were determined.

  • Automatic identification of requestor
  • Reduce data entry
  • Validation of dates, specifically the automatic calculation of days absent (excluding weekends)
  • Mr Burns is not the only approver, we will need the workflow to route the leave approval to the right supervisor
  • We have a central leave calendar to update

Additionally, Mr Burns likes to keep an eye on things and has a large series of monitors in his office that he uses to watch what is going on around the plant. Thus, he requires a dashboard that shows him a birds-eye view of the leave process from end-to-end.

Next steps..

The first step is to convert the existing manual leave form into its InfoPath equivalent. So in the next exciting article, we will get to see just how easy (or not) InfoPath really is!

Thanks for reading

Paul Culmsee


 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle

Powerful questions part 3: The “I told you so” question

Send to Kindle

Hi all

I just recorded the third video on the topic of powerful questions. The purpose of this series of videos is to help facilitators, project managers, business analysts and SharePoint peeps ask better questions of their stakeholders. The first video introduced the platitude buster question and the second video unveiled the key focus area question. Both are hugely important – especially for SharePoint projects and any SharePoint governance efforts because failure to answer these two will positively kill your project. This 3rd powerful question is related to risk perception and how you can frame questions to get a much better sense of what the real risks are in projects or problems. In this video, I made the contention that asking “What are the risks” is not a great way to identify and subsequently manage risks. The inference for SharePoint people here is that if you think you have done your job by creating a risks and issues list (ala Project Server) and asking for people to fill it in, I am here to tell you that there is much more to the story…

Don’t believe me? Then watch the video. 

Like the previous post, I suggest you watch this video in full screen. Enjoy!

How to find out what the real risks are…
 Digg  Facebook  StumbleUpon  Technorati  Deli.cio.us  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle