Sometimes in GoldMine it’s necessary to use a text field to hold numeric data. Examples are the in Key Fields and in Details.

The only difficulty comes when you want to sort the data. Because the numbers are being interpreted as characters (you can think of them as letters), ’20’ will come after ‘1000’ – just as ‘ball’ will come after ‘antidisestablishmentarianism’.

In a SQL query there’s a very simple fix though: just change the way that SQL interprets the characters. For example, to sort numeric data in the contsupref field of a Detail, try the following:


select * from contsupp where contact='Your Detail Name' order by cast(contsupref as int)


The ‘cast’ function turns the text values into integers.

Note that if the query hits a value that can’t be converted to a number then you will get an error.