InfoPath Dev

I can't seem to find any answers on this, so hopefully someone here will be able to provide some insight.

I have an IP form (web-enabled), and I'm trying to figure out the best submit option to use that will be the most reliable, efficient in terms of user interaction (don't want to introduce lag or 'wait' times while each form populates), and manageable.

The form itself will have several data connections used for business operations (lookup to a standard SP list, as well as an external CT fed from SQL) to populate fields and feed workflow steps.  It's been asked that I look at and compare, the different types of submit options to determine what would be the best for our scenario, but I'm having a hard time finding information that discusses the pros/cons of each method.  Ideally, I'd like to just have the form submit to a list (I'm familiar with that approach already and personally feel it's adequate), but I'm curious how that compares to submitting to a SQL database or form library (as XML I believe).

I can detail more specifics of our form if needed, so please let me know if that will help in doing a proper comparison...and thanks in advance for any help offered.

- R

Hi,

We have quite a bit of experience in this area and I would be happy to discuss options and also talk about the tools that we have that will help you out.

If you want to send me a private message I would be happy to setup a call.

Ted

Hi Ted,

Thanks for the response.  I was actually just hoping for a reference to documentation or a list of pros/cons for each method to help me decide on the approach to take...we're not looking at using any 3rd-party tools (budget constraints), rather we'd like to use OOTB functionality for our solution (OOTB in as far as what InfoPath, SP Designer, and SP2010 offers).

- R

Hi R,

I'll answer the submit side of InfoPath. Let me know if you also want info on query options.

