Condition regarding the length of the data in a List view

[johnsgp]

I would like to write a condition in a List view that finds only those rows where a certain field has a certain length.   For example, I want to find rows where the telephone number is more than 15 characters long.   Is this possible using the UI only (no scripting)?

[coryseering · Accepted solution]

Hi johnsgp,

This is not possible using the filters on a list view. You can do it via a script, though it's also not very performant. You could write a Script Include which queries all of the records in the table where that field has a value, gets the value as a string, and checks it's length. The script could return a comma-separated string of sys_ids where the length matches, and your filter could use "sys_idINjavascript:YourScriptInclude.yourFunction()" to limit the results.

This is not a good thing to do. It's slow, it requires a full table scan, and if the result set is huge it's going to be very annoying to work with.

What is your specific use-case? Perhaps there is a better way to accomplish your goal, using reporting or maybe Business Rules or calculated values on the records themselves.

[Mike Allen]

You could also create a field on the table that holds how many characters the field in question has.   It would just involve setting the default value to the length of the string, having a before insert/update business rule that keeps the value correct, then populating the value in the existing records, but all of that is scripting.

[johnsgp]

Thanks, Cory - all good ideas which I can try.   My use case is data cleanup before adding formatting rules, and trying to delegate the cleanup across a larger group than administrators.   The "phone number" is not the actual thing that needs to be worked on but it's an easy-to-understand example.