It's been a very long while since I've blogged. Just been really really tied up exploring every nook and corner of SharePoint 2007. I have received a HUGE number of messages from my site, asking if I am writing a book on SharePoint. Well I am not, but await a big announcement on similar lines.
In this post I am going to solve some very critical problems every InfoPath 2007 developer faces, centered around customizing forms server, and giving it exactly the look and feel you need, or exactly the workflow/pageflow you need, alongwith demonstrating a somewhat complex (yet simple) application.
The problem description -
Northwind Traders have a SQL Server database called "Northwind". They have a table there called "Customers". While the system works, there is a certain power user who wishes to be able to maintain "Notes" for a given customer. While he could involve IT in developing such an application, he wants to try his hand at SharePoint, and be able to connect Northwind.Customers to a SharePoint list, and use SharePoint to store notes (or anything such) for a given customer.
Thus, in this example I am going to demonstrate a single infopath form, hosted in sharepoint/forms services, that is rendered through the browser, and has the ability to aggregate data from both a SQL Database, and a SharePoint List. Additionally, I am going to make this form in such a manner that the user doesn't have to deal with any "infopath-ness". i.e. no specifying a .xsn filename, it will talk/walk/and behave 100% like an ASP.NET app. 100% flexibility/customizability.
Lets get started.
- First step is to make sure that you have VS2005 SE installed. While this entire operation can be done via Infopath, I am going to be cool and use Visual Studio instead :). Okay, not just to be cool, but someday in the future, I intend to complicate this requirement further, and add code-behind using VSTA.
- In VS2005, create a new Infopath Form Template project. Now the name is misleading. This is a template for infopath forms. So it can do both Infopath forms, and infopath template parts :). We are going to create a blank Infopath Form, that has browser compatible features enabled only, as shown below.
- Great. With the form created, go to the "Design Tasks" pane on the left and click on "Data Source".
- In the "Data Source" pane, at the bottom, click on "Manage Data Connections".
- Add a new Data Connection using the following steps.
- Click "Add" in the "Manage Data Connection" window to pop open the Data Connection Wizard.
- Choose "Create New Connection to Receive Data" (We will first create a connection to receive CustomerName info).
- Choose from "DataBase (Microsoft SQL Server Only)"
- Hit "Select Database" and in the ensuing File Open Dialog, choose "+NewSQLServerConnection.odc".
- Specify the name of your SQL Server, use Windows Auth. SQL Auth requires some additional settings in CA that I am going to skip for this blogpost.
- Choose Northwind as the database, and "Customers" as the table.
- The next screen will prompt you to save the .odc file it created in your "My Data Connections" which is under "My Documents". To me personally that was weird, as I like to know where I am storing stuff - not all over my disk, but whatever, go ahead and save it there. You don't HAVE to store it there :), you could even store it in a doc lib on SharePoint, and it'll work just fine.
- The next screen prompts you for what columns you are interested in, in our case only the "ContactName" column is useful, but it really doesn't matter, so I left this screen as is.
- When prompted to "store copy of this data in the template" - leave that unchecked.
- Finally, give the connection a name. I called it "Customers".
- Okay good. The next step is to start constructing your form. Go back to "Design Tasks", and click on Layout. Add a Table with a Title - you can make it as pretty as you want, I am going to keep it basic.
- From the toolbox, drag drop a drop down list on the surface of the form.
- Right click on the Drop Down List Box and choose Drop Down ListBox Properties, and change the name of the control to customerName, and under "List Box Entries", modify as shown below -
- Next, drag drop a TextBox on the surface of the form. Resize it, so it becomes a multiline, and right click --> Properties on this textbox, and give it the name "customerNotes".
- Next, you need to perform such magic to be able to submit this form. We intend to submit this form to a document library on sharepoint. So, do the following.
- Create a new document library on sharepoint called "Customer Notes". This will be a "Forms Library".
- Now just as you had created a data connection to receive data, now create a data connection to submit data. Instead of a database, you will now be submitting to a SharePoint list document library. The only kicker here is that in the Data Connection Wizard, when specifying the document library, and filename, choose to create the filename based on a formula as shown below.
In effect what you've done is, the user now doesn't need to specify a filename. The filename will now automatically be the same as the customer name. Also go ahead and checkbox the "Allow overwrite if file exists" checkbox.
- Finally save this data connection, call it "Customer Doc Lib Submit" or something like that.
- Next, add a button to the bottom of the form. The purpose of this button will be the "submit" the data back to a document library. Customize the button using the following steps.
- Right click the button, choose "Button Properties".
- Under "General" choose "Action" as "Submit".
- Click on "Submit Options", and modify the submit options as shown below -
- Your form, at this time is done, and with some lipstick will look like as shown below -
- Your form, at this time, in design mode should look like as below -
- Good. Now that your form is done, we need to make it work on Forms Services. In order to do so, go to design tasks, and click on "Publish this form template", and follow the following steps -
- In the publishing wizard, choose, submit to SharePoint server with our without Infopath Forms Services.
- Enter the location of your MOSS site where you want the form to sit.
- Now you might be welcomed by the following -
WTF? What does it mean - "This form template is browser-compatible, but it cannot be used browser-enabled on the selected site"?????
I made sure I was using a browser compatible form!! WTF!. Well, what you need to do is, go to central administration, and under Operations, under "Upgrade and Migration", click on "Enable Features on Existing Sites", and check the checkbox that allows all sites on the farm to use Office Enterprise Features (including forms services). Hit OK, and restart with the publishing wizard to publish the infopath form, ONCE, the enterprise features have been enabled on all sites on the farm.
- Assuming that you have enabled enterprise features, check the checkbox "Enable this form to be filled out by using a browser". You would note that the yellow text warning message now says, "An Administrator must approve this form template, before it can be filled out using a browser". Yeah, I'm the administrator, and I'll take care of that shortly.
- For the location of the Form Template, save it anywhere on your disk. I put it on desktop, and I called it "Customer Notes.xsn". Infopath is reminding me again that an administrator will need to approve this template, before I can use it in a browser. I get it, I get it!!
- Now the next screen is important. It prompts you to extract fields out of the infopath form, so you can use it - say as site columns, in a content type. I extracted out both Customer Name and Customer Notes as shown below.
- Great, now publish the form. As you might note, after the form is published, Infopath warns me again - it tells me to give the administrator some information, so the server admin can enable the browser filling of this form. Oh man, if infopath reminded me one more time, I'd have to officially rename the product to Mrs. Malik. Anyway, with the form published, we need to do the next important thing, i.e. - The server administration portion. (Enable the form so it can be filled out by the browser).
But, before I dive into that, first let us consider why should the administrator have to be involved here? Well, the theory is, the business user can author a form like I just did above, and the administrator can still maintain some control on what gets published. Afterall, you have impersonation and external data being accessed, you don't want your form server to poop. Right? Okay good, lets go ahead and don our administrator hat next.
- Go to central administration --> Application Management.
- Click on "Manage Form templates".
- Click on "Upload Form Template", and upload the "Customer Notes.xsn" that you put on your desktop.
- Now here is the ultra cool thing - this thing that you just uploaded, has been changed into a Feature for you. You can activate this "feature" at the site collection level through site collection administration, or the farm administrator can activate it at a given site collection using the menu as shown below -
- Now, back in the site collection, go to the form library where you wish to be able to store customer information.
- Go to Form Library Settings, and under Advanced Settings, choose to Allow management of content types, and, choose to allow opening browser enabled documents, as a web-page.
- Back under form library settings, choose to use a different content type, viz. the "Customer Notes" content type. Now for the sharper ones of you, you hadn't ever created such a content type, then where did it come from? Well, it came from that feature you just activated in step #4 above.
- Knock off (delete) the "Form" content type, cuz we really don't need it. (Confused about content types? Read this.)
- Great! Now we are ready to use the system. w00t!
Back in the document library, click on "New Customer" as shown below -
As you would note, forms server kicks in. Happily enough, forms server now shows me data from Northwind, I fill the form as below -
and I simply hit "Submit".
You would note that the forms server didn't ask me for a filename. It didn't bother me with any weird Q's, it simply stored the .xsn in the document library. Not only that, it extracted Customer Name and Customer Notes as columns, and will maintain Customer Name uniqueness (due to overwriting options we specified), and now I can simply create a view on the document library as shown below -
Now tell me that ain't awesome :-).
Here are a few ways to extend this sample and make it really really cool. I will try and blog about the below when I get time, or if you figure 'em out, feel free to leave the answer as a comment.
1. You can easily get rid of those two submit/save as bars that forms services renders for you. It's literally a matter of checking a checkbox inside infopath when you are authoring the form itself.
2. Forms services renders the .xsn as HTML using a server control on an aspx. You can switch what ASPX to use, and you can customize the ASPX, and embed this form in a custom branded ASP.NET page - so it won't even look/feel like it is backed by InfoPath.
3. You can use VSTA to put in rules, so you can't use cuss words for instance etc. etc.
Have fun! :-)