Formula field bends itself beyond limits while keeping small footprint

Having this in mind, I started to think about separating data from the formula logic. After some exercises, I came up with a solution where data is stored in custom settings and a formula that reads and evaluates that data. Instead of using CASE() I am using FIND(), MID() and VALUE() functions in the formula. Data is stored in a hierarchy custom setting with as many text fields as needed. In my example, I am using two.

All numeric value/language pairs are stored in text fields (Language_Boost__c and Language_Boost_2__c) in the custom setting. The first field also contains the default value. After filling up the first text field in the custom setting record, I just continued to put data in another text field and adjusted the formula (string concatenation) to bring in that data as well. So whenever you hit the field limit of 255 characters, just add a new field. A numeric field was also added (Language_Boost_Digits__c) that contains the number of digits present in the text fields, in this case, 3. If you need bigger numbers, just set a bigger value in this field and change the number of digits for each value in the text fields.

The two custom setting fields look like this (I have removed data for readability). The first value, “000,” is the default value, just like the last value in CASE(). Each value/language is made up of “nnn=xxxxx,” where nnn is the numeric value and xxxxx is the language. Default value and all languages must be appended with a “,” but you may use your own defined separator as well:

000,101=Brazilian Portuguese,102=Chinese (Hong Kong),105=English,
118=Polish,119=Portuguese,120=Spanish,121=Swedish,

The formula looks like this:

For one of our customers, I created a custom object to manage the language to score mapping. Then I wrote a trigger on that object that collects all records and creates the custom setting record field values automatically according to the format described above. That brings a standard Salesforce GUI interface for managing the custom setting, which is just great, and the customer knows for sure how to handle it.

This example is just an eye-opener for what formulas can deliver. By using the concatenation of custom setting text fields, the data limit used by formulas just increases a lot since data is not stored in the formula itself, only in the reference. And there are many other use cases where you can store data in a smart and innovative format and create simple algorithms for using it.

Please let me know if you come up with something useful. Good luck!