Every now and then you come across a limitation in ACL. This happened to me the other day when I needed to relate a couple of tables using a descriptive text field. Who would have thought that an INDEX field of more than 247 characters would be an issue? Well they are and, unfortunately, my text [key] fields were over 1,000 characters in length. No good.
My first attempt to deal with the problem was to create a computed field based on a SUBSTRING of the text field, setting the length of the computed field to 247 characters. This dealt with the initial problem, but unfortunately created a new problem – my key field was truncated and no longer unique so it didn’t work effectively to relate my two tables. Back to the drawing board.
Fast forward a few days and I’d just been brushing up on one or two of the newer features of ACL v11. One in particular caught my eye – the HASH function. It sounded pretty cool for those who needed to protect private or confidential information. There’s definitely going to be someone out there who needs it, but not me just yet.
The HASH function takes any type of input field and converts it into a 64 character coded hash value:
Then it hit me – MORE THAN 247 CHARACTERS + HASH = SOLUTION to my ‘long key’ problem.
To be clear, if I HASH my long text field, the result will be a character field with a length of 64. No matter how long (or short) my field is, HASH will give me 64 characters out the other end. Perfect!
So I created a new computed field in each of my tables:
and was able to relate them straightaway without any glitches. I didn’t even have to worry about ‘key harmonisation’ because both keys were created in the same format by the HASH function. Brilliant!
That wasn’t quite the end of the story, because there were one or two ‘trivialities’ that I had to consider:
It really is that simple. Next time you have an index field of more than 247 characters, don’t look for a work around that may or may not do the trick, simply HASH it!