Formula Management Overview

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:

  • Pricing, including Pricing Engine and other productized pricing methods that calculate Commerce pricing, are 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 page

Formula Management Editor

ClosedLeft Panel

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.

ClosedMain Panel

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.


ClosedIcon Definitions

Icon Description
Edit icon Click this icon to open a formula for editing.
Delete icon Click this icon to delete a formula.
Accept/OK icon Click this icon to accept a formula after changes have been made.
Revert icon Click this icon to revert to original formula.
Auto Update Checkbox

Auto Update Checkbox

  • Auto update will execute a formula using AJAX. The AJAX execution of formulas occurs before the auto update on the main document.
  • When an input attribute is changed for a for a formula with "Auto Update" enabled, the formula will be executed. If the formula output is an input attribute to any other formulas with "Auto Update" enabled, those formulas will also execute.
  • Only formulas marked for auto update will be executed, so be sure to consider which values will be updated and which will not.

ClosedWizard Input Mode

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.

ClosedOperators

ClosedAvailable Functions

ClosedElement Colors


ClosedText Input Mode

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.

Input Text Mode

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.

Edit in Input Text Mode

Note: Administrators will not be allowed to save a formula if any errors exist.


Administration

ClosedCreate 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.

  1. Navigate to the Admin Home Page.

  2. Click Process Definition in the Commerce and Documents section.

  3. Select Formulas for the applicable Process Navigation drop-down, and then click List.

  4. 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.
  5. Drag & drop Extended Price into the Add Attribute bar under the Attribute Name column.

    Drag & drop Extended Price

    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.

  6. Drag & drop the Net Price attribute into the formula bar.

    Drag & drop the Net Price attribute into the formula bar

  7. Type the multiplication operator * into the formula bar.

    Type the multiplication operator * into the formula bar

  8. Drag & drop the Quantity attribute into the formula bar.

    Drag & drop the Quantity attribute into the formula bar

  9. Click the Add icon icon to add the formula to the formula list.

    add the formula to the formula list

    Formula Management Quotes


ClosedCreate a Formula with Functions Using Wizard Mode

  1. Navigate to the Admin Home Page.

  2. Click Process Definition in the Commerce and Documents section.

  3. Select Formulas for the applicable Process Navigation drop-down, and then click List.

  4. 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.
  5. Drag & Drop Net Price into the Add Attribute bar under the Attribute Name column.

    Drag & Drop Net Price into the Add Attribute bar

    The Attribute Name will display the variable name or the label name.
  6. Collapse the Attributes section and open the Functions section, in the left panel.

     open the Functions section

  7. 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.

    If Else dialog

    When you hover over a function, you see the required inputs and return.
  8. Populate the Condition field.

    In this case, Net Price should be calculated if %/Amt = Amt.

    Condition

    Drag & drop is not available in the pop-up windows, so you will need to begin typing the label or variable name.
  9. 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.

    True Value

  10. 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.

  11. Click Save.
  12. Click the Add icon icon to add the formula to the formula list.

    When the Net Price formula is complete, it appears in the formula list.

    Net Price formula


ClosedUsing 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.

A value of 0.0 is returned if nothing is found.
  1. Navigate to the Admin Home Page.

  2. Click Process Definition in the Commerce and Documents section.

  3. Select Formulas for the applicable Process Navigation drop-down, and then click List.

  4. Search for List Price (listPrice_line) in the Attributes panel.
  5. Drag & drop List Price into the green Add Attribute Name section under the Attribute Name column.
  6. The Attribute Name will display the variable name or the label name.
  7. Collapse the Attributes section and open the Functions section, in the left panel.
  8. 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.
  9. Use the Table drop-down to choose the Data Table you will be querying.

    Query Details

  10. Enter Conditions for the query to run.

    Query Conditions

  11. Select the Data Table Column to Return.
  12. Select the checkbox Use Default Value if No Value is returned.
  13. Enter 0.0 as the Default Value.

    Default Value

  14. Click Save.

    When the List Price formula is complete, it will appear in the formula list like the following example:

    Formula


ClosedSUM 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.

SUM and AVG Function Editor Parametersdialog

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.


ClosedCopy or Edit a Formula Using Text Mode

  1. Navigate to the Admin Home Page.

  2. Click Process Definition in the Commerce and Documents section.

  3. Select Formulas for the applicable Process Navigation drop-down, and then click List.

  4. Select the Input Text Input Mode.

    Input Text Mode

  5. Click the Edit icon for the applicable formula.

    Edit formula

  6. To copy a formula, highlight the formula, then right-click your mouse and select copy or enter <Ctrl> C on your keyboard.

    Copy Input Text

  7. To edit a formula, modify the formula in the text window.

    Modify formula

  8. Click Accept/OK icon to save changes, or click Revert icon 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.


ClosedCreate a Formula Using Text Mode

  1. Navigate to the Admin Home Page.

  2. Click Process Definition in the Commerce and Documents section.

  3. Select Formulas for the applicable Process Navigation drop-down, and then click List.

  4. Select the Input Text Input Mode.

    Input Text Mode

  5. Enter an Attribute Name or drag & drop the applicable attribute from Attributes in the left panel.

    Add formula in Input Text mode

  6. Enter the formula in the Formula text window.

    Formula text window

  7. Click the Add icon 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.


ClosedFormulas 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.

Related Topics

Related Topics Link IconSee Also