6 Replies Latest reply: May 9, 2017 7:33 AM by Marcus Sommer RSS

    Is there any reason why string functions come out null in a data model?

    Nikita Naresh

      I have a field in my data called ID which is supposed to be the format - A111111, so 1 character, followed by 6 numbers.

       

      There are a few values where this ID is preceded by some garbage text, so to remove this, I use the statement:

      If(isnum(right(ID,6)) and istext(left(right(ID,7),1), ID,'N/A') as ID

       

      This statement works perfectly on the front end. When I select any ID, I can see the proper format displayed. But for some strange reason, this statement only works for a few values in my ID list on the back end. Even in the case of multiple rows with the same ID, only 1 instance displays the correct ID and all the other are nulls.

       

      I tried to debug this and pulled out the actual characters from my Left and Right statements and these are showing up as null as well even though the ID field is 100% populated.

       

      I've tried trimming and using upper on my values, but nothing seems to work. Is there any reason why basic string functions turn up as null when the field is populated?