Author: Yuri Gee
Date: 2 Jun 2025
10 minute read time
In this discussion, I will introduce a method leveraging an extended Sugar Logic functions to extract multiple related records using advanced SQL-like criteria. This includes filtering with logical expressions, sorting, concatenating, and aggregating numerical and textual data. The approach will be presented to display key insights—such as Top Follow-up Tasks, Top Sellers, and the Value of Top Open Opportunities—directly within the account record view.
Scenario
SugarCRM offers multiple ways to display contextually relevant information, including Focus Drawers, Record View Dashlets, Subpanels, and Reports. Additionally, stock Sugar Formulas can be used to display data from related records.
This discussion introduces a new perspective by showcasing a working prototype of a Sugar Formula function called getConditional(). This function enables simplified SQL-like queries on related records, allowing for the extraction of key data directly into the Sugar record view.
Unlike the standard related() function, which selects a random related record, or stock conditional and rollup formulas that process data based on a single conditional field and operation (such as summing values), getConditional() enhances this functionality. It allows conditional expressions within the function itself, as well as operations such as sorting by a field, limiting the number of output records, extracting distinct values, concatenating, and summing selected values.
The demonstration consists of three key scenarios:
- A formula to display distinct sellers of the top five open or renewing opportunities with the highest value.
- A formula to display the total value of these five top opportunities in the system's default currency (USD by default).
- A formula to extract descriptions of the three most recently created active tasks with non-empty descriptions, displaying each on a new line within a text area field.
This approach offers an additional way to extract and present relevant insights directly in the record view for improved usability and decision-making.
Here is the output for the sample Account record after implementing the formula.
The formula output includes the following Accounts, Opportunities, and Tasks.
Function getConditional() code
Here is the getConditional() function code, tested in version 25.1 with basic data types—string, float, integer, and boolean. While functional, it may require further testing and enhancements to support all possible scenarios. The current implementation, written in PHP, consists of slightly over 200 lines of code, contained in the file :
custom/include/Expressions/Expression/Enum/GetConditionalRelatedExpression.php
<?php
require_once 'include/Expressions/Expression/Enum/EnumExpression.php';
//this file location: custom/include/Expressions/Expression/Enum/GetConditionalRelatedExpression.php
class GetConditionalRelatedExpression extends EnumExpression
{
public function evaluate()
{
try {
$params = $this->getParameters();
$linkField = $params[0]->evaluate();
$fields_expression = $params[1]->evaluate();
$initialValues = $params[2]->evaluate();
$targetField = $params[3]->evaluate();
$opsList = $params[4]->evaluate();
//Relate type (an array of SugarBean objects) is expected
if (!is_array($linkField)) {
return [];
}
if (!is_array($initialValues))
{
preg_match('/[^ ()!&=]+/', $fields_expression, $matches);
if (empty($matches)) return [];
$initialValues = [[$matches[0], $initialValues]];
}
$values = [];
foreach ($initialValues as $entry)
{
if (!is_array($entry)) continue;
$field = array_shift($entry);
if (empty($field)) continue;
if (!isset($values[$field])) {
$values[$field] = [];
}
$values[$field][] = $entry;
}
$tokens = preg_split('/(&&|\|\||!|\(|\))/', $fields_expression, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
$tokens = array_map('trim', $tokens);
$tokens = array_values(array_filter($tokens, fn($value) => trim($value) !== '' && $value !== null));
$sortParam = $targetField;
foreach ($opsList as $operation) {
if (is_array($operation)) {
[$opName, $param] = $operation + [null, null];
} else {
$opName = $param = null;
}
if (in_array($opName, ['sortup', 'sortdown'])) {
$sortParam = $param ?: $targetField;
break;
}
}
$array = [];
foreach ($linkField as $link) {
$index = 0;
$fieldPositions = [];
if ($this->evaluateLogicalExpression($tokens, $index, $link, $values, $fieldPositions)) {
$array[] = [$this->getSafeValue($link, $targetField), $sortParam ? $this->getSafeValue($link,$sortParam) : $this->getSafeValue($link,$targetField)];
}
}
$hasSortOperation = false;
foreach ($opsList as $operation) {
if (!is_array($operation) || count($operation) > 3 || count(array_filter($operation, 'is_array')) > 0) continue;
if (is_array($operation)) {
[$opName, $param, $param2] = $operation + [null, null, null];
} else {
$opName = $param = $param2 = null;
}
switch ($opName) {
case 'sortup':
case 'sortdown':
if ($hasSortOperation) break;
usort($array, function ($a, $b) use ($opName) {
return is_numeric($a[1]) && is_numeric($b[1])
? ($opName === 'sortup' ? $a[1] - $b[1] : $b[1] - $a[1])
: ($opName === 'sortup' ? strcmp($a[1], $b[1]) : strcmp($b[1], $a[1]));
});
$hasSortOperation = true;
break;
case 'distinct':
$columnData = array_map(fn($v) => is_scalar($v) ? (string) $v : "", array_column($array, 0));
$counts = array_count_values(array_filter($columnData));
$seen = [];
$array = array_filter($array, function ($v) use ($counts, &$seen) {
$key = is_scalar($v[0]) ? (string) $v[0] : "";
if ($key !== "" && $counts[$key] > 1 && !isset($seen[$key])) {
return $seen[$key] = true;
}
return $key !== "" && $counts[$key] === 1;
});
$array = array_values($array);
break;
case 'limit':
$start = max((int) ($param2 ?? 0), 0);
$length = min(max((int) ($param ?? 1), 0), count($array));
$array = array_slice($array, $start, $length);
break;
case 'sum':
if (is_numeric($array[0][0])) {
return array_sum(array_column($array, 0));
}
return implode((is_string($param) && !empty($param) && strlen($param) <= 5) ? $param : PHP_EOL, array_column($array, 0));
}
}
return array_column($array, 0);
}
catch (Exception $e)
{
return [];
}
}
protected function evaluateLogicalExpression($tokens, &$index, $link, $values, &$fieldPositions = [])
{
$result = null;
$operator = null;
while ($index < count($tokens)) {
$token = $tokens[$index++];
if ($token === "(") {
$subResult = $this->evaluateLogicalExpression($tokens, $index, $link, $values, $fieldPositions);
$result = $this->applyOperator($result, $subResult, $operator);
} elseif ($token === ")") {
return $result;
} elseif ($token === "!") {
$field = $tokens[$index++];
$position = $fieldPositions[$field] ?? 0;
$subResult = !in_array($this->getSafeValue($link,$field), $values[$field][$position] ?? []);
$fieldPositions[$field] = $position + 1;
$result = $this->applyOperator($result, $subResult, $operator);
} elseif ($token === "&&" || $token === "||") {
$operator = $token;
} else {
$position = $fieldPositions[$token] ?? 0;
$subResult = in_array($this->getSafeValue($link,$token), $values[$token][$position] ?? []);
$fieldPositions[$token] = $position + 1;
$result = $this->applyOperator($result, $subResult, $operator);
}
}
return $result;
}
protected function getSafeValue($link, $field_name) {
$value = $link->$field_name;
if (is_numeric($value) || is_string($value)) {
return $value;
} elseif (is_scalar($value)) {
return (string) $value;
} else {
return "";
}
}
protected function applyOperator($left, $right, $operator)
{
if ($left === null) return $right;
if ($operator === "&&") return $left && $right;
if ($operator === "||") return $left || $right;
return $right;
}
// Return something in JS function
public static function getJSEvaluate()
{
return <<<JS
JS;
}
public static function getOperationName()
{
return ['getConditional'];
}
public static function getParameterTypes()
{
return [
AbstractExpression::$RELATE_TYPE,
AbstractExpression::$STRING_TYPE,
AbstractExpression::$GENERIC_TYPE,
AbstractExpression::$STRING_TYPE,
AbstractExpression::$GENERIC_TYPE,
];
}
public static function getParamCount()
{
return 5;
}
}
Function Syntax for the Above Scenarios
The function takes five arguments:
- The relationship name between your module and the related records module (stock one to many relationships were tested).
- A string defining the field_names to be checked for equality conditions, using brackets, &&, ||, and ! to represent logical AND, OR, and NOT operators. Fields can repeat as needed.
- nested list of (field_name, values), where each field_name appears in sequential order within the expression and is matched with an array of values to be evaluated for equality using the indirect OR operator.
- The field_name to retrieve as the function’s output.
- A list of cumulative operations applied to related records that match conditions 2 and 3, such as sortdown, sortup, distinct, limit, and sum.
Each operation can take up to two parameters, and their order matters. For instance:
- Sorting operations (sortdown, sortup) require a field name for sorting; if not specified, the function will use the field defined in argument 4 by default.
- Limit accepts integer parameters—one for the number of records to return, another for the starting row.
- Distinct has no parameters.
- Sum returns either an arithmetic sum for numerical fields or a concatenated result, using a specified delimiter or a new line by default.
Examples:
TextArea - Top 3 Priority Task descriptions
getConditional($tasks,"(status && !description)",createList(createList("status","In Progress","Not Started"),createList("description","")),"description",createList(createList("sortdown", "date_entered"),createList("limit","3"),createList("sum")))
TextField - Top 5 Opportunity Sellers
getConditional($opportunities,"((!sales_stage && opportunity_type) || (renewal)) && !assigned_user_name",createList(createList("sales_stage","Closed Won","Closed Lost"),createList("opportunity_type","Existing Business"),createList("renewal","1"),createList("assigned_user_name","")),"assigned_user_name",createList(createList("sortdown","amount_usdollar"),createList("limit","5"),createList("distinct"),createList("sum", ", ")))
Currency (System, USD) - Top 5 Opportunity Value
getConditional($opportunities,"((!sales_stage && opportunity_type) || (renewal)) && !assigned_user_name",createList(createList("sales_stage","Closed Won","Closed Lost"),createList("opportunity_type","Existing Business"),createList("renewal","1"),createList("assigned_user_name","")),"amount_usdollar",createList(createList("sortdown","amount_usdollar"),createList("limit","5"),createList("sum")))
Function Constraints and Considerations
This function is a prototype tested in version 25.1 with standard integer, float, boolean, and string fields, as well as datetime values converted to strings. However, additional testing may be necessary to ensure it meets specific requirements, including performance testing, various data types, and record data handling. Before considering production use, each function application should be tested in a test instance.
Certain field types, such as currency fields or objects, and datetime comparisons, may require modifications or extensions to the code. Additionally, handling potential PHP warnings and validating extra parameters may be necessary.
Currently, the function does not implement automatic recalculation when related records change. To apply changes, the main record should be re-saved.
The function is implemented in PHP, which is sufficient for backend data handling. However, JavaScript integration is not included in this version.
Additional Notes
Considering the limitations and testing requirements mentioned earlier, the process involves using Admin → Repair → Rebuild Sugar Logic Functions followed by Admin -> Repair -> Repair and Rebuild to ensure function is applied. Then, adding a formula to the field requires using the correct syntax, field name expressions, and parameters.
I’d be interested to hear your thoughts and feedback on this discussion, including the related scenarios and proposed implementations!