Extract Attachments Inside or Embedded Within InfoPath XML Forms

SITUATION: It's been a while but I'm back to working more with SharePoint - YAY - and was hit with my first unique request back in 2018: we want to find a way to extract files that are embedded inside of InfoPath attachment controls. Along the way, also had to figure out how to download all files in a library.

RESOLUTION: I apologize now: THERE IS NO OOTB WAY TO JUST GRAB THESE SUCKERS...you must use SOME form of code or script :(. So, I figured out two ways originally and have maintained one way to do this: PowerShell. This way, you can download the files locally and then extract the attachments (does use more disk space) but is probably done on a SharePoint admin server - I welcome some assistance in porting this to O365 so that anyone can run this on their own computer if they have appropriate perms

******CAVEATS******
The ways presented are definitely a beta and should be applied only to a test instance of SharePoint so you don't kill your server or computer. I'm definitely not responsible for you using this script and it messing stuff up - review it, test it, follow your company's standards for verification and testing, etc.
I welcome feedback if you discover a bug in it but feel free to use how you see fit, tweak it, whatever. It'd be nice to keep some credit if you end up doing amazing things with it - just let me know :)!
This was tested using PowerShell on a SharePoint Server 2010 instance. This may or may not work in other versions, I'm sure you could test it and let me know.
******END CAVEATS******

Now, it took me a while to get all the pieces together, so here are the links to the information needed to pull this off <begin credits>:

1. MS explains how to do this in visual studio code here
2. Chris White expounds on the same thing in some different detail here
3. Different stack exchange questions related to pushing and pulling from XML in a form library as well as handling the attachment raw data and rebuilding it here, here, and here
4. Masanao Izumo has a cross-browser implementation for converting an unsigned 8 byte integer array back to a string (text) here
5. (2022 update) - MS has some documentation on the byte header that they insert before files that have been embedded within their xml files here <end credits>

Powershell Script - Most recent update was 2022-05-02. Has 2 requirements and 3 optional pieces for which it prompts: 1) URL of the sharepoint web (aka the regular site, not site collection), 2) The name of the form library, and 3) if you are extracting InfoPath forms, what data source in the xml file do you want to use to create folder(s) on your computer in which to place all of the attachments - if it is an XML attribute instead of a proper data source then that is ok too, and 4) and 5) a cutoff date in case you want to only download items before a certain date or after a certain date. The last part that you *might* edit is the $filepath since that is the main path on your computer for where you want to stick all these attachment files. Here's the script link: https://github.com/hornerit/powershell/blob/master/Get-SharePointInfoPathFilesAndAttachments.ps1

Further Explanation: So here's how it pulls together:

1. InfoPath XML attachment files are first a long string that has been "encoded" into something called Base64. You don't have to know what that means, you just have to know they were encoded - which means to use them we need to decode them. That's the first step once we have the InfoPath XML file.
2. The attachment files (once decoded) are composed of a header portion and the data portion. The header consists of 2 parts: a fixed header set of information and the filename of the attachment.
3. Byte 20 of the header tells you just how long the filename is as a number. This is a byte but needs to be doubled for how the information is encoded (unicode or UTF8).
4. Once we know how long the filename is, we look *just* past the end of the regular, fixed header (byte 24) and copy every other byte till the end of the filename section - except for the very last one as it has a byte to tell the system that this is the end of the file name. The reason we do every other is that the ones in between are basically nothing or what programmers call 'null'. One weird thing - PDFs in my environment didn't have a file extension at the end of their name...so I created a small workaround to just append .pdf if there was not any extension mentioned.
5. Now that we have the filename, we need to separate out the contents from the header, so we create a new array that just starts after the end of the filename.
6. This new array needs to be somehow converted into a file...in *Windows* Powershell, we use the WriteAllBytes function to make it a file and we just tell it where the path is that it should write and give it this blob of goodness
7. Getting access to the file itself is easy for Powershell - when the script is done, go look at your folder. There's the "_DownloadedRawFiles" subfolder, which you can ignore for now, and there's all the other folders generated by the extraction process based on the group by aka Folder Structure Node supplied.

