Overview
Formula Management allows you to write formulas that will set quote-level and line-level attribute values through a drag & drop interface and a visual editor. This enables you to build complex calculations without scripting.
Notes:
- Customer-specific pricing is calculated before formulas are evaluated.
- An attribute will not have the option to use a formula for its default value until after a formula is created and deployed.
Formula Management Editor
In the left panel you'll find the following collapsible headers:
-
Attributes: Filter or scroll to find main document attributes, line item attributes, and system variables to set or use in formulas.
Note: System variables and main document version attributes are only available in the Formula column. System variables and main document version attributes are not available in output Attribute Name column since the user cannot modify these values.
- Groups: Filter or scroll through a list of Attribute Groups to edit the groups or use them in formulas. See the topic Attribute Groups for more information.
- Functions: Filter or scroll through a list of functions to be used in formulas.
In the main panel you'll find the list of formulas.
-
Display Mode: Select Label or Variable for the Attribute Name.
Note:Label display mode is not available when using Input Text input mode.
- Input Mode: Select Wizard or Input Text:
- Wizard is the default drag & drop input mode.
Input Text provides an alternate input method to create formulas using plain text entry.
Note: Input Text mode is available beginning in CPQ 23A.
| Icon | Description |
|---|---|
|
Click this icon to open a formula for editing. |
|
Click this icon to delete a formula. |
|
Click this icon to accept a formula after changes have been made. |
|
Click this icon to revert to original formula. |
|
Auto Update Checkbox
|
You can create formulas to use on quote and line-level attributes by using drag & drop functionality within a visual editor. In the left panel, use the filter functionality to quickly search for and find Commerce attributes and functions that will be used to create each formula. In the right panel, drag & drop an attribute to the Attribute Name section and then use operators, functions and attributes to create its formula in the associated formula bar.
| Operator | Meaning | Looks Like |
|---|---|---|
| + | Addition |
 +
|
| - | Subtraction |
 -
