none
Wrong display format when using number field in a lookup

    Question

  • We have a custom list (A) with a number field in it. On a second list (B) i create a lookup to this number field. Now when opening the 2nd list (B) in datasheet view there are 14 digits placed behind the number, for example 2 will be transformed in 2.00000000000000. If i open the original list (A) in datasheet view there are no digits behind the number.

    I played around with some reginal settings (site, user and pc), but this does not seem to be the problem. Also since the first (A) and second list (B) show up with different behaviuor this eliminates regional settings. It looks like the problem is in the lookup and SharePoint is parsing the number as a string after the lookup.

    Thursday, April 25, 2013 8:49 AM

All replies

  • as a workaround, you can add a hidden calculated field to first list, convert it to text and use that column in lookup

    ceren


    • Edited by ova c Thursday, April 25, 2013 8:51 AM
    Thursday, April 25, 2013 8:51 AM
  • We have the same problem but we eliminated the data sheet part. (and thus regional client settings)

    In addition to your example, we created a calculated column in custom list (A) adding the number field where the formula is =[Numberfield] and the return value is a string field

    When using this calculated column as a lookup in list (B) the standard view also displays the extra 14 digits.

    Thursday, April 25, 2013 8:56 AM
  • as a workaround, you can add a hidden calculated field to first list, convert it to text and use that column in lookup

    ceren



    This only works for us when we use  =TEXT([OriginalNumberField],"0") as a formula and the data type returned is a single line of text. Only using =[OriginalNumberField] isn't enough.
    Thursday, April 25, 2013 9:00 AM
  • So now we have a workaround for a workaround. Not really a solution in my case, since all the lists and columns are being pre populated with a custom solution.

    It would be better if Microsoft has confirmed somewhere that all lookups are being transformed into strings.

    Thursday, April 25, 2013 9:14 AM