I have two entities, Toolbox and Tool, where Toolbox contains a set of tools (hammer, saw, screwdriver).
The trick is that there can be several hammers in the same toolbox, so I need an amount, or quantity.
All in all the overlay looks like this: Overlay of Toolbox -> Tool
Here my toolbox contains one Drill and two hammers.

Although SharePoint lists are far far from any database, one could imagine it like this: enter image description here
Here ToolboxTool is a "joining table" connecting the two entities, with the additional information about Quantity. Of course in a normal database one could justify quantity by creating duplicates of ToolboxTool instances, but this is perhaps more SharePoint-like.

My actual question

What is the perfect way to setup something like this in SharePoint?
Several times I have faced similar problems when defining Lists, but I have never taken the step to find the perfect solution, which is why I ask here now.

Some virtual restrictions:

  • I'm looking for OOTB solutions, but any suggestions are more than welcome.
  • Does not have to be bound to foundation, but non-enterprise answers are preferred.
  • OOTB presentation (e.g., XsltListViewWebPart) should be part of the consideration.
  • Erik, as far as I know lookup columns and some alterations in the form is the best way.. Normally if I have many relations I put in the parent view and on click make a different page and drop a new item form or display form to show details... or use web part connections to display on single page... use some javascript to set default values in OOB forms etc! – Arsalan Adam Khatri Nov 13 '12 at 14:52

By looking at your reputation, I am not sure if you not ever heard about lookup columns as explained in this MSDN article they are doing exactly what you want but only difference is they are playing with bunch of bananas and your with tools ;)

Hope it helps too :)


Since your looking for XsltListViewWebPart you can use CAML Joins and Projections as explained in book SharePoint 2010 as Development Platform

  • Hehe yes I have heard of them. But to solve my problem I would end up with three lists, and a "joining" list (ToolboxTools) with SPFieldLookups. Quite the mess. So that would even make ´NewForm´ hard to use, as one must add references in ToolboxTools before the item in Toolbox is created. (Three lists is how I would do it, and I have a feeling that solution is not very good). – eirikb Nov 13 '12 at 16:23
  • If you explain your question with real case scenario we might get somewhere but with these tools and hammer in my mind, only thing I feel like doing is break my SharePoint farm ;) Lol – Muhammad Raja Nov 13 '12 at 16:25
  • I'm not sure if I did this right, but tried creating a chat, as I'm afraid this question is escalating: chat.stackexchange.com/rooms/6408/… – eirikb Nov 13 '12 at 16:35

im a bit confused asto what your trying to achive? Why create so many lists to make the relationships when you have sql? surely this can all be done with three tables in you database? one table is the toolbox another tools and the last is the connection between the two. From there you can use what you want be it list or webpart(i think is best) to display as you need, You can even add/ammend or remove from the list.

Sharepoint should be the one that displays the information and sql is the one that contains the relationship to the data. You pull out the data from the SQl DB using stored procedure that will show up as information in the webpart ;).

Its no OOB feature but its far far better solution and wouldnt take long atall infact could be done in couple of hours if that! SQL is already there and is specifically designed for this task.

hope it helps :)

  • Sorry to confuse you. I don't have a SQL, I don't have any lists. I'm trying to ask for the "perfect" setup to solve the simple scenario with plain SPLists in SharePoint – eirikb Nov 13 '12 at 16:20
  • its ok, just confused as to why you want to complicate things ;) to have sharepoint you need sql Database. This is a good read and explains alot for you :) community.bamboosolutions.com/blogs/bambooteamblog/archive/2009/… – Ali Jafer Nov 13 '12 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.