0
0
In this topic, we will cover the fundamental structure of a fast formula. We will discuss the components that constitute a formula definition, including the associated conditions. The five key sections of the formula structure are alias statements, default statements, input statements, the calculation section, and the return statement.
Formulas are run from the top in sequence until a return statement is reached.
An alias statement provides a different name for a database item or global value. Database items are named when they are created by the system. And sometimes, these names are too long to be conveniently used in the formula.
You cannot shorten the name of a database item or global value itself, but you can set up a shorter alternative name within the formula using alias statements.
If alias statements are used, they must be the first statement in the formula.
Once set, use the alias name instead of the original database item name throughout the rest of the formula.
Using an alias is more efficient than assigning the database item to a local variable with a short name. The default false statement sets a default for values that may be null or not passed to the formula. Setting a default allows a value to be used for a formula input if a value is not provided.
The default false statement also allows a value to be used for a database item, if the database item value is not found, or if a non array database item value is null. Some database items are defined to require a default value because they would return no data or null values from the database.
You can use the WAS DEFAULTED statement to determine if a database item or input is null. The content of input statements varies depending on the formula type. Input statements can transfer element input values to a formula during processing. The input name must match the element's input value, replacing spaces with underscores in the input value name.
For instance, an element with the input value "earned date" would be represented as EARNED_DATE. Inputs should be separated by commas. The data type is optional for numeric inputs but mandatory for non-numeric inputs. In this example, the input value HOURS_WORKED is numeric.
If the input value is not numeric, you must specify whether it is text or a date. For example, for an input labeled START_DATE, you would indicate it as START_DATE (date). It's important to note that the input statement should always be plural, even if only one variable is defined. Therefore, it should be written as "inputs are" rather than "input is."
The calculation section is the central part of the formula, where values are calculated or validated. It includes assignment statements. This section may also feature conditional processing, utilizing if and while statements to dictate the execution flow within the formula. An if statement consists of three components.
In the if clause, the formula checks whether the condition (in this case, YEARS_SERVICE greater than or equal to 10) is true. If it is, we move to the THEN clause, where the formula assigns ANNUAL_LEAVE a value of 25. If the condition is false, the ELSE clause is executed, assigning ANNUAL_LEAVE a value of 20.
The ELSE condition in an if statement is optional. In this example, it is necessary unless the value of ANNUAL_LEAVE is set prior to the if statement. If multiple conditions need to be validated or several steps need to be executed, they should be enclosed in brackets. Conditions are utilized in if and while statements to manage the execution flow within a formula.
Conditions can be employed to compare expressions. For example, you might check if the sum of bonus and salary is greater than 20,000, or if city equals "San Francisco." Other conditions include is executable, which assesses whether a formula can be executed, and WSA_EXISTS, which checks if an item exists in the working storage area. Additionally, the Array.EXISTS method is used to determine whether there is a value at a specific index in an array.
Conditions can also be combined using logical operators like and, or, and not. For example, you might check if BONUS plus SALARY is greater than 20,000 and if AGE is less than 21. The types of statements that can appear in a calculation section include:
- WHILE loop
- CALL_FORMULA for invoking another formula
- SET_INPUT for passing input values to a formula
- EXECUTE for executing a statement
- GET_OUTPUT for retrieving output values
- IF statement for conditional processing
- Default data value for any array of database items
- EXIT statement to break out of a WHILE loop or IF statement
- GET_CONTEXT to obtain the value of a context, as illustrated in the slide.
Context is a condition used to test whether a context value is set, as demonstrated in this example. The CHANGE_CONTEXT statement is used to modify context values. A return statement can be included in the calculation section if it is part of the conditional logic. However, if the condition is true and the return statement is executed within the IF statement, any subsequent lines in the formula will not be executed.
Next, we have the ASSIGNMENT statement, which assigns a value to a database item. The RETURN statement is used to return values stored in local variables and to pass calculation rules back to the application. A single RETURN statement can return multiple values, separated by commas. Once the RETURN statement is processed, the formula stops executing.
Any statements that follow the RETURN statement are ignored, except when the RETURN is part of an IF THEN and ELSE clause. In such cases, the formula can continue to process additional statements based on the conditions defined within those clauses. Some programs may expect values to be returned using predefined names for the output variable, which is important for ensuring compatibility with other parts of the application.
It's crucial to structure your formulas correctly to ensure that the logic flows as intended, especially when using conditional statements. This allows for more complex calculations and validations to be performed effectively.
Assignment statements are utilized to assign values to variables or array variables. These statements can include multiple arithmetic operators and functions as necessary. For instance, in this example, we are assigning a value to ANNUAL_BONUS. Each function or calculation represents a single expression, and you can nest expressions to develop more complex calculations. This flexibility enables the creation of sophisticated formulas capable of managing diverse logic and arithmetic requirements.
Brackets can be used to control the order in which the calculations are performed. This formula calculates the assignment statement inside the brackets and from left to right in the following steps. SALARY_AMOUNT divided by 100 times BONUS_PERCENTAGE divided by 183, END_PERIOD_DATE and START_DATE plus 1, and then the result of multiplication, which is truncated to two decimal places.
A formula executes sequentially, unless you use brackets to group statements in conditional logic. Note that without the ELSE statement, the latter half of the formula won’t execute, as it is contingent on the initial condition.
I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.