I'm using (and limited to) OOTB Foundation 2010, and have been exploring lookup lists. I am discovering a few things that aren't working the way I'd like, seem to be working inconsistently, and are complex enough that I'm finding it difficult to ask the great oracle at Google the proper question.
Let me begin with my most current example. I'm building a project tracking site. It's not so much for true project management as it is for the stakeholders in the project to view project, add commentary and share documents. Think of a collaboration site which has, at its heart, a list of initiatives up for discussion and prioritization rather than Gantt charts and resource allocation. Seems pretty straightforward.
I've got a seven item task list that's very high level. This would be the executive level summary of the initiatives...like "upgrade to Office 2013" or "update disaster rcovery plan". I have a second custom list that includes abount seventy mini projects WITHIN the initiatives. Things like "do a hardware scan to ensure that every PC has minimal requirements for Office 2013" and "research licensing for Office 2013". I am using a lookup column to math the mini projects with their initiatives, so all the mini-projects realted to the upgrade can be rolled up together, but separate from the mini-projects associated with the disaster recovery plan.
Here's where I start seeing issues, generally in one of two broad categories:
- Sometimes, a column in the lookup list will not be available to bring over with the lookup value. I can lookup "Initiative description" and be able to check "created by", "Modified by" and "modified date", but "Assigned to" is not listed among the pickboxes.
- I do not appear to be able to create calculated columns based on lookup data for instance, If I have an hours estimate in the initiatives list, I cannot use that in a formula to calculate that hours used in mini-project A are x% of the total allocated hours for the initiative.
- There does not seem to be a discernable pattern between when I can or can't acess a field for calculation.
- There does not seem to be a discernable pattern between when items are available to choose on a lookup list or not.
- While there may be some differences between how task lists vs. discussion lists vs. custom lists vs. issues lists interact with their respective lookup behavior, I have been unable to find any documentation specific to that topic.
I would liek someone more familair with the issues than I to explain what's happening, how lists interact, how lookups can (or can't be ) used in calculations, perhaps pointing me to some better and more specific) sites for my research.