# Reporting and Analytics for Service

## Get Involved. Join the Conversation.

This is a public Forum  public

## Topic

Text Field To Number Issue
Topic posted February 1, 2018 by
Title:
Text Field To Number Issue
Content:

#### 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.

Thanks

Image:

## Comment

• 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.

• 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

• 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)?

• 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.

Thanks

• 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

• 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.

Thanks

• 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)
```

Bastiaan