InfoPath 2007 – Populating from data/dropdown info from SharePoint lists using XML form data

This article applies to InfoPath 2007 only as 2010 and 2013 no longer have this limitation.

At some point during your career, you will need to connect to a SharePoint list to retrieve information for a drop down list.  This process has changed since 2007 but I thought that I would go ahead and share this.  InfoPath 2007 only reads the data in the list on how it was entered in to SharePoint.  For example, if you uploaded a list of addresses in no particular order, this is the way that InfoPath reads it.  Now, what if you would like to see your selection sorted alphabetically?  You will first need to either modify your existing view of  the list you will be using as a data source or create a new view.

***Note: In order to see the entire list, the view must be changed to show more than the default 100 records

InfoPath will not recognize this view and you cannot sort lists when forms are browser based. As stated earlier, it only sees the original layout. In order for InfoPath to populate the drop down list with the correct format you will need to do the following:

  1. Double click the drop down list that you want to add the data connection to. This box will pop up. Follow the steps in the remaining screenshots to step 4. Then follow further directions.
  2. 1  
  3. 2  
  4. Go to the site that contains your list that you want to pull your data from.
    Example: http://yoursite.com/subsite/ and open the list
    Click “Settings” on the taskbar of the list then click “List Settings”
    Click on the view that you would like to use located at the bottom of the page
    When the page opens, highlight the entire address bar and press Ctrl+C
    Open a text editor (Notepad) and press Ctrl+V. Your address will look something like this:http://yoursite.com/subsite/_layouts/ViewEdit.aspx?list=%7B9C833605%2D8911%2D4B69%2D84AA%2D24DF8775735F%7D&View=%7B734A3221%2D4E03%2D4B10%2D9BE9%2D857775EBF802%7D&Source=%252Fdepartments%252Fhealth%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D%25257B9C833605%25252D8911%25252D4B69%25252D84AA%25252

    D24DF8775735F%25257D

  5. You will need to modify this address to provide the form with the correct XML values. To do so, either type or copy/paste from this address the following.
    Make sure to replace yoursite.com/subsite with your own variables:http://yoursite.com/subsite/_vti_bin/owssvr.dll?Cmd=Display&List=%7B9C833605%2D8911%2D4B69%2D84AA%2D24DF8775735F%7D&View=%7B734A3221%2D4E03%2D4B10%2D9BE9%2D857775EBF802%7D&XMLDATA=TRUE
  6. The %, letter, number string represents the {GUID} for the page. The &XMLDATA=TRUE converts the page into an XML format that the InfoPath form can read. The &View and string represents the {GUID} for the “AllItems View”
  7. Once you have modified the url, copy and paste it into the next setup page. Close the text editor and do not save when it asks. Follow the steps in the remaining screenshots.
  8. 4  
  9. 5  
  10. 6  
  11. 7  
  12. 8  
  13. 9  
  14. Save the InfoPath form and publish it to the SharePoint site.  Next, login to SharePoint and go to the form that contains this data connection. Verify that the information is displayed in the correct order.

This little trick will save you all kinds of headaches if you rely on information supplied by your SharePoint lists.

Improperly setup AD account can cause issues

If you are using the people picker column in a list, watch out.  You could be faced with an odd little issue.

Recently, I created a list to control the views in an InfoPath form.  This list uses a people picker column that pulls the “UserName”.  What I noticed was that three of the users were displaying differently.  Instead of all lowercase letters, the first two letters of the username were capitalized.  As it happens, one of those users was unable to see a certain view that they had access to.

I had our AD guy change the names of the users to all lowercase letters.  I then ran a full AD import into SharePoint.  I waited a little while, pressed CTRL+F5 on my keyboard and noticed that the name changed in the permission checking list.

I logged in as the user and was now able to see the correct view.  Weird huh?

SharePoint 2007 – Enable left navigation for Web Part pages

If you have published an InfoPath form to a SharePoint site, chances are that you have created a webpart page and have published it there.
We part pages are great but you lose the left navigation and it can become confusing to some users.

There is however a simple fix that will enable the left nav for your pages. You will need to open up SharePoint Designer to do this.

  1. Open SharePoint Designer and navigate to your page
  2. Switch to code view and scroll down until approximately line 45.
  3. You see the following two lines.
  4. Highlight these two lines and delete.
  5. Save your page and that’s it!

<asp:Content ContentPlaceHolderId=”PlaceHolderLeftNavBar” runat=”server”></asp:Content>
<asp:Content ContentPlaceHolderId=”PlaceHolderNavSpacer” runat=”server”></asp:Content>
It will look similar to the image below: