1

I am working with SharePoint 2013 at a hosted site (not Office 365).

Most of the questions I see when I search relate to including a lookup field in a calculated column. That is not my issue (but it would be a possible solution, if it were possible).

I have a lookup list of Members. It contains two fields, Name and Title, that I combined into a calculated column (NameAndTitle).

I have a dozen roster lists that reference a dozen members from the Members list, pulling the name, home phone, and mobile phone. Each roster can contain a member in more than one position (so I can't combine the rosters into the Members list). Now I want to find a way to use the NameAndTitle field from Members instead of the Name field. But calculated columns are not in the list of fields available from the lookup list.

What options do I have to pull the NameAndTitle field from the lookup list?

Improve this question

2 Answers 2

Reset to default
1

You don't actually have a value in your NameAndTitle field; you have a lookup that gets the values from two other fields. Sounds like you want to abstract that again -- a lookup on a lookup? That doesn't work in SharePoint.

You have to write the value you want to the NameAndTitle field -- if you have SQL access you can do it with a trigger. Otherwise, you could set (or get) the value with a workflow. jQuery or Javascript could also work (to write the value).

Depending on your usage, you could use linked lists (handled on the page via web parts). Not sure if that would meet your application needs.

Optionally, rethink your approach. You have Rosters, Members and Titles. Rosters are unique. Members belong to a Roster and may belong to more than one Roster. Titles are variables, assigned to members? Titles belong to Rosters? and members are assigned to titles?

Combine all your lists AND include metadata for your roster lists (instead of separate lists). That way your key would be "Member + Title + Roster." From that master list, all you have to do is set up views by Roster.

Improve this answer
3
  • Thanks for the comments and suggestions. I can't add Roster data to Members, because some members have more than one position in a given Roster. Titles belong to the member. Positions (think Roles) belong to a given Roster, and a member could have two or more Positions in the same Roster.
    – CigarDoug
    Mar 4, 2015 at 2:15
  • Sounds like you need to rethink whatever you are trying to do with the relationships ... Any "data" application that "can't have a key" is a candidate for Word or Excel and a typist in my book. The database option (trigger) and workflow option would be feasible. You always have the option to impose whatever manipulations you want via code. To the original question: setting a look up value from a look up field won't work in SharePoint. The look up field needs to get it's data from a field that actually contains data.
    – Terri
    Mar 4, 2015 at 19:45
  • One other option.... rework your lists so you have clean sets and use Content Query or Content Search web parts to pull the data into whatever groups / format you choose for display. You still won't be able to do a lookup on a lookup.
    – Terri
    Mar 4, 2015 at 19:50
1

I really don't like to admit that "No, you can't" is an acceptable answer, but sometimes it is. Since the end result of the project was Excel spreadsheets, I had to move some of the computation to Excel. A bit kludgy, but in this particular case there was no viable option. So, to sum up my process for some future Google searcher (Hi, Google searcher!):

  • Create a list of Members, with Name as the one field, and Title as another, like "Dr.", "Mr.", "Professor", etc. NOTE: The default Title field is actually the full name of the member, so don't confuse it with my Title field.
  • Create a calculated field NameAndTitle that concatenates Name and Title fields (mine is actually more complex, involving dates and such).
  • Create a Roster list with a Position field. Add a lookup field to Members that includes Name and Title, the necessary fields for the calculated field formula.
  • Populate the Roster list with Members and Positions and export it to Excel, including all the fields.
  • In Excel, create a NameAndTitle calculated field, using the same formula from the Members list.
  • In Excel, hide all fields except Position and NameAndTitle. Distribute this spreadsheet (or copy just those columns to a new spreadsheet).

I appreciate Terri's suggestion, in SOME cases you should be able to redesign your schema. In my particular case, Title and Position were not related, and a Member could have more than one Position in more than one Roster, so combining into one list wasn't an option. But valid advice for most situations.

UPDATE: One final note, another option was to copy the calculated field to a text field in Members, and reference that copied field. But then, I would have to manually update that calculated field every time a Member's Name or Title changed. In my solution, since I have to reproduce the Excel spreadsheets on a regular basis, I can just copy a block of data to the spreadsheet, preserving the calculated column in Excel.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.