FileMaker can do many things, but alas does not include a built in hexadecimal to decimal converter. But I managed to build one…even in the standard version without the custom function writer.
If you’re interested, here’s how I did it. In this case, I had to work with 5 digit Unicode Code Points. If you’re working with 2 digit color codes (2 digits per color), it’s much easier.
Note: This requires the use of tables and relations, so this solution only works in FileMaker 7 and above.
- In the main table, I created a field for the Hexadecimal value. I’ll call this HexValue for now. It must be a Text field. You can create a Decimal field (Calculated), but you won’t be able to fill in the formula yet.
- Then I created a second table to store the correspondence between a hex digit (0-F) and its decimal value (0-15). The HexValuefield is Text, but the DecValue field is a Number. See the sample table below (some values skipped).
HexValue (Text) DecValue (Number) 0 0 1 1 2 2 3 3 A 10 B 11 F 15 - To do all the conversions, you need some intermediate values. I created fields to extract the value for each place in the hex number (four digit FFFF) as follows. In case you’re wondering, the name of the places are semi-inspired by Roman numerals and algebra.
- Rightmost digit Units (n) : nhex = Right(HexValue)
- Third digit Tens (t) : thex = Middle(HexValue;3;1;)
- Second digit Hundreds (c) : chex = Middle(HexValue;2;1)
- First digit Thousands (m): mhex = Left(HexValue)
- I need to set up some Relationships so that each extracted digit can look up the decimal equivalent. For each of the intermediate digit fields above, I created a link to an instance of the Hexadecimal Lookup table (there are 4 instances total). It’s important to make sure each instance has a name you can remember later; mine mention which digit I am working on. See the Relationships diagram below.
- Now we can finally get that decimal value! If you haven’t already, create a DecimalValue field and make it Calculated.
- Here’s my calculation. I’ll explain what the parts mean below
HexLookup N::DecValue + 16*HexLookup T::DecValue + 256* HexLookup C::DecValue + 4096*HexLookup M::DecValue- “HexLookupN::DecValue” means give me the equivalent decimal value column based on the hex value in the “HexLookupN” (units digit) table instance.
- “HexLookup T::DecValue” does a look up for the tens unit. I multiply the value by 16 an add it to the ones value. Remember the hex #FF (F=15) means 15*16+15
- I look up the hundreds place decimal value and multiply it by 256 (16^2), then the thousands place decimal and multiply it by 4096 (16^3).
- I add up the results of each converted decimal digits times its appropriate power of 16.The calculation is complete.
Now at this point, you may be wondering if it’s worth the effort, and my answer is “Heck yes”. Before I got this toy, I had to open my Hex calculator for each value (and Unicode has millions of possible values).
If the Hex Math is Confusing…
I strongly recommend finding the lyrics of Tom Lehrer’s song “New Math”. It cleared up some mysteries for me.
Now, that actually is not the answer that I had in mind, because the
book that I got this problem out of wants you to do it in base
eight. But don’t panic! Base eight is just like base ten really –
if you’re missing two fingers! Shall we have a go at it?
Hang on…
…
Now instead of four in the eights place
You’ve got three,
‘Cause you added one,
That is to say, eight, to the two,
But you can’t take seven from three,
So you look at the sixty-fours…
Sixty-four? “How did sixty-four get into it?” I hear you cry!
Well, sixty-four is eight squared, don’t you see? (Well, ya ask a
silly question, ya get a silly answer!)