When sorting financial report results by document number, instead of giving the results from smallest to highest number, it is giving them in "alphabetical order". Is there a way to sort results on a financial report from smallest to highest number using document number

AI generated answer (using internal Slack threads):

In NetSuite, when sorting by document number, it treats the numbers as strings and sorts them in alphabetical order. This is because the document number field is a text field. However, you can use a workaround to sort the document numbers in numerical order. You can use the

TO_NUMBER

function in combination with

REGEXP_SUBSTR

to extract the numeric part of the document number and convert it to a number. This will allow you to sort the document numbers in numerical order. Here is the formula you can use:

TO_NUMBER(regexp_substr({number}, '\\.*[[:digit:]]+\\.*[[:digit:]]*'))

You can use this formula in a saved search by setting it as a Formula (Numeric) and using Maximum as the summary type. This will return the document number of the most recent credit memo. Please note that this solution assumes that your document numbers are in a specific format, where the numeric part comes after a non-numeric part. If your document numbers are in a different format, you may need to adjust the regular expression in the formula accordingly. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******