Problem Statement from the User

The goal is to build out a contract library in SharePoint, fed by and queried by Infopath forms. looking for someone with experience developing enterprise portals, content and document management systems using SharePoint 2010, InfoPath 2007 with SQL Server for connectivity to Infopath forms and Sharepoint libraries for querying purposes.

This is an interesting problem and in some ways well suited for the SharePoint product family.

There are several possible directions to take with impacts on various dimensions such as:

• form complexity
• client deployment
• support profile/risk
• development requirements
• platform gotchas

Some example directions:

use IP data connections if only meta-data (e.g. drop-down lists) from DB is needed in the form

access/write back DB data directly from IP form…store form template in formlib…user invokes IP client (simplest to develop…many cons)

same as above, but store form template on network shared drive

access DB from SP 2010 external lists…then map forms to views or tables in DB…enhance forms using SPD if necessary (synch/lockouts…how to handle repeating tables)

create DB views of the forms…separate views for repeating data…access thru custom web services…these become IP connections…web services "maps" DB data <---> forms

use IP 2010 (lots of small gotchas fixed/squashed)

use IP Forms Services (headaches associated with client deployments are greatly reduced)…only form designers/developers/tests need IP client at all…less opportunity for JS tweaks

lots of workflow gotchas no matter which path taken (e.g. very difficult to get it to work with BCS)

need SP2013 for full-featured BCS interfaces (e.g. alerts, export to Excel)

is search needed? lots of considerations here…maybe BCS just for this…not for form access

should form data be queried thru IP? maybe make a small custom webpart for this

How to handle the DB security setup? Is this already in place?

--------------------------------------

References:

http://office.microsoft.com/en-us/infopath-help/submit-form-data-to-a-microsoft-sql-server-database-HP010107060.aspx

http://go.limeleap.com/community/bid/246651/Creating-Forms-for-SharePoint-Using-SharePoint-Designer-2010

http://go.limeleap.com/community/bid/275464/Surfacing-External-Data-in-SharePoint-2010-with-BCS

http://go.limeleap.com/community/bid/289541/Creating-InfoPath-Forms-that-Save-to-SQL-Server-Database

Securing InfoPath forms data precludes use of Forms Services

I am a big proponent of InfoPath Forms Services, in both MOSS and SP 2010. This is because it is a server-side processor of InfoPath form templates and (in some cases) it will render and allow fill-in requiring only the browser and no client side components.

I also believe forms processing (generally) has a great potential to eliminate mistakes and protect the integrity and actual values of data from harm.

I started digging deeper into this for a demo I am preparing for a health care form. Of course, all PHI (i.e. personal health information) must be protected from deliberate or inadventent disclosure. I am still working to implement this in the Microsoft software stack. Unfortunately the following has become clear:

• Although digitally signing forms data works, this does not encrypt the data so forms containing sensitive data are open and subject to compromise.
  
• It is possible to extend the InfoPath client function in order to encrypt/decrypt some or all of the fields in the form data. This must be done using the InfoPath object model. Of course, doing this compromises the no code goal.
  
• Finally and most disappointingly, use of these code extensions is not compatible with Forms Services. This means that to use this facility, all form-fill-in users must have the InfoPath desktop client!
  

Bummer!!

Regards..

References:

Encrypting and Decrypting InfoPath Form Data

Extending the Save Functionality in InfoPath 2003

Protecting Sensitive Data

Password-Protecting Sensitive InfoPath Form Fields

data denormalization?? who cares?

This posting about getting data (your data, that is) into a place where it is more useful to your organization.

Let's say you have been keeping some data of yours (such as shipments) in an Excel spreadsheet. This is very common.

You have set up some very basic reporting using this data. You want other people in your organization to input their information into this "database".

You have a few options, for instance, you can:

• give them the spreadsheet on a USB stick
  
• can send them the spreadsheet thru email
  
• store the spreadsheet on a fileshare where they can access it
  
• upload the spreadsheet to the "cloud" such as Google Docs
  
• upload the spreadsheet to your internal web store (maybe a SharePoint document library)

The problem with all of these methods is that the other people will still be inputting and editing their data using Excel. Besides for all of the conflicts associated with the methods above, the likelihood of out-of-date and erroneous data is still very good. Let's look at an example:

First Name	Last Name	St
Joe	Smith	MD
Sally	Jones	NV
Sue	Kwon	Cal
Bill	Lyon	CA

If you saw this data in Excel, does it look clean? How about the St column for Sue Kwon. You might guess St means state, but by looking only at the first row, it might mean occupation.

