{"id":19,"date":"2012-12-04T14:12:55","date_gmt":"2012-12-04T19:12:55","guid":{"rendered":"https:\/\/www.ericsross.com\/?p=19"},"modified":"2012-12-04T14:12:55","modified_gmt":"2012-12-04T19:12:55","slug":"infopath-sharepoint-and-retrieving-data-a-terrible-problem-solved","status":"publish","type":"post","link":"https:\/\/www.ericsross.com\/?p=19","title":{"rendered":"Infopath, Sharepoint and retrieving data; a terrible problem solved"},"content":{"rendered":"

Recently I had worked on a temporary InfoPath application to be run on the internal Sharepoint server for doing performance reviews for staff.\u00a0 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.<\/p>\n

So, to be clear, I was working with existing data in a predefined repeating section.\u00a0 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.\u00a0 Turns out there were about 750 goals total for all the employees.<\/p>\n

First, I had to get the data out.\u00a0 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…).\u00a0 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.\u00a0 I chose ~ because it was unlikely to be in the data.\u00a0 So, I create a TextBox (this was later changed to a Rich Textbox, but more on that later) with the default value of:<\/p>\n

xdMath:Eval(xdMath:Eval(..\/my:goal, ‘concat(my:goalDescription, “~”)’), “..”)<\/p>\n

This function of evaluating an evaluation will go in and iterate through each goalDescription and concatenate them with the “~”.\u00a0 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 “..\/”.\u00a0 This took a ton of trial and error and getting it to work exactly was very frustrating.<\/p>\n

\"\"<\/a><\/p>\n

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?\u00a0 Without it, it would never update).\u00a0 So, I had to come up with a reasonable way to update all the forms (200 total) as efficiently as possible.\u00a0 I solved this by adding a form rule that was applied any time the form was opened.<\/p>\n

\"\"<\/p>\n

This opens up a dialog that allowed me to add a Rule, which I did here:<\/p>\n

\"\"<\/p>\n

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.\u00a0 When I changed the XPath from that, it was successfully updating my AllGoals variable with my concatenated value.\u00a0 Here is the global version:<\/p>\n

xdMath:Eval(xdMath:Eval(my:sectionFour\/my:goal, ‘concat(my:goalDescription, “~”)’), “..”)<\/p>\n

 <\/p>\n

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.\u00a0 But yet there were issues.\u00a0 I published the AllGoals variable for Sharepoint to show me (on the last screen of the publish wizard, adding the variable to access).\u00a0 I saw the entire value in my TextBox, but when I viewed it in the sharepoint list view, it was truncating at 255 characters.\u00a0 Apparently all textboxes default to “Single Line of Text” in the column settings.\u00a0 I was able to get around this by removing the published variable, change it to RichTextBox and then publish it again.\u00a0 That caused it to be “Multiple Lines of Text” which gave me all my data.<\/p>\n

 <\/p>\n

Okay, home stretch!<\/p>\n

I could then export the view to Excel which gave me all my goals and employee names like this:<\/p>\n

Employee1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Goal1~Goal2~Goal3~<\/p>\n

But I wanted it to look like this:<\/p>\n

Employee1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Goal1<\/p>\n

Employee1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Goal2<\/p>\n

Employee1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Goal3<\/p>\n

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

 <\/p>\n

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.\u00a0 Problem solved, but please don’t make me do it again!<\/p>\n","protected":false},"excerpt":{"rendered":"

Recently I had worked on a temporary InfoPath application to be run on the internal Sharepoint server for doing performance reviews for staff.\u00a0 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<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,11],"tags":[],"class_list":["post-19","post","type-post","status-publish","format-standard","hentry","category-problems","category-sharepoint"],"_links":{"self":[{"href":"https:\/\/www.ericsross.com\/index.php?rest_route=\/wp\/v2\/posts\/19","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ericsross.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ericsross.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ericsross.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ericsross.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=19"}],"version-history":[{"count":5,"href":"https:\/\/www.ericsross.com\/index.php?rest_route=\/wp\/v2\/posts\/19\/revisions"}],"predecessor-version":[{"id":25,"href":"https:\/\/www.ericsross.com\/index.php?rest_route=\/wp\/v2\/posts\/19\/revisions\/25"}],"wp:attachment":[{"href":"https:\/\/www.ericsross.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=19"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ericsross.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=19"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ericsross.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=19"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}