1. SharePoint Form Library
• Pros: simple out-of-box SharePoint data connection - just convert to UDC file for browser forms, promote fields and you have it; should be faster than submitting from client, easy integration with workflow
• Cons: SharePoint libraries don't scale beyond several thousand entries, can't extract repeating data and some data types have limits
2. SQL Database
• Pros: extract repeating data, do more complex reporting than SharePoint, send data downstream for other line-of-business process
• Cons: doesn't work from browser forms directly (requires Web service), hard-coded mapping can be brittle down-the-road (when you want to change the form), lose support for rich data types (images, rich text, etc.)
3. Custom Web Service to SQL Database
• Pros: works with browser forms, lower maintenance cost since logic lives in Web service (changes are less costly); faster submit times for client
• Cons: still need one Web Service per XSN, still lose rich data types and have brittle issues
4. Generic Web Service to SQL Database (e.g. DBXL)
• Pros: loosely-coupled and data-driven means you can change your form or your database without interrupting business process; storing XML in SQL preserves data type fidelity
• Cons: harder to build (but Qdabra's DBXL provides an inexpensive option that many companies use in production environments)

Thanks for the response Patrick, that is very helpful information.  I was also able to find this resource which gives a nice comparison between lists and form libraries for submission.

I'm still leaning towards submitting to a list, given it's straight-forward design, ability to easily design SP workflows, and the overall simplicity of our form, but let me detail what we have so I can make sure I'm on the right track.

Our form is a conversion from 'paper' - an HR form used for employee records that is used for new employees, change of title/position/supervisor etc for existing employees, and separation of employees.  It will connect to a master SQL DB that contains all employee info (my thought was to just expose this in SP as an external CT), a parsed dataset (also from SQL, and exposed to SP via another external CT) based on an export from Active Directory, and a custom list in SP (contains codes and definitions for separation reasons).

For a new employee, they wouldn't yet have an "Employee ID" assigned (that takes place through a separate process outside the scope of this form), so the user filling in the form would simply add in the new employee's info (basic contact info), the type of "Action" being performed (i.e. "New Hire" in this case, or "Change of Position", "Separation", etc. for others), the position type, title/class, supervisor/department...all standard fields for new employees.  This would then go to the supervisor for approval then HR for completion (I'll have a workflow that handles user alerting for each step throughout the process).

For an existing employee (changes or separation), the user would enter in the "Employee ID" of an existing employee which would then cause the form to be auto-populated with the current set of information from the master database (external CT connected to SQL).  Once populated, they could then make any changes needed (type of "Action" then associated fields that match) then run through the same process - submit to supervisor for approval, then on to HR for completion.

In any of the above situations, the lifecycle of the form ends with HR as they print out the form upon completion then delete it from SP (not too happy with that myself, but I'm working with them to discuss archiving options for the forms in a separate area of SP).

The concerns that have been brought up are essentially on two things: security of information (forms will contain personal and private info), and storage of data from the "Master DB" in multiple locations (i.e. external CT's and the actual form data itself).  The master DB is read-only, so we'll never be writing back to it, so in my mind, no matter where the form data is submitted it'll be separate from that data anyway, so there's no real way around having the data from the master DB existing in multiple locations (as soon as you save the form, according to their definition: since it contains information that exists in the master DB, you're storing that data in multiple locations).  Using an external content type simply exposes the data within SharePoint, so it's never actually stored in SP (and I believe I can create the connection without the standard CRUD operations and make it read-only), so I should be able to mitigate that concern with a proper explanation to them.  But on the question of security, on the surface it would appear that both a list and a form library are similar in that respect, and since we're not using any form of digital signatures, either would work equal.  How would either of these compare to the use of a web service in respect to security?  Would a web service be less/more secure?

 Thanks again for the help, I appreciate it.

- R

SharePoint Lists do not support rich XML forms. Specifically, you don't have any support for repeating structures (tables, sections). And, there is no hierarchical information. Even though SharePoint lists use InfoPath they are not really XML forms and thus they cannot be used for most forms without creating a lot of complexity on the SharePoint site (separate lists and web part mashups). If you are doing employee records, you will probably need repeating data and the number of fields per form will most likely be >20. Therefore, I don't recommend SharePoint lists. Microsoft has an interest in selling you on the list option because it helps sell SharePoint. SharePoint is not required for plain InfoPath XML forms. If you already have SharePoint, it doesn't matter since you can do both.

Querying from and submitting to SQL will require a Web service if you are using browser based forms. There are lots of reasons to use a Web service even if you have the filler. In general, you reduce cost down the road because you can easily extend your schemas without having to incur data large migration costs. Storing XML in SQL is also a best practice because you can support full data-type fidelity (roundtripping of images, attachments, rich text, and other types that aren't natively supported in SQL). Most importantly probably is the flexibility with architecture - using a direct connection to SQL doesn't work across the internet.

Your workflow is simple and should be easy to implement with either lists or libraries.

Autopopulating is easy with a SQL query. We do this all the time with our DBXL Web service. You could also look into a BCS solution but it's harder to setup and less flexible when schemas change.

If you are deleting forms after submit, you probably don't care so much about data migration issues. Still, you may need a Web service to interface with SQL if you are using browser based forms (since SharePoint's IPFS doesn't handle double hop authentication for your users).

Security is important. I would say you're better off having your data in SQL than in SharePoint (and using the external content type or a secure Web service like DBXL to control permissions). SharePoint can still show the data using a data view web part which calls the Web service and this is secure since the DBXL Web service authenticates. Recently we had an interesting exchange in the LinkedIn group (InfoPathDev) about this - see Richelle's post - and Jeffrey Metcalf came up with a nice solution using [ME] and locking down the views. That works great for SharePoint lists but not for form libraries. However, it's not totally secure for lists since people could query them using SharePoint's web methods and the issue is that it only locks down viewing for the created by which means you can't assign the form to someone else and lock it down to that person. However, if the person creating is always the person editing, that might be a good option.

So, in summary, Web service would be more secure. In fact, we have a lot of customers who purchased DBXL for that specific scenario (see the testimonial from Cynthia Nalley)

One more idea would be to encrypt the form using qRules. This would be a very low cost way of preventing people from viewing the data (see qRules blog showing this technique). Other than qRules which is inexpensive it would only require your staff to create and remember a password. Of course, if you lose the password the data is gone. In your case since you are archiving the data anyway, that might be a simple expedient. No external data, no Web service, just encrypted data. Here's a link with a sample: http://www.infopathdev.com/blogs/mel_balsamo/archive/2010/05/31/how-to-use-qdabra-s-encryption-xtp.aspx. In your case, you'd encrypt all of the fields that you consider sensitive an d it woud be a one button click operation.

Thanks for the help Patrick, this should be enough information for me to make a decision on how to proceed.

- R