Support > Repository > A formula > Basic writing style
ja | en

You can express it in the expression by putting the item ID in the place holder.

In the "Detail> Automatic calculation" field of the Model Item Detail Definition dialog box, write the calculation formula.

Figure 1 Example of 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
Since the value of the automatic calculation item is uniquely determined by calculation, it is a read-only item on the registration/update screen.

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.

Find the value obtained by adding 1 to the model item value1.
${value1} + 1
Calculate the product of the model item kosuu and the model item tanka multiplied by 1.05.
${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".

Figure 2 Saving values ​​in the database but in a valid state
  • If the calculation item is "Save value in database", it is calculated only at registration/update.When displaying, the value stored in the database is used as it is.
  • The calculated item is "Save value in database"Not inIf it is displayed, it will always be recalculated with the latest value at the timing of display.

When computing yourself

Suppose the following formula is set for numerical type item.

Me()+1
Me Is a special function that indicates itself.

At this time, the calculation result varies depending on whether or not the database is saved.

Classification Description
With DB storageThe value is incremented by 1 each time it is updated.
DB not savedThe 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.

Calculation of amount (four arithmetical operations in consideration of calculation error)

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".thereVALUEBy applying the function, it can return to 8 byte floating point.ROUNDFunction rounds the number to the specified number of digits.For details, please read the description of each function.

Specifying the output format

You can specify the output format.For example, if you specify the "%" character during formatting, the calculation result will be multiplied by 100 automatically.Detail is"Numeric - Output format"Please refer to.

When expressing the calculation result of integer type as a decimal point type

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}
This method is for Java developers.("Type" such as double conforms to Java grammar.)

Writing formulas to avoid calculation errors

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)

ROUNDIf the calculated value passed to the function is 10.8499999999 ROUND will result in 10.8 instead of 10.9.

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)
Care must be taken when the calculation result temporarily exceeds the range of integers.In the example above, if item 1 takes a value of 1 billion, it will be 12 billion if it is multiplied by 12, but since it exceeds the representable range (4.2 billion) of integer type, it will not be the correct result.The range of numbers that can be represented by integer type is "model definition> TypePlease read.

"Null" is used to mean "no value".Items with null set will be blank when displayed.

"Null" and "0" are different.Also, "null" and "" (null character) are also different. It has a special meaning that null has no value.
Required items can not be set to null.

The value of the non-input item (the input field but nothing) is as follows.

Typevalue
Numerical value0
other than thatnull

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})

ISEMPTY function

ISEMPTY functionYou can use it to determine if an item has 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 propertiesReturn value of ISEMPTY functionDescription
Numeric type - requiredfalseIn case of mandatory item, initial value 0 is set.
Numeric type - Non-essential: Not yet enteredtrueIt is possible to judge that there is no input.
Numeric type - Not required: With inputfalseIt becomes false at the time of input.

You can include the ternary operator "?:" In the formula.

(Expression) Value when the? Expression is true: value when the expression is false
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.

Example: Set null to the result of automatic calculation using ternary operator

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 to set 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})

When describing an expression in the initial value of the search condition, if the target item isSearch rangeIf it corresponds to the following rules.

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