Microsoft InfoPath 2010 XML Drill-Down

Just wanted to throw a quick fix out there for a common problem I see with InfoPath. Often times InfoPath developers find themselves wanting to drill down information based on a selected field in a drop down list. Typically this information is stored in XML format (if it isn't, it gets messy in a hurry).
I'm using InfoPath 2010 in this example.
Before I jump into the how-to, I'll give you a look at my XML source:

It's pretty primitive, but for the purpose of this document, it will work.
The way my particular form is set up is just a drop down list and a text box. You could expand on this an infinite amount of times if you wanted to (that might be a long day).

The first step is to set the data source for the drop down list. You do that by right clicking on the dropdown list, and clicking on Drop-Down List Properties then under the List Box Choices heading, select the third radio button – Get Choices From External Data Source. Then click Add to add a data source. Here, you need to navigate to your XML file that is storing the information. If you XML file is formatted properly, you should see a list with "folders" and then values inside them. Here is what mine looks like:

Select the value that you want to populate the drop down list. Because I wanted to retrieve the ShipsNumber when the user selects the name of the ship, I'm selecting Name for this one.
Now you can "OK" out of everything and go to your rule manager for the drop down list. You'll create a new action that will fire when the list box is changed. We're going to Set a fields value when this rule runs. We want to set the value of the textbox field (which I named "ShipNumber") with this rule.

Now here comes the tricky part. We need to set it to the corresponding ShipNumber of the ship that was selected. To do this, we have to apply a filter as follows.
Click on the Fx (formula) button to the rite. It will bring up a new dialog box where we can enter a formula:

We're then going to click Insert Field or Group:
When it brings up the list, make sure that the XML file is selected as the field source; as highlighted below. Here we select the value that we want to drill down. In my case, that is ShipNumber.

Now click on Filter Data and yet another dialogue box will appear. We want to Add a new filter and yet ANOTHER dialogue pops up (only 1 more, I promise). Here is where we declare the expression for the filter. I want to show the ShipNumber where the Name is equal to the name that the user selected in the drop down list:

When the final dialogue box appears, you simply select the drop down list and you're good to go.



When you first start doing this, it can get a little ugly as the rabbit hole of dialogue boxes keeps getting deeper and deeper. I hope by using an excess of pictures, it'll be a little easier to figure out. Be sure to post if you have an questions.
Best of luck!

Microsoft InfoPath 2007 and web services

InfoPath 2007 is an amazing product that comes loaded with features right out of the box. You can accomplish a tremendous amount of work without ever writing code. That being said, there are still times where you need to delve a little deeper into InfoPath to accomplish your goals. Using SQL Server and web services offers a lot of flexibility when working with databases and in this tutorial it will show you how to use these features.

I will be using InfoPath 2007, SQL Server 2008, and Windows Server 2008 R2. There are essentially three steps involved and they are :

1. Creating a stored procedure
   
2. Creating the endpoint/webservice
   
3. Connecting to it in InfoPath
   

Creating the stored procedure

 

The first step is to create the stored procedure. The store procedure is a reusable, ready built command that SQL Server holds on to for us until we're ready to use it. If you're a programmer, you can think of an SP as a function. The way we build is SP is as follows:

CREATE
PROCEDURE spNameOfProcedure @myDate datetime, @myName varchar(255)

AS

SELECT
*
from myTable where TheDateColumn = @myDate and TheNameColumn = @myName

@myDate and @myName are variables that you tell the stored procedure to expect every time it is used.

Using this stored procedure would look like this:

    EXEC spNameOfProcedure
'12/1/2010/, 'Elliott'

You can go here if you are still struggling with this.

Creating the Endpoint/Web Service

 

The next step is to create the endpoint and web service. You can think of the endpoint as the doorway into SQL Server. InfoPath will knock on the door, say "hey I need to use one of your web services and see what happens". SQL Server will respond with a list of available web services and depending on the web service that InfoPath chooses, it will run the respective stored procedure. Please keep in mind that this opens up your computer to the world and if not used correctly, can be a security risk.

Below is an example of the SQL statement that creates one endpoint with two web services:

    CREATE
ENDPOINT [SomeNameForTheEndPoint]

    AUTHORIZATION [myUserName]

    STATE=STARTED

    AS
HTTP (PATH=N'/theServiceName',
PORTS
=
(CLEAR), AUTHENTICATION =
(NTLM), SITE=N'myUserName',
CLEAR_PORT
= 80,
COMPRESSION=DISABLED)

    FOR
SOAP (

WEBMETHOD
'WebServiceName1'( NAME=N'[DataBaseName].[dbo].[spSomeStoredProcedure]'

,
SCHEMA=DEFAULT

, FORMAT=ROWSETS_ONLY),

WEBMETHOD
'WebServiceName2'( NAME=N'[DataBaseName].[dbo].[spSomeOtherStoredProcedure]'

,
SCHEMA=DEFAULT

, FORMAT=ROWSETS_ONLY),

BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'Some Database',
NAMESPACE=N'http://tempuri.org',
SCHEMA=STANDARD, CHARACTER_SET=XML)

 

GO

 

You can visit this site for more information on creating an endpoint.

In order for users to be able to access the web service, you need to give them permissions within SQL Server to do so. Otherwise InfoPath will essentially knock on the door and SQL Server will tell it to go away.

 

Connecting to it in InfoPath

 

    Now we go to InfoPath and add a new connection:

    

Next we want to choose SOAP request

        

    Next we enter the URL of the web service we created. Mine is on an intranet so I just enter the name of my computer and then the name of the endpoint.

    

    Now you should see the web services we created with the endpoint. Select one and click next.

    

Here you can see the two parameters that we told the stored procedure to expect. You can click 'Set Sample Value' and just enter anything because we will be changing this in a minute. Then Click next. You can then click your way through the rest of the prompts until you press finish.

    

Now you are able to use this data connection like any other data connection in InfoPath. The tricky part however, is assigning the variables that you pass to the stored procedure. To do this, we create a rule that "Sets A Value" and we change the sample value from above:

Then set the myDate variable to whatever you want.

If you did everything correctly you should be able to assign a value to all the queryFields expected by the web service and retrieve the data. Just remember that after you set the variables you will need to refresh the connection in order for the new information to populate.

Happy InfoPathing!

 

Microsoft InfoPath – Having more than 5 conditions on a rule

Microsoft Office SharePoint vs. Drupal

Introduction

"Why not just use Drupal? Its open source." This is a question that I have seen a lot from folks that are new to Content Management Systems (CMS). The answer rests within the age old saying "Apples to oranges" except this is more like apples to watermelons.

First I need to explain that I am by no means an expert in Drupal and there are definitely smarter SharePoint developers out there than myself. I currently work with SharePoint at Microsoft and have had the good fortune of seeing the world's largest and most robust SharePoint deployment in action. That being said, if you feel I've made an error please leave feedback and I will do my best to correct the original publication.

Different Target Markets

The biggest difference between the two products is their core competency and respective target markets. However, as both products and their supporting technologies have been evolving, the line between the two is blurring considerably. Typically Drupal is deployed as an internet facing site and SharePoint as an internal site. While SharePoint can make some awesome internet facing sites (Some examples), it is extremely expensive to purchase the SharePoint For Internet Sites (FIS) CAL.

Development

How a product is developed isn't always a big deal for people. However, when you really get down to the nitty-gritty considerations like security and scalability it can sometimes be a deal breaker.

Drupal is open source

Because Drupal is open source, anyone can work on and contribute to it. This has led to an extremely large community that is constantly adding new modules and themes. What typically happens is when someone realizes a need for a functionality they create it themselves. This often leads to quick advancement within the Drupal community and consequently there are a lot of modules and themes that are available for Drupal.

SharePoint is not

In some peoples' view (myself included) the fact that SharePoint is not open source is actually a plus. While the development of SharePoint products may often be slower, you typically get a very solid product from Microsoft because it has undergone extensive testing. Microsoft has spent millions of dollars in R&D for SharePoint. This makes for a much more airtight and secure product making it better suited for enterprise deployments.

Furthermore, SharePoint is compliant with Sarbanes –Oxley , GAAP, and HIPAA. I won't go into any more detail on this but understand that it is a VERY IMPORTANT quality as more and more businesses transition to a digital enterprise.

 

Supporting Technologies

Another huge divider between the two is the supporting technologies and add-ins. To be honest, this is an area where Drupal doesn't stand a chance. Drupal is essentially a web-design package. Meaning, if you want to manage content other than blogs, polls, or pictures you're barking up the wrong tree.

SharePoint on the other hand is specifically designed to be incorporated with a many of the Microsoft products such Office and Outlook. The actual possibilities of different types of integration are so far-reaching that to list them would be outside the scope of this article. A huge benefit of SharePoint is document management. SharePoint can search, index, and organize all the documents, emails, chat sessions, and just about anything else on your company's intranet. Furthermore, SharePoint does all of this while maintaining permissions: meaning if you don't have permissions to view a certain document or document type, it won't let you.

Another important feature of SharePoint is workflow. With SharePoint and InfoPath, you can create interactive forms. These forms can pull data from just about anywhere and let you work with it. The data can then be stored many different ways, approval can be requested, and emails can be sent…all automatically. SharePoint's gives a business the ability to completely streamline almost any process and reduce paperwork tremendously.

Future

In the future they're probably going to have to come up with different terms to describe these products. While they're both technically CMS products they do two very different things. SharePoint is going to keep evolving and with the rest of the windows products and will likely become common place in every medium-large company. Drupal on the other hand doesn't have many other markets that it can expand into. It will continue to do what it does well: build great websites.

Conclusion

I realize I rode the SharePoint bandwagon pretty hard in this article. This is partly due to the nature of my work. However, anybody planning on working in corporate America in the years to come should know about SharePoint, I guarantee you'll run in to it eventually. That being said, anyone with any interest in ever having a website should know how to use Drupal. It has a bit of a learning curve but the fact that it is free and has a large community makes it a great tool for any web designer's tool belt.

If you interested in learning more about similar technologies consider reading up on Joomla and Wordpress which are both big competitors to Drupal or Oracle Beehive which is trying to compete with SharePoint.