
Hex to Decimal Conversion
joey May 15, 2014 10:03 AM (in response to JCC _)No, there isn't a function in V7 as far as I know. If you upgrade to V9 or higher, you can define custom functions and reuse them in other models. This would be a great example. I very highly recomend this. Below is code I created for doing this in V9 or 10. Below I'll explain how to convert this code to V7.
Function CONVERT_HEX_DIGIT  Converts a single hexidecimal digit to decimal:
if(isalpha(HexDigit),Asc(upper(HexDigit)) ASC("A") +10,val(HexDigit))
Funcion Hex_To_Dec  Converts a string up to positions long to a decimal:
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  0,1)) * (16^0) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  1,1)) * (16^1) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  2,1)) * (16^2) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  3,1)) * (16^3) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  4,1)) * (16^4) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  5,1)) * (16^5) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  6,1)) * (16^6) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  7,1)) * (16^7) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  8,1)) * (16^8) +
CONVERT_HEX_DIGIT(substr(HexString,len(HexString)  9,1)) * (16^9)
This code ws made much easier because I could reuse the CONVERT_HEX_DIGIT function within my Hex_To_Dec function. Since you don't have that feature in V7, you'll need to take my function Funcion Hex_To_Dec, and replace every instance of CONVERT_HEX_DIGIT with the code for the function. Then, HexDigit should be replaced with substr(HexString,len(HexString)  0,i), where i is the number of the position in the HexString.
As I said above I VERY HIGHLY recomend V9 or 10 rather than giving my approach for converting the two functions to a single function.

Hex to Decimal Conversion
Nick OsdalePopa May 15, 2014 10:03 AM (in response to joey)Here's an alternative allinclusive formula for Hex numbers up to 8 positions (Where HEX is your hexadecimal field):
((INSTR(SUBSTR(RIGHT("00000000" + HEX, 8),1,1),"0123456789ABCDEF")1)*16+
(INSTR(SUBSTR(RIGHT("00000000" + HEX, 8),2,1),"0123456789ABCDEF")1))*256^3+
((INSTR(SUBSTR(RIGHT("00000000" + HEX, 8),3,1),"0123456789ABCDEF")1)*16+
(INSTR(SUBSTR(RIGHT("00000000" + HEX, 8),4,1),"0123456789ABCDEF")1))*256^2+
((INSTR(SUBSTR(RIGHT("00000000" + HEX, 8),5,1),"0123456789ABCDEF")1)*16+
(INSTR(SUBSTR(RIGHT("00000000" + HEX, 8),6,1),"0123456789ABCDEF")1))*256+
(INSTR(SUBSTR(RIGHT("00000000" + HEX, 8),7,1),"0123456789ABCDEF")1)*16+
(INSTR(SUBSTR(RIGHT("00000000" + HEX, 8),8,1),"0123456789ABCDEF")1)
You could make two separate hidden calculated fields for the above formula to make it a bit shorter:
One formula would hold the 8position HEX field (named HEXNUM)
RIGHT("00000000" + HEX, 8)[/code]
The other formula would just be the lookup string (named HEXCONV):
Thus the formula would now look like this:
((INSTR(SUBSTR(HEXNUM,1,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,2,1),HEXCONV)1))*256^3+
((INSTR(SUBSTR(HEXNUM,3,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,4,1),HEXCONV)1))*256^2+
((INSTR(SUBSTR(HEXNUM,5,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,6,1),HEXCONV)1))*256+
(INSTR(SUBSTR(HEXNUM,7,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,8,1),HEXCONV)1)[/code]
For a 10digit HEX, make the necessary adjustment to HEXNUM
RIGHT("0000000000" + HEX, 10)[/code]
and the formula would be:
((INSTR(SUBSTR(HEXNUM,1,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,2,1),HEXCONV)1))*256^4+
((INSTR(SUBSTR(HEXNUM,3,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,4,1),HEXCONV)1))*256^3+
((INSTR(SUBSTR(HEXNUM,5,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,6,1),HEXCONV)1))*256^2+
((INSTR(SUBSTR(HEXNUM,7,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,8,1),HEXCONV)1))*256+
(INSTR(SUBSTR(HEXNUM,9,1),HEXCONV)1)*16+
(INSTR(SUBSTR(HEXNUM,10,1),HEXCONV)1)[/code]
EDIT:
On further inspection of Joey's formula, mine really isn't that much different. :o
