Sharepoint - Sharing data between multiple site collections
Have you taken a look at the business data catalog (BDC) on moss 2007?
The BDC (renamed Business Connectivity Services in SP2010) is an out of box feature enabling external business data (in your case a list of offices) to be surfaced within SharePoint site collections without writing any code.
It may not be the perfect fit for your requirements, since if you actually want to store the list of offices within SharePoint itself, you may need to write something to get the data moulded into a shape that the BDC likes, but it's probably worth playing around with to get a better idea of its functionality ( +/- quirks etc ;-)
http://msdn.microsoft.com/en-us/library/ms563661(v=office.12).aspx
It would make sense to logically store the data in a root web application where everyone might have access to update the information. With some metadata and various views, you can slice and dice the information very easily for teams to digest.
To consume the information in other places, you can use page viewer web parts or use data view web parts to pull in the data.
Another possibility would be to have a core master list of basic information at a root level. Then create secondary lists specifically for individual teams that need to extend that information. Those secondary lists could use lookup columns to reference data to create relationships. With Data view web parts, you'd be able to insert joined subviews so you'd be able to see the basic data for a user, then see inventory assigned to the user for example.
Lookup columns do not work across site collection boundaries. This is by design and makes good sense considering that site collections can be placed in different content databases.
I can see two possible strategies to tackle your problem:
- Replicate data across site collections
- Keep data in one place and 'reference' data across site collections
I am sure there are many solutions for both strategies. So please do not regard the following examples as a complete list of possibilities.
Replicate data
You create a SharePoint list with the offices of your company, let's say in the root site collection of your SharePoint farm. You add a guid field to the offices list so that you have a unique and independent identifier for each entry in the list. You create an ItemAdded, ItemUpdated and ItemDeleted event receiver on that list to replicate these changes to 'consumer' lists in other site collections. In the 'consumer' site collections you can use standard SharePoint lookup columns within these site collections to reference data from the offices list.
An available solution for data replication might be this component (sync root list to a table in database and hook up 'consumer' lists to that table): http://www.layer2.de/en/products/Pages/SharePoint-Business-Data-List-Connector.aspx
Reference data
You can create a custom lookup field that can be used across site collections. In the lists in the 'consumer' site collection you use this field type to reference information from the offices list in the root site collection directly. You will definetly have to solve the issue how to update the referenced data, because your custom field will have to store a copy of the selected value (e.g. address) when the list item is created in the 'consumer' site collection.
An existing 3rd party solution for this could be the Cross-site lookup from SharePointBoost.
Update:
An OOTB approach to replicate the data could be to use the above mentioned Business Data List Connector component from Layer2 and hook that up to the RSS feed (feed.aspx) of the root list. The Layer2 component can consume XML files and the feed.aspx can be customized with an XSLT for the purpose.