The default behavior of Additional Fields is to sort themselves alphabetically (alphabetic collation). This can cause problems where you are trying to sort the Account list by an Additional Field, or perform queries based on whether a value is less than or greater than a given number or date.

Sorting Numerical Fields

Alphabetical number sorting results in numbers being sorted in the following order:

1,

11,

124,

4,

77 etc.

Therefore writing a query to return numerical values of less than 4 on the Data above will return: 1, 11 and 124.

This can be useful in certain circumstances, however in most cases, we would want the numbers to be sorted numerically.

Numerical sorting would result in the above list being returned in the following order:

1,

4,

11,

77,

124 etc.

If your Additional Field contains only numerical Data you can amend the data type of the Additional Field to make it sort the correct way.

WARNING: Non-numerical Data will be lost if the Data type is changed, non-numerical Data includes special characters such as '-' ',' and '!'. Only the numbers 0-9 and the decimal point character can be stored in a numeric Field.

To do this go to Setup > Field Headings > Account Additional Fields and select ‘Change Type’.

The Field Type dialog will be brought up. Amend the 'New Type' to be ‘integer (zero decimal places)’ if your numbers are whole numbers or select ‘decimal number’ if your numbers contain decimal points (typically currency fields).

If your Data is not completely numeric then you will need to prefix the numbers with the relevant number of zeros in order to make the alphabetic sort correct.

The above list prefixed would be:

001,

004,

011,

077,

124 etc.

Numbers in this format will sort in the same order regardless of whether they are a Data type of string, decimal number or integer (zero decimal places).

Sorting Date Fields

It is possible to enter dates into Additional Fields in their default Data type (string). However, this will prevent the greater than and less than operands from working properly on a query.

To fix this problem you will need to change the Data type of the Additional Field to date/time.

To do this go to Setup > Field Headings > Account Additional Fields and select ‘Change Type’.

The Field Type dialog will be brought up. Amend the 'New Type' to be 'date/time'.

Click here to view a video guide covering this topic