Report grouping based on substring of short description (letter cases sensitive issue)

[Joe Wong]

Hi ServiceNow Community,

Running into a roadblock and seeing if there is anyway around this.  I am creating a report on Platform Analytics where it needs to group by the first 13 characters from the short description field.  The most straight forward way and one which I got closest to the solution was using a Function Field and glidefunction:substring.  This works until I noticed that some of the short descriptions letter cases are different.  Example is I have one that is "Equip Setup" vs "Equip setup" is group is separate groups because of the upper and lower case "S" in the word "setup".

There is no glidefunction to lower all cases and I have also tried Calculated Field, but that does not work with reports.

Any thoughts on this, or am I doomed to need to export this data and run the report on Excel...

Thanks!!!

[Vishal Jaswal]

Hello @Joe Wong 

Referenced from: https://www.servicenow.com/docs/r/washingtondc/platform-administration/platform-support-functions.ht... 

Try below:

glidefunction:lower(glidefunction:substring(short_description, 0, 13))

[Joe Wong]

Thank for the suggestion Vishal.  I saw that during my search too and tried it and it did not work.  All I can think is ServiceNow somewhere removed the "lower" function from function field.  I am getting "invalid function" on my function field.

[Vishal Jaswal]

@Joe Wong The other solution I can think of for the time being is to have a custom string field whose value will be "short_description.toString().substring(0, 13).toLowerCase();" and the server side logic runs on create and update (when short description changes).

[Joe Wong]

Hi Vishal,  again, thank you for the suggestion.  But unfortunately that solution though will solve what I am looking for will not pass our architect.  Having an custom field with a script on a core table that will run on every insert or update will not fly here.

Thanks again.