Transform your SugarLogic formulas with Upsert® Advanced Formulas. This powerful plugin extends SugarAI's (formerly SugarCRM) native formula capabilities with advanced formulas designed to handle complex calculations, conditional aggregations, and data relationships that go beyond standard SugarLogic. Advanced Formulas includes both performance-optimized extensions of core SugarAI functions and custom functions that provide entirely new capabilities.
Note: Upsert® Advanced Formulas is included at no additional cost with the purchase of Upsert® Calculated Fields. Together, these plugins provide a complete solution for advanced formula calculations with real-time updates.
Why choose Advanced Formulas?
Performance optimization
Many Advanced Formulas are enhanced versions of SugarAI's core formula functions, optimized for better performance and efficiency. These improved formulas provide the same functionality as their core counterparts but execute faster and with reduced resource consumption, making them ideal for complex calculations across large datasets.
Missing functionality
Advanced Formulas also includes custom functions that provide entirely new capabilities not available in standard SugarLogic. These unique formulas enable advanced boolean logic, data validation, permission checks, and specialized operations that extend the boundaries of what's possible with SugarAI formulas.
Available formulas
Advanced Formulas provides specialized formulas organized into five categories:
Numeric aggregation formulas
These formulas perform mathematical operations on numeric fields from related records. Many are performance-optimized extensions of SugarAI's core rollup functions:
| Formula | Description |
|---|---|
| uafRollupSum | Sum values from related records with improved efficiency. Perfect for calculating total amounts, quantities, or scores across relationships. |
| uafRollupAve | Calculate the average of numeric values from related records with better performance characteristics. Ideal for determining average deal sizes, case resolution times, or performance metrics. |
| uafRollupMin | Find the minimum value from related records with improved speed. Useful for identifying lowest prices, earliest dates, or minimum scores. |
| uafRollupMax | Find the maximum value from related records efficiently. Excellent for tracking highest values, peak performance, or maximum amounts. |
| uafCount | Count the total number of related records with better performance than standard SugarAI count operations. Essential for tracking quantities, totals, or occurrences across relationships. |
Conditional numeric aggregation formulas
These formulas perform the same aggregations as above, but only on related records that meet specified conditions. These are performance-optimized extensions that combine aggregation with conditional filtering:
| Formula | Description |
|---|---|
| uafRollupConditionalSum | Sum values from related records that match conditional criteria in a single efficient operation. Calculate totals only for records meeting specific business rules. |
| uafRollupConditionalAve | Calculate the average of numeric values from related records that match conditions with improved performance. Find averages for filtered subsets of data. |
| uafRollupConditionalMin | Find the minimum value from conditionally filtered related records. Identify minimums within specific data subsets. |
| uafRollupConditionalMax | Find the maximum value from conditionally filtered related records. Track maximums within filtered datasets. |
| uafCountConditional | Count related records that meet conditional criteria with better efficiency than standard approaches. Count only records that satisfy specific conditions. |
Date aggregation formulas
These formulas work with date fields from related records. These are optimized extensions of SugarAI's date rollup capabilities:
| Formula | Description |
|---|---|
| uafMaxRelatedDate | Find the latest date from related records with improved efficiency. Identify most recent activity dates, completion dates, or milestone dates. |
| uafMinRelatedDate | Find the earliest date from related records with better performance. Track initial dates, start dates, or first occurrences. |
| uafRollupConditionalMaxDate | Find the maximum date from conditionally filtered related records with improved efficiency. Get the latest date from a filtered subset for tracking most recent activity by category. |
| uafRollupConditionalMinDate | Find the minimum date from conditionally filtered related records with better performance. Get the earliest date from a filtered subset for tracking first activity and historical events by category. |
Boolean expression formulas
These formulas return true/false values based on conditions. These are custom functions that extend SugarAI's boolean capabilities with new operations not available in standard SugarLogic:
| Formula | Description |
|---|---|
| uafIsEmpty | Check if a field or value is empty. Validate data completeness and identify missing information. |
| uafIsNotEmpty | Check if a field or value is not empty. Ensure required data is present and validate data integrity. |
| uafIsUpdated | Check if a record has been updated. Track modifications and identify changed records. |
| uafIsCurrentUserAdmin | Check if the current user has administrator privileges. Implement role-based logic and access controls. |
| uafLesserThan | Compare two values to determine if one is less than another. Perform conditional comparisons in formulas. |
Business time expressions
These formulas calculate business time metrics, accounting for business hours, timezones, and custom schedules. These are custom functions that provide specialized time calculations not available in standard SugarLogic:
| Formula | Description |
|---|---|
| uafCountBusinessDays | Calculate the number of business days between two dates with support for custom business hours and timezones. Returns decimal values for partial days. Perfect for SLA calculations, project timelines, and business day tracking. |
| uafCountBusinessHours | Calculate the number of business hours between two dates with support for custom business hours and timezones. Defaults to 9 AM to 5 PM, Monday-Friday. Ideal for time tracking, billing calculations, and SLA monitoring. |
| uafCountBusinessMinutes | Calculate the number of business minutes between two dates with support for custom business hours and timezones. Defaults to 9 AM to 5 PM, Monday-Friday. Essential for precise time tracking and minute-level SLA calculations. |
| uafBusinessDaysUntil | Calculate the number of business days until a specified date with support for custom business hours and timezones. Returns positive values until the date, negative after. Perfect for deadline tracking, countdown calculations, and SLA monitoring. |
| uafBusinessDaysSince | Calculate the number of business days since a specified date with support for custom business hours and timezones. Returns negative values for future dates, positive for past dates. Useful for age calculations, time since creation, and historical tracking. |
Key benefits
Performance-optimized calculations
Many Advanced Formulas are enhanced versions of SugarAI's core functions, designed for superior performance. These optimized formulas execute faster, consume fewer resources, and handle large datasets more efficiently than their standard counterparts.
New capabilities
Advanced Formulas includes custom functions that provide entirely new capabilities beyond standard SugarLogic. These unique formulas enable advanced operations, data validation, permission checks, and specialized logic that wasn't possible before.
Powerful aggregations
Perform complex calculations across relationships with support for conditional filtering. Calculate totals, averages, counts, and more from related modules with improved performance.
Flexible date operations
Work with dates across relationships to find earliest, latest, or conditionally filtered dates. Perfect for tracking milestones, deadlines, and activity dates.
Advanced boolean logic
Implement sophisticated conditional logic with specialized boolean expressions for data validation, permission checks, and comparison operations.
Seamless integration
All formulas integrate seamlessly with SugarLogic and work with existing calculated fields. Use them alongside standard SugarLogic formulas for maximum flexibility.
Integration with Calculated Fields
Advanced Formulas is designed to work seamlessly with Upsert® Calculated Fields. While Advanced Formulas provides powerful calculation functionality, Calculated Fields ensures these calculations update in near real-time without impacting system performance. Together, they provide:
- Real-time accuracy: Calculations update automatically as data changes
- High performance: Backend processing ensures calculations don't slow down the system, with optimized formula execution for even better efficiency
- Advanced capabilities: Access to formulas beyond standard SugarLogic, including performance-optimized extensions of core functions and custom functions that provide new capabilities
- Complete solution: Everything you need for sophisticated calculated fields
System requirements
- Sugar Sell, Serve, and Enterprise products
- Versions 14.x and higher
- Hosted in SugarCloud or on-site
- Recommended: Use with Upsert® Calculated Fields for optimal performance