Support > Repository > A formula > Calculation expression example
ja | en

We introduce examples of various automatic calculation formulas including calculation of age and required time.

To remove extra spaces (half-width, double-byte), tab, new-line character from the character string included in the "note" item of the character string type, set the following expression.

TRIM(${note})

Detail is"TRIM functionPlease read the explanation.

Calculate the number of elapsed months from the date entered in the date type "joinday" item to the present.

DATEDIF(${joinday}, TODAY(), "M")

Detail is"DATEDIF functionPlease read the explanation.

In the "customer" model, we show examples in which "birth" item and "age" item are prepared.

An automatic calculation formula for age item (integer type) is described as follows.
DATEDIF(${birth}, TODAY() ,"Y")

You can let the notation "many years and months" be done.In this case, the age item is a character string type.

You do not need to enter a line break.(I am putting it here for the sake of clarity.)

DATEDIF ($ {birth}, TODAY (), Y) + "Year" + DATEDIF ($ {birth}, TODAY (), "YM") + "months"

Detail is"DATEDIF functionPlease read the explanation.

An example of automatically calculating "required time" from "start time (date 1)" and "end time (date 2)" of the support model is shown below. Here, please use "time required" time type.In addition, minutes exceeding 24 hours will be truncated.

BETWEENTIME(${date1}, ${date2})

For details, refer to "BETWEENTIME functionPlease read the explanation.

Calculate the difference between the hourly leaving time (endtime) and the attendance time (starttime).However, taking break time into account. Each item is defined as a time type.

You do not need to enter a line break.(I am putting it here for the sake of clarity.)

TIME ($ {endtime}) - TIME ($ {starttime}) - TIME ($ {breaktime}), "HH: mm: ss"))

The above formula can also be written as follows.

You do not need to enter a line break.(I am putting it here for the sake of clarity.)

BETWEENTIME (TOTIME (MOVEMINUTE ($ {starttime}, HOUR ($ {breaktime}) * 60 + MINUTE ($ {breaktime}))), $ {endtime})

Here is an example of finding the required time between items in the repeating container.

Fig.1 Operation image

Items to prepare

Define the repeating container as follows.

Figure 2 Prepare a repeating container

Set the following expression in the "Time difference" (kks/keisan) field.

TIMEVALUE (TEXT (TIME ($ {kks / komoku 2}) - TIME ($ {kks / komoku 1}), "HH: mm: ss"))

Here is an example of obtaining the day of the week of the date type item "shipment date (syukka_date)".

You do not need to enter a line break.(I am putting it here for the sake of clarity.)

CHOOSE (WEEKDAY ($ {syukka_date}), "Sun", "Moon", "Fire", "Water", "Wood", "Gold", "Sat")

For details, refer to "WEEKDAY function"When"CHOOSE functionPlease read the explanation.

An example of defining the integer type item "productid" (primary key) of the product model as 4-digit of zero padding is shown below.

PADDING("0", 4, ${productid})

For details, refer to "PADDING functionPlease read the explanation.

We introduce an example of changing the numerical type item cvalue in the same model by the value of the numeric type item source. The following rules shall be realized.

Value of source The value of cvalue
Less than 10 100
Less than 20 200
other than that 300

At this time, the expression of the item cvalue can be described as follows.

You do not need to enter a line break.(I am putting it here for the sake of clarity.)

IF ($ {source} <10, 100, IF ($ {source} <20, 200, 300))

Suppose you have a model definition that includes "planned date (plandate)" and "actual date" (realdate).Here, it is assumed that the value of "delay" (integer type) is calculated by the following rule.

conditions Delay calculation rule
Missing actual date Difference between today's date and the planned date (plandate)
Actual date entered The difference between the actual date (realdate) and the planned date (plandate)

At this time, the expression of the delay item (integer type) can be described as follows.

You do not need to enter a line break.(I am putting it here for the sake of clarity.)

IF ($ {realdate} == null, DATEDIF ($ {plandate}, TODAY (), "D"), DATEDIF ($ {realdate}, $ {plandate}, "D"))

Here, "$ {realdate} == null" means that this value is not set. Item types that can be compared with null are string type, date type, time type, date time type.

It is also possible to describe sitem 3 as an automatic calculation by concatenating the character string items sitem 1 and sitem 2.

It becomes the following expression.

${sitem1}+":"+${sitem2}

By the way, in the case of the above expression, we do not consider cases where the value of sitem 1 or sitem 2 is not set (null) or blank. When sitem 3 is output only when both sitem 1 and sitem 2 are valid (not set yet, not blank), the following expression is obtained.

You do not need to enter a line break.(I am putting it here for the sake of clarity.) "+" Instead of operator,CONCATENATE You can also use functions.

IF (AND (! ISBLANK ($ {sitem 1}),! ISBLANK ($ {sitem 2})), $ {sitem 1} + ":" + $ {sitem 2}, "")

In the repeating container payment, the date type item pdate and the amount amount are prepared. At this time, set the total value of payment/amount to the total item (not this repeating container). However, the following rules apply:

  • If payment/pdate is before today 's date, we use the value of payment/amount.
  • Otherwise 0.

In the item total that stores the aggregate valueConditional SUM functionApply.

You do not need to enter a line break.(I am putting it here for the sake of clarity.)

SUM ($ {payment / amount}, "DATE ​​($ {payment / pdate}) <= DATE (TODAY ())")

When comparing dates,DATE functionIt is necessary to quantify the date using.

To summarize the above, the definition is as follows.(We extract only necessary items.)

Item name (Japanese) Item name (English) Item type Automatic formula
Aggregate total 8 byte integer SUM(${payment/amount},
"DATE(${payment/pdate})<=DATE(TODAY())")
payment payment Repeat container
Number of payments payment/pid Repeating Container ID
Date of payment payment/pdate date
payment payment/amount 8 byte integer

I will explain how to proportionally distribute "sales" items (numerical values) to repeated containers. However, if there is a remainder, we will add it to the top repeating container.

For example, if the sales value is "100", it becomes "50", "50" if there are two repeating containers.If it is three, it will be "34", "33", "33".An example of operation when it is divided into eight is shown in Fig.

Figure 1 Proportion of sales

Let the sales item be uriage, and count the item that stores the number of repeated containers. The following expression is applied to the count item.

COUNT(${cont})

Set the formula for the repeating container item "sales" as follows.

CIDThe function returns the number of rows in the repeating container. ROUNDFunction rounds the number to the specified number of digits. MODThe function finds the remainder of dividing a number by a divisor.

($ {Uriage} / $ {count}, 0) + MOD ($ {uriage}, $ {count}), ROUND ($ {uriage} / $ {count}, 0 ))

"SQL expression> Prepare an item to hold the value before savingPlease read.