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

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

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

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.

DATEDIF(${birth}, TODAY() ,"Y") + "年" + DATEDIF(${birth}, TODAY() ,"YM") + "ヶ月"

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

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

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.

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

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

The above formula can also be written as follows.

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

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

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

Fig.1 Operation image

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/komoku2})- TIME(${kks/komoku1}), "HH:mm:ss" ) )

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

CHOOSE(WEEKDAY(${syukka_date}), "日","月","火","水","木","金","土")

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

An example of storing the year, month, day part as a character string in the separate item from the date type item "shipment date (syukka_date)" is shown below.

TEXT(${syukka_date},"{1,number,00}")

TEXT(${syukka_date},"MM")

TEXT(${syukka_date},"dd")

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

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.

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

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

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.

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.

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

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.

IF( AND( !ISBLANK(${sitem1}), !ISBLANK(${sitem2}) ), ${sitem1}+":"+${sitem2}, "" )

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

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.

Apply a conditional SUM function to the item total that stores aggregate values.

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

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

When comparing dates, you need to quantify the date using the DATE function.

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 dividing into eight is shown in Figure 1.

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.

The CID function returns the number of rows in the repeating container.

The ROUND function rounds the number to the specified number of digits.

The MOD function finds the remainder by dividing a number by a divisor.

The ROUND function rounds the number to the specified number of digits.

The MOD function finds the remainder by dividing a number by a divisor.

IF(CID()==1, ROUND(${uriage}/${count},0)+MOD(${uriage},${count}), ROUND(${uriage}/${count},0) )

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