Prescriptive Guidance – InfoPath List Forms Implementation Lifecycle for SharePoint 2010

Have you ever wondered how to use InfoPath List Forms for the SharePoint 2010 and what really happens behind the screen? Have you ever wondered how would you retract customized SharePoint List form and cleanup the form completely from your SharePoint systems? Have you ever wished you had end to end guidance to design, deploy, and maintain InfoPath List forms for the SharePoint 2010? To me, these are the real questions asked by SharePoint/InfoPath developers while designing forms in the real world scenarios. With this article, hopefully you have a reference material which will provide all these answers.

Ever since I have presented “Real Word InfoPath 2010 List vs. Library Forms”, I had promised attendees that I will write detailed blog articles on real world best practices to design, develop, and maintain InfoPath 2010 List and Library forms with SharePoint 2010. Although everyone likes benefits of quick rapid forms design and development using InfoPath 2010 for SharePoint 2010, I have seen more often or not they would get frustrated quickly. In most cases, their frustration is not because of the InfoPath product itself, but because of the developer or development process itself. It is important to remember that InfoPath 2010 development with SharePoint 2010 requires discipline and it must follow specific pattern or guidelines. Based on my experience, one of the biggest things we are lacking in SharePoint 2010 is prescriptive guidance on how to use any feature from end to end in real world scenarios. This article would try to fill the gap between most of the books written on InfoPath 2010 with SharePoint 2010 and real world scenarios.

Although late is better than never, here is the blog article on Prescriptive Guidance for InfoPath List Forms Implementation Lifecycle. Please note that this article discusses InfoPath List Forms, not Library Forms. If you want to read the differences between List and Library Forms and when to use which one, please read this article. Additionally, please note that this is not detailed step by step guide to demonstrate end to end example using lots of screenshots. It will take reader to typical real-world situations during InfoPath forms lifecycle in SharePoint 2010 – Prepare the Environment, Design the InfoPath 2010 Form, Deploy InfoPath 2010 forms to SharePoint 2010, Upgrade/Maintain InfoPath 2010 forms to SharePoint 2010, and Retract/Cleanup InfoPath 2010 forms to SharePoint 2010.

Here is prescriptive guidance on how to create and maintain sample “Project Request Form” by walking through end of end example and what really happens behind the screen.

Step 1 => Prepare the SharePoint and InfoPath Environment

First step of the SharePoint and InfoPath 2010 development is to make sure you have all the tools installed for InfoPath form design and all the SharePoint services configured. Follow this step to ensure your environment is prepared for the SharePoint and InfoPath development.

• Prepare SharePoint 2010 Environment
• Have SharePoint Server Enterprise CAL Installed. Browser based InfoPath Forms available in SharePoint Enterprise CAL as InfoPath Form Services.
• InfoPath Form Services enabled on SharePoint 2010 Enterprise by default. You can Configure InfoPath Form Services Settings on the Central Administration -> General Application Settings -> InfoPath Forms Services -> Configure InfoPath Form Services -> Enable/Disable User Browser Enabled Form Templates
• Prepare Development Environment
• Install InfoPath 2010 using Office Professional Plus 2010 – InfoPath Designer 2010 and InfoPath Filler 2010

Step 2 => Design the InfoPath List Forms and SharePoint Application

This step will create sample SharePoint List (e.g. Projects) to host InfoPath List Form (e.g. Project Requests Form) and customize the form using InfoPath Form Designer 2010.

