How to link two lists using a multi-valued lookup column?

Question

I have two lists, one contains files, each file has one or more ID(depending on its content). These IDs are lookups to another list. Now, I generate another list on fly, this list has the ID column as well (same lookup table). What I need to do is to somehow link the two lists together(many to many relationship i suppose) so that i could see all the files, that have one or more common IDs with the generated list. Any idea, how to do this? I could use SP Designer, Visual Studio or any other tool I guess..

Thanks

Dave

Answer 1

Have you tried joined lists in SharePoint Designer? I am not sure how they work with lookups, but their purpose is exactly what you're trying to achieve - connect two lists through a common field.

Answer 2

create a new field of Lookup column type within your document library. Choose the list which you wonna link it to your library and pick the column you need (in this case the ID). So when you upload a new doc you should choose the ID that you will assign with the file from a drop-down list.

Answer 3

Obviously the OP has an answer (the question is 3 years old), but I wanted to post another answer that might help someone else.

I've done this in the past using a DVWP. Within the XSLT of the web part, you can set up a variable using the value of Lookup Column A, then filter your results on that variable using something similar to this:

[contains(@LookupA, concat(@LookupB, ';#'))]

The actual value of the lookup column is the item ID followed by a semicolon, then a pound sign. A multiple value lookup will be passing values like "1;#2;#3;#4" etc., so what you need to do is filter out individual IDs from the string. You could also do this by passing the multiple value lookup into an array, but I find the contains option easier. Including the semicolon and pound sign in your filter will exclude items that include your number (ex - filtering on 10 returns 100 or 310 as well).