none
SharePoint List Calculation - Choice Field Value versus Choice Field Label?

    Question

  • I am trying to find out if it is possible to have a choice (dropdown) field that contains user friendly text labels, but the value is actually a number when submitted. I am trying to reference the number value and use it in calculating the value of another field.

    Any help would be much appreciated.

     

    Thanks

    Monday, February 14, 2011 9:31 PM

Answers

  • When I create a Calculated column, lookup column will not appear in the candidate listbox to use in the formula, and if I type in "=[LookupColumnName]" as formula, I will get error message when saving.

     

    However, Choice column can be used in formula for calculated column.

     

    If your user friendly choice list is:

     

                    SharePoint 2010

                    Windows Server 2008

                    SQL Server 2008 R2

     

    You can change the choice list a little bit as:

     

                    1;SharePoint 2010

                    2;Windows Server 2008

                    3;SQL Server 2008 R2

                   

    And use "=Left([ChoiceColumnName],1)" as formula for calculated column.

    Wednesday, February 23, 2011 6:56 AM
    Moderator

All replies

  • That happens by default with lookup columns.  They display the column you selected during the creation of the column, but the true underlying value is the ID (number) of that list item so that uniqueness can be guaranteed.  If you want to have your own number as the value, then you would have to convert the list to InfoPath forms, create a receive data connection to the source list, then create a text field in the current list for the lookup, and finally displaying the text field as a dropdown that is connected to the list data connection where Value is set to your number field and Display Name is set to your friendly name field.  Be aware that this does not ensure uniqueness.  Instead, you would have to ensure 100% uniqueness in your number field in order to make sure the dropdown items don't get mixed up.


    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Monday, February 14, 2011 10:14 PM
  • When I create a Calculated column, lookup column will not appear in the candidate listbox to use in the formula, and if I type in "=[LookupColumnName]" as formula, I will get error message when saving.

     

    However, Choice column can be used in formula for calculated column.

     

    If your user friendly choice list is:

     

                    SharePoint 2010

                    Windows Server 2008

                    SQL Server 2008 R2

     

    You can change the choice list a little bit as:

     

                    1;SharePoint 2010

                    2;Windows Server 2008

                    3;SQL Server 2008 R2

                   

    And use "=Left([ChoiceColumnName],1)" as formula for calculated column.

    Wednesday, February 23, 2011 6:56 AM
    Moderator
  • I just signed in to say that this is either poorly written or just plan wrong. I have tried the formula provided and it is useless.
    Wednesday, April 09, 2014 4:14 PM