• Create the Custom List (Projects List) to hold the Project Requests
• Add List Columns. In real world, I like to standardize the lists or library metadata with Site Content Type.  For this example, create list columns directly on the list.
• Basic Info
• Fiscal Year – 2011-2015 – Required – Choice
• Project Name – Required (Rename Title Column)
• Project Description – Single Line of Text
• Department – IT, HR, Finance, Legal, Sales, Marketing, Other – Required – Choice
• Project Requester – Required – Person & Group
• Project Sponsor – Single Line of Text
• Project Priority – High, Medium, Low – Choice
• Project Type – New Implementation, Modifications, Enhancements, Replacement – Choice
• Business Case
• Problem Statements – Multiple lines of Text – Rich Text Field
• Project Mission (including Project Scope) – Multiple lines of Text – Rich Text Field
• Project Benefits – Multiple lines of Text – Rich Text Field
• Business Dependencies – Multiple lines of Text – Rich Text Field
• IT Dependencies – Multiple lines of Text – Rich Text Field
• Configure Advanced List Properties as needed
• Enable the Versioning (Version Settings)
• If you have created content types for Project Request Item, Enable the Content Type (Advance Settings) and Add Projects Content Type and Delete the Default Item Content Type
• Configure Default View – Remove unnecessary column

• In the Out of box SharePoint Lists, If you add new item without customizing the forms, item view form would look like this as Standard UI.

• Behind the screen – What really happens?
  • Three out of box List Form Web Parts available on the List Ribbon UI.

    

  • You can open the site collection in SharePoint Designer and navigate to the All Files -> Lists -> <List Name> -> <Content Type (e.g. Item)> folder. Folder should be empty and there won’t be any custom InfoPath templates and forms.

•  Customize the InfoPath Lists Forms
• Use “Customize Form” from the Projects List Ribbon bar and it should open the InfoPath 2010 Designer to customize the Lists Forms.

• Projects Request InfoPath Form will be automatically configured in the InfoPath Form Designer
• InfoPath Form Fields are linked to the Underlying SharePoint List
• InfoPath Form Publishing Location is already configured as Underlying SharePoint List
• Design Projects Request Data Entry InfoPath Form
• Configure Views (UI and Branding), Rules, and Data Sources.
• Configure Forms Options – Toolbar options, default display view
• Review Data Connections – Main Data Connection would be configure to retrieve and submit data to the underlying SharePoint List. You can notice from the following screenshot, publishing location is already configured to underlying List URL.

Step 3 => Deploy InfoPath List Forms

• Publish the InfoPath Forms to the SharePoint List
• Publish the Form using Quick Publish from InfoPath Designer.

• Behind the screen – What really happens?
• As you soon as you publish the form first time, Newly publish form will be deployed to the Site Collection Content Database along with the List contents.
• Three additional Form Web Parts Customization Menu Items will be added to the List Ribbon UI.

• You can open the site collection in SharePoint Designer and navigate to the All Files -> Lists -> <List Name> -> <Content Type (e.g. Item)> folder. Folder should contain InfoPath template (template.xsn) and three additional default Edit, Add, and Display forms (newifs.aspx, editifs.aspx, and displayifs.aspx). All your forms and form template gets stored in the content database.

