Using a lookup field on a choice field workaround

Unfortunately, it’s impossible to map a lookup field on a choice field. There’s a workaround, however, by using a calculated field which copies the information from the choice field. The look up field is then able to map the calculated field’s value.

Start out by creating 2 custom lists; One list where the choice and calculated columns reside:

create a custom list with content

And another list where the lookup will be done:

create a custom list with lookup

Navigate to the list with content, and click on the “List” contextual tab in the ribbon. With the list options open, click on the “Create Column” button. When the dialog pops up fill in the desired options and select “choice” as column type:

choice column

Click on OK. The choice column should now be added to your list. Click the “Create Column” button again, but now select “Calculated” as column type:

calculate column

Scroll down and reference the choice field in your calculation formula:

calculate column properties

Click OK. The calculated column should now be added to your list with content. Add a new item to check whether the calculation formula works:

custom list with content

Navigate to the list where the lookup will be made and create a new column. Fill in a desired name and select “Lookup” as column type:

lookup column

Scroll down again, and select your list with content where the lookup column should get its information. Next, select the title to be looked up and as you can see, the calculated column shows up as a selectable property:

lookup column properties

Finish adding the column by clicking OK. Whenever you add a new item to the lookup list and use the lookup column, the chosen choice field from the content list will be shown:

custom list with lookup content

In an ideal situation, Microsoft addresses this issue and makes choice fields able to be looked up in the near future.

Advertisements

15 thoughts on “Using a lookup field on a choice field workaround

  1. This works a treat but my column is a check box with multiple selections so it doesnt show as a selection in the calculate column. Any work around that doesn’t involve code, designer or infopath as my IT dept will not allow me access to any of these! Jan

  2. Its helpful but one problem i got is

    I did this as a result of optimisation but at the end is not working
    example i have a field Release and i took a calculated column as Release_ref which is getting value from Release
    List1
    Column (choice) Coloumn2
    Release Release_ref
    10 10(=release)
    11 11
    12 12
    13 13
    13 13
    11 11

    List2
    Now i am using Column2 as look up for Rel(column name) in list2
    so for list 2, and coloumn named as Rel i want only distinct entries should come i.e. 10 11 12 13

    But Actually duplicate entries are also coming ,,, so my purpose was defeated ,, i want that only distinct entrries should come as lookup

  3. “In an ideal situation, Microsoft addresses this issue and makes choice fields able to be looked up in the near future.”

    Did this get fixed in SP2013? Or will we be hoping for it in the next version?

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s