Constituent ID, Gift ID, and Membership ID fields are not numeric fields, but "alpha-numeric." That means they can contain numbers, letters, and special characters. This also means that their sorting is a different from a true numeric sort where each character is sorted in the field and spaces are considered. (See an example below.)

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.)
For example if querying on constituent ID's greater than or equal to 1747138, create the constituent query criteria as follows:

Constituent ID greater than or equal to 1747138
AND Constituent ID like ???????
   
  • 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.
A0000001
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.

For the ToNumber function to work properly, the field you want to convert must be populated with numbers only. When using this function, you should first test the value with the NumericText function. If NumericText returns the value TRUE, then you can convert it to a number. If you try to convert a value to a number that is not numeric, the formula produces an error in the report.

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:

NumericAlpha-Numeric
1
2
3
10
26
34
134
267
1289
 
1
10
1289
134
2
26
267
3
34
 

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.