Creating a printable InfoPath form

I don't like printing.  But inevitably, someone will want to do it somewhere.

We have a list, which uses an InfoPath form for data entering and viewing.  In here, we created a Print view, which is basic black text on white background, and read-only.

However, when trying to print this view, the rest of our SharePoint master page and branding is printed with it, and it doesn't look very professional.  So we needed to create a printable version of this Print view.

Some suggestions online included using jQuery to remove everything BUT the InfoPath form, but this sounded like a messy way of doing things.  Instead, I figured I could just create a new blank page in SharePoint Designer and grab the item ID from the URL.

My first attempt failed.  I tried using a Query String Filter Web Part, but then realised that sending an ID to an InfoPath Web Part doesn't do what you'd think it would do.  It doesn't magically open the item you're referencing with the ID, it simply changes the InfoPath form item ID.  You could use this method for pre-populating an InfoPath form based on parameters in your query string, but not for actually loading an already-existing form.

I figured that you could instead use that ID to filter a List View of your list, and then send that single item to the InfoPath web part.  This would also mean I wouldn't need the Filter Web Part, as a List View has its own Filter and Parameter settings.  So, here's what I did.

1. Using SharePoint Designer, create an ASPX page in your site called PrintForm.aspx.
2. In the ASPX page, add an InfoPath form web part, and configure the web part to use the list you're trying to print items from.  Configure this web part to use the correct Print view that you've already created in InfoPath Designer.
3. Somewhere else on the page, add a Data View, and again configure to use the list you're trying to print items from.  Configure this web part to be hidden or not visible (unless you want to see the list view as well).
4. With the List View web part selected, add a Parameter.  This will be a query string parameter with the name ID, and the Query String Variable of ID as well.  (You can change this to ItemID or something else if you want)
5. Next, add a Filter for the List View web part.  This will get the Field Name ID to equal the parameter you just created, which will be displayed as [ID] or [ItemID] or whatever you chose in the previous step.
6. Finally, Add a Connection from the List View to the InfoPath web part.  You want to Send Row of Data To the InfoPath web part, using the Target Action "Get Form From".  
7. Save and check in the file as appropriate.

Now, if you try to access the URL of this page with an ID added to the end, you will get a nice, basic printable view of your data.  You'll want the URL to look like this:

http://sharepoint/sitename/Pages/PrintForm.aspx?ID=25

What you'll need to do then is add a Print button to the normal Read or Edit view of the InfoPath form, which will send the user to that URL above, with the current item ID after the ID= (in the example above, the item ID is 25).

There's also a little bit of JavaScript you can throw in to your PrintForm.aspx page which will tidy up a few last remnants of the InfoPath web part - namely, the web part name, and the light blue border.  It also presents the user with their browser Print dialog, to save them having to go to File/Print.  It looks like this:

<script src="/scripts/jquery-1.9.1.min.js" type="text/javascript"></script> 
<script type="text/javascript">
  $('.ms-WPHeader').hide();
  $('.ms-WPBorder').css('border', 'none');
  setTimeout(function() {
    window.print();
  }, 500);
</script>

Edit: added a setTimeout before printing, because it was printing a blank page, even though print preview was working correctly.

SharePoint document groupings not showing the grouped documents when expanded

One of our sites on SharePoint is used to store customer contracts.  A SharePoint list item, called a Contract Record, stores the data relating to that instance of a customer.  Separate to this, a document library, called Contract Files, stores the actual contract files, which are then associated with the Contract Record.

When viewing the files, they are grouped by Contract Record.  And when clicking on a Contract Record to view more information, the files are displayed below, using a simple filtered web part.

We already hold all of our customers’ names in SAP, so we have an SSIS package taking an export from SAP and throwing the data into a SharePoint list, called Customers.  So, when you create a Contract Record, you use an InfoPath form to search (using CAML) for the Customer, and a combination of the Customer Name, Country Code, and Customer ID are used to create the title of the Contract Record.

Simple, right?

Anyway.  This whole process was working fine, until recently.  While Contract Files assigned to an existing Contract Record were displaying fine under the grouping, new Contract Records were not.  The grouping title (the Contract Record name) was displaying correctly (along with the number of files in that group), but when you expanded that group, nothing appeared underneath.

Similarly, the filtered document library web part was not showing any files, even though files had been assigned to that Contract Record.

So we dug around in the data obtained using CAML.  It appeared that there were a few additional characters being inserted into the raw CAML output:

<d:LongName>Customer Name - FR&#xD; (000059184) </d:LongName>

Did a bit of Googling and found that &#xD; is basically a line feed.  This was ignored in the Contract Files lookup for a Contract Record and seemingly ignored when grouping documents, but when expanding a group, the line feed is taken into consideration and no grouped files exist.  Or something like that.

So we looked to the data imported using SSIS, and sure enough, this line feed is added to the end of most of the country codes.

<d:Country xml:space="preserve">FR&#xD;</d:Country>

I did a test – I created a new SSIS package and had it copy all data from Customer into a new, identical list called CustomerTest.  This removed the line feed.  But obviously, there’s not much point creating a new SSIS package just to fix data from an existing one, so I’ve had the original SSIS package only bring back the first two characters from the Country code.  Seems to have worked.

Note: this relates to another post I made not too long ago, here: Filtering Large SharePoint list data in InfoPath

Filtering large SharePointlist data in InfoPath

I have a site which consists of a large (30,000+) list of customer records.  In another list, I want to create an InfoPath 2010 form that contains a lookup to that customer list.  Of course, with such a large number of customer records, that means the performance is extremely poor at times, and forcing the user to browse through 30,000 customers is also extremely poor.  So I'd like to filter that lookup list.

The most obvious option is to filter client-side.  This would resolve the usability factor, but would still cause poor performance.  So the next option is to do something server-side.  However, using the InfoPath data connection wizard, there doesn't appear to be a way to filter a SharePoint list.  It's all or nothing.

I did a bit of digging around and remembered ListData.svc.  It's a byproduct of ADO.NET data services, so I made sure that was activated on my SharePoint server (it was), and then used this forum post as a guide - InfoPathDev: Query Sharepoint List - Partial Match.

Since my customer list is on a subsite, I had to use common sense to locate the actual list:

http://sharepoint/sitename/_vti_bin/ListData.svc/Customer

Shoving this into my browser showed the XML data (I'm using XML Tree, a Google Chrome extension), but of course, it's the full 30,000+ records and took a few seconds to display.  So I continued reading, and found the filter system query options on OData.org, so came up with this:

http://sharepoint/sitename/_vti_bin/ListData.svc/Customer?$filter=substringof(tolower('SearchQuery'),tolower(ColumnName)) eq true

I'm amazed at how well this works.  I've used tolower() to surround my search query and the column name - this forces a case insensitive search and doesn't seem to affect performance.

My next challenge is to make this work in InfoPath.  I have a text box, named CustomerSearchField, where the user can enter the search query, and when a Search button is hit, the REST URL for the data connection is updated.  The first rule for the button is "Change REST URL".  On the "Rule Details" screen, select the correct Data connection that you would have created earlier, and click the "fx" button next to REST Web Service URL.  In here, I used this:

concat("http://sharepoint/sitename/_vti_bin/ListData.svc/Customer?$filter=substringof('", CustomerSearchField, "',tolower(LongName)) eq true")

Don't forget the concat().  This caused me a lot of stress trying to get this working.

Finally, add another rule to the Search button - "Query for data", and then choose the same Data connection defined above.  There, working.