Verified Answers

If an answer to your question is correct, click on "Verify Answer" under the "More" button. The answer will now appear with a checkmark. Please be sure to always mark answers that resolve your issue as verified. Your fellow Community members will appreciate it!  Learn more

  • State Not Answered
  • Replies replies
    6
  • Subscribers subscribers
    169
  • Views views
    227
  • Users 0 members are here
  • Locked Locked
Related
Options
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

How to sort numbers correctly in a Text field

Thu Le

The "IssueID" and "Title" fields in my project, both are "Text [System]" field in the SBM Composer and "VARCHAR2" in the database. The "IssueID" field, which has all numeric characters automatically assigned by the system, is sorted correctly in its numeric order. However, the "Title" field, which contains both String and Numeric characters input by the users, is sorted in a weird order, because SBM treats the numeric characters just like string characters. For example, here is the sort order of the Title field:

ALUDEIDABR1
ALUDEIDABR12
ALUDEIDABR13
ALUDEIDABR14
ALUDEIDABR15
ALUDEIDABR2
ALUDEIDABR3
ALUDEIDABR4
ALUDEIDABR5
ALUDEIDABR6

What field type should I use or what should I do in this case? in order to have this field sorted as following:

ALUDEIDABR1
ALUDEIDABR2
ALUDEIDABR3
ALUDEIDABR4
ALUDEIDABR5
ALUDEIDABR6
ALUDEIDABR12
ALUDEIDABR13
ALUDEIDABR14
ALUDEIDABR15

 

  • 0

    when it's just numbers, I've copied the numeric values from a text field to a numeric field, then used an orchestration to get the count, and re-order, sorting in descending order and kind of reverse order items. but I feel like my scenario is different from yours. 

    the only way to sort in a text field would be to add a 0 before the 1...

  • 0 in reply to pearsallc

    Thank you for your suggestion, but adding the leading 0 doesn't really change the sorting mechanism that was already built by SBM java code

  • 0  

    Where is the sorting happening? Are you in an orchestration, javascript, modscript, something else?

  • 0 in reply to Vickie Ortega

    We were in the work center and tried to sort a column from the results of a report.

  • 0   in reply to Thu Le

    The 0's before the numbers should work as it is a text field and sorted via text rules. It would sort like this:

    • ALUDEIDABR01
    • ALUDEIDABR02
    • ALUDEIDABR03
    • ALUDEIDABR04
    • ALUDEIDABR05
    • ALUDEIDABR06
    • ALUDEIDABR12
    • ALUDEIDABR13
    • ALUDEIDABR14
    • ALUDEIDABR15

    If you are having users manually enter the title, then it will be harder to force them to enter the leading 0's. In one of our apps, we have added modscript to automatically generate titles on submit which adds a number like you have. It just does a search of existing items and adds one. In your case, you would also need to know how many numbers to prepare for. In other words, how many leading 0's do you need to add to future proof your titles.

  • 0 in reply to MichaelCFiala

    Thank you so much for your explaination and example. I get it now. 

Resources

Support
Documentation
Learning Services
Partner Programs
Privacy
Compliance
Help
Company
Privacy Policy
Terms of Use
Accessibility
Anti-Slavery Statement
Support
Contact Us
Careers
Code of Business Conduct and Ethics
The opinions expressed above are the personal opinions of the authors, not of OpenText. By using this site, you accept the Terms of Use. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.), Hewlett Packard Enterprise Company, or Micro Focus. As of January 31, 2023, the Material is now offered by OpenText, a separately owned and operated company. Any reference to the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks is historical in nature and the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks are the property of their respective owners.