I have two lists named 'Programs' and 'Projects'. I need to be able to add multiple Projects under each Program. Each of these Projects should be a list item in the 'Projects' list.

Please help me on designing this.

update: Thanks for the quick response guys. I guess I wasn't detailed enough in my query. Let me try again. My 'Projects' list already uses a lookup from 'Programs' list. What I'm looking for is this - There is a rich text field in the programs list where the managers will enter all the projects listed under the program. Now each project from this field need to point to (or become a) corresponding list item in the projects list. How can I do this?


You need to use lookup columns for this.

Add to your Projects list a column of type lookup that points to your Programs list.

| improve this answer | |

look up columns should work. reference: http://office.microsoft.com/en-us/sharepoint-server-help/create-list-relationships-by-using-unique-and-lookup-columns-HA101729901.aspx Hope this helps.

| improve this answer | |

There are several ways you can structure this. One that is already mentioned is for Project to have a lookup to the Program list. Another is for each program to have a multi-valued lookup pointing to each project that "belongs" to it. You could also do both (not suggested, as they could get out of sync). A final option would be to have a 3rd list that defines the relationship between Projects and Programs; it would be a list with two columns, a lookup to Project and a lookup to Program.

The question of which option is best is going to be partially based on how you plan to use these lists. Which types of queries do you see yourself asking more often:

  • What is the program for this project?
  • What are all of the projects for this program?

The first is more efficiently asked with the first option I presented to you, the second is more efficiently asked with the second option I presented to you. If you are frequently asking both questions then the 3rd option would be more efficient, but it would also take more time and effort anytime items were added, created, or when the relationship changes. If the data is largely static and just queried a lot this might be a good option, but if it's changing a lot it will be much harder to maintain.

The last option I presented is going to be less efficient for most of these queries, at least most of the time. It's primary advantage is that it allows for a many to many relationship, rather than just a one to one relationship. It also separates the relationship from the content of the items, so if you are usually querying for information just about the relationship between the lists then this could be a worthwhile choice.

| improve this answer | |

Change the rich text field to multivalue lookup field. You managers would pick multiple projects from the list instead of typing into a text field.

You could create a feature receiver that would process the project list entered somehow in the rich text field and convert it to links or set the lookup field with corresponding values but it would be a complicated workaround. Better solution would be t design the field types right.

Update: Usual design is having pages to create new projects and new programs; typically in their corresponding lists. Linking projects to programs (or programs to projects) is done by single- or multi-value lookups. You'd use standard SharePoint features.

If you let the users type in rich text field and then create/link the item to other entities you'd have to develop and deploy your own solution to SharePoint. While solutions are meant to customize and extend SharePoint it doesn't mean that you should use them anytime it is possible. Think about the 80-20 rule. You shouldn't bend SharePoint to perform you first design idea. You should design to reuse as much as possible from standard usage patterns; it work usually the better the more you know about SharePoint. Otherwise you'll spend time cracking nuts and looking for tricks instead of delivering features...

--- Ferda

| improve this answer | |
  • But as per the original requirement the projects cannot be 'looked up' from elsewhere. The rich text field was where they would enter it first! I think this needs a design modification completely. – PurpleShadow May 11 '12 at 19:33
  • Yes, it does; I'm afraid. I still don't know how your application looks like; you uncovered just a part of handling the relation between projects and programs. It can be that I misunderstood you. That's why I wrote rather a general suggestion than a concrete solution. – Ferdinand Prantl May 12 '12 at 8:16

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.