Sort URL query/list based on string field length

[Philip Lee2]

I'm trying to sort survey results from the Metric Result [asmt_metric_result] table based on string length in the [string_answer] field using the following: 

In the URL: [instance]/asmt_metric_result_list.do?sysparm_query=instance.taken_onONYesterday@javascript:gs.beginningOfYesterday()@javascript:gs.endOfYesterday()%5EORDERBYjavascript:string_value.length&sysparm_first_row=1&sysparm_view=assessment

Unfortunately I don't have the ability at this time to simply add an additional field to the table and have this calculated at that level - which is why I'm using this round-about method at the moment.

Any ideas?

[sergiu_panaite · Accepted solution]

First of all to call the order by you need to use sysparm_orderby:

https://docs.servicenow.com/bundle/helsinki-platform-administration/page/administer/exporting-data/reference/r_URLQueryParameters.html

Then the LENGTH needs to be passed as a database function rather than a javascript call, as we would need to send a query on database something like:

SELECT * FROM asmt_metric_result WHERE <your conditions> ORDER BY LENGTH(string_value);

We do not have this possibility in the list of operators, see here:

https://docs.servicenow.com/bundle/kingston-platform-user-interface/page/use/common-ui-elements/reference/r_OpAvailableFiltersQueries.html

Hope this helps.

[Philip Lee2]

I figured this wasn't possible but I wasn't 100% sure why - so this is exactly what I was looking for.

Thanks Sergiu!

[hakan_semerci]

Please check the function fields added in Kingston. That might be helpful.

https://docs.servicenow.com/bundle/kingston-application-development/page/build/platform-functions/concept/platform-support-functions.html