Extending Sugar Logic Formulas with Custom String Search Function

Author: Yuri Gee

Date: 31 Dec 2024

5 minute read time

In this article, I'll show you how to extend Sugar Logic with a powerful string search function. This function can search for string fragments in SugarCRM fields across various scenarios. I'll also provide an example of how to easily extract the first value from a multi-select field using this function.

Sugar Logic Extension Function Code

Sugar Logic built-in SugarCRM feature lets administrators implement calculations for a field or control its visibility. Here's an example of a Sugar Logic extension, based on the provided developer guide article.

I developed the following example file that implements a new strpos function, designed to search for the position of a substring within a string:

custom/include/Expressions/Expression/Numeric/CustomStrposExpression.php

<?php

require_once 'include/Expressions/Expression/Numeric/NumericExpression.php';

class CustomStrposExpression extends NumericExpression

{

    /*** Returns position or -1 if a substring is not found */

    public function evaluate()

    {

        $params = $this->getParameters();

        if (count($params) != 2) {

                throw new Exception("strpos: should have 2 parameters");

        }

       $result = sugarStrpos($params[0]->evaluate(), $params[1]->evaluate());

       return $result === false ? -1 : $result;

    }

    /*** Returns the JS of the evaluate function. Not implemented in this example*/

    public static function getJSEvaluate()

    {

        return <<<EOQ

                        return -1;

EOQ;

    }

    /*** Returns the operation name that this Expression should be called by*/

    public static function getOperationName()

    {

        return 'strpos';

    }

    /***Returns the exact number of parameters needed*/

    public static function getParamCount()

    {

        return 2;

    }

    /*** All parameters are to be strings*/

    public static function getParameterTypes()

    {

        return AbstractExpression::$STRING_TYPE;

    }

}

It's recommended to thoroughly test this code example in a test environment before considering it for production use. To apply the changes, run Quick Repair and Rebuild, followed by Rebuild Sugar Logic Functions. Keep in mind that it may take some time for the new function to appear in Studio.

Formula Utilizing the Custom Function

The example formula below utilizes the newly created strpos function to extract the first option (item name) from a multi-select field $multiselect_test_c, and is set up for a dropdown field:

strReplace("^","",subStr(concat(toString($multiselect_test_c),","),0, strpos(toString($multiselect_test_c),",")),false)

This formula is simpler, more efficient, and more general than the one used in the previous article, which only employed stock functions and could extract values of up to 30. It can also be used to more easily extract arbitrary text fragments.

The result of applying this formula to the test multi-select field, Case Status, is shown in the following picture.

Obtaining value from multi select field

Breakdown of Functions

`toString($multiselect_test_c)`: This function converts the multi-select field's object into a string.
`concat(…,",")`: Adds a comma separator to ensure that strpos (when searching for it) will always return a positive value.
`substr(...,0,strpos(…))`: This part extracts a substring from the string in step 2, ending just before the position index returned by strpos.
`strReplace("^","",,substr(…),false)`: This part replaces the ^ symbols surrounding the multi-select values with blank characters.
` strpos (…,",")`: This is the key part that searches for the position index of the first comma separator, which separates individual values in multi-select fields.

Purpose and Considerations of This Formula

The formula is designed to extract the first option from a multi-select field by using a custom strpos function. This function searches the string representation of the field to identify the position of the comma separator that marks the end of the first value.

strpos is an example of a general function that can be applied to arbitrary strings, such as email and note texts, HTML tags, etc. The back-end code uses a function from the SugarCRM utils library to calculate string positions, assuming the underlying function exists within its scope.
You may need to update the JavaScript implementation for real-time view of calculations, as it currently returns a value of -1 at all times. This does not appear to significantly impact the user experience when saving a record.
Ensure that you check if the function returns -1 (when the substring is not found) in any formulas that depend on this function.
The code sample is provided as-is and needs to be tested in a test environment first to ensure it meets your requirements.

Conclusion

We’ve explored extending the Sugar Logic function with a versatile strpos function that can parse strings more effectively. This approach simplifies the extraction of multi-select field values, making the process smoother and more efficient.

By leveraging the strpos function, you can handle arbitrary strings, such as emails, notes, and HTML tags more easily. Remember, always test any new function in a safe environment to ensure it meets your needs. Happy coding!

Yuri Gee

19 days ago

The function can be enhanced to use regular expressions (regex), which are symbolic patterns for matching text structures. This allows users to detect and extract complex sequences—such as repeated letters, specific keywords, or structured lines from SugarCRM record fields.

For instance, the following formula will extract all email addresses found in KB article and store them in a text field. If an index is specified in the third parameter, the function will return the corresponding match, otherwise empty string.

regexMatches($description,"/[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}/i",-1,", ")

Output Example in the Emails field

Regex function entry into KB Module

Code sample (PHP only; JavaScript not implemented). Afterward, please run Quick Repair and Rebuild, then execute the Rebuild Sugar Logic Functions job under Admin > Repair, and finally clear your browser cache. Ensure thorough testing is completed prior to considering a production environment.

<?php
require_once 'include/Expressions/Expression/String/StringExpression.php';

//custom/include/Expressions/Expression/String/CustomRegExpression.php
class CustomRegExpression extends StringExpression
{
    /*** Returns position or -1 if a substring is not found */
    public function evaluate()
    {
        $p = $this->getParameters();
        if (count($p) != 4) throw new Exception("regexMatches: should have 4 parameters");

        $text  = (string)$p[0]->evaluate();
        $regex = (string)$p[1]->evaluate();
        $index = isset($p[2]) ? (int)$p[2]->evaluate() : 0;
        $sep   = isset($p[3]) ? (string)$p[3]->evaluate() : ",";

        // Limit regex and text length for performance
        if (strlen($regex) > 256) return false;
        if (strlen($text) > 10000) return false;

        // Validate full regex: must start/end with / and contain only safe characters, optional modifiers allowed
        if (!preg_match('/^\/[a-zA-Z0-9\s\.\,\-\_\@\|\(\)\[\]\{\}\^\$\+\*\?\/\\\\:;%!=<>~]+\/[imsxuADSUXJ]*$/', $regex)) return false;

        // Disallow nested quantifiers like (.*)+
        if (preg_match('/\((?:[^()]*[\*\+][^()]*)\)[\*\+]/', $regex)) return false;

        // Test regex syntax
        if (@preg_match($regex, '') === false) return false;

        // Collect matches
        $matches = [];
        preg_match_all($regex, $text, $matches, PREG_SET_ORDER);
        if (empty($matches)) return '';
        $matches = array_slice($matches, 0, 100);

        // Return all matches if index < 0, otherwise specific match
        if ($index < 0) {
            $all = array_map(fn($m) => $m[0], $matches);
            return implode($sep, $all);
        }
        return isset($matches[$index][0]) ? $matches[$index][0] : '';
    }

    /*** Returns the JS of the evaluate function. Not implemented in this example*/
    public static function getJSEvaluate()
    {
        return <<<EOQ
        return '';

EOQ;
    }

    /*** Returns the operation name that this Expression should be called by*/
    public static function getOperationName()
    {
        return 'regexMatches';
    }

    /***Returns the exact number of parameters needed*/
    public static function getParamCount()
    {
        return 4;
    }

    /*** All parameters are to be strings*/
    public static function getParameterTypes()
    {
        return [
            AbstractExpression::$STRING_TYPE,
            AbstractExpression::$STRING_TYPE,
            AbstractExpression::$NUMERIC_TYPE,
            AbstractExpression::$STRING_TYPE,
        ];
    }
}