text Field to numeric

I have a text field for totals that was created many years ago that needs to be able to be search on with operators like <>= but of course since it's a text field that does not work.  Unless we are doing that wrong.  To find totals greater than 10,000.00 are putting in the total field >10,000.00. but it will not calculate this and I am assuming it's because its a text field.

Is there a way to convert this field into a numeric field?  I  can only think to build a new field called Total1 then some how copy all from Total to Total1 and rename Total1 after completed and total deleted.   

Any help would be greatly appriceated.
 

Michael,
You will need to create a new field in the DocuWare file cabinet with a type of either numeric or decimal. Then, you will need to transfer the contents of the text field to the new numeric or decimal field using a query in the database, since you cannot transfer text data to numeric with Autoindex.
Generally MSSQL will transfer data from a varchar field to an INT (numeric field) as an implicit conversion - meaning that you do not need to specify a conversion. You will however need to pay careful attention to decimal places if you use an INT (numeric field).
If your text value is '10.00' then you need to strip the decimal and write 1000 to the numeric field (if you have it set to 2 decimal places in DocuWare).
If you want to write it to a decimal field then I think that will work as a straight copy since that conversion is also an implicit conversion. My preference would be to create the new field as decimal as this will be a simpler conversion.
When transferring data, you must also be careful not to try and write anything else but numerical data otherwise your query will fail on the first record where the conversion cannot be completed.
Whatever you do you need to check the converted data carefully to ensure that you have written the values correctly depending on the target field type.

If all that seems a little difficult, Professional Services will be pleased to provide a quote to do the conversion for you.


Phil Robson
Senior Director Client Services, Americas