Aggregating multiple data sources in a browser based InfoPath 2007 form.

Posted on 8/16/2007 @ 8:25 PM in #SharePoint by | Feedback | 11300 views

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.

  1. 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.
  2. 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.

     
  3. Great. With the form created, go to the "Design Tasks" pane on the left and click on "Data Source".
  4. In the "Data Source" pane, at the bottom, click on "Manage Data Connections".
  5. 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".
  6. 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.
  7. From the toolbox, drag drop a drop down list on the surface of the form.
  8. 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 -

     
  9. 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".
  10. 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.
    1. Create a new document library on sharepoint called "Customer Notes". This will be a "Forms Library".
    2. 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.
    3. Finally save this data connection, call it "Customer Doc Lib Submit" or something like that.
  11. 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.
    1. Right click the button, choose "Button Properties".
    2. Under "General" choose "Action" as "Submit".
    3. Click on "Submit Options", and modify the submit options as shown below -

       
  12. Your form, at this time is done, and with some lipstick will look like as shown below -
  13. Your form, at this time, in design mode should look like as below -

     
  14. 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 -
    1. In the publishing wizard, choose, submit to SharePoint server with our without Infopath Forms Services.
    2. Enter the location of your MOSS site where you want the form to sit.
    3. 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.
    4. 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.
    5. 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!!
    6. 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.

       
    7. 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.

  1. Go to central administration --> Application Management.
  2. Click on "Manage Form templates".
  3. Click on "Upload Form Template", and upload the "Customer Notes.xsn" that you put on your desktop.
  4. 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 -

  5. Now, back in the site collection, go to the form library where you wish to be able to store customer information.
  6. 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.
  7. 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.
  8. Knock off (delete) the "Form" content type, cuz we really don't need it. (Confused about content types? Read this.)
  9. 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! :-)

Sound off but keep it civil:

Older comments..


On 8/31/2007 4:15:14 PM Bryant said ..
Hey, I have been using techniques like these for over six months now, but I have been having a problem with the "you cannot use that with a browser" stuff. All of the cool stuff info path does, cannot be used in a browser. Will it accept all of the custom code from the VSTA in a browser? or are you limited there too?


On 1/8/2008 5:34:20 AM prudhvi said ..
that was what i exactly needed. thanks alot !!!


On 1/9/2008 11:42:39 AM zullu said ..
Great thing !!!


A lot seems different from InfoPath 2003 for VS 2005.


But that's for better....:-)


I have tried following this blog with each step...but strangely though I did not see the two columns (CustomerName and Notes) appearing as columns in my Doc Library.


Am I going wrong somewhere. Any idea??


I appreciate your blogging and thanks for any kinda help.


Zullu.


On 1/9/2008 12:51:28 PM zullu said ..
Hey I managed to get that. There was no problem anywhere.


It was just that the columns were not availble to the [All Documents] views. :-)


So, got that by adding the reqd columns.


Thanks again.

Now to move forward...with 2 questions.


#1.


I am try to create the form with several views. As in IP 2003, where we were able to create views from within the


Design Layout, I am unable to figure out from where and how to create "Views".


eh..should be simple enough, but where??


#2.


I have several other controls (textboxes, dropdowns, etc accross multiple views) which need to be populated


from the Sharepoint list, when a Value is selected in One Dropdown. How to achieve this??

Thanks in advance for any kinda help.


Zullu.


On 1/10/2008 4:00:04 PM zullu said ..
Great goings !!!


Today I was able to see the "Create a View" link when I restarted my virtual machine.


Not sure what was going on yesterday. But yep, #1 is now resolved. :-)


Now just wondering about my #2 question...


How to retrieve data based upon a selected value from the drop down.


Any idea?


c'mon, I have been waiting to see a response from u.


Zullu.


On 1/10/2008 6:10:49 PM Sahil Malik (Out of Office) said ..
Thank you for your message, which has been added to a queuing system.


You are currently in 3522312nd place, and can expect to receive a reply in


approximately 19 years 234 days 4 hours and 57 seconds.

Okay man, just kidding. But I'll try and answer - but no promises, just really really tied up here.

But here is a quick answer to point you in a direction to dig in - Look at the DQWP.


On 1/10/2008 6:11:57 PM Sahil Malik said ..
.. PS .. DQWP == Drop Down on a web page.


For a drop down on an infopath form, you'd have to take a different approach.

Not sure exactly what the problem is - but I'd suggest, hit the newsgroups.


On 1/16/2008 9:23:02 AM zullu said ..
Thanks for your comments and time.


I really apreciate that.


I'll try to look for more info at the other resources.


Thanks again.


Zullu


On 10/17/2008 7:06:23 AM AJIT said ..
Hello Can someone help ?

I have infopath form uploaded on sharepoint portal, and there is "Drop down list box" control which retrive the text field values from Sharepoint list but the problem is when we open that from in browser then it dont sort the vales "Alphabatically" infact it sorts the values by "itemid" property of library.

And when we open that from in infopath installed on local macine then it retrives the values alphbatically!

Please someone help!


On 2/11/2009 10:17:55 AM Disco Dave said ..
My solution to the Browser based sort order can be found here and adapted :-

http://social.msdn.microsoft.com/Forums/en-US/sharepointinfopath/thread/4f739cec-220e-4ab2-a6d9-13079e43844a


On 3/31/2009 12:24:55 AM Amit said ..
Hey Sahil,

Thanks for this great blog. I have done what you said in here but there is one problem, It's still opening in Excel instead of browser.When I m saving it and then its showing a option edit in browser and I can see the form in browser but earlier by default it is excel.I read in some forms that by default if you have excel it 'll use excel and if you don't have excel then only it 'll use browser,thats true or I m missin something.


On 7/7/2009 4:50:41 PM Carolina said ..
thank you very much, this worked great :)


On 7/14/2009 11:31:14 AM Edgart Herrera said ..
great i follow this post and i publish the form but when i open i get this error


An error occurred accessing a data source.

An entry has been added to the Windows event log of the server.


Log ID:6932


the form display and i can access but the data from sql in my drop dowwn doesnt show any idea what im doing wrong


thanks you are the best