• Three new Edit, Add, and Display forms contains the newly introduced web part in SharePoint 2010 “InfoPath web part” and configured by adding InfoPath Form Template with  default
• Display Form Markup – location – list infopath from, content type id, list form mode – Readonly
•  <WpNs0:BrowserFormWebPart runat=”server” AllowEdit=”True” AllowConnect=”True” SubmitBehavior=”FormDefault” ConnectionID=”00000000-0000-0000-0000-000000000000″ Title=”InfoPath Form Web Part” IsIncluded=”True” Dir=”Default” ExportMode=”All” IsVisible=”True” ShowFormRibbon=”True” AllowMinimize=”True” ExportControlledProperties=”True” ZoneID=”Main” ID=”g_64f73c52_bc1c_4f65_9e84_eae4ce99e904″ FormLocation=”~list/Project Request/template.xsn” FrameState=”Normal” SendDataOnPostback=”True” AllowHide=”True” SuppressWebPartChrome=”False” DetailLink=”" ChromeType=”None” HelpLink=”" MissingAssembly=”Cannot import this Web Part.” PartImageSmall=”" AllowRemove=”True” ListFormMode=”ReadOnly” ContentTypeId=”0x010094C0C4EE6041654B87BF74AF58C0253000AACADF5B2CC621429FBDD7D807134CF8″ HelpMode=”Modeless” FrameType=”None” AllowZoneChange=”True” PartOrder=”2″ Description=”Use this Web Part to display an InfoPath browser-enabled form.” PartImageLarge=”" IsIncludedFilter=”" __MarkupType=”vsattributemarkup” __WebPartId=”{64F73C52-BC1C-4F65-9E84-EAE4CE99E904}” WebPart=”true” Height=”" Width=”"></WpNs0:BrowserFormWebPart>
• Edit Form  Markup – location – list infopath from, content type id, list form mode – Editable
• <WpNs0:BrowserFormWebPart runat=”server” AllowEdit=”True” AllowConnect=”True” SubmitBehavior=”FormDefault” ConnectionID=”00000000-0000-0000-0000-000000000000″ Title=”InfoPath Form Web Part” IsIncluded=”True” Dir=”Default” ExportMode=”All” IsVisible=”True” ShowFormRibbon=”True” AllowMinimize=”True” ExportControlledProperties=”True” ZoneID=”Main” ID=”g_0bda7a45_0e6c_4e4e_b861_1492d88ce90c” FormLocation=”~list/Project Request/template.xsn” FrameState=”Normal” SendDataOnPostback=”True” AllowHide=”True” SuppressWebPartChrome=”False” DetailLink=”" ChromeType=”None” HelpLink=”" MissingAssembly=”Cannot import this Web Part.” PartImageSmall=”" AllowRemove=”True” ListFormMode=”Editable” ContentTypeId=”0x010094C0C4EE6041654B87BF74AF58C0253000AACADF5B2CC621429FBDD7D807134CF8″ HelpMode=”Modeless” FrameType=”None” AllowZoneChange=”True” PartOrder=”2″ Description=”Use this Web Part to display an InfoPath browser-enabled form.” PartImageLarge=”" IsIncludedFilter=”" __MarkupType=”vsattributemarkup” __WebPartId=”{0BDA7A45-0E6C-4E4E-B861-1492D88CE90C}” WebPart=”true” Height=”" Width=”"></WpNs0:BrowserFormWebPart>
• New Form Markup – location – list infopath from, content type id, list form mode – Editable
• <WpNs0:BrowserFormWebPart runat=”server” AllowEdit=”True” AllowConnect=”True” SubmitBehavior=”FormDefault” ConnectionID=”00000000-0000-0000-0000-000000000000″ Title=”InfoPath Form Web Part” IsIncluded=”True” Dir=”Default” ExportMode=”All” IsVisible=”True” ShowFormRibbon=”True” AllowMinimize=”True” ExportControlledProperties=”True” ZoneID=”Main” ID=”g_29a83ffc_f17a_47ff_8812_ad34639e713f” FormLocation=”~list/Project Request/template.xsn” FrameState=”Normal” SendDataOnPostback=”True” AllowHide=”True” SuppressWebPartChrome=”False” DetailLink=”" ChromeType=”None” HelpLink=”" MissingAssembly=”Cannot import this Web Part.” PartImageSmall=”" AllowRemove=”True” ListFormMode=”Editable” ContentTypeId=”0x010094C0C4EE6041654B87BF74AF58C0253000AACADF5B2CC621429FBDD7D807134CF8″ HelpMode=”Modeless” FrameType=”None” AllowZoneChange=”True” PartOrder=”2″ Description=”Use this Web Part to display an InfoPath browser-enabled form.” PartImageLarge=”" IsIncludedFilter=”" __MarkupType=”vsattributemarkup” __WebPartId=”{29A83FFC-F17A-47FF-8812-AD34639E713F}” WebPart=”true” Height=”" Width=”"></WpNs0:BrowserFormWebPart>

• Best Practices – Do not customize the InfoPath Form Web Part properties on the Add, Edit, and Display InfoPath forms from the SharePoint Designer. Instead customize web part propreties from the browser UI. Form the List Ribbon, customize the form web parts from the content types section.
• With the Customized InfoPath List Form, SharePoint List item view form would look like this.

 Step 4 => Upgrade InfoPath List Forms

