Changing single digit number to text

[CoArtist]

Hello,

I;m hoping someone out there can guide me on this...I have single digit numbers imported from an xls in a field, which I need to replace with a term in another field. The script I am using to substitute the number with the word doesn't work using he number, and I apparently need to convert the number to text.

The conversion code I have found doesn't work when I try it (replacing the field name to my own). I get error messages. Does anyone have a simple way to change a number to text. Oh, the fields are all text fields. I did try using a calculation field for the huge code I found on this site, but to no avail.

Thanks,

[bcooney]

GetAsText (numberField)

[LaRetta]

I'm reading this a bit differently so, just in case Barbara didn't nail it, here's more information:

The conversion code I have found doesn't work when I try it (replacing the field name to my own).

I would seriously question your structure but sometimes we are stuck with things. If you have a number and a corresponding text (that should replace it), it is best to use a related table (example, named LOOKUP) with:

numberField

textField

Then relate the two tables using [color:blue]= between the two number fields (be sure both of these number fields are of the same data type). Then whenever you want the text instead of the number, just select Lookup::textField instead.

It would really be best to supply a file for us - just in case you DO have a problem structure and just in case you CAN adjust it (if it's at an early stage in design).

LaRetta

Edited January 14, 2009 by Guest
Added a bit

[Kris M]

Would not a scripted use of the case statement and the substitute function work?

[bcooney]

we really need more info.

[LaRetta]

I wanted to respond to something:

Would not a scripted use of the case statement and the substitute function work?

Sure. And so would using [color:blue]Choose ( numField - 1 ; "value1" ; "value2" ; "value3" ) but any calculation such as this will mean hard-coding the text (and text-to-number relationship) directly within the calculation. Using a calculation will mean: 1) a needless increase in file size and 2) a Developer will need to modify the calculation every time there is even a simple change or addition. Using a calculation within a SCRIPT will mean: 1) the data is NOT repaired between script runs and that is sloppy and 2) a Developer will also need to modify the calculation within the script.

Even if there are only four or five text-to-number relationships, I prefer another table so I can make it easily accessible for modification and growth. By using a related table for the 'words', and simply placing this related 'word' field directly on the main layout in place of the number, it will/can dynamically change/update if the 'words' are changed in the word table. The only thing that can be depended upon in the business world is that everything will change. The more we design accordingly, the better.

NOTE: CoARtist is using vs. 6 so cannot even take advantage of newer features.

LaRetta :wink2:

Edited January 18, 2009 by Guest
Added note and sentence