How can I combine data from 5 separate columns in a form library into a single column on a separate list

Question

I have a form library that has 5 columns per entry for possible sites, I've been asked to come up with a view that summarizes all possible sites in one place. So Site A may be in Site1 column for form1 and Site3 column for form 3. I was exploring pushing the data I need, these 5 fields plus form name to a separate list to see if that would work.

Instead of current data being: Form name Site1 Site2 Site3 Site4 Site5

I would want a two column list Site Form name

Where all data in columns Site1-Site5 becomes an individual row in the new list with form name appended.

In the end we are looking for something that can present like a grouped view for a summary, i.e. At a glance Site A was mentioned 5 times, expand to see forms it appears.

Working with InfoPath 2010 and SharePoint 2010. Thanks

Answer 1

If I understand your requirements correctly, the best option will probably be to do the following:

1. Create a web part page called "Summary" (or whatever you need)
2. Open that page in SharePoint Designer and create a new DVWP on the page
3. Use your forms library as the data source for the DVWP
4. Insert custom grouping that will group (and total) forms based on whether or not ANY of the five columns contain the site

As a result, you should end up with exactly what you're describing - a summary rollup where Group 1 is Site A, Group 2 is Site B, Group 3 is Site C, etc. As long as you're dealing with a known, finite group of sites, this option should be manageable and fairly easy to set up.

The downsides to this approach are that you have to be familiar with customizing XSLT, and you have to be dealing with a known value (in your case, the sites ).

Answer 2

Going to shoot from the hip and offer search as a possible solution.

If you created a managed property that maps to all 5 of the crawled properties corresponding to your 5 columns, then a search for "Site A" will show all items where Site A is in one of the 5 columns. You can then customize the search display to something nicer, and you could even end up having them grouped (although in XSLT it will take some hacking).