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.

InfoPath – Difference between days

I had a request for a form to count the number of days between two date pickers as one of it’s functions.  All that you need to make this happen is add two date picker controls and one text field to display the count.  I have included an InfoPath sample form that explains this in detail and should provide you with the knowledge to add something like this to one of your forms.

I originally found this form on the following site when I faced this issue: https://alecpojidaev.wordpress.com/2008/12/30/infopath-codeless-programming-walkthrough-2/

Alec Pojidaev has many great techniques for using no code solutions which I am a big fan of.  I suggest you check out his site to see all of the advanced features he has laid out.

Click the link below to download the zip file.

DifferenceBetween2Days

SharePoint – Dropdown Menu/Navigation Does Not Work

Have you ever logged on to your SharePoint site and the drop down navigation does not work?  After days of clicking around, I decided to fix the issue, which really was not much of fix.

Turns out, this is an issue with my MS Office installation.  All that you have to do is run a repair of your Office  install under the add/remove programs area.  Once the repair is complete, log back on to your SharePoint site and the menu should now appear.

Editing a Windows 7 Hosts File

I know that this may be common knowledge to most people in the IT world, but sometimes I forget even the simplest of tasks.  I haven’t edited a hosts file in 7 years so it took me a minute or two to remember where I was supposed to do this.  Now that I am running Windows 7, there is one more thing that I had to do…..Run as Administrator.

I decided to create a short video on how to edit your hosts file.  Enjoy!

[KGVID width=”640″ height=”360″]http://saltypc.com/wp-content/uploads/2013/02/SaltyHostsFile.mp4[/KGVID]

 

Synchronize SharePoint list using Access 2007

If you need to make changes outside of SharePoint to a list, you always have the option to use Access.  Follow these steps and you will be well on your way to using this feature.

1. Open your SharePoint list

2. Click Actions  –> Open with Access

Actions dropdown

3. A dialog box will pop up on your screen. When it does, click OK.

Access Prompt

4. After you click OK, Access 2007 will pop up on your screen. Double click on the list.

SharePoint List

5. When the list pops up you can begin to edit. Once finished, do not click the Publish to SharePoint Site button. Simply close Access 2007.

Access View
Please remember that Access 2007 is online with SharePoint and any changes you make will reflect instantly. There will be no going back.

A No-No for having Multiple SPD Workflows for Lists/Libraries

Word of warning:

If you need to have more than one workflow for a particular list/library, DO NOT copy a SPD workflow and paste back into the same library.

What happens is that even if you change all of the properties of the files (names, etc.), there is still a hidden property that SharePoint likes to hold on to and uses to identify the workflow.  Think of it as an ID.

Basically, you will have two different workflows with the same ID.  When you publish one of these to a list/library, everything works fine.  When you go to publish the second one, everything seems fine but after close evaluation, you will notice that the first workflow that you published is no longer attached to the list/library.

Moral of the story, don’t be lazy.  Create a new workflow even if all of the information is the same.

Retrieve Active Directory Information from the User Profile Service – part 1

Very often in InfoPath, I need to query Active Directory for certain pieces of information just so that I can have some type of functionality within my forms.  One of the most used and easiest forms of this is the user profile service.  I use this to my advantage to query e-mail addresses, user account info, etc.  Also, this is a must if you use the Contact Selector control from within InfoPath.

Follow these steps and you will be on your way.  Stay tuned for part 2 as I will be showing how to query the display name for AD accounts in a later post.

GetUserProfileByName Data Connection

  1. Create a new data connection to “Receive Data” from “Web Service”.
  2. Click Next.
  3. Type in the following url: http://yoursite.com/_vti_bin/userprofileservice.asmx?wsdl
  4. Click Next
  5. Select “GetUserProfileByName
  6. Click Next
  7. Leave the default value for Parameter
  8. Click Next
  9. Leave box unchecked

10. Click Next

11. Name the connection “GetUserProfileByName”

12. Check the box to automatically receive data

13. Click Finish.

14. The wizard will look like the images below.

 WebService

DataConnection

Need to edit multiple items to kick off a workflow? No prob Bob!

I was faced with this task recently.  Let me paint the picture for you.

I have a document library with over 1,000 items in it.  I added a new column (Named) that needs to have the same information as an existing column (Name).  For whatever reason, the web part I am using for my list search does not recognize the existing column.  I created a SP Designer workflow to copy the contents from the ‘Name’ to the ‘Named’ on creation or edit.

To do this, there are a couple of options.

  1. The long drawn out option – Edit each file individually
  2. The best option for me – Content and Structure
  3. Power option – SharePoint Designer

I went with option 2 because I did not want to lag the server with a whole lot of who ha.  Here are the steps I followed to make this happen.

  1. Go to the site, Site Settings, Content and Structure
  2. Expand the library to perform this action
    01
  3. Place a check into the ‘Select All’ check box
    02
  4. Click on the ‘Actions’ drop down and select ‘Check Out’
    03
  5. Once checked out (you may have to do this twice), click the ‘Actions’ drop down and select ‘Check In’
    04

Make sure to check that you have performed this action for all items in your view.  Mine were in groups of 100, which is generally the standard view.  The fasted option for me would have been the SharePoint Designer route but I did not want to take a chance of checking out over 1,000 items at once and then check them back in.  We already have enough load on the servers from all of the other users accessing SharePoint.

The converter failed to save the file….well crap!

Have you ever seen this message before? Chances are, probably not but in my organization this might rear it’s ugly head from one time or another.

I received a call from one of my user’s and they said that they could not edit an Excel file from withing SharePoint. Since we have a mixed MS Office environment, I first verified what version of office was installed (2010). After that, I checked to see how my system performed (no issues). Then, I go through the process that most admins go through when troubleshooting; “Why is this working for me”, “What could it be”, “Maybe it’s just the user”, etc. Once that wears off, I sit back and think for a minute. Turns out, not a big issue at all, just a step left out in an upgrade process.

What happened was the user had Office 2003 installed on their computer and installed the compatibility pack to open newer versions. Eventually, the user upgraded to Office 2010. When this happened, the office compatibility pack was not installed, resulting in the error ‘The converter failed to save….’ blah blah blah.

The solution? Well, you guessed it…. Uninstall the compatibility pack. Simple. Done.