• Modify/Enhance/Upgrade the InfoPath List Form
• Any list schema changes requires updating the InfoPath form. Any major schema changes in the SharePoint List would make associated InfoPath Form Invalid. Use “Customize Form” from the Projects List Ribbon bar.
• Update/Redesign the List Form and republish it to sync the List changes to the InfoPath Form Schema

Step 5 => Retract InfoPath List Forms

• Clean up the InfoPath List Form
• From List Settings -> Form Options, you have option to revert back to the out of the box ASP.NET forms and option to permanently delete the associated list form or preserve the InfoPath forms

Hopefully this will be helpful designing your next InfoPath List Form for SharePoint 2010.

InfoPath 2010 and SharePoint 2010 – List Forms vs Library Forms

Not many aware of two different kinds of InfoPath forms available in SharePoint 2010 – List Forms and Library Forms. Having understanding of both kind of InfoPath forms is one of the most fundamental knowledge one need to have to be better InfoPath Developer for SharePoint 2010. Since you can’t switch from List forms to library forms or vice-a-versa (Yes, you read it right, you can’t switch or migrate forms from one to another), deciding on List vs Library form becomes one of the most fundamental architectural decisions part of InfoPath-SharePoint solution.

This article highlights high level differences between SharePoint 2010 List and Library forms and guidance on when to use which kind of forms. Microsoft has published wonderful article on this topic and my article is extension of what’s been published on InfoPath blog with some additional notes I have taking over the last two years.

InfoPath Team Blog Reference – http://blogs.msdn.com/b/infopath/archive/2010/04/22/comparing-list-and-form-library-forms.aspx

 List Forms – Tied to the List – New in SharePoint 2010

• Key Characteristics
• InfoPath Form Tied to specific SharePoint list, Not reusable
• InfoPath Form Schema tied to SharePoint List Schema
• InfoPath Form data stored in the SharePoint List.
• Each control (e.g. text box) in the InfoPath Form is bound to a column in the SharePoint List.
• Supported on Simple SharePoint Lists like Custom List, Tasks, Announcements, Links, Contacts from Browser Interface
• Not supported in Document Libraries and advanced SharePoint Lists like Calendar
• You can customize or edit the published form template by clicking the “Customize Form” on the List Ribbon Bar
• Requires SharePoint Designer to customize the External Lists Forms
• Limitations
• Tied to only 1 list and data stored directly in list
• No Custom Code Support
• No Digital Signatures
• Because it’s tied to specific list, No Custom Submit or Publish Behavior
• No Repeating and Nested Data
• No Print Support – No Print Button
• Not available to the Document Library
• Use cases
• To customize the out of the box List New Form, List Edit Form, and List Display Forms
• Ideal for Prototypes or quickly get started building InfoPath forms on SharePoint
• Ideal for No-code solutions using SharePoint Designer and Browser Customization for Pre-Packaged Departmental SharePoint Sites designed on production farm.
• To use forms offline with SharePoint Workspace
• Important Note => Do not use List InfoPath Forms in formal code management and deployment scenarios from development to staging to production.

Library Forms – Tied to the form libraries – Same as SharePoint 2007

• Key Characteristics
• Form data stored as the XML document in SharePoint Form Library
• More complex and requires more disciplined/structured approach
• Can be deployed as Sandbox or Farm Level Solutions
• Unlike list forms, Form library forms can be deployed as a template to associate the forms to multiple form libraries across multiple sites or site collections or multiple web applications within farm
• Publishing Mechanism
• Single Library
• Deploy/Publish to specific form library
• Not reusable in another library
• Only one form template per form library, Same benefits as list InfoPath forms. If Form is directly published to form library, another form can’t be published or used in the form library.
• Reusability across Site Collection – Multiple libraries or sites within Site Collection
• Deploy/Publish as a Form Content Type and Later use Content Type in the Form Library
• Enables deployment to multiple document libraries
• Supports multiple content type per library. It means,  multiple types of Form Templates can be tied to the Form Library using multiple Form Content Types
• Reusability across Farm – Multiple Site Collections or Web Applications
• Deploy/Publish as a Administrator Approved Form Templates
• Once deployed and activated on the site collection, Administrator-Approved Form Templates behaves same as Content Type Form
• Deploy as features and solutions framework, PowerShell, or manual upload
• Use cases
• Form Templates with Code
• Form Templates with Tabular Structure (Nested or Repeated Data) or Complex Schema
• Form Templates with Printing Support from Ribbon Bar
• Form Templates with Custom Submit Behavior
• From Templates needs to be digitally signed
• Great fit for Enterprise Level Form Solutions with Advanced and Reusable Functionality
• Admin Approved forms eases future enhancements, upgrade, maintenance, and deployment
• Fits perfectly in formal deployment process of moving features from development to staging to production.
• Form is not hosted in SharePoint or you need to store results as an XML document

