Sort a String field like a Number field?

[zschneider]

I have a custom field of just numbers that is basically like an Invoice Number. This field is the primary display value for a table on reference fields, so it needs to be able to work with STARTSWITH queries. The problem is users want lists sorted by this field to sort like an integer (ie 1,2,3,4,5,6,7,8,9,10) and not how Service-Now sorts numbers that are strings (1,10,2,3,4,5,6,7,8,9).

Does anyone have any suggestions on how to allow a number to use the STARTSWITH query, but sort like a number field? Removing commas from an integer was simple, but I can't seem to find anything on fixing the sort/query problem.

Thanks in advance.

[prdelong]

Easiest way would just to have a custom integer field on the table but hidden. You could run a background script and copy and convert the value from the Invoice Number field. After that, you could use an "After" business rule to do the same function for all new records.

I'm not sure how I'd approach it if there are characters or preceding prefixes (ie INC, CHG, et al), but the above solution allows for reporting as long as you keep the numbering consistent and educate the end user. It might be simpler to create a report for them.

[rushputin]

Is formatting the strings an option? 01, 02, 03, etc vs. 1, 2, 3, etc?

[zschneider]

That's a good idea. I'll give this a try.