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.
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
Did a bit of Googling and found that
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.
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