Routing infopath forms based on department

Mon, 12 Nov 2007 16:34:04 Z

Is there a way to route an infopath form based on the department the user filling it out belongs to?

IE, can I make a global PTO Request form that would route to a person(their respective supervisor) based on their department?

Routing infopath forms based on department

Thu, 15 Nov 2007 01:46:25 Z

If you have the department as part of the form, then you could use that in SPD to do the routing.

Are you using SPD?

Routing infopath forms based on department

Fri, 16 Nov 2007 16:26:58 Z

I forgot to specify this, but I am using InfoPath Services on MOSS 2007.

I have SPD and I don't mind doing a little work in it, although I would prefer to do as little as possible (Who wouldn't).

Routing infopath forms based on department

Sat, 17 Nov 2007 22:47:12 Z

Hey,

I just wanted to add my two cents worth. I also wanted a form (browser enabled) to route based on someones manager, so I used the following approach:

1, Uploaded all of the Manager information and EmployeeID information to Active Directory (not heavily utilized by many IT Managers, but possible).

2. Used the Form Initialization Rules to query the ProfileWebService in MOSS to fill in the Manager, EmployeeID and current user information into fields in the form.

3. Using VSTA to program a changing event to lookup the ManagerID (which comes in as the NTName) against the SSP Profile Database to retrieve the Manager's PreferredName and Email address.

4. Other than all of the other Form Stuff which obviously changes form to form, I published the form to a Sharepoint Form Library (Through the Central Admin Site). By the way, you do need to add a DCL file to Central Admin to access the SSP Profile Database efficiently.

5. Used SPD to create a custom workflow which routed based on the Manager Email, PreferredName and the rest of the form information.

I did mess with the InfoPath "Contact Selector" Active-X component a little as well, but I didn't like it as much, and you couldn't retrieve anything but the users name from it.

I realize this method is not a "Cut and Dry" easy method, but it was the only way I could guarantee results of who was logged in and who their manager was without having to have the user type in or have a drop-down list to fill out.

If you would like any examples of code or procedures I have used, just let me know. I will be more than happy to share my research. Trust me, if you have done any of these things I have mentioned, you know how challenging they can be. On some of my projects, I just had to scrap InfoPath and go straight .NET Web Applications.

Bryant

Routing infopath forms based on department

Tue, 20 Nov 2007 13:31:30 Z

That's a great detailed explanation.

Can you elaborate on #4?

Thanks!

Routing infopath forms based on department

Tue, 20 Nov 2007 16:53:10 Z

BryantDuke wrote:

Hey,

I just wanted to add my two cents worth. I also wanted a form (browser enabled) to route based on someones manager, so I used the following approach:

1, Uploaded all of the Manager information and EmployeeID information to Active Directory (not heavily utilized by many IT Managers, but possible).

2. Used the Form Initialization Rules to query the ProfileWebService in MOSS to fill in the Manager, EmployeeID and current user information into fields in the form.

3. Using VSTA to program a changing event to lookup the ManagerID (which comes in as the NTName) against the SSP Profile Database to retrieve the Manager's PreferredName and Email address.

5. Used SPD to create a custom workflow which routed based on the Manager Email, PreferredName and the rest of the form information.

I did mess with the InfoPath "Contact Selector" Active-X component a little as well, but I didn't like it as much, and you couldn't retrieve anything but the users name from it.

Bryant

This is a great response, but will it work with infopath services ie without actually having Infopath.

Routing infopath forms based on department

Wed, 21 Nov 2007 15:39:26 Z

Hi, all.

I need to do something very similar of what you guys are doing.

