Remove Decimal(.0) from ( Days Open) Field eg:209.0

[Chandu12]

Note : Not a global table and it is set as read-only, as the name simplifies it counts the number of days Open.

Issue Details:

I'd like the "Days Open" field to only be integers and stop putting the additional .0 after the values.   We don't use it.

I also want to ensure this number field is able to be sorted appropriately and actually do numerical order smaller to largest and vice versa.   Right now, if I try to sort cases on Days Open they won't go in numerical order and go off the front number regardless of how high the number is (e.g., 209.0, 21.0, 211.0, 2112.0, 214.0).  

Note : can we do this without scripting i.e., scriptless or else if we can use scripting what would be the logic.

[Chuck Tomasi]

If you want it to behave like an integer, you'll need to make the field an integer type. To do that, I strongly recommend backing up the data in that column (e.g. export it). You may not be able to change a string to an integer directly unless you first delete the field and recreate it - hence the export/import idea.

[Chandu12]

Okay but can we do this using scripting.  

[Chuck Tomasi]

You would need to find out where that field is being set. Do you have that information? If it's being calculated by a script, you should be able to make adjustments to the script to truncate the 0.

As for sorting, no. That cannot be done via script because string fields sort differently than integer fields.

Integer fields

1

2

4

10

17

21

121

String fields

1

10

17

121

2

21

4

That's not a ServiceNow thing so much as a programming language data type thing. They all do that.

[johnolivermendo · Accepted solution]

Hey Chalan,

Chuck is correct in that you need to create a backup of that field's values. I have just tested a way to do this without exporting and importing and it's going to involve creating a temporary integer field to hold all of those values. It's very simple actually.

My method is going to require a mix of manual and scripting:

1. Create a new temp field on your table with type=integer

2. Run this script which will transfer all of your days_off [String] values to the days_off_temp [Integer] field. Something like this:

var gr = new GlideRecord('[YOUR TABLE HERE]');
// Add any extra queries here
gr.query();

while(gr.next()) {
     gr.u_days_open_temp = gr.u_days_open;
     gr.update();
}

I just tested it and it does automatically convert the string version to integer version just by setting the value. No need for any extra parsing call.

3. Confirm that all of your data was successfully transferred to the temp field before continuing to step 4

4. Go to the dictionary table and delete your days_open field

5. Recreate your field and set it to the correct field type of Integer

6. Run the same script except now your transferring from days_off_temp to days_off (look at line 6)

var gr = new GlideRecord('[YOUR TABLE HERE]');
// Add any extra queries here
gr.query();

while(gr.next()) {
     gr.u_days_open = gr.u_days_open_temp;
     gr.update();
}

7. Confirm that all of your integers have transferred to the original field 'days_open'

8. Delete the temp field when not needed anymore

Sorting should not be a problem anymore at this point.