Defining Actions that Assign A Value: User-Defined Formulas
You can dynamically assign values to collection plan elements as you enter quality results. Values are assigned when action rules associated with Assign a Value actions are evaluated and found to be true. When you assign a value to a collection plan element, its current value is overwritten. See: User-Defined Formulas and Business Examples: User-Defined Formulas
Failure Conditions
Formulas and SQL scripts may fail to process if one or more of the following conditions exist:
- The value represented by an output variable token is null. For example, if the results value represented by the token &QTY is null in the formula &DEFECTIVE/&QTY.
- More than one value is returned by a SQL statement. For example if the SQL statement select assigned_to from customer_reps
where customer_name = &CUSTOMER returns three values.
- The SQL statement returns no rows.
- An invalid SQL statement is specified.
Note: This is not an exhaustive list.
Prerequisites
To define a user-defined formula:
1. From the Quality Actions window, select an Action Rule in the Action Rules region.
2. In the Actions this Rule Invokes region of the Quality Actions window, select the Assign a value action.
4. Select a collection plan element to assign the value to.
You can select any collection plan element.
5. Choose the appropriate check box to define the value using either a Formula or an SQL script. The default is formula.
Formula: Use when an arithmetic expression is required. Arithmetic express can include simple operators (+, -, *, /), specific values (3, 2, 4000, 34500, 50), complex operators (square root, sin, cosine and so on), functions (SQRT), and/or output variables tokens (&DEFECTIVE) that represent quality results values. Output variables correspond to collection plan elements (e.g. &QTY represents the Quantity Defective collection plan element). As quality data is collected, quality results values are passed to your formulas via the output variables you have defined.
An example of a formula is as follows:
In this example the output variable &QTY corresponds to the Quantity Defective collection plan element.
SQL Text: Use when data must be read from database tables. You can include specific values and output variables tokens (&PARTICLE_SIZE) in your SQL text as well.
An example of SQL Text is as follows:
select kit_grade_name
from lot_grades
where &PARTICLE_SIZE between low_size and high_size
Note that in the case of user-defined formulas, it is not necessary to place a semicolon (;) or a forward slash (/) at the end of each SQL statement.
6. If you have included output variable tokens in your Formula or SQL Text, choose the Variables button to associate these output variable tokens with the action. See: Associating Output Variables with Actions.
7. Choose OK to Save your work.
Define quality actions for collection plan elements. See: Defining Collection Plan Element Actions.