Infopath, Sharepoint and retrieving data; a terrible problem solved

Recently I had worked on a temporary InfoPath application to be run on the internal Sharepoint server for doing performance reviews for staff.  This was a great learning experience that was a lot of trial and error to solve several upgrade requests from a previous version and to pull forward goals that had been previously defined in an earlier version.

So, to be clear, I was working with existing data in a predefined repeating section.  Anyone could have any number of goals and I had to figure out how to get the data out to then import it into a specific performance review tool.  Turns out there were about 750 goals total for all the employees.

First, I had to get the data out.  I found that in InfoPath, during a publish, you can select a repeating section and it will automatically give you all the contents of the fields merged. This is great, but there is no way to split the information out if its just a bunch of text, because of no delimiters put in automatically (boy, that’d be nice…).  So after searching around online, I discovered that for any type of repeating section, I would have to create a new text box to contain the data and to use some magic to concatenate them together with a specific delimiter.  I chose ~ because it was unlikely to be in the data.  So, I create a TextBox (this was later changed to a Rich Textbox, but more on that later) with the default value of:

xdMath:Eval(xdMath:Eval(../my:goal, ‘concat(my:goalDescription, “~”)’), “..”)

This function of evaluating an evaluation will go in and iterate through each goalDescription and concatenate them with the “~”.  It is important to note that in order to get this to work, I had to edit the XPath to make sure that the my:goalDescription did not start with “../”.  This took a ton of trial and error and getting it to work exactly was very frustrating.

This evaluation gave me a chance to see all the goals together, but my problem was that this box would only update when I added a new goal or changed the goals value (note the “Update this value…” checked box?  Without it, it would never update).  So, I had to come up with a reasonable way to update all the forms (200 total) as efficiently as possible.  I solved this by adding a form rule that was applied any time the form was opened.

This opens up a dialog that allowed me to add a Rule, which I did here:

I had to change the XPath to work for the global location, because the goal data was buried not at the relative “../”, but in sectionFour.  When I changed the XPath from that, it was successfully updating my AllGoals variable with my concatenated value.  Here is the global version:

xdMath:Eval(xdMath:Eval(my:sectionFour/my:goal, ‘concat(my:goalDescription, “~”)’), “..”)

 

It was a quick task to them open each of the forms from the Sharepoint library manually to have this run to populate the AllGoals value for each employee.  But yet there were issues.  I published the AllGoals variable for Sharepoint to show me (on the last screen of the publish wizard, adding the variable to access).  I saw the entire value in my TextBox, but when I viewed it in the sharepoint list view, it was truncating at 255 characters.  Apparently all textboxes default to “Single Line of Text” in the column settings.  I was able to get around this by removing the published variable, change it to RichTextBox and then publish it again.  That caused it to be “Multiple Lines of Text” which gave me all my data.

 

Okay, home stretch!

I could then export the view to Excel which gave me all my goals and employee names like this:

Employee1                   Goal1~Goal2~Goal3~

But I wanted it to look like this:

Employee1                   Goal1

Employee1                   Goal2

Employee1                   Goal3

So, I turned to macros in Excel.  I should say that I’m not a macro guy, having done VBA a few times before, I really don’t like it (explanation).  After frustrating hours of messing with it, I posted the question on MrExcel.com and got an answer in two hours 😉  Here it is, if you care to see yucky VBA that worked perfectly: MrExcel Forum Thread

 

I was able to get preconfigured data out of Sharepoint, format it correctly and then send it off to be imported to the new system.  Problem solved, but please don’t make me do it again!