Return to main navigation Page
Using the Query Function
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.
Using the Query Function
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.
- Navigate to: Admin Home Page > Process Definition Quick Links> Select a Process > Click Formulas
- Search for List Price (listPrice_line) in the Attributes panel.
- Drag & drop Next Price into the green Add Attribute Name section under the Attribute Name column heading.
The Attribute Name will display as the variable name, not the label name.
- 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'll see the required inputs and return.
Once the if function has been placed in the Formula Bar, a Query Details pop-up opens.
Use the 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 Entered'.
Enter the Default Value of 0.0.
When the List Price formula is complete, it will appear in the formula list as such:
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.
The data type of attributes used in the
formula must be the same as the data type of the attribute that is being
returned. When setting a float attribute, you can use integer, float,
and/or currency attributes in the formula. However, when setting an
integer attribute, the return type must be integer.
Formulas defined for an attribute can be self-referencing. Self-referencing formulas only run once.
You can have a validation 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.
When evaluating a Boolean attribute
in a query function, the equal (=) symbol only compares one attribute
to another, such as Bool1 = Bool2.
To evaluate if Bool1 is true, put
Bool1. To evaluate if Bool1 does not equal true, use
Formulas will run after the simple modification of an action, but before the advanced BML.
Customer-specific pricing is calculated before formulas are evaluated.
When a +
is used in combination with the 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. 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.
(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
Default attribute values can be set to a
formula. Attributes that don't have a formula defined will not have
this option on the page.
In setting formulas for string
attributes, you will not be able to set a literal value that contains an
apostrophe or single quote (') (for example, that's great). This
example will throw an error and there is no way to escape the
character. Similarly, the single quote cannot be used as a search value
for the LIKE operator of the Query function. This again, cannot be
NULL and blank Integer values are treated as separate values.
- NULL= 0
- Blank = ""
If you use logic that tests for NULL values in rule conditions or BML, this logic should be updated.
Using "null" as an attribute value is strongly discouraged.