Limit number of words in an InfoPath textbox

So you might have figured out you can limit the number of characters in a textbox in InfoPath, but what if you have to limit the number of words someone types? This came up in one project because they were posting the words onto a PowerPoint slide. To do this you must use 'data validation' (InfoPath 2007) or a 'validation' rule (InfoPath 2010) and you will need to select 'the expression...' for your first dropdown in the conditions and type this:

string-length(.) - string-length(translate(., " ","")) > 60 (or the number of words you want)

What this does is pretty straightforward - it counts the number of typed characters in your field (assuming you are placing this rule on the field in which people type - that's the string-length(.) part) and then it subtracts the same number of characters but with all the spaces removed (they were removed by the translate function) and this checks to see if the total is greater than 60...if it is, then your validation rule will tell the user something's wrong because they done typed more than 60 words o_O. This goes on the assumption that every space represents a word...which should be pretty close. Have fun!

InfoPath Form Security Checklist / Flowchart

The following graphic should help most of you prevent many security issues with your InfoPath forms.  To be fair, one particular piece of functionality requires a codeplex addon called "SPDActivities" that you may have to convince your SharePoint manager to implement (if he/she hasn't already) or else you would be in SP 2010 and use the impersonation step.  These are the pre-requisites to this chart:

1. Create the following permission levels -
• Audit - Copy read and add the ability to "View Usage Data", "Manage Personal Views", and "Enumerate Permissions"...this permission is used for directors and auditors to see everything and do some reporting.
• Restricted Contribute - Copy contribute and remove the ability to "Delete Items" and "Delete Versions" and "Manage Personal Views"...this is used for users who have to edit an infopath form and, with versioning turned on for the library, they can't delete the original version of the form.
• Add Only - Copy Read and add the ability to "Add Items"...this is used for users who have to submit a form and need no subsequent access to it (or you want to secure it at that point)
2. Anonymous Users = users who don't login
3. Always remember that those with Contribute permissions can easily switch to Explorer view or the Merge/Repair pages to view every form in your library...so, try not to ever give anyone contribute.
4. A couple of these things will appear redundant - it's to doubly make sure you do them :)

Limit the number of characters in InfoPath multi-line boxes

Situation: we have a textbox we made in InfoPath. We decide that this box should be able to type multiple lines. We open the settings for the box and change click the checkbox that says "Multiline". It then greys out our ability to limit the number of characters in the box. We want to still limit how many letters that they can type! Here's how:

Use Data Validation on the box (in 2007, right-click the textbox and then click "Data Validation", click "Add"; in 2010, click the textbox, click the Control Properties in the ribbon, and click "Manage Rules", then click Add -> Validation rule, then click the condition).
[Update: a comment from a user below highlighted a limit in the solution: line breaks. A better way to accomplish this is to use the 'the expression' in the first dropdown instead of your field and then type "string-length(.)>500" without the quotes]
Now that you have the data validation window open you should see the ability to set your condition. The first box should be your field, the middle box should say "Does not match pattern", and then you click "Select a pattern" in the third box -> click custom pattern, and then type this:

.{1,500}

See, the Period stands for any character except new line breaks or carriage returns. Now, normally you would put an asterisk after that to mean that you can type as much as you want. Instead, the little braces allow you to place lower and upper limits for the number of characters. The 1 means that they HAVE to type at least 1 letter and the 500 means they get capped off at 500 for data validation purposes.

Now, this does NOT mean that the InfoPath box will STOP letting them type! It just means that, when they tab out of it, it will glow red and have a tooltip that you specify for them when they've typed too many characters. I tested this and it seems to work well for me; give it a try and tell me your results with it!

InfoPath Tutorial updates

Hey everyone,
I just wanted to let you know that I've updated the Introduction and Part 1 of my InfoPath 2007 Tutorial.  I'm sorry it took so long (over a year) to update them...work has been crazy and we've been learning so much about InfoPath and SharePoint 2007 that it's been scary...then we decided to push on to SharePoint 2010 so much of my time has been learning and planning on our 2010 setup.  Once I finish my 2007 tutorial, I'm going to put out a series on InfoPath 2010 so that you will have something for either program.  One thing I do have to say:  you can use InfoPath 2010 to make 2007 browser forms...and dear Lord if you have the opportunity to do that...TAKE IT!  There are a few major improvements in InfoPath 2010 that can be used to drastically cut the amount of time it takes to make a 2007 form.  I look forward to releasing InfoPath Tutorial - Part 2 - Color Schemes and Controls next week.

InfoPath Tutorial - Part 1

InfoPath date calculations and date comparisons without code

If you have the unfortunate task of doing some date calculations within InfoPath, you may have already found out the fun news:  InfoPath only gives you TWO functions dealing with date and time:  addDays (which lets you add a certain number of days to a date) or addSeconds (which lets you add seconds to a dateTime field).  This can be a little difficult when you intend on doing some calculations like adding a month to a specific date or subtracting a few days from a date to determine another day.  Here's a few ways you can do date calculations:

1.  Use addDays - the addDays function looks like this:  addDays(insert_a_date_field_here,"type_the_number_of_days_to_add_here").  An example would look like:  addDays(StartDate,"1") and this would add a single day to the Start Date field.  Key thing to note - you can add negative numbers here to subtract a certain number of days.  This can be useful in conditional formatting or data validation for greater than/less than comparisons (e.g. If DueDate field is less than [addDays(StartDate, "10")] then give the error message "Your due date is too soon; please set it for more than 10 days after the start date" in the data validation tooltip.

2.  Use addSeconds in the same manner as addDays - this can be done for time issues like turning an item in by 5pm on a certain day.  The reason this might be necessary is that, often, when calculating dates, the time assumed for a day is 12:00 AM on that day...so if you want a realistic time like 8am, you will need to use the addSeconds to give you a few hours...you'll just have to use a calculator to figure out how many seconds to add for so many hours/minutes.

3.  Adding a month or year to a date:  this is tricky but doable without code.  What you have to do is a little behind-the-scenes work with the date field.  Here's the steps:

1. Have your two date fields, the first one is where the user picks a day (we'll call it UserDate), the second one is for that day plus 2 months (DatePlus2).
2. Create 4 text fields:  temporaryDate, month, day, and year (you'll hide all these but I'm showing you them in the example)
3. Use a rule to set the temporary date to the value of the UserDate.
4. You'll notice that the format that it sets that field in looks like this:  YYYY-MM-DD
5. Now you get to use the dreaded substring-before and substring-after functions to pull out the year, month, and days.  The idea of substring is that a "string" is a bunch of letters and numbers (like a sentence, word, whatever) and that you are getting a portion or less than the whole thing (like subtracting out a certain part of the string...substring).  There are different types of substring-ing like looking before or after a certain symbol (like a slash or dash in our case) or substring a certain number of letters in (e.g. you have a Social Security number field that always starts with SS#...you could say you want to substring starting at the 4th letter and get the next 11 characters to get all the numbers and dashes for a social security number).

6. Use a rule on the temporaryDate field to set the YEAR field to the following formula:  substring-before((click Insert Field and choose temporaryDate), "-")...this will get all the characters before the first dash that it finds (so all the year numbers).
7. Add another action to the rule on temporaryDate to set the MONTH field as the following formula:  substring-before(substring-after((insert temporaryDate here), "-"),"-")  This looks weird, but what it's going to do is substring after the dash (which grabs the MM-DD) and then substrings before the dash of what you grabbed (so it grabs MM since it's the only thing before the dash).
8. Add another action to the rule on temporaryDate to set the DAY field as substring-after(substring-after((insert temporaryDate here), "-"),"-")  This looks very similar to the Month one but it substrings after the first dash (so MM-DD is what we have) and then it substrings AFTER the dash this time (so it gets DD)
9. Phew, almost done, you've now got the numbers in the month, day, and year fields for your day...all you need to do now is add to the month, day, or year field.  The only problem is this:  InfoPath thinks these fields are TEXT fields, not numbers, so if you try to just add 1 or 2 to the Month field, it's gonna laugh at you (because, as far as it knows, you're trying to add 2 to AA and it thinks you don't know how math works...seriously, it'll laugh at you).  So, here's what you do:
10. Add another action to the rule on temporaryDate to set the MONTH field to this formula:  value((insert MONTH field here))+2 (or however many months you want to add to that month field).  The value function will tell infopath that what you have in the MONTH field is really a number - so you can then do some math with it like adding a few months.  The only hard part here is when you get toward the end of the year...you'll need to have another rule that checks to see if the month is greater than 12 and, if it is, subtract 12 from it but also add 1 to the year.  Again, that's only if you are adding months...if you add to the year, then you won't have to worry about figuring out the month :)
11. The final step:  add another action to the rule for temporaryDate to set the DatePlus2 field to this formula:  concat((insert YEAR field),"-",(insert MONTH field),"-",(insert DAY field)).
12. Below are a couple of images showing the 3 rules I used here to first set the calculated date, second and third check to see if the month is over 12 and do different things based on what the number is.  The reason I had 2 rules for that is that, if you subtract 12 from 13, you get 1...which is correct; however, InfoPath will want you to have "01" in the Month field instead of "1"...so I had to change what I say when I'm setting the calculated date field

Input Mask in InfoPath fields - entering phone numbers and emails

If you have a field in InfoPath where people are supposed to put in their email addresses or a website or phone number - you may have asked whether or not we can double-check that they put it in in a certain way (e.g. a phone number has to be entered in the format (123)555-7899.  There is a way to make this happen - even in browser forms - using something called Regular Expressions.  You won't see the phrase "regular expression" in InfoPath but you will see something like "matches pattern" or "does not match pattern" - which are regular expressions.  Here's the basic way they work:
Using either Conditional Formatting or Data Validation in your InfoPath form, you should have [Field1] [Does not match pattern] [some pattern goes here] - and the action or tooltip should either disable/hide the control or have a message stating something like "Please type your information in the correct format".  Patterns use the following building blocks:

0. If you want to have a symbol for any letter:  \p{L}
1. If you want to have a symbol for any character (letter, number, or symbol):  . (yes, that's a period)
2. If you want to have a symbol for any digit:  \d
3. If you want to use a period, parenthesis, or dash symbol:  \. or \( or \-
4. If you want one of the above to be used a certain number of times, you can use an *, a +, or a ? but they must go AFTER the thing you want to be only used a certain number of times.  * = 0 or more of that thing (e.g. \d* means either no digit up to a huge number - as big as you want).  + = at least 1 or more of that thing (e.g. \d+ means at least 1 digit but can get to be as big as you want).  ? = 0 or 1 of that thing (e.g. \d? means either a single digit or nothing).

So, how does this help you with making people enter phone numbers or emails correctly?  Here's what the patterns should look like:
(for phone numbers):  \d?\(\d\d\d\)\d\d\d\-\d\d\d\d
Notice how there's that optional digit at the beginning for the USA country code?  Obviously, international numbers will be a bit more complicated, but this will make people type in their phone numbers as 1(123)456-7899 or (123)456-7899 - depending on if they want a country code.

For email addresses:
.+@.+\.\p{L}\p{L}\p{L}?\p{L}?

• See the first period means at least 1 character or more but can be any size.  This works great to make sure they at least type something but it could be anything like jdoe or bob or super_director.  
• The @ symbol doesn't need anything special so you can just type it.
• The \p{L}+ means at least 1 character but as many as you need to type your domain like gmail or aol or hotmail or yourcompany
• The period needs that slash in front of it because periods do other things in regular expressions
• After the period, there are two \p{L} because almost every ending to an email will have at least 2 letters (e.g. .us, .com, .net, .info, .gov, .edu, .org, etc.)
• The last two \p{L}'s are followed by a question mark to mean that they are optional but that the most you can have is 4 letters.

So, for something else to try, why not see if you can make a pattern that checks to see if they type a web address correctly (as in, they have to type http(optional 's' here)://(www or some other word).something.something_up_to_4_letters (e.g. http://www.google.com).  If you want more, look up Regular Expressions or Regex to see what you can accomplish!

RELATED POST:  I have another post about this in regard to limiting the number of characters people are able to type in MULTI-line fields (if you have a regular textbox, there's an easy option to limit the number of characters on the Display tab...but NOT so if you choose to make that textbox multi-line):  Limit number characters in multi-lines of text InfoPath fields

InfoPath Form Layout - Proper Design

So,
You've decided to create an infopath form but don't want to do the whole "let's make this and see how it works and then figure out the 'right' way to do it" approach. One simple tip that will really help in laying out your form for both presentation sake and for data source/structure's sake: EVERY label AND control should have their OWN cells inside a table which is inside a section which is inside another table.

This may sound strange, but let's break it down further. You have a simple form with 4 fields (First Name, Last Name, Favorite Food, and Favorite Color). You should firstly think of how the information should be grouped (name info and favorites info) and use that to think of how many sections you will need. For this we would need two sections. Once you have your number of sections, add 2, and that's how many rows you need for a simple form (one of those rows is the title row and the other will be for the submit button). To create this layout, I used a Table with Title layout table, split the second cell into 3 rows, removed the shading on the second row and all borders on those bottom rows, gave it a title, put two sections in their respective rows, and put a button down at the bottom. From there, you place a Custom Table inside the sections to allow you to put controls and their labels into the section in an organized fashion. Having done that, name all the controls and sections by double-clicking them and changing the Field or Group Name. Here's what the results looked like in both the table and then the data source menu:





This particular way of doing things will keep all of your data sources in named folders and makes it easy for those coming later to identify where a data source should exist on the basic page. All corresponding data is automatically grouped together for easier retrieval, if necessary, from them pesky programmers. Have fun!

Since you can't do Cascading Dropdowns...

We have a business requirement to create a course/section scheduling system with an automatic registration/attendance and then eval survey for special workshops/classes being taught to faculty within the university. We were trying to find a way, for attendance, to be done in InfoPath and let the administrators of a particular class setup a laptop that people can use to sign-in inside each workshop (lots of laptops needed, but hey, it works).

As you may or may not know, cascading dropdowns is impossible for Browser-compatible forms in SharePoint (at least for 2007). What we figured, though, was that there won't be a whole lot of sections for a particular course at one time; we decided to create a data connection to our SharePoint lists for both the Courses and the Sections. Now, the Sections list contains the Course ID, Course Name, and Course Description; we grabbed all of that from the Sections list and only the Course ID and Course Name from the Courses list.

To get what we want, we used a drop-down menu that connected to an "external data connection" and used our Courses list (setting the value to the Course ID and the Display Name to the Course Name column). We then used the Data Source menu (over in the right with the task pane) to create a repeating section with controls for all of the items in the Sections data source. When the repeating section is placed on the page, we deleted the unnecessary text/controls and then set Conditional Formatting on the table to hide the section if the Course ID was not equal to the Course selected in the Course drop-down at the top of the page. This works. If you want detailed instructions on how we did this, please comment, let me know.

Simple InfoPath 2007 Tutorial

This is the start of a series of tutorials regarding the creation of InfoPath electronic forms. For the sake of simplicity and expandability in SharePoint, I will be limiting the form to a browser-based form (so that the form can be filled out using a browser like Firefox or Internet Explorer but this requires that you are running the Enterprise version of SharePoint and not the free version). Note some of the following limitations of using a browser-based form in SharePoint:
[EDIT: I've revised this information slightly on 2/25/2011 to better fit what I have learned over the past year...so if you read this before, it will have changed]

1. Performance - Browser forms means that another computer than your own must handle anything complex or difficult to calculate...this can result in a slower form than you might like. If you aren't using browser forms, then the form will open in InfoPath 2007 on your users' computer (if installed).
2. Security - You will want SharePoint to be running in an secure environment. How do you know if it's running in a secure environment? One way (but it's not always THE way) is to look at the very beginning of the web address for sharepoint in your company. If it starts with HTTPS instead of HTTP then you are good.  If it is not, InfoPath transmits data as clear text in an xml file and thus can be intercepted. YOU are responsible for the security of the information in this form.
3. Difficulties with large lists - you will not want to use your form to connect and pull from a large list or library in SharePoint (or one that will be large any time soon...large being 4000+ items). In 2010, we will have the ability to just ask for specific information from SharePoint so this won't be a problem.
4. You must be willing to think and think logically to make a secure, fast form.

REQUIRED MATERIALS:
InfoPath 2007 and a well-rested mind.
Recommended: A SharePoint site in which you can create a Form Library (if you use a default configuration of SharePoint, you will probably need Full Control of a site or have the Site Owner create a Form Library for you to use and give you Full Control of it).

You can click below to go to the first step - planning out your form, menus, and layout tables - or use the links farther below for more information about InfoPath.

Next post in this series:  InfoPath Tutorial - Part 1 - Planning and Layout

To get more information directly about InfoPath:
http://www.bizsupportonline.net/ - Site with topical tutorials on InfoPath 2003, 2007, and browser forms - includes code stuff
http://blogs.msdn.com/infopath/ - the blog of the team who actually design InfoPath
http://office.microsoft.com/en-us/infopath/FX100647031033.aspx - Microsoft's 'InfoPath: Help and How-To' site that usually has a bunch of quick articles and facts to help

Will InfoPath help me?

So,
You've heard about SharePoint or WSS and that it can revolutionize your paperwork with electronic forms through InfoPath. You're probably wondering whether or not this InfoPath is the way to revolutionize your office. Here are some things to consider when it comes to using InfoPath:

1. Do you have WSS or do you have MOSS (Microsoft Office SharePoint Server)? MOSS Enterprise 2007 (or Forms Server 2007 and prior) includes Forms Services, which handles server-side rendering of InfoPath forms (what does that mean? It means that the form can be filled out online using Internet Explorer or Firefox instead of needing InfoPath)

2. If you decide to go the non-browser route, everyone who fills out the form needs to have InfoPath and access to both the form itself and wherever you want the form to send the data. If you go the browser-form route, you'll have to learn how to place it in SharePoint or see how that process works in your work environment

3. If you want the form filled out by the browser, know that there are several limitations:

• You can connect browser forms to databases and SharePoint but cannot, without programming, filter what you get from them. For instance, you can pull an entire column of information but not a certain portion of it (so, if you have 100,000 items in the column then it pulls them all)
• You can't use several controls that are normally available in InfoPath
• You can't have error message windows appear on the screen
• Using a lot of special conditions and special formatting in browser forms can slow performance
• If you do end up having to use programming, you have to get the uppity ups of SharePoint involved at your work

4. There is a learning curve to figuring out InfoPath, though not as in-depth as Microsoft Access or Adobe Designer forms. In our line of work, we get most people developing great, advanced, codeless forms with appx 6-10 hours of training (though advanced forms may take several weeks to develop well)

5. InfoPath does allow for using signature pads to capture the signature and place it as a JPG on the form. Unfortunately, this will work only as long as you aren't using browser forms (because it's usually an Active X control that accomplishes this). You might be able to accomplish this otherwise but not that I could find. Digital Signatures are difficult to apply as well in browser forms but are supported in regular forms.

6. Apart from these considerations, are you using a lot of paper? Do you have a secure place to store electronic forms? Dear Lord, InfoPath can help you (so could Word forms, Adobe forms, or even Access, but they all have different pros/cons). InfoPath was made to help transform the business processes of many current situations. If you combine InfoPath with SharePoint and possibly SharePoint Workflows, you can have a truly powerful business process system to handle most forms of paperwork like vacation/absence forms, applications, quizzes, and more.

Why am I here?

I have been working lately with WSS3.0/MOSS 2007 (SharePoint 2007) and InfoPath 2007 to accomplish business processes. Everywhere I turn, I run into a roadblock and the only solutions seem to be programming-oriented. I may not be a genius but I realize neither I nor many of my colleagues have the skill or access to fix these problems (like filtering a data connection in a browser-based infopath form).

I am creating this blog to help vent some of that frustration and to provide training and tutorials for those interested in learning different Office products and what they can do without special programming/code. If you happen to stumble upon this site in its infancy, please let me know of problems you have encountered that you wish would be answered without using (or using the bare minimum of) CSS, HTML, VBA, etc.