Merging lists in Sharepoint Foundation

Question

We're working with SharePoint Foundation and staying within all its limitations. We have a 3500-document library for which we're designing the metadata. The various metadata fields we're capturing are from three different sources, and all of them are "keyed" by filename. We will end up with a handful of files, like this:

   File 1: FilenameFromSourceA,Metadata1,Metadata2,Metadata3
   File 2: FilenameFromSourceB,Metadata4,Metadata5,Metadata6
   File 3: FilenameFromSourceC,Metadata7,Metadata8,Metadata8

We want to create a final list like this:

Filename, Metadata1, Metadata2, Metadata3, Metadata4, Metadata5, Metadata6, Metadata7, Metadata8, Metadata9

Where all metadata from the three source files is listed by a single occurence of the file name.

To complicate matters, Because of differences in the times the source files are created, the three lists of filenames may be slightly different. While MOST of the filename lists will be identical, there may be a dozen or so that are on only one or two of the lists. In those cases, I want to add an instance of the filename to the final list, but leave the respective metadata blank.

Anyone know of a way to do this with OOTB Foundation 2010?

Answer 1

Had you think about webpart? Do CAML query to each list and merge results in codebehind. I would suggest use of caching for better performance.

Answer 2

We ended up merging the files in Excel, then doing a cut-and-paste into DataSheet View. Not as elegant as I would have preferred, but it worked