locked
How to apply trim() function for values in Infopath drop down? RRS feed

  • Question

  • Hi Folks,

    I had drop down in Infopath which fetch values from Sharepoint List column. Values are coming correctly, but prefix with some junk data. e.g. "20#;CorrectValue1". "20#;" are junk values

    While research, I got to know that Sharepoint List column is lookup and it is referencing another Sharepoint List column. And junk value are row number in reference List. I mean - "20#;" is the 20th row for 'CorrectValue1' in reference List.

    Is there any Trim function to trim junk values. If yes, please let me know how to achieve this.

    Please advice.

    I am using Infopath 2007 with MOSS.

    - Chintan
    Tuesday, March 15, 2011 12:06 PM

Answers

  • I ran into a similar issue and my resolution was to use a simple workflow that copied the value from the lookup column into a single line text column. Then I used the values from the single line text column to populate the values for my drop-down list box.
    Planet Technologies || SharePoint Task Force
    • Proposed as answer by Clayton Cobb Thursday, March 17, 2011 4:06 AM
    • Marked as answer by David HM Wednesday, March 23, 2011 1:11 AM
    Wednesday, March 16, 2011 1:40 AM
  • Exactly. You can't use a calculated column. I don't believe there is an out-of-the-box way to trim part of the value in the drop-down. You can't apply a function to the display name of a list box entry. That's why I created a workflow to copy the value from the lookup column into a text column.

    I imagine that this can be done with code, but because browser-enabled forms with managed code require extra steps to upload, approve, and activate them, I didn't go that route.


    Planet Technologies || SharePoint Task Force
    • Proposed as answer by Clayton Cobb Thursday, March 17, 2011 4:06 AM
    • Marked as answer by Chintan123 Thursday, June 7, 2012 2:15 PM
    Wednesday, March 16, 2011 5:50 PM

All replies

  • I ran into a similar issue and my resolution was to use a simple workflow that copied the value from the lookup column into a single line text column. Then I used the values from the single line text column to populate the values for my drop-down list box.
    Planet Technologies || SharePoint Task Force
    • Proposed as answer by Clayton Cobb Thursday, March 17, 2011 4:06 AM
    • Marked as answer by David HM Wednesday, March 23, 2011 1:11 AM
    Wednesday, March 16, 2011 1:40 AM
  • Hi Roland,

    Thx for your suggestion. However, I tried new stuffs. There is a column type call "Calculating" in MOSS. Which will copy/paste specified data in updates of another column.

    However, my column is a look up, so this method will not work.

    Folks, please advice.

    - Chintan

    Wednesday, March 16, 2011 7:45 AM
  • Exactly. You can't use a calculated column. I don't believe there is an out-of-the-box way to trim part of the value in the drop-down. You can't apply a function to the display name of a list box entry. That's why I created a workflow to copy the value from the lookup column into a text column.

    I imagine that this can be done with code, but because browser-enabled forms with managed code require extra steps to upload, approve, and activate them, I didn't go that route.


    Planet Technologies || SharePoint Task Force
    • Proposed as answer by Clayton Cobb Thursday, March 17, 2011 4:06 AM
    • Marked as answer by Chintan123 Thursday, June 7, 2012 2:15 PM
    Wednesday, March 16, 2011 5:50 PM
  • Folks, please advice.
    You WERE given advice.  Did you follow it and see if it worked for you?
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Thursday, March 17, 2011 4:07 AM
  • 20#; is not a junk value.  20 is the ACTUAL value of the lookup, because lookups bind to the ID.  The display value is just for display...it's not the real data.  All lookup type columns in SharePoint are in the form of ID#;DisplayName when you look at the raw XML.  Lookups have to bind to the ID, else you cannot track updates to the original item - it is the only guaranteed unique value.  There is no trim, but there is a set of substring functions that you can use in InfoPath, however, they can't be applied to the DISPLAY name of a dropdown data connection.  You need to save off the text after the semicolon to a hidden text column like Roland said, then make your dropdown point to that field instead of the lookup field.
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Thursday, March 17, 2011 4:10 AM
  • Thx Roland/Cobb,

    It would be great if you can provide URL to your solution "simple workflow that copied the value from the lookup column into a single line text column"

    Thx 

    Chintan

    Thursday, March 17, 2011 9:55 AM
  • There is no URL.  Just try it.  Use Set Field in Current Item, and in that activity set the text field to the value of the display column in the source list where Lookup Column = Display Name Column.
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Thursday, March 17, 2011 2:04 PM
  • Hello Folks,

    i tried it with workflow and it worked. However, it do not work with Calculated column type. it gives weird error. :(

    Pls. advice.


    Thanks, Chintan

    Thursday, June 7, 2012 2:15 PM