Assigning permissions automatically to items within a list based on a value

Question

I am looking for some assistance performing a task in Sharepoint 2007.

In our organization, we have a hierarchical structure, such as this:

• Organization
  • Department A
    • Department A, Team 1
    • Department A, Team 2
    • Department A, Team 3
  • Department B
    • Department B, Team 1
    • Department B, Team 2
    • Department B, Team 3
  • Department C
    • Department C, Team 1
    • Department C, Team 2
    • Department C, Team 3

We are looking to create a list that meets the following requirements

• Read and Write permissions should be assigned to the Team level
  • Department C, Team 3's items can only be viewed by Department C, Team 3; Department C; and the Organization
  • Each level of this hierarchy consists of 3-4 people, at most
• Permission should be assigned automatically, based on one of the following
  • The list the item gets added to (if each team has its own list)
  • The team selected from a drop down list
• We should have the ability to create a "report" of all items in the entire hierarchy, all items within a department, or all items within a team

My issue is that I cannot find a way to meet all three requirements. Here are my scenarios:

Create a list for each team, and team members post their items within that list - If I assign permissions to the list, meets requirements 1 and 2 - I cannot find a way to create a "report" of all items within multiple lists

Create one list, and create a view for each level of the hierarchy, showing only the items they are allowed to see - Since I cannot assign permissions to a view, I must open up the entire list to all members, failing requirement 1 - I can assign permissions to the team level, but it would have to be performed manually for each item, failing requirement 2 - This allows me to generate a report for the entire hierarchy

Any ideas?

Answer 1

Clone your lists, create Team List A, Team List B, Team list C and Team List Master.

Create a workflow and attach to Team lists A,B,C that copies list items to Team List Master based on whatever trigger you desire.

This allows you to manage your rights separately and still be able to create your reports based on a compiled list of all three entities.