You have a requirement to display two list view web parts of two SharePoint lists. These two lists are linked together using a multi-valued lookup field in one of them. So for example you have a list called Companies and another called Branches and in the list of Companies you have a multi-valued lookup field to the list of branches. Please note that the field is in the Companies list not the branches list and it is done so as per the requirement to be able to browse all branches of a specific company by simply opening the company item.
Possible Solutions and Challenges
So the first thing you would thing so what this shouldn’t be that hard right. SharePoint has out of the box a web part connection feature out of the box. So you create a page, add two list view web parts to it and configure them so that the Companies web part would provide the row value to the Branches web part. The branches web part accepts the lookup value as a filter parameter linked to the name of the branch. Well this will not work as you see the lookup parameter is multivalued and also it is not text parameter so it passes a value such as “1#Branch1;2#Branch2;” this value will not match any Branch name and hence the filter will remove all values.
The second attempt I thought about was to create a custom web part to do this; and then it hit me; this is a sandbox solution so I cannot actually instantiate a ListViewWebPart from my custom web part so this means I will re-write all ListViewWebPart logic using ASP.NET controls .
The third thing I tried was to create a Silverlight web part (so this adds good looks also) and then add that web part to the page. But this is a sandbox environment and it has the client object model and the data services disabled and I cannot enable them.
So what I ended up doing is that I added the two web parts to the page and then I edited this page using SharePoint designer. So in SharePoint designer I created a new custom parameter in the Branches web part. Then I created a web part connection so that the Companies web part would pass the row value to the Branches web part. The branches web part would then link the lookup field value to the custom parameter. Finally I created a conditional formatting rule on the Branches web part so that it hides the row using an advanced condition that would look like this.