Step by Step – Debug InfoPath 2010 Forms Deployed on SharePoint 2010 Using Visual Studio 2010

Unlike typical SharePoint 2010 development with Visual Studio 2010, debugging browser based InfoPath 2010 forms code is never easy. InfoPath custom coding requires specific software like VSTA (Microsoft Visual Studio 2005 Tools for Applications) to write custom code and one of the biggest limitations of the VSTA is it doesn’t support attaching debugger. Without VSTA supporting inbuilt debugging of InfoPath Code, you have to go through series of manual workarounds to debug the code.

Although Microsoft has documented detailed blog on the InfoPath team blog on how to debug InfoPath forms code, there are several details missing and it may become frustrating experience if you can’t hit the breakpoint while debugging InfoPath 2010 code. There are couple of clarifications Microsoft blog is missing including setting correct compile settings in the visual studio configuration manager. Without correct configuration manager setting as Active(Debug), even though project settings display debug for build settings, VSTA compiles code in the release mode which would further frustrate the developer if breakpoint never gets hit during debugging.

Based on lessons learned, I have finally decided to write detailed step by step blog to debug InfoPath 2010 Forms using Visual Studio 2010. Please keep in mind that to debug the browser based InfoPath forms, you will have to attach the SharePoint process in the Visual Studio, not Visual Studio Tools for Applications. Any version of Visual Studio is fine – VS 2005, VS 2008, or VS 2010.

Microsoft Resources

• Debugging Browser Forms – http://blogs.msdn.com/b/infopath/archive/2006/11/24/debugging-browser-forms.aspx
• How to Debug Browser-Enabled Forms Deployed on InfoPath Form Services – http://msdn.microsoft.com/en-us/library/aa944831(v=office.11).aspx
• Investigating Issues with InfoPath 2010 SharePoint Applications – http://msdn.microsoft.com/en-us/library/gg271285.aspx

Here are the high level steps.

Step 1 – Ensure the InfoPath Form Template is compiled in the Debug Configuration so the XSN includes the symbol (PDB) file.

You must configure Visual Studio Tools for Applications (VSTA) to build PDB files for your form solution by outputting full debug information.

• 1A – Open the InfoPath form template in the Design Mode using InfoPath Designer 2010
• 1B – Open the code editor from the “Developer” Tab in the InfoPath Designer 2010. This will open the code files in the using Visual Studio Tools for Applications.
• 1C – Verify that VSTA debugging options are enabled
• Visit the Tools -> Options -> Projects and Solutions and make sure “Show Advanced Build Configuration” is checked.

 

• Visit the Tools -> Options -> Debugging and make sure Enable “Just My Code (Managed Only)” and “Suppress JIT Optimization on module load (Managed Only)” Options are checked.

• 1D - Make sure you have Active(Debug) configured from the Build Configuration Manager => Build -> Configuration Manager
• Set Active Solution Configuration – Debug
• Set Active Solution Platform – Any CPU

• 1E – Verify Project Debugging Options are properly configured to Active (Debug) from the projects properties window by right clicking, Project -> Properties.
• On the Build Tab -> Set Configuration to Debug and Advanced -> Debug Info to Full. If you see only Debug option and missing Active (Debug) as configuration options, please ensure that Debug is configured on the Configuration Manager as described on previous step.

