Convert Singleline textfield to filtered lookup dropdown

A new version can be found here.

08.04.2011 A small makeover to get rid of some extra script references and to add compatibility to all major browsers.

This one is related to my post on Cascading dropdowns, but is used to convert one column of type “Single line of text” or a column of type “Hyperlink or Picture” to a filtered dropdown.

You can populate this dropdown from any list in current site, sub site or parent site – as long as the user has read access to the list holding the information. The lookup can be against all elements – or filtered by any metadata in the source list item – like an “active/inactive” – Yes/No Checkbox-column.

In your list – add a column of type “Single line of text”, with a nice “FieldInternalName” (a name without spaces and special characters) – you can edit the column name as soon as the column is created to get a readable “DisplayName”. It is this newly created “Single line of text-column” that is to be converted to a dropdown. by this script. In this example I have used the “Title-column” as the field to convert.

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). Make sure all users have read access to that folder.

Download the file “dropdownFromTextOrHyperlinkField.js” from here

Upload it to the selected folder.

Add a CEWP below your NewForm list-form (and EditForm if you like) like this:

IMG
With this code:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/dropdownFromTextOrHyperlinkField.js"></script>
<script type="text/javascript">
fields = init_fields_v2();

singleFilteredDropdown('{1b128964-7075-491a-be7b-55c40d94714b}',L_Menu_BaseUrl,'Title','Active','Boolean','Eq','1',false,'DropdownNr1','<select>','Title',true,false,false);

</script>

Please note that the call to “init_fields_v2()” has changed from “init_fieldInternalName()” from earlier versions. You have to change the “src” to the file “dropdownFromTextOrHyperlinkField.js” to match your local path.

The list that is source for my lookup has a listGuid of {1b128964-7075-491a-be7b-55c40d94714b}, a Yes/No column named Active, and the Title column which holds the value to populate the dropdown.

IMG

The source list is located in the same site as the target list – hence the variable L_Menu_BaseUrl which SharePoint provides for us.

You should end up with a result like this:
IMG
Here some of the elements in the sourcelist is set to “inactive”:
IMG

To get a hyperlink back to the selected element – use a column of type “Hyperlink or Picture” – with hyperlink format, and change the parameter “showAsURL” to true.

Please ask if something is unclear.

Alexander

