Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Text Field To Number Issue
    Topic posted February 1, 2018 by Todd Bronze Medal: 1,250+ Points 
    158 Views, 7 Comments
    Text Field To Number Issue

    We have a reporting requirement that is giving me trouble.  We have a custom test field that is populated with a duration in either whole numbers or decimal.  For example, values could be 1, 2.5 or .5 etc but will not have more than one decimal place so I converted it to a number and used the functions in this post 

    This function to_number(incidents.c$custom_field) + to_number(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1))/100 results in the actual number being changed as seen in Duration1 column of attachment.

    This function to_number(incidents.c$custom_field) + to_number(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1))/power(10, length(substr(incidents.c$custom_field, instr(incidents.c$custom_field, '.') + 1))) results in the actual number being changed also as seen in Duration2 column of attachment.

    I have been struggling with the proper function so that the number is not transposed and show the actual number if it is a whole number without any decimals and if the Duration is a decimal with only one decimal 0.5 or 0.2 also so that I can make further calculations as a number.  Screenshot attached of the report issues with columns. 

    Any help would be appreciated.






    • Sebastiaan Draaisma

      So, if it's 8.00 you want it to show 8 and if it's 8.01 you want it to show 8.01 correct?

      Maybe you could convert it into a string first, extract the first character with substr, convert that back to a number so you have nr 8 and devide your custom field by that number. If it equals 1 you know you have a whole number as 8.00 / 8 = 1 where as 8.01 / 8 = 1.00125 and therefore not whole.

      An if(customfield / ExtractedNR = 1,ExtractedNR,customfield) might do the trick

      If the outcome is 1 (a whole number) show the extraction only, if not show your custom field that contains the decimal.

    • Todd

      Thanks Sebastiaan.  To clarify the field is a text field and if it has 8 (without any decimals) I need to convert it to a number it in order to do some calculations but it gets changed to either 8.08 (Duration1) or 8.80 (Duration2) and need it to remain 8 or even 8.00 would probably be ok.  In addition if the actual value in the text field is 0.5 it gets changed to 0.05 but the second function handles that correctly and is 0.50 shown in Duration2 column.  I hope that helps...

      Thanks again

    • Sebastiaan Draaisma

      I replaced incidents.c$custom_field with '8' as to see what I got and I get 8.08
      to_number('8') + to_number(substr('8', instr('8', '.') + 1))/100
      The reason for your outcome is that you first have your 8 to which you add 0.08 with to_number(substr('8', instr('8', '.') + 1))/100

      Please observe that instr('8', '.') gives a 0, this as there is no '.' in the custom field '8'
      So, you have 8 and substr('8', instr('8', '.') + 1) also returns 8 which you devide by 100 to get 0.08 (8 + 0.08 = 8.08)

      Could you tell me which outcome you expect to receive or what you are trying to acomplish (what it is you try to calculate)?

    • Todd

      Thought it would be straightforward but I'm mainly trying to get a total duration as these numbers would be hours i.e. 2 hours or .5 for 1/2 hour etc.


    • Sebastiaan Draaisma

      Ah, so the custom field is a text field that needs to be converted to time?
      Have you tried the to_date(str, format) function and have the system convert it to time format?

      See Functions

    • Todd

      Thanks again Sebastiaan .  I tried using the to_date but noticed the to_date functions have the YYMMDD included and all I need is to convert it as Hours if the value is a whole number like 2, 4 etc and Minutes if it 0.5 or 0.2 that is if it is not possible to convert to just a number.


    • Bastiaan van der Kooij

      hey Todd,

      you can check if there is a dot in the number and only add the decimal part of the function if the dot is there;

      to_number($number) + if (instr($number, '.') > 0, to_number(substr($number, instr($number, '.') + 1)) / 10, 0)