If querying on an ID field and trying to do a numeric sort, try one of the following to help. (

**Note:**The following guidelines work with any alpha-numeric field. The Constituent ID field is used for example purposes.)

- Create a constituent query and filter on the different ranges of constituent ID that have the same number of digits. On the Sort tab, sort by the ID. For example, query on Constituent Information, ID like ?. Then, create another query for Constituent Information, ID like ??, another for ???, etc. (The question mark is a search wild card.)

Constituent ID greater than or equal to 1747138

AND Constituent ID like ???????

- Include the desired ID in the Query or Export output. Export in the CSV format and open the file in Excel. Sort by the ID column in Excel.

- When entering IDs, enter placeholders before the value so all have the same number digits. For example, insert a letter and then leading zeroes. (Note: Just leading zeroes can be used. However if data is exported / imported a lot from Excel, that program can drop leading zeroes. The leading letter is a means to minimize this.

A0000002

A0000003 ... and so on

- If working in Crystal Reports, use the ToNumber function in Crystal:

The ToNumber function in *Crystal Reports* can convert a non-numeric field, specifically a text field, into a numeric field when you need to perform arithmetic on the field or sort the field in numeric order. For example, you export Constituent ID for sorting or grouping in a report, but discover that because it is an alphanumeric field, it sorts in alphanumeric order instead of numeric order. To sort or group by this field numerically, convert the field using the ToNumber function.

The following example uses both functions: If the text string, Constituent ID, can be converted to a number, then convert it to a number else, use zero as the value.

`If NumericText ({CONSTITUENT.EN_US}) Then ToNumber ({CONSTITUENT.EN_US}) Else 0`

With this formula, the Constituent ID field is converted from a text string to a number and may now be used for sorting or grouping in numeric order.

**Additional Information**

For a more technical explanation: The Raiser's Edge is a SQL database where the ID fields are "varchar" or "various characters" -- or alpha-numeric or "strings." That means the field is not just numbers, but can also have letters and symbols. When sorting, each character of the value is sorted itself as 0-9 then A-Z. Once it finds something to sort on, it moves to the next value.

For example in just an ID number list, consider the IDs of 1, 2, 3, 10, 26, 34, 134, 267, 1289. These are in numeric order from smallest to largest. However since ID fields are alpha-numeric, the IDs will sort as follows in Query: 1, 10, 1189, 134, 2, 26, 267, 3, 34 (each number is sorted and spaces are considered).

Comparing the two sorts:

Numeric | Alpha-Numeric |
---|---|

1 2 3 10 26 34 134 267 1289 | 1 10 1 2891 342 26 267334 |

Here are couple examples of the Alpha-Numeric sort above:

In the Alpha-Numeric sort, consider the example ID of 1189. That will sort as 1 then 2 then 8 then 9, not the mathematical value of 1289. In our list above, 1289 comes before 134. Each of the 1's are the same, so then the sort moves to the second number. The 2 of 1289 (the 1 has already been sorted) comes before the 3 of 134 (the 1 has already been sorted). Since those can sort, 1289 is put before 134 and the sort goes on to the next values.

In the Alpha-Numeric sort, consider the example ID of 267. That will sort as 2 then 6 then 7, not the mathematical value of 267. The 2 of the 2-6-7 comes before 3. There are no more values after the 3, so the sort is done and puts the 267 before 3.