37 Comments on “Convert Singleline textfield to filtered lookup dropdown”

  1. Another great one. Is there a way to add additional functionality to this where if the user wanted to add a new item to the list the user types the new item in the FilteredDropdownItem list and the value updates the source list with the new item, which would be available the next time the form is loaded?

  2. Larry,
    Yes, that is no problem at all. I will make up a post on it. I will also put in an new version of this script that pulls the items from a list view and therefore is much more versatile (eliminating the coded filter in the script).

    Alexander

  3. Pingback: Creating mailing lists for SharePoint sites and blogs [No Code Required] – Part 4: Creating the Country lookup elements | End User SharePoint

      1. I call BS on this one.
        The sourceListURL parameter isn’t used for anything in this script. So I guess you can thank the Placebo effect for your success.

    2. I have a opposite situation. I have a lookup column in a list. and now user like to have it as free text field. is there a way to change a lookup column to single line text field without losing data?

      thanks,

  5. can this pull from a people/group field? I am having an issue making this work. I get a query error. when I change the field I’m ok. Odnt want to add another field and a calculated field will not read people group. any suggestions?

    1. Yes it can. Edit the code in the CEWP like this:

      singleFilteredDropdown('{1b128964-7075-491a-be7b-55c40d94714b}',L_Menu_BaseUrl,'Responsible','Active','Boolean','Eq','1',false,'DropdownNr1','<select person>','Title',true,false,false);

      The only thing edited from the original is “Title” changed to “Responsible” – where “Responsible” is the FieldInternalName of your “People picker”.

      Alexander

    2. Responsible is not the FieldInternalName of my People Picker. Does this matter. The name is actually “Name” When I apply your change like this
      singleFilteredDropdown(‘{6B0D4A3B-1D6F-4C6E-8DF3-D533DEBDD1F1}’,’/sites/olr’,’Responsible’,’Created’,’Boolean’,’Neq’,”,false,’DropdownNr1′,’Select a Gatekeeper’,’GateKeeper’,true,false,false); I get an error
      An error in the query:

    4. Thats what I thought, and Have been trying different combinations, Still getting the same error..

      So your saying nothing in that line of code changes. If using a text field or people picker as long as you are using the fieldinternal name?

  6. I was looking for an easy way to convert a text column to a lookup on a library that already has about 10,000 records (with approval enabled), and it wasn’t looking pretty. I think your technique allows me to effectively do this without having to mess with the library itself. I have a couple of questions though.

    My library actually has two text columns that I want to convert to lookup columns. Is this possible, and can you point me in the right direction to the changes required (both a would pull from the same list)?

    Depending upon the content type of the document type being edited, one, both, or neither of the columns might be on the editform. I’m not a java programmer, but I’m guessing that for such a situation I would just delete the “alert” line in the singleFilteredDropdown function if I didn’t want to be notified?

    thanks for a great post.

  8. Yes, my document library has a single line text column “To Company” and a single line text column “From Company”. I want to create a lookup for both, pulling from the same “Companies” list. I’ve added a 2nd CEWP to the EditForm page. The first CEWP represents the “To Company” while the 2nd represents the “From Company”. Once I remembered to rename the dropdown identifier in the 2nd CEWP, it worked like a charm. Thanks for your assistance Alexander.

  9. can this script be converted into radio buttons instead of a dropdown? My probelm is I have a form with radio buttons. the list items are numeric 1-5. once the response posts to the list the list does not recognize the responses as numeric, therefore I can not perrform averages in my views. I know if I use this script with the dropdown I can get my responses to return as numeric . it’s just the radio buttons would be easier to use.

  10. Pingback: Validate text field input against value in another list « SharePoint JavaScripts

  12. having a small issue getting this to work in google chrome, as the page loads, the text field (EditForm) is populated with the selected value, but when load completes, the dropdown appears, and is blank. No way to select a value, and if required field, cant save changes. this works great in IE. Haven’t tried firefox yet.

    2. Do you need the other browsers to work. Internally we only use IE, but I did do some research and found that you could use
      [sourcecode]
      $.browser

      this contains severl flags you can use. maybe you can check the browser and execute a differnt code if not IE.

      One thing I did read is
      Because $.browser uses navigator.userAgent to determine the platform, it is vulnerable to spoofing by the user or misrepresentation by the browser itself.

  14. great article, but doesn’t work in custom form, even after I followed your another article on how to use script in custome form.

    In precheck action, you are using ‘FieldType = ‘ to check, so always fail.

    1. Hi,
      That is correct. The article you have read was made before i started to use the “FieldType” parameter. You must add that to your fields object as well, or just commetn out the “precheck”.

      Alexander

  15. A, you know I am one of your biggest fans. I wanted to let you and your followers know this script failed when I upgraded to jquery 1.7. took me almost 45 minutes to figure it out. No error, just nothing populating in the dropdown. the easy fix was to point to an earlier version of jquery. this is not to deminish any of your work. just an FYI for readers

  16. Alex,

    I know you are taking soe time away from your blog, but I hoped you could help me with a situation I have come upon. I am using your DropDownFromTextorHyperlink JS. I have added the following code to my CEWP:

    $(document).ready(function() {

    fields = init_fields_v2();

    ShowProc()

    $(“select[title=’Process Team’]”).change(ShowProc);

    var obTeam = $(“select[title=’Process Team’] option:selected”).text();

    singleFilteredDropdown(‘{9AE59E2C-9774-4421-B001-36C9F8A07461}’,’http://sp001.jpmchase.net/sites2/spmlyysx/ProcessExecutionEscalations-Opportunities/’,’ti

    tle’,’Process_x0020_Team’,’text’,’Eq’,obTeam,false,’Proc1′,”,’Process_x0020_Name’,true,false,false);

    function ShowProc() {
    var obTeam = $(“select[title=’Process Team’] option:selected”).text();
    var obProc = $(“select[ID=’Proc1′]”).parent().parent().parent();
    if (obTeam !== ‘0’) {
    obProc.show();
    } else obProc.hide();
    };

    });

    The drop down is popping up but it is blank I can not get it to populate the information from my index list. I keep getting an error that says it is an issue with line 148 Character 2 of the DropDownFromTextorHyperlink.js I have looked this up and it references the folowing command:

    148 if(viewFields.indexOf(‘ID’)<0)viewFields.push('ID');
    149 if(viewFields.indexOf('FileDirRef')<0)viewFields.push('FileDirRef');
    150 if(viewFields.indexOf('ServerUrl')<0)viewFields.push('ServerUrl');

    If you have any thoughts or can help me at I would greatly appreciate. I have been looking at your blog for some time and you always have great solutions.

    Thanks in advance.

    1. Hi,
      You have specified “SourceFieldInternalName” as “title”. the title field has the FieldInternalName of “Title” – with capital T.

      Alexander

  17. Thanks. I did end up noticing that. Ultimatley for what I was trying to do I ended up using your cascading drop downs instead. It is a great help. Thanks

  18. i know this is an older topic, but I am wonder is there a way to make this work but to allow for multi selection? Perhaps by converting the filtered choices into checkboxes?

    I have a big form load issue due to a mult-select lookup and need a way to allow for a multi-select that doesn’t first return all the possible choices prior to filtering (like all SPservices actions do).

  19. Is it possible to filter the values using a column/value combination in the same list? For example, I have a column for Category and I want to only pull those hyperlink values when Category equals “Blue”.

    Also, is it possible that this can be used where the user selects a value then can click a button (perhaps) to go to the link in the hyperlink field from the list?

    I’m looking for a solution similar to the old ‘I need to’ web part functionality as it helps streamline web design in the case of many links on a page.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.