Lookup column uses ID instead of Title in rule formula RRS feed

  • Question

  • I have two SP lists: Partner and Request.

    Partner just has a Partner Name column, plus the ID column.

    In Request, I have a lookup column pointing to the Partner list. There is also a Title column and a Request Date column. When I set up the lookup I set it to get info from the Partner list’s Partner Name column, not the ID column.


    I made an InfoPath form to make a new Request, with the Partner being selected from a Drop-Down List Box. When this form is submitted, I would like to change the Title field to be a string concatenation of Partner Name, a comma, and the Request Date. I made a rule to do this. Example of what I want: “Partner A, 8/27/2010”.  However, the result is that the Partner ID is used instead of the Partner Name and what I get is “1, 8/27/2010”.


    (More: In production I would use the Form Submit rule to do this. For development I used a rule that runs when the field changes. If I change the Drop-Down List Box properties Value property (on the data tab) from d:ID to d:Title the string is formatted as desired, but the form won’t submit and shows a tooltip that only positive integers are allowed.)


    How can I make the rule use the Partner Name column in the formula for the rule, instead of the ID?


    Thank you,


    Thursday, August 5, 2010 1:03 AM

All replies

  • When you use a lookup column in SharePoint, it actually uses both the ID and the value of the field you chose to look up and stores it in the XML like this: 1#;Partner.  In the browser, you see Partner, but the value is hyperlinked.  That's because they are only showing you a friendly value, but the real value is the concatenated string I showed you.  It does this so that the reference to the original item is always unique.  Display names are not, so the ID is always used to identify the item.

    Now, what I'm thinking is that you left out some information.  Here is where I'm missing info:

    • You said you made two SP lists, one of which was Request
    • You then said, "I made an InfoPath form to make a new Request."  The way you wrote this was as if it was SEPARATE from the SP list you already said you created earlier in the thread.  Did you mean to say that you clicked "customize form" in the existing Request list so that you could modify it with InfoPath?
    • Assuming the above answer is "yes," then that means your dropdown column in the form is nothing more than the lookup column you described previously that pulls from the Partner list.
    • If so, then that's why the field that the dropdown is bound to will only accept an integer.  What you need to do is de-couple the lookup.

    Ok, so what I mean by decoupling the lookup is that if you are using InfoPath to build the Request form, then you don't need to do the lookup column, because that's really only for when using the OOTB list form.  Follow these steps:

    1. Delete the partner lookup column
    2. Re-create it as a single line of text.
    3. In the Request form template, create a data connection to retrieve from the Partner list
    4. Change your Partner text box to a dropdown
    5. configure the dropdown to receive external data from the Partner data connection
    6. Set the Value and Title as the Partner field (no ID)
    7. Publish and try it

    PS. No reason not to use a simple rule or default value for the Title field.  Just be sure it's either not visible or disabled so that users don't type anything in it.  I also think it's a poor idea to put a comma and space in the Title, but that's up to you.

    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    • Proposed as answer by mhoran Thursday, May 17, 2012 5:30 PM
    Sunday, August 22, 2010 2:21 AM
  • This is a rather annoying 2010 change that I've come to loathe as I upgrade InfoPath forms.

    In 2007, what you did is right.  If you add a list as a data source, you will get XML that has the value, not the ID in the lookup field's element.  In 2010, you get the lookup ID.

    There may be some secret setting somewhere in the list or form to change the behavior.  However, the only two ways around it that I've found are to:

    1) bring in the source list as well (Partner in your case) and "lookup the lookup"

    2) change it to a text field.

    I go with #2 when I can, since it keeps me from having to introduce more moving parts which may break down the line.  But, if you have a complex app that involves many people providing data entry and need to mitigate dirty data, it's better to stick with #1.


    Friday, September 17, 2010 1:40 PM
  • Or do what I said...
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Friday, September 17, 2010 7:59 PM
  • I am having the same issue here. I have 2 custom lists: Company and Deal. In the Deal list, I have a multiple-selection lookup column Company_Multiple which is pointing to the Title of Company list. In the Deal list, I also have a MultipleLine TextBox called Company_Multiple_String which will concat all selected companies from the Company_Multiple. The end-result of my concat will give me a bunch of IDs such as "1, 2, 3, 4" instead of "CompanyA, CompanyB, CompanyC, CompanyD". Below is the concat code.

    normalize-space(substring(eval(eval(Value[. != ""], 'concat("; ", .)'), ".."), 2))

    If anyone has a solution for this, please let me know.

    Thanks in advance


    Wednesday, December 15, 2010 7:36 PM
  • Why do you loathe this in 2010?  That feature was not even available in 2007.  You couldn't convert list forms to InfoPath in 2007, so there are no forms built in 2007 that have a SharePoint lookup column inside.  Any 2007 form with a similar lookup has a secondary data connection bound to text field in the main data source, and you get to choose whether you use the display name as the value or the ID or something else.  It's all configurable.  What the OP is talking about here is an actual lookup column in a list where the form has been converted to InfoPath, which then converts it to an InfoPath-based lookup with secondary data connection, but it's not the same as what you do in 2007.  The OP did not add a list as a data source.  He created a lookup column in the list, and then converted to InfoPath, which is much different.  My suggestion was to CHANGE it back to a custom data connection bound to a text field instead of an actual lookup column.
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Wednesday, December 15, 2010 9:51 PM
  • GMC, please create a new thread as it's a different question, though it is related, so provide a link back to this thread.  I gave a long solution to the original problem that the OP didn't bother to respond to, but it wouldn't be good to have another, separate long explanation buried lower in the thread.  Please create new, and I'll answer it there.  However, if you read my long explanation above, it should explain the situation.  Were you not able to figure out what to do based on what I said?

    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Wednesday, December 15, 2010 9:53 PM
  • Hi Clayton,

    Thanks for the response. I am also working on SP 2010 environment. I think I am facing the exact problem as the OP here. I created a lookup column in the Deal list and customize the list using the InfoPath. The lookup column is converted into the multiple-selection listbox. I can set the Value to ID, and set the Display Name to Company. Like you said it is all configurable. However, when I create a Rule to copy the Display Name from the multiple-selection listbox to a textbox, I cannot get the Display Name, and it always gives me to Value. :(

    I understand what you explained to the OP, but I can't do it for an obvious reason. I am having a multiple-selection box instead of a single-selection field. You might say that I should use the multiple-choice field, and then I can change the datasource to lookup from the other list. Ok, I did that too but there is one more obstacle. When you try to filter by the choice field on your view, it only shows the choices you have on your choices list. If the users add some new companies into the company list, the admin must keep track of those changes and modify the choices accordingly.

    Anyway, I moved my question to here. Please help me to answer it. Thank you,

    Wednesday, December 15, 2010 11:03 PM
  • Hi Clayton,

    I have a list that is very large that I need to lookup a name field from. I need to update another list column with that name. A dropdown list is not feasable. Is there another option because I need to capture the name not the ID. I'm talking possibly 6000 new entries a month.

    Thank you,


    Tina L. Murphey
    Tuesday, October 11, 2011 8:46 PM
  • Hi Tina.

    Did you ever resolve this issue... I've got the same issue. a huge list providing the lookup data, I'm assuming as a lookup the form will grind to a halt..



    Sunday, November 26, 2017 8:19 PM