2

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.

1

Mike has a nice post exactly about your question here. This post shows the patterns you are looking for. In the same post furthermore, there are solutions to the problem using OOTB SharePoint 2010 features.

BTW, if you stuck in any steps of his solution, you can always use a SharePoint2010 designer workflow to set a "single line of text" field with the value you want. Any calculations can be done in the designer workflow with any field and any lookup field from the lookup list can be retrieved using the workflow lookup function. Just store them in a "single line of text" field and use them any place you need. Attach the workflow to the list and check the boxes for "automatically run after item added" and "automatically run after item updated".

0

The simple answer is that they are common pitfalls. Why Microsoft doesn't give this kind of functionality out of the box, I'm damned if I know.

The work around is you create duplicate fields using workflows. So for example, you have LookupField1, and you create a workflow to "Set _LookupField1 to [%LookupField1%]", where "_LookupField1" is a text field that you've created separately.

It gets more complicated as you have to find workarounds for the problems you have with the workarounds, as these won't work if the field you are copying from is Blank, so better then to copy as a string and then in your calulated column you use a formula like this to extract the actual value from the field:

=MID(_LookupField1,(FIND("#",_LookupField1,1)+1),250)

At least this is what I've done...

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.