• On the Debug Tab -> Set Configuration to Active (Debug)

• Compile the InfoPath Code in debug mode to include the PDB files
• Verify that DLL and PDB files are created in the \bin\debug folder

Step 2 – Publish the InfoPath Form and verify that XSN file contains the debugged version of the compiled code.

Rename the XSN files to the CAB file to extract the InfoPath Form Template files and it should contain the debug version of the DLL and PDB files

Step 3 – Deploy the Debugged Version of InfoPath Form Templates to the SharePoint 2010

Step 4 – Debug the InfoPath Form Code through Visual Studio, not  VSTA

• 4A – Open the browser and browse to the SharePoint site where forms are deployed. This will fire up the IIS worker process which needs to be attached to the Visual Studio for the debugging.
• 4B – Open the FormCode.cs in the Visual Studio 2010

• 4C – Attach w3wp Process from the Debug -> Attach to Process menu in the Visual Studio 2010. Make sure show all the processes from all the sessions and all the users are checked. Additionally, Make sure “Managed” is selected in Visual Studio 2008 and “Managed (v2.0, v1.1, v1.0) code” is selected in Visual Studio 2010.

• 4D – Set the breakpoint in the code where you want to debug
• 4E – Run the Browser based InfoPath form to execute the process that will cause the breakpoint to be hit. Congratulations. You are all set.

Hope this helps to clarify the steps needed to debug the InfoPath 2010 code deployed to the SharePoint 2010 environment.

InfoPath 2010 SPSChicago Presentation is Available

First of all, thanks to everyone who has made to my InfoPath 2010 List vs Library Form session today at the SharePoint Saturday Chicago. It was so nice to see virtually full house in all of the sessions and special interest on InfoPath forms. Overall, It was a great fun and nice to meet many folks I have known from twitter.

As promised, here is the presentation available on the slideshare. Feel free to use it as a reference. I am hoping to write a detailed blogs on the design/development life cycle of list forms and library forms.

http://www.slideshare.net/patenik2/real-world-infopath-with-sharepoint-2010-list-vs-library-forms

Feel free to reach out to me if you have any questions.

Speaking on InfoPath List vs Library Forms Usage at the SharePoint Saturday Chicago

 

I will be speaking at the SharePoint Saturday Suburb Edition on June 11th with the session title – Real World InfoPath in SharePoint 2010 – List Forms vs Library Forms

I will be taking InfoPath and SharePoint developers and business analysts on the journey of design patterns of electronic forms in the SharePoint 2010 discussing basic differences of List Forms vs. Library Forms, real world use cases of each type of forms, under the hood of form design and deployment, and full life cycle of InfoPath applications (design, deployment, upgrade, retract, cleanup) in the SharePoint 2010 environment.

Please visit SharePoint Saturday Chicago site for more info -  http://www.sharepointsaturday.org/chicago/default.aspx. You can also follow the twitter IDs – @SPSChicago and @nikxpatel and twitter hashtag #SPSChicago for most recent updates. Here is the full schedule – http://spschicago.com/schedule/schedule.pdf

This year’s suburban edition of SharePoint Saturday 2011 will feature 3 unique tracks in hopes to provide more value to the attendees.  These unique tracks will be

• SharePoint 101 and will seek to walk users through the beginning of their SharePoint journey from the basics of what is SharePoint to planning your implementation.
• Office365.  You have heard about it, but what is it really and how may it apply to your needs?
• Advanced topics in SharePoint.  If you are one that typically does not go to user group or community events because there is not enough depth. This track is for you.  We will be showcasing some experts in the SharePoint community imparting their bits of wisdom. 

In short this year’s suburban edition will seek to provide something for everyone, and attract an audience from the entire spectrum. 

Looking forward to see you there.

InfoPath User Roles in Browser-Based Forms in the SharePoint 2010 Without Code – What’s Really Working?

Recently one of my clients asked if I can disable or enable some of the buttons on the browser based forms based on the SharePoint Security groups without any code. Based on what I have learned from the community and conferences, my response was Yes. Based the many of the blog articles referenced in this article, I thought it would be one of the no-brainer tasks but it turned out it is much more complicated than that.

