none
Creating a calculated column that includes a lookup column

    Question

  • I've just started implementing SP and I've run into a problem:

    I would like to create a calculated column in List 2 that concatenates the value in the lookup column (CourseLookup) along with some other fields from List 2. SP doesn't let me do that. Does anyone know if there is a way to reference this in the formula? Can I simply return the lookup field text and use it in the calculated column? Right now I'm using the web interface, but is this something that I should be using SP Designer to do?

    List 1: Courses
    Columns:
     Course Number (eg COMP101)
     Course Title (eg Intro to Composition...)
     CourseLookup (calculated: [Course Number]&" - "&[Course Title])

    List 2: Sections
    Columns:
     Start Date (eg 4/1/2011)
     Schedule (eg Day/Evening/Weekend)
     Course  (Lookup column: Courses.CourseLookup)
     SectionLookup (calculated: [Courses].[CourseLookup]&" - "&[Start Date]&" - "&[Schedule]) <-- this can't be done

    List 3: Students
    Columns:
     Name  (eg Dave Thomas)
     Section_V1 (Lookup column: [Sections].[Course]) <-- this can't be done
     Section_V2 (Lookup column: [Sections].[SectionLookup]) <-- this can't be done either

    Tuesday, March 15, 2011 4:23 PM

Answers

  • You could do a simple workflow using SharePoint Designer that would copy the value from your lookup column into a single line of text column. Then you could use a calculated column to concatenate values.


    So in your example, in List 2, you would have a SPD workflow copy the value of "Course" into a column called "Course Plain Text" (or whatever you prefer). Your SectionLookup column in List 2 would be calculated using the "Course Plain Text" column.


    Hope this helps.


    Planet Technologies || SharePoint Task Force
    Tuesday, March 15, 2011 10:17 PM

All replies

  • Can you send me some populated stp templates of your lists and I'll have a look later today.  I think I know a way, but I'd rather test first.  I'm on baron_72@hotmail.com


    Steven Andrews | SharePoint Professional | http://www.twitter.com/backpackerd00d | https://baron72.wordpress.com/

     

    Moderator Note: Forums are not for one-on-one support and you may well find it a bad idea to both offer this and especially to give your e-mail address in a public forum. You are likely to be swamped with both spam and requests for free assistance. As the poster, you could delete this post and save yourself a lot of grief.

    • Edited by Mike Walsh FIN Tuesday, March 15, 2011 7:08 PM Moderator Note added
    Tuesday, March 15, 2011 5:10 PM
    Answerer
  • You could do a simple workflow using SharePoint Designer that would copy the value from your lookup column into a single line of text column. Then you could use a calculated column to concatenate values.


    So in your example, in List 2, you would have a SPD workflow copy the value of "Course" into a column called "Course Plain Text" (or whatever you prefer). Your SectionLookup column in List 2 would be calculated using the "Course Plain Text" column.


    Hope this helps.


    Planet Technologies || SharePoint Task Force
    Tuesday, March 15, 2011 10:17 PM