I need to retrieve data from Active Directory (like login user name, supervisor's name and email address, etc) and display them on the form. After user submits the form, a workflow will send email to the right supervisor for approval.

Can someone elaborate steps by steps how to retrieve data from Active Directory?

Thanks in advance.

Routing infopath forms based on department

Wed, 21 Nov 2007 16:20:45 Z

Yes this will work with Form Services (MOSS 2007) and I designed this system just for that reason. The powers above me do not like the idea of having to install InfoPath 2007 on every desktop in the company (40000, yikes !).

I will try and Elaborate on #4 above:

When creating the InfoPath Secondary Data Connection, follow these steps:

You will need the name of your Shared Service Provider Database in SQL. In this example, the name is SPS_SSP_db.

1. In Sharepoint, create a Data Connection Libary (DCL) in your Site Collection. (if you need these steps, let me know)

2. In InfoPath2007, go to the Tools, Data Connections option:

a. Click "Add", then select "Create a new connection" and "Receive" options

b.  Click next.

c. On the "From Where......" screen select "Database (Microsoft SQL Server Only)" option

d.  Click next.

e. Click "Select Database", then double-click on "+NewSQLServerConnection.odc"

f.   Type in your SQL Server Name and instance (if you have one). i.e. SQL12\SPS

g. Leave the Logon Credentials to "use Windows Authentication"

h. Click next.

i.   Select your SSP database in the dropdown menu (in my case, SPS_SPS_db)

j.   In the "Connect to a specific table" window, select the UserProfile_Full table.

(This table does not include the EmployeeID, but if you need I can explain how to create an SQL View which will populate this information into a different view. If this was done, then you would select the view instead of the table. In this example, I did not create the additional view).

k.  Click next, and then finish (if you want to add description, you can, but it is not necessary).

l.   Click next, next and finish.   Take note of the name of the Data Connection.

2. From the Data Connections Window. Select the Data connection you just created and click "Convert"

a. In the Convert Data Connection window, select the option "centrally managed connection library (advanced)"

b. Click on the "Browse" button.

c. Browse to and select the DCL you created in step 1 and either leave the default name or type in a new one (it really doesn't matter as long as you know what the filename is).

d. Then click "Save", and OK.

4. The DCL file is now "Converted" and ready for use only with InfoPath2007 the client, but not the Forms Service yet.

5. Go to the DCL in Sharepoint:

a. Right-click on the file and choose "Save Target As".

b. Save the file somewhere on your computer

c. Open the file in notepad. (You will need a username and password which has "Read" access rights to the Sharepoint Profile Database.)

d. In the file (which is in XML Format), replace the entire section with:

Code Block

  <udc:Authentication>
      <udc:UseExplicit CredentialType="NTLM">
          <udc:UserId>DOMAIN\UserAccount</udc:UserId>
          <udc:Password>strongpassword</udc:Password>
      </udc:UseExplicit>
  </udc:Authentication>

e. Save the file.

f. Delete the existing file which is in the DCL (the one you just downloaded), and replace it with the new file you just edited.

g. Go to Central Administration, Application Management Tab.

a. Under the section InfoPath Forms Services, click "Manage Data Connection Files"

b. Click "Upload"

c. Click "Browse", and select the file you just edited, and "Open"

d. Check the box "Allow HTTP Access to the File"

e. Click "Upload".

6. Remember, if you are testing the file in the InfoPath2007 client, then the file must remain in the DCL on the Sharepoint Site. However, it doesn't need to be there for the Form Services to work. Forms Services will use the one uploaded to the Central Administration Website.

I hope this helps clarify my comments in step #4.

Good Luck.

Bryant

Routing infopath forms based on department

Wed, 24 Jun 2009 11:41:27 Z

Hi Bryant,

Can you please explain , how to create a DCL?
I am not worked so much on infopath and want to fetch user profile in a form from ssp.
By reading your post , i think it is useful for me but not able to understand about DCL!!

Thanks in advance.

Routing infopath forms based on department

Wed, 24 Jun 2009 13:32:17 Z

DCL = Data Connection Library. It's only a MOSS feature, but they exist on any site collection. When you create your data connection in InfoPath, you choose to save it in an existing DCL, which can be your regular site or in Central Admin for administratively-approved forms with full trust.

However, you don't need a DCL for using the UserProfileService to get data from the SSP's profile database unless you're doing some crazy stuff like Bryant. For regular forms, you don't have to do that and can just connect directly. Refer to my blog for additional info on doing this.

SharePoint Architect || My Blog

Routing infopath forms based on department

Thu, 25 Jun 2009 12:57:02 Z

Thanks.

Routing infopath forms based on department

Wed, 01 Jul 2009 20:19:16 Z

Hi Bryant,

Thanks for the detailed explanation.

I created a test site and designed an InfoPath form with a control to look up data in the profile database as you describe. I then tested the form in the client and it works well. I proceeded with the “Convert” method, modified the file as described in the DCL, uploaded it in “Central Admin” and tested it again on the client, so far so good. Next step was to publish the form to a SP site, this step required Administrator approval, and so I saved the form with a new name, uploaded the form in Central Admin and approved it.

I receive the standard browser error message when trying to open the file:

There has been an error while processing the form.
Click Continue to resume filling out the form. You may want to check your form data for errors.
Click Start Over to load a new copy of the form.

Again, at all times I could open the file in the IP client. Any thoughts?

Thanks,
David

Routing infopath forms based on department

Wed, 01 Jul 2009 20:35:47 Z

After approving in Central Admin, did you activate it on your site? There is too much to read above, but is there some necessary code in your form that is requiring the administrative approval?

SharePoint Architect || My Blog

Routing infopath forms based on department

Thu, 02 Jul 2009 12:28:44 Z

I actived the form in Central Admin for the the site collection. The form is very simple, one control with a data connection that looks up names in the profile database. There is no code.

Routing infopath forms based on department

Thu, 02 Jul 2009 13:11:02 Z

Then why is it adminitratively approved, and why is it using a UDC hosted at Central Admin? Just do the relative path UDC so that it saves in a DCL on the same site collection. Then, just publish the form straight to your site. What made you go through all that other trouble of going through Central Admin?

In your error message above, was there not a "show details" button to tell you what the error was?

SharePoint Architect || My Blog

Routing infopath forms based on department

Thu, 02 Jul 2009 14:21:42 Z

Well, here is the issue - once I convert the file in IP, upload it to the DCL and modify it, it does not let me publish a browser enabled form without Admin approval.
The "show details" error is a standard one:
An error occurred accessing a data source.
An entry has been added to the Windows event log of the server.
Log ID:6932

Routing infopath forms based on department

Thu, 02 Jul 2009 14:26:11 Z

But there are two options when you convert - relative path and central location. Central location makes you put it in Central Admin, which you do not want. You just want relative path so that you can store it in the same site collection as your forms library. There is no reason for it to be administratively approved if it has no code.

Interestingly, I have never seen 6932, so that should point you in the right direction for researching. I will look it up later, but I have to make my commute now.

SharePoint Architect || My Blog

Routing infopath forms based on department

Thu, 02 Jul 2009 14:42:34 Z

Many thanks Clayton! This resolved the issue!!
Happy 4th.
David

Routing infopath forms based on department

Thu, 02 Jul 2009 17:08:16 Z

Awesome! Glad it was simple. Shoot me an answer if you don't mind. =P

SharePoint Architect || My Blog