Many "home brew" data sets are designed like this. A way to keep it cleaner is to have St be a lookup field, where St is the State and comes from another "location" where there is only one entry for California (not Cal or CA)!

This can be done with Excel using the =VLOOKUP() function (see Ref below).

When data is changed to behave like this, the data is said to be "normalized". There are a couple of other database-techie components to this definition, but we will only work on this one for now.

When the data is in the form above, it is said to be "denormalized" and is subject to all manner of data cleansing problems.

The next posting will go into how data like this can be stored in Sharepoint, in a normalized fashion using "no code".

Regards..

Ref: Learning VLOOKUP in Excel

why do Contributors have access to edit my main page?

This issue only occurs with publishing templates. With collaboration templates, Contributors do not have any Site Actions menu choices.

Some people go off making permission levels to fix this. This will not work since Contributors still need Edit & Delete rights to the lists in the site.

This issue occurs because in sites created with the publishing template, Contributors have Add/Edit/Delete rights to the Pages document library (which is where the home page is typically stored).

The simple fix for this is:

• break the permission inheritance for the Pages doclib
  
• change the permissions for the Contributors (probably Home Members) to only allow read access
  

Although the Site Actions menu will still be present, Create Page and Edit Page will no longer show up!

Regards..

why is the Include-Content button greyed when making a list template?

Apparently this occurs when there is a Lookup-type column in the list. Just remove this column and it should be save-able (sic) with the content. Another optin is to "de-reference" the lookup column. Do this by:

- create a new text column
- go into datasheet mode
- select the entire lookup column and copy/paste into the new text column

The reason this occurs is because a lookup column works by referencing an interal generated name for the list it references.

The "instantiate a template" function cannot recreate the lookup column field (since the referred-to-list may not even exist where the new list is instantiated), so they chose to disable saving the content if this field is present.

Document conversion not very useful & broken in one case

This is another case where the Publishing parts of MOSS are not integrated cleanly IMO.

I created a site collection under a publishing root level site. This site collection did not have the Publishing Features activated.

When I uploaded a Word 2007 file, the context menu has an item labelled Convert Document >> From Word Document to Web Page.

Not knowing what this was I tried it. You immediately get an error screen displaying:

The site is not valid. The 'Pages' document library is missing.

There is no indication what the problem is related to.

So I created a 'Pages' document library. Tried the same menu item and got the same message.

Then I added the Publishing Infrastructure to my site. Same result.

Then I looked on the web about this error message and I was instructed to check Central Administration. I did this and saw:

Document Conversions Launcher Service is present and started

Finally I made a site collection using the Publishing site template. Then I uploaded the document to this new site collection and tried the menu item again. Lo and behold:

The document converter was able to create HTML from this document, but it had to modify the output:
1 embedded image(s) from the document were not preserved in the HTML.

Click OK to go to the created page.

I went to the page and it looked like Word's old SaveAs Html option. Not very useful!

So this menu item is enabled even though in many instances it will cause an error. Not the A answer!

Regards..

Roger Williams
Franklin Laboratory

copying including associated metadata

There is some good news on this issue.

Basically if you copy a document library item that is a certain content type and contains certain meta-data to another location, using ECB >> Send To >> Other Location, the entire content type will be copied providing that the destination document library has content types enabled as well as the "copied from" content type.

In my tests, if the content type management is not enabled in the docuement library, only the document gets copied and it truncates the metadata. The fact that no message is issued on loss of dat is a bug IMO.

To test this do the following steps:

• optionally create any needed site columns
  
• create a content type (using those columns)
  
• create the target document library
  
• enable content types (List Settings >> Advanced Settings >> Allow management of content types)
  
• include the source content type in the target doclib (List Settings >> Add from existing site content type)
  
• save a copy of the target document library URL
  
• go to the source document library
  
• navigate to the folder or view containing the item to copy
  
• use the Edit-Control-Block to perform the copy (ECB >> Send To >> Other Location)
  
• paste in the target document library URL (strip off Forms/AllItems.aspx)
  
• hit OK twice
  

This should do it. Note that there are several additional capabilities this should have, including:

• let the user pick the doclib from a screen
  
• let the user select folders
  
• let the user select several items to copy simulatneously
  
• the system should strip the URL for the user
  
• reduce the number of clicks!!
  

Other needed tests include:

• content type mis-match but meta-data match (using doclib list columns
  
• built-in content types and complete meta-data match
  

Anyway, at least it works for the center-of-the-street case.

Regards..

Roger Williams
Franklin Laboratory