I am using the InfoPath 2010 as a SharePoint 2010 list forms and as many of you may know, List Forms doesn’t support the custom code.  So, Lets look at our options and what would be best options to implement security roles in the browser based forms in the SharePoint 2010 without custom code.

Option 1 – User Roles

• Only available on the InfoPath Client Forms, Not Available on the browser forms. It means, it requires InfoPath 2010 on the client machines to access the InfoPath forms stored in the SharePoint.
• http://office.microsoft.com/en-ca/infopath-help/add-a-user-role-HP010165119.aspx
• Biggest limitations of the User roles cannot be created based on SharePoint Groups, only AD groups.
• Nik’s Conclusion – Can’t be considered for browser based forms or SharePoint Security Groups

Option 2 – User Profile Service

• UserProfileService.asmx – http://technet.microsoft.com/en-us/library/ee662538.aspx
• User Profile Service is the interface for remote clients to Read and Create User Profiles in the SharePoint
• In SharePoint 2010, this service requires both User Profile Service and User Profile Synchronization Service. In MOSS 2007, profile synchronization must be enabled on the SSP.
• This options Works for browser based form in MOSS 2007 but with limitations in the SharePoint 2010. More precisely, at the moment of writing, this option works for AD DL, AD SG, and SharePoint Groups in MOSS 2007 but Works only for AD DL in the SharePoint 2010. As many of you may know, SharePoint 2010 Profile Sync is using the FIM and it’s not returning the AD SG through profile service. Until Microsoft fixes this issue, AD Security Groups are not available through User Profile Service. Here is the active thread on the MS forum to discuss this issue lead by Clayton Cobb – http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/8ede2f40-2b11-416b-b426-51c1b6479c33
• To use User Profile Service in MOSS 2007, there are several blog posts available.
  • Option 1 - userprofileservice.asmx, GetUserMemberships
    • In SharePoint 2010, Supports only AD Distribution Lists, No AD Security Groups, No SharePoint Security Groups
    • In MOSS 2007, Supports AD Distribution Lists, AD Security Groups, and SharePoint Security Groups
    • Laura Rogers’s Article - http://www.sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?ID=88
  • Option 2 - userprofileservice.asmx, GetCommonMemberships
    • In SharePoint 2010, Supports only AD Distribution Lists, No AD Security Groups, No SharePoint Security Group
    • In MOSS 2007, Supports AD Distribution Lists, AD Security Groups, and SharePoint Security Group
    • Clayton Cobb’s Article - http://claytoncobb.wordpress.com/2009/07/19/infopath-user-roles-in-browser-enabled-forms-using-groups/
• Nik’s Conclusion – Use this method is you have MOSS 2007. This method is not reliable for the SharePoint Security Groups or AD SG groups in the SharePoint 2010. Use this method only if you have users maintained in the AD Distribution List. Limitations of the AD DL list is it can’t be used as the SharePoint Security Groups in SharePoint 2010 or maintain the duplicate SG and DL groups in the AD. In other words, in most real world scenario, this method is not useful in SharePoint 2010. If you’re still planning to use this method then make sure both User Profile and User Profile Sync service is enabled. I have tested it with only User Profile Service is enabled and this method doesn’t return any AD DL info if only User Profile Service is enabled.

Option 3 – Duplicate SharePoint List to emulate the users and their roles

• There are several workarounds by the community members to map the SharePoint Security Groups or AD SG groups in the SharePoint Lists to use the security in the browser based forms. Downside of this approach is to maintain the security info in two places and additional auto or manual synchronization steps may be required.
• SharePoint Solution’s Custom List Method - http://sharepointsolutions.blogspot.com/2009/04/how-to-emulate-user-roles-in-infopath.html
• Clayton Cobb’s Contact List Method - http://claytoncobb.wordpress.com/2009/06/14/user-roles-in-browser-enabled-forms/
• Nik’s Conclusion – Unless someone convince me with the better method, In SharePoint 2010, these are the most reliable methods to implement the security. I have implemented SharePoint Solutions method and without doubt, even though it maintains the duplicate security info, it’s most reliable method.

