InfoPath gaps in SharePoint and SQL - Patrick Halstead
in

InfoPath Dev

Patrick Halstead

InfoPath gaps in SharePoint and SQL

SharePoint 2007 limitations (MOSS)

  • SharePoint views cannot be configured to show repeating data from an XML form. No workarounds here.
    • Reporting is flat – no repeating data so the data can not be reported on out of the box. You would need to move the data to a database.
    • Export to Excel from InfoPath does not supported for more than one repeating section and not feasible for more than 10-20 XMLs at a time.
  • Document level permissions – you can manually set permissions in SharePoint, but controlling permissions based on document level fields cannot be done automatically and would require extra SharePoint event handlers or code.
  • SharePoint list view performance degrades for large form libraries (3000+ forms). This is a known limitation in SharePoint. Not a limitation if the data is in SQL. Workarounds include:
    • Archive forms so you have <3000 active. Not a solution if you need to see all of them.
    • Restrict views to show very few forms and enable paging. Can’t see all forms.
    • Add DBXL later and use Excel or SRS if you need fast reporting on all forms
  • Form library columns – number is limited (based on data type)
  • Browser-based forms (IPFS)
    • Can’t use SQL – everyone must have InfoPath rich client if directly connecting to SQL. Web Service (e.g. DBXL) is workaround
      You can still use IPFS but it would require creation of a separate hardcoded Web service (~$1-2k), code to impersonate users ($2-4k), or configuration to impersonate network service account when submitting to DB (<$1k) which limits certain scenarios
    • Auto-upgrade doesn’t work
    • Permissions. No locking when form is opened (last writer wins)
  • Submit performance would be slower (~4x slower) going to SharePoint than to a Web Service. No workaround.
  • No Active Directory integration to assign forms and capture username for comments, etc. SharePoint’s GetUserProfile can be used, but does not allow searching for manager/users or enumerating directs.
  • No filtering for prepoluated data, i.e. dropdowns populated from SharePoint lists - all data is populated on load leading to slow load times. Filtering via OWSSVR can be done but requires code. qRules is another alternative. DBXL WSSP is fastest way to do this since it supports parameterized server-side filtering
  • SharePoint out-of-box search does not support wildcard searches for forms (details). You can’t search for App* and find both Apple and Application. DBXL Search Webpart does support wildcard searches albeit the syntax is different from Siebel.

SharePoint 2010 limitations

  • Using SharePoint Designer to create write-backs to External Content (SQL DB's) is complicated and not recommended for complex data structures.
  • Visual Studio (custom code) is the recommended development tool to use for complex (related) DB write-backs.  DBXL allows you do manage this without code. 
  • BCS cannot be configured on the fly after an InfoPath form is deployed. You have to convert main data source. Also, BCS requires SharePoint licenses.
  • Data migration of XML forms still requires special stsadmin tools (DBXL provides Doc Migration tool).

Web Service limitations

  • Default Web Service hardcodes data source to parameters in WSDL. Not data-driven. Need 1 Web Service per form (extra cost). DBXL is data-driven and handles any number of forms.

 SQL Database form issues

  • Changes to XSN schema – adding fields to form do not show up in SQL. You have to add to SQL and then convert your main data source. Previous XML data would not migrate forward.
  • Changes to DB schema will invalidate previously saved XML files in SharePoint.
  • Images, rich text, and other non-SQL data types cannot be saved to SQL. Must be saved in XML but then you have migration issues per previous two bullets.
  • No server-side filtering of large SQL datasets – you could add code to do this but it would impact cost $1-2k and probably be specific to each query
  • No locking of data – multiple writes could happen
  • No dynamic role-based permissions – you would have to add users and/or groups to SQL manually and specify READER or WRITER. Can’t dynamically determine reader and writer based on field value. 
Bottom line: DBXL adds a lot of value for very little cost.

 

Comments

No Comments

About Patrick Halstead

I'm the founder of Qdabra Software, a small bootstrap software company that provides add-ons and accelerators for InfoPath. I help organizations design and implement electronic forms solutions using off-the-shelf technologies such as InfoPath and SharePoint. Recently, I have been focusing on cloud-based scenarios that leverage AWS and Azure. In my spare time I enjoy running and annual travel to Japan.
Copyright © 2003-2012 Qdabra Software. All rights reserved.
View our Terms of Use.