In the "Detail> Automatic calculation" field of the Model Item Detail Definition dialog box, write the calculation formula.
The four arithmetic equations supported by Wagby are as follows.
operator | Description |
---|---|
( ) | brackets |
* | Multiplication |
/ | Gradual calculation (important point...) |
+ | Addition |
- | Subtraction or sign inversion |
% | remainder |
Items (in the same model) can be specified in expressions.Specify the item name as "$ {...}".(Specify the English item name in the part of ...).)
${...}
An example is shown below.
${value1} + 1
${kosuu} * ${tanka} * 1.05
How to write the condition judgment formula depends on the "type" of the target item.
Type | Numeric type | String type | Date type (* 3) |
---|---|---|---|
match | ${v1}==${v2} | EXACT(${v1},${v2}) | DATE(${v1})==DATE(${v2}) Or EXACT(${v1},${v2}) |
Mismatch | ${v1}!=${v2} | !EXACT(${v1},${v2}) | DATE(${v1})!=DATE(${v2}) Or !EXACT(${v1},${v2}) |
Greater than | ${v1}>${v2} | - | DATE(${v1})>DATE(${v2}) |
Less than | ${v1}<${v2} | - | DATE(${v1})<DATE(${v2}) |
that's all | ${v1}>=${v2} | - | DATE(${v1})>=DATE(${v2}) |
Less than | ${v1}<=${v2} | - | DATE(${v1})<=DATE(${v2}) |
Not set | - | ISBLANK(${v1}) | ISBLANK(${v1}) |
The standard is "save the value in the database".
Suppose the following formula is set for numerical type item.
Me()+1
At this time, the calculation result varies depending on whether or not the database is saved.
Classification | Description |
---|---|
With DB storage | The value is incremented by 1 each time it is updated. |
DB not saved | The initial value 0 is incremented by 1 and is always 1. |
In Excel, the number of significant digits that can be stored is 15 digits.Therefore, "11000.00000000001" forcibly becomes "11000.00000000000" for example.But since Wagby conforms to the Java floating point specification, it behaves differently from Excel.(In the above example, the decimal part is never forced to be zero.) Therefore, in calculations including decimal points, the result may differ from Excel.Here, we will explain some methods to avoid calculation errors.
Instead of adding, raising, raising and lowering operators (+, -, *, /), functions ADD,SUB,MUL,DIV , It is possible to perform addition, subtraction, multiplication, and division in consideration of calculation error.It is effective when dealing with a large amount of money or calculation involving decimal numbers in the middle.
For example, the expression $ {item1} + $ {item2} can be written as follows.
ROUND(VALUE(ADD(${item1},${item2})),1)
The return value of ADD, SUB, MUL, DIV function isString representation of numbersis.For example, it becomes "123.456".By applying the VALUE function, you can return to 8 byte floating point.
The ROUND function rounds the number to the specified number of digits.For details, please read the description of each function.
You can specify the output format.For example, if you specify the "%" character during formatting, the calculation result will be multiplied by 100 automatically.For details, refer to "Numeric - Output format".
When you want to express the calculation result of integer type with decimal point, there is a method of "converting type" (integer type) to decimal type at the time of calculation.
- As in the following calculation formula, "(double)"And" 8 byte floating point (double) type ".
(double)${uriagekingaku}/(double)${yosankingaku}
Please be aware that the decimal type operation always contains an error.For example in the following equation
ROUND(${item1}*0.12+${item2}*0.34, 1)
Using ADD, SUB, MUL, and DIV functions can avoid such problems, but as another simplified method, there is a method of minimizing the operation error by introducing the expression into integer arithmetic as much as possible by devising the expression There is.
The above expression can be created as follows.
ROUND((${item1}*12+${item2}*34)/100.0, 1)
"Null" is used to mean "no value".Items with null set will be blank when displayed.
The value of the non-input item (the input field but nothing) is as follows.
Type | value |
---|---|
Numerical value | 0 |
other than that | null |
For this reason, careful handling of the value "0" in the equation is necessary.This is because it can not be distinguished whether the input value is "0" or not.
For example, if a value is entered in the numeric type item item 1, if you use it, if you do not enter it, write the value of item 2 as follows: If the item 1 is not entered contrary to the intention, the value "0" It will be returned.
IF(${item1} >= 0, ${item1}, ${item2})
If the value stored in the item item 1 is always greater than "0", the operation is as intended by excluding the equal sign (=).
IF(${item1} > 0, ${item1}, ${item2})
You can use the ISEMPTY function to determine if an item contains a value.Returns true if not entered.It returns false if it is already entered.
I will explain with numerical type item as an example.
Types and properties | Return value of ISEMPTY function | Description |
---|---|---|
Numeric type - required | false | In case of mandatory item, initial value 0 is set. |
Numeric type - Non-essential: Not yet entered | true | It is possible to judge that there is no input. |
Numeric type - Not required: With input | false | It becomes false at the time of input. |
You can include the ternary operator "?:" In the formula.
( 式 ) ? 式が真の場合の値 : 式が偽の場合の値
Format | Description |
---|---|
Expression? X: y | If the expression is true it returns x.If expression is false, it returns y. X, y may also be referred to as an expression. |
The next example sets 1 if the value of item item 1 is greater than 0.Otherwise it will set null.
${item1} > 0 ? 1 : null
It can not be multiplied by zero.To avoid this we can use a ternary operator.
( (${bunbo}!=0) ? ${bunsi}/${bunbo} : 0 )
This means "0" if the value of the item bunbo (assumed to be integer type in this case) is 0, meaning storing the calculation result of bunsi/bunbo otherwise. For judgment that the numbers are not equal, "! =" Is used.
In the above expression, the items bunsi and bunbo are integer types, but when storing the decimal point, the calculation results are given (double) before the item ID as followsForced typingYou can do.
( (${bunbo}!=0) ? (double)${bunsi}/(double)${bunbo} : 0.0 )
The timing for setting the initial value is "after" of automatic calculation. Therefore, it is not possible to make a description that uses the initial value for conditional determination of automatic calculation.
Internally, the repetitive items store data in "array" format. Therefore, you can set the value of a repeating item to another repeating item using a function that takes an array as an argument and returns an array.
Example: To extract only the mail address part from the repeated item toAddress and set it to the iteration item toEmailAddress as well, write the following expression in toEmailAddress.
MAILADDRESS(${toAddress})
In case of range search, two items indicating the upper limit and the lower limit are prepared internally, and "1jshparam" "2jshparam" is added to the original item name, respectively.For example, for integer type item num, num1jshparam and num2jshparam are prepared internally.
Example: To set a value obtained by adding 1 to the upper limit value of num that is the same search condition item as the initial value of the search condition item item 1, it is described as follows.
${num1jshparam}+1
In the parent-child model relationship, the information of the child model is reflected on the parent model side.
Example: When you prepare the expression COUNT ($ {child_lp}) for counting the number of child models in the item on the parent model side, the corresponding items of the parent model are also changed when registering or deleting the child model.
When using Internet Explorer 9 for browsers, automatically calculated items are not updated even if you move the focus after entering values using the autocomplete function.This is the specification of IE, as inputting values with autocomplete does not cause a change event (in JavaScript).