Hope this helps making right decision for given situation.

InfoPath Form Services – 5566 Error – “exceeds the lookup column threshold”

I have recently came across the error – “5566  – Lookup columns exceeds the threshold enforced by the administrator” while browsing the InfoPath forms on the SharePoint Site using the users with the “Domain Users” active directory permission. My curiosity of this problem got increased as I was able to browse these InfoPath forms using the users logged in with the “Domain Admins” active directory privileges.

I had a SharePoint list with 1 lookup field and 10 person/group fields. As many of us are aware that by default, SharePoint installation configures each web application list view threshold to 8. You can access this setting from Central Administration -> Application Management -> Manage Web Applications. Select the web application hosting your site collection or site and select the General Settings -> Resource Throttling. From this page, you can maintain the list view thresholds and it specifies the maximum number of Lookup, Person/Group, or workflow status fields that a database query can involve at one time. Please note that this setting doesn’t apply to the SharePoint users with the “Domain Admins” active directory privileges. It applies to only general end-users with the “Domain Users” active directory privileges.

What I really didn’t know is what columns were considered as lookup columns.  All I knew until this moment is only Lookup fields were considered as a Lookup columns in the list view threshold limit.  Based on the error message, it was obvious that if I increase the lookup column threshold for the SharePoint web application through central admin site, users with “Domain Users” won’t have any issues accessing the InfoPath forms on the browser. The real issue here is should I increase the threshold or find the alternative solution.

 

As you create the SharePoint Lists, the list view threshold limits number of lookup, person/group, or workflow status columns can have in specific views. Please note that I have mentioned here views. SharePoint list doesn’t limit you to have 8 columns in the list. You can create more than 8 lookup and person/group columns as long as your view doesn’t show more than 8 columns. But, if you have more than 8 columns, list throttling takes in place behind the screen causing performance degradation.

The real Problem starts when you are consuming SharePoint list in the client applications like SharePoint Workspace, Microsoft Access, or InfoPath including InfoPath Form Services. Let’s say, If you have customized the InfoPath forms based on the SharePoint list and created a InfoPath Views based on the SharePoint fields and later on use in the InfoPath Web Part, InfoPath will throw an error if users with “Domain Users” AD permission accesses the InfoPath form views. The reason for this is even though InfoPath form view might won’t show all the fields, InfoPath form itself trying to synchronize all the InfoPath form fields with the underlying SharePoint list. it means in my scenario where I have 1 lookup field and 10 person/group fields, it tries to query more than 8 fields with lookup or people/person group columns. In this case, the above error of the “Lookup columns exceeds the threshold enforced by the administrator” shows up. For the office synchronization, SharePoint list threshold limits the number of lookup and person/group fields. Please note that workflow status columns used only in the SharePoint Views. Since workflow status columns are not considered as a List Column for the specific list, it gets excluded from the threshold limits for the offline clients or office apps synchronization.

To resolve this issue, basically there are really only two options. If you can find more, let me know.

1 – Increase the threshold on the Central Admin Site for the given web application. Downside of this approach is since you are changing these settings at the web application level. For performance reasons, SharePoint limits the number of lookups allowed when offline clients try to sync. If you change these settings, it will apply to all the site collections and sub sites underneath it causing potential performance issues.
http://planetmoss.blogspot.com/2010/07/sharepoint-workspace-2010-exceeds.html

2 – Try to limit the total number of workflows, lookup, and person/group fields less than or equal to number of threshold settings. Please remember that every list or document library has two in-built people/group fields called created by and modified by. So, in reality, if you would like to use the lookup, person/group columns in the SharePoint Views and Client applications like SharePoint workspace, InfoPath, and Microsoft access and if your threshold settings are 8, you can’t have more than 6 fields with lookup or person/group fields. Now, that’s what you call harsh reality.

Hopefully this post will give you balance views of two sides of 8 lookup column list threshold limit and you will able to make educated decision.