While Searching for Customers by their External ID via the Advanced Search using the Between option, results are returned that appear to be outside the range entered for the search.
Reproducing the Issue
- Go to the Customers work centre.
- Go to the Account view.
- Select All and click the icon for Advanced Search.
- For the field External ID, select the option Between.
- Provide a Value from 100000000 (100000000 is the value) which is smaller than the value entered for Value to 100001000 but has the same amount of characters.
- Click Ok and then Go.
- Go to the last page of your results.
Results are returned with are higher than the Value to amount.
This is expected system behaviour.
The External ID is a character field (alphanumeric External ID's allowed/max length 100), not an integer field.
For character fields a range/ between search works like this:
External ID between 100000000 and 100001000
1000002222 is stored one the data base like 10000022220....00000 (in sum 100 char)
therefore a result larger than the Value to can be found, example 1000002222 is found.
It is also possible in some systems, that some External ID's have one amount of characters 123 (123 representing the amount of characters) and others have 456 characters which can make the result of the search confusing.
KBA , LOD-CRM-ACC , Account , How To