|
| / | Division |
/
|
| * | Multiplication |
*
|
| > | Greater Than |
>
|
| < | Less Than |
<
|
| = | Equals |
=
|
| NOT = | Not Equals |
NOT =
|
| >= | Greater Than or Equal To |
>=
|
| <= | Used in condition grouping |
<=
|
| AND | Used in condition grouping |
AND
|
| OR | Used in condition grouping |
OR
|
| NOT | Used in condition grouping |
NOT
|
| () | Used in condition grouping | |
| LIKE | Used in queries only |
| Function | Description | Return Type |
|---|---|---|
| sum | Combines a set of numeric inputs and return the value as a number. | Numeric |
| avg | Calculates the average of a set of numeric inputs and return the value as a number. | Numeric |
| sumIf | Evaluates the sum of a given sub-doc attribute of a given attribute based on the condition. The condition will evaluate each sub-document independently. | Numeric |
| avgIf | Evaluates the average across all sub-documents of a given attribute based on the condition. The condition will evaluate each sub-document independently. | Numeric |
| capitalizeFirst | Capitalizes the first letter of the string. | String |
| max | Returns the largest of the given inputs. | Numeric |
| min | Returns the smallest of the given inputs. | Numeric |
| median | Evaluates the median of the given numeric inputs. | Numeric |
| ceil | Converts a number with fraction to the next whole number. | Numeric |
| round | Returns the first number rounded to the precision defined by the second numeric input. | Numeric |
| sqrt | Returns the rounded positive square root of the number. | Numeric |
| pow | Returns the value of the first numeric input raised to the power of the second numeric input. | Numeric |
| exp | Returns Ruler's number e raised to the power of the numeric input. | Numeric |
| abs | Returns the positive equivalent to the numeric input entered. | Numeric |
| mod | Returns the remainder of the first numeric input divided by the second numeric input. | Numeric |
| formatAsCurrency | Returns a float or integer in the correct currency format. | String |
| upper | Converts all of the characters in the input string to upper case. | String |
| lower | Converts all of the characters in the input string to lower case. | String |
| substring | Returns a part of the text from a larger string. | String |
| replace | Returns a string with all found occurrences replaced with the new string. The option integer input defines the number of occurrences to replace starting from the beginning of the string. | String |
| concat | Concatenates, or combines, strings. | String |
| trim | Removes white space from both ends of a string. | String |
| len | Returns the length of a string. | Integer |
| find | Returns the position/index of a substring within a string. | Integer |
| startswith | Checks whether a string starts with a particular substring. | Boolean |
| endswith | Checks whether a string ends with a particular substring. | Boolean |
| isNumber | Returns true when a string is a number and false in all other cases. | Boolean |
| contains | Checks whether a string contains a particular substring. | Boolean |
| query | Allows users to search through Data Tables and return select values. | Column type |
| toFloat | Converts text that represents a number or integer value into a float. | Float |
| toString | Converts a numeric value into a string. | String |
| toInteger | Converts a string that represents a number or a float value into an integer. | Integer |
| if | Checks whether a condition is met and returns one value if TRUE and another value if FALSE. |
| Color | Description | Example |
|---|---|---|
| Blue | Represents main-document attributes. |
|
| Purple | Represents sub-document (aka line item) attributes. |
|
| Orange | Represents functions. |
|
| Light Green | Represents literals or static entry. |
|
| Dark Pink | Represents table columns used in a query. |
|
| Red | Represents system variables. |
|
Beginning in Oracle CPQ 23A administrators can use the Text Input mode to create formulas using plain text entry. The new formula entry method vastly increases ease of use and supports the following functionality:
- View all formula attributes and operators in one place.
- Easily navigate to any position in the formula.
- Allow quick copy-paste between formulas.
To edit an existing formula in plain text entry, select the Input Text Input Mode, and then click the Edit icon for the applicable formula.
Note: Administrators will not be allowed to save a formula if any errors exist.
Administration
Create a Formula to Set Price Values Using Wizard Mode
Example Use Case: You need to set the values of the Extended Price, which is based on Net Price and Quantity.
-
Navigate to the Admin Home Page.
-
Click Process Definition in the Commerce and Documents section.
-
Select Formulas for the applicable Process Navigation drop-down, and then click List.
-
Search for Extended Price in the Attribute Filter in the left panel.
When you hover over an attribute, you see its variable name and data type. -
Drag & drop Extended Price into the Add Attribute bar under the Attribute Name column.
The Attribute Name will display the variable name or the label name.Next, you need to populate the formula bar. Here we are using main-document attributes. In this example, the formula is Extended Price = Net Price * Quantity.
-
Drag & drop the Net Price attribute into the formula bar.
-
Type the multiplication operator * into the formula bar.
-
Drag & drop the Quantity attribute into the formula bar.
-
Click the
icon to add the formula to the formula list.
Create a Formula with Functions Using Wizard Mode
-
Navigate to the Admin Home Page.
-
Click Process Definition in the Commerce and Documents section.
-
Select Formulas for the applicable Process Navigation drop-down, and then click List.
-
Search for Net Price in the Attribute Filter in the left panel.
When you hover over an attribute, you see its variable name and data type. -
Drag & Drop Net Price into the Add Attribute bar under the Attribute Name column.
The Attribute Name will display the variable name or the label name. -
Collapse the Attributes section and open the Functions section, in the left panel.
-
Drag & drop the if function into the formula bar.
Once the if function has been placed into the formula bar, the if/else condition window pops up.
When you hover over a function, you see the required inputs and return. -
Populate the Condition field.
In this case, Net Price should be calculated if
%/Amt = Amt.
Drag & drop is not available in the pop-up windows, so you will need to begin typing the label or variable name. -
Populate the True Value dialog box.
This is the formula that will run if the condition is met. In this case, the True Value is
List Price - Discount.
-
Populate the False Value field.
This is the formula that will run if the condition is not met.
In this case, the False Value is
Line Price - (Line Price*Discount)/100. - Click Save.
-
Click the
icon to add the formula to the formula list. When the Net Price formula is complete, it appears in the formula list.
Using the Query Function in Wizard Mode
Formula Management allows you to query Data Tables and return results that will populate a formula and provide a calculation for a line item or main-document attribute.
In this example we have queried a Data Table to return the current line items volume pricing.
-
Navigate to the Admin Home Page.
-
Click Process Definition in the Commerce and Documents section.
-
Select Formulas for the applicable Process Navigation drop-down, and then click List.
- Search for List Price (listPrice_line) in the Attributes panel.
- Drag & drop List Price into the green Add Attribute Name section under the Attribute Name column.
- Collapse the Attributes section and open the Functions section, in the left panel.
-
Drag & drop the query function into the formula bar.
When you hover over a function, you see the required inputs and return.
Once the query function has been placed in the Formula Bar, a Query Details dialog box appears. -
Use the Table drop-down to choose the Data Table you will be querying.
-
Enter Conditions for the query to run.
- Select the Data Table Column to Return.
- Select the checkbox Use Default Value if No Value is returned.
-
Enter
0.0as the Default Value.
-
Click Save.
When the List Price formula is complete, it will appear in the formula list like the following example:
SUM and AVG Function Editor Parameters
SUM and AVG functions in Formula Management can now have Attribute Groups defined as parameters. The Parameters field within a SUM or AVG function’s dialog box will now suggest Attribute Groups that are similar in name to what the user has entered. The SUM Function dialog box is shown below.
If an Attribute Group is a parameter in a SUM or AVG function, all attributes within the Attribute Group are included in the function’s calculation.
Copy or Edit a Formula Using Text Mode
-
Navigate to the Admin Home Page.
-
Click Process Definition in the Commerce and Documents section.
-
Select Formulas for the applicable Process Navigation drop-down, and then click List.
-
Select the Input Text Input Mode.
-
Click the Edit icon for the applicable formula.
-
To copy a formula, highlight the formula, then right-click your mouse and select copy or enter <Ctrl> C on your keyboard.
-
To edit a formula, modify the formula in the text window.
-
Click
to save changes, or click to revert to the original formula.Note: If the formula is not valid an error message will be displayed and you will not be able to save the formula until the error is resolved.
Create a Formula Using Text Mode
-
Navigate to the Admin Home Page.
-
Click Process Definition in the Commerce and Documents section.
-
Select Formulas for the applicable Process Navigation drop-down, and then click List.
-
Select the Input Text Input Mode.
-
Enter an Attribute Name or drag & drop the applicable attribute from Attributes in the left panel.
-
Enter the formula in the Formula text window.
-
Click the Add icon
to save the new formula.Note: If the formula is not valid an error message will be displayed and you will not be able to save the formula until the error is resolved.
Formulas in Reconfiguration Scenarios
The Reconfigure action is split into one parent (Reconfigure) and one sub-action (Reconfigure Inbound). This eliminates the need for conditionals to segregate logic.
- The parent action (Reconfigure) contains all things common to both Reconfigure actions and its own identifiers: Label, Variable Name, Description, Action Icon, Show Loading Dialog, Layout Path, and Document Views Tab.
- The sub-action (Reconfigure Inbound) only contains what is specific to it.
- This allows administrators to execute a formula per attribute on:
- Outbound paths from a Commerce Transaction.
The Formula will run immediately after the user clicks the Reconfigure action in Commerce, and before the user enters Configuration.
- Inbound paths to a Commerce Transaction.
The Formula runs after the user clicks Save in the Reconfiguration, and before the user returns to the Commerce page.
- Formula execution is consistent with the existing order of operations for inbound and outbound executions.
Notes
- When a + is used in combination with a query function, the query will aggregate the results of the query. This will also occur when a query is used inside of a sum function.
- Formulas defined for an attribute can be self-referencing. Self-referencing formulas only run once.
- A validation runs before deleting a formula that is being used in an Action’s Modify tab or default value. This validation asks the user to remove any references prior to deletion and will provide a link to the attribute that is used in the formula.
- Numeric stings are treated as literal (numeric) in conditional formulas.
- In setting formulas for string attributes, you will not be able to set a literal value that contains a "." Similarly, the single quote (') cannot be used as a search value for the LIKE operator of the query function. Neither can be avoided.
- The data type of the attribute used in the formula must be the same as the data type of the attribute that is being returned.
- HTML attributes are not refreshed when a Commerce formula automatically updates.
- When you hover over an attribute, you see its variable name and data type.When you hover over a function, you see the required inputs and return.
- Main document formulas can be executed when users add a line item (sub-document) to the quote, regardless of whether the line item is a result of Configuration, Search, or Quick Key Line Items.
When a new line item is added to a Quote, the formulas that are defined for line item attributes will run to determine the default values of the attributes.
- Nested functions cannot be used with the avgif and sumif functions in an action; they can be used with conditions.
- When using query logic on a Data Table, if you use an OR operator and the system finds multiple matches, only the first match will be returned.
- The function sumif cannot return an integer data type.
- NULL and blank Integer values are treated as separate values:
- NULL= 0
- Blank = ""
- Using NULL as an attribute value is strongly discouraged.
- If you use logic that tests for NULL values in rule conditions or BML, confirm that the logic takes this difference into account.
