Support > Repository > Function > Set processing
ja | en

We introduce 36 functions related to set operation processing. Applicable to repeating items, repeating containers, foreign key child models, arrays.

COUNT |  SUM |  AVERAGE |  MAX |  MIN |  FIRST |  LAST |  SLICE |  JOIN |  DJOIN |  DDJOIN |  RANK |  LARGE |  SMALL |  GCD |  LCM |  ARRAY |  SORT |  REVERSE |  CONTAINS |  ISEMPTY |  ADD |  GET |  INDEXOF |  LASTINDEXOF |  ISEQUALS |  ISSAMELENGTH |  REMOVE |  REMOVEELEMENT |  REMOVEELEMENTALL |  UBOUNR7.8 |  AGCOUNR7.8 |  AGSUR7.8 |  AGAVR7.8 |  AGMAR7.8 |  AGMIR7.8 |  Handling model reference items |  How to specify a foreign key child model |  Restriction

ここに紹介する関数は、演算対象が繰り返し項目・繰り返しコンテナ内の項目・外部キー子モデル内の項目、となります。演算結果(数をかぞえる、積算する、最大値を取得する...)はコンテナや外部キー子モデルの項目に格納してください。

COUNT (repeat item, [condition])

COUNT (repeating container name, [condition])

COUNT (foreign key child model name, [condition])

Calculate the total number of records.Please set the type of the item storing the calculation result to "integer". If there is no data matching the condition, "0" is displayed. [Restriction ...]

Example: Find the number of repetition item number.

COUNT(${number})

Example: Calculate the number of repeated container reports.

COUNT(${report})

Container name onlySpecify.It is not necessary to include any item names in the container.

Example: Find the number of foreign key child model support cases.(Detailed method of specifying foreign key ...)

COUNT(${support_lp})

Specify condition

Write a conditional expression in double quotation mark (double quotation).Enclose the item name in $ {...}.

Example: For the repetition item number, find the number of positive numbers.

COUNT(${number}, "${number}>0")

Example: Calculate the number of repeated container reports.However, the report/date item in this container is only for this year.

COUNT(${report}, "YEAR(${report/date})==YEAR(TODAY())")

Example: Calculate the number of items kingaku in the foreign key child model support item.However, it is assumed that only the item whose value is set to the item name of the child model.(Detailed method of specifying foreign key ...)

COUNT(${support_lp}, "${support_lp.name}!=null")

Example: Specify the model reference item in the condition part.Judge by ID partIf.

COUNT(${report}, "${report/type#id}==1")
COUNT(${support_lp}, "${support_lp.type#id}==1")

Example: Specify the model reference item in the condition part.When judging in the content sectionFor string match comparison EXACT functionIt is used in combination.

Reference linked items are not supported.

COUNT ($ {report}, "EXACT ($ {report/type # content}," male ")")
COUNT ($ {support_lp}, "EXACT ($ {support_lp.type # content}," male ")")
The COUNT function does not deal with "search results of your model".in this case RESULTSIZE Use the function.

SUM (repeating item, [condition])

SUM (repeating container item, [condition])

SUM (foreign key child model item, [condition])

Performs addition of specified aggregate values. The type of the item that stores the calculation result, and the type of the calculation target item should be "integer" (or other type expressing the numerical value). [Restriction ...]

Example: Find the sum of the repeat item number.

SUM(${number})

Example: Find the sum of the repeat container report/number.

SUM(${report/number})

Example: Calculate the total of the item kingaku of the foreign key association model support.(Detailed method of specifying foreign key ...)

SUM(${support_lp.kingaku})

Specify condition

Write a conditional expression in double quotation mark (double quotation).Enclose the item name in $ {...}.

Example: For the iteration item number, find the sum of positive numbers.

SUM(${number}, "${number}>0")

Example: Find the sum of the repeat container report/number.However, the report/date item in this container is only for this year.

SUM(${report/number}, "YEAR(${report/date})==YEAR(TODAY())")

Example: Find the sum of the item kingaku of the foreign key child model support.However, it is assumed that only the item whose value is set to the item name of the child model.(Detailed method of specifying foreign key ...)

SUM(${support_lp.kingaku}, "${support_lp.name}!=null")

Example: Specify the model reference item in the condition part.In the case of judging in the ID section.

SUM(${report/number}, "${report/type#id}==1")
SUM(${support_lp.kingaku}, "${support_lp.type#id}==1")
Items of the condition part are respectively in the repeating container

Example: Specify the model reference item in the condition part.When judging in the content sectionFor string match comparison EXACT functionIt is used in combination.

Reference linked items are not supported.

SUM ($ {report/number}, "EXACT ($ {report/type # content}," male ")")
SUM ($ {support_lp.kingaku}, "EXACT ($ {support_lp.type # content}," man ")")

AVERAGE (Repeat item, [Condition])

AVERAGE (repeating container item, [condition])

AVERAGE (foreign key child model item, [condition])

Calculate the average value.

When calculating the average value for an integer type item, we recommend that the type of the item storing the calculation result be "8 byte floating point".(The computation accuracy is higher than "4 byte floating point".For other description examples, restrictions SUMIt is similar to.

MAX (Repeat item)

MAX (repeating container item)

MAX (foreign key child model item)

MAX (numerical value 1, numerical value 2), MAX (numerical value stored array)

MAX (date 1, date 2), MAX (array storing dates)

MAX (character string 1, character string 2), MAX (array storing character strings)

Returns the maximum value among multiple arguments. If there are two or more arguments,ARRAYIt can be used in combination with a function.

Repeat item, repeating container item, foreign key child model can be specified as argument. You can also specify a date type and a string type for the item type.

Example: multiple_item is a repeating item

MAX(${multiple_item})

Example: For the repeat container item report/number

MAX(${report/number})

Example: For kingaku item of support model linked as foreign key child model

MAX(${support_lp.kingaku})

Example: Two items item1, item2 are of the same type

MAX(${item1}, ${item2})

Example: Multiple items item1, item2, item3 have the same type

MAX(ARRAY_INT(${item1}, ${item2}, ${item3})

MIN (Repeat item)

MIN (repeating container item)

MIN (foreign key child model item)

MIN (numerical value 1, numerical value 2), MIN (numerical value stored array)

MIN (date 1, date 2), MIN (array storing dates)

MIN (character string 1, character string 2), MIN (array storing character strings)

Returns the minimum value among multiple arguments. If there are two or more arguments,ARRAYIt can be used in combination with a function.

Repeat item, repeating container item, foreign key child model can be specified as argument. You can also specify a date type and a string type for the item type.

Example: multiple_item is a repeating item

MIN(${multiple_item})

Example: For the repeat container item report/number

MIN(${report/number})

Example: For kingaku item of support model linked as foreign key child model

MIN(${support_lp.kingaku})

Example: Two items item1, item2 are of the same type

MIN(${item1}, ${item2})

Example: Multiple items item1, item2, item3 have the same type

MIN(ARRAY_INT(${item1}, ${item2}, ${item3})

FIRST (repeating item, [condition])

FIRST (repeating container item, [condition])

FIRST (foreign key child model item, [condition])

Get the value of the first record.

It can be used in various types such as character string type/integer type/date type.Make the type of the item storing the calculation result the same as the type of the item to be calculated.(However, please specify the reference type for model reference.)

It is recommended to hide the sort button for all the items in the container repeatedly when making this setting.(If it is sorted, the last record will change.)

For other description examples, restrictions SUMIt is similar to.

LAST (Repeat item, [Condition])

LAST (repeating container item, [condition])

LAST (foreign key child model item, [condition])

Gets the value of the last record.

It can be used in various types such as character string type/integer type/date type.Make the type of the item storing the calculation result the same as the type of the item to be calculated.(However, please specify the reference type for model reference.)

It is recommended to hide the sort button for all the items in the container repeatedly when making this setting.(If it is sorted, the last record will change.)

For other description examples, restrictions SUMIt is similar to.

SLICE (Repeat item, start record number or item name, [alternative value])

SLICE (repeating container item, start record number or item name, [alternative value])

SLICE (foreign key child model item, start record number or item name, [alternative value])

Gets the value of the Nth record.

It can be used in various types such as character string type/integer type/date type.Make the type of the item storing the calculation result the same as the type of the item to be calculated.(However, please specify the reference type for model reference.)

The start record number is specified (as a second argument separated by comma) next to the item name.Record number is counted from "0".If the specified record does not exist, blanks are displayed.

The start record number specifies a fixed value.You can also use the special notation "first" and "last" to indicate the start record number."First" means the first record, "last" means the last record.

If the specified record does not exist, you can specify the value to be set.Please specify a substitute value for the third argument separated by comma.

Example: Retrieve the value of the first item of repeat item number.

SLICE(${number},0)

Example: Get the value of the first item from the beginning of the repeat container report/number.However, if the record does not exist, "None" is acquired.(The type of item to be acquired must be string type)

SLICE ($ {report/number}, first + 1, "none")

Example: Retrieve the value two (2) before the end of the item kingaku in the foreign key cooperation model support.However, when there is no record, 0 is acquired.(Detailed method of specifying foreign key ...)

SLICE(${support_lp.kingaku}, last-2, 0)

Specify the item name on behalf of the start record number

The item name can be specified as the start record number of the second argument.Enclose the item name in $ {...}.

SLICE(${report/number}, ${item}+1)

You can add simple arithmetic operations to item names, but you can not include other functions.

JOIN (repeating item, [concatenated character])

JOIN (repeating container item, [concatenated character])

JOIN (foreign key child model item, [concatenated character])

JOIN (checkbox type item, [concatenated character])

Creates a string that concatenates all item values.It can be used in various types such as character string type/integer type/date type.The type of the item storing the calculation result should be "string type".

You can specify the character at the time of concatenation as the second argument."," (Comma) is used when omitted.

When applied to items of check box type, "checked data" is consolidated.

Example: Retrieve the string concatenated with the value of the repetition item number.The delimiter uses the default value "," (comma).

JOIN(${number})

Example: Retrieve the concatenated string of values ​​of the repeat container report/staff.";" (Semicolon) is used as a delimiter.

JOIN(${report/staff},';')

Example: Get the string concatenating the value of the item companyname of the foreign key linkage model support.The delimiter uses the default value "," (comma).(Detailed method of specifying foreign key ...)

JOIN(${support_lp.staff})

DJOIN (repeating item, [concatenated character])

DJOIN (repeating container item, [concatenated character])

DJOIN (foreign key child model item, [concatenated character])

DJOIN (checkbox type item, [concatenated character])

It is a join that does not allow duplication of consolidation items.

For example, if the character string items to be concatenated are "aaa", "bbb", "aaa", "ccc", when concatenated with the delimiter ",", the character string "aaa, bbb, ccc" is generated.

DDJOIN (repeating item, [concatenated character])

DDJOIN (repeating container item, [concatenated character])

DDJOIN (foreign key child model item, [concatenated character])

DDJOIN (checkbox type item, [concatenated character])

It is a join that does not allow duplication of consolidation items.Furthermore, if there is a delimiter in the consolidation subject item, it will be split and duplicate check done.

For example, when the character string items to be concatenated are "a, b, c", "a, b, d", when concatenated with a delimiter ",", a character string "a, b, c, d" is generated I will.

RANK (repeat item, repeat item (range), [order])

RANK (repeating container item, repeating container item (range), [order])

RANK (foreign key child model item, foreign key child model item (range), [order])

Returns the ranking of the set of arguments.Although the first argument is the target item and the second argument is the item indicating the range, usually the item names of the first argument and the second argument are the same.The order of the third argument can be omitted.When it is omitted, it will be in ascending order (descending order).If you specify "1", it will be in ascending order.

Figure 1 Example of specification of RANK function

Example: Target repetitive container item staff/tensuu.Set ascending order.

RANK(${staff/tensuu}, ${staff/tensuu}, 1)

Example: Take a tensuu item of a staff model linked as a foreign key child model.Set as descending order.

RANK(${staff_lp.tensuu}, ${staff_lp.tensuu})

LARGE (Repeat item, rank)

LARGE (repeating container item, ranking)

LARGE (foreign key child model item, rank)

Returns the ranking from the larger one.The ranking of the second argument specifies a numerical value (first place, second place ..)."Top" item in the use example of RANK function corresponds.

Example: For the repeating container item staff/tensuu

LARGE(${staff/tensuu}, 1)

Example: For tensuu item of staff model linked as foreign key child model

LARGE(${staff_lp.tensuu}, 1)

SMALL (Repeat item, ranking)

SMALL (repeating container item, ranking)

SMALL (foreign key child model item, rank)

Returns the ranking from the smaller one.The ranking of the second argument specifies a numerical value (first place, second place ..)."Lowest" item in the example of using RANK function corresponds.

Example: For the repeating container item staff/tensuu

SMALL(${staff/tensuu}, 1)

Example: For tensuu item of staff model linked as foreign key child model

SMALL(${staff_lp.tensuu}, 1)

GCD (Repeat item)

GCD (repeating container item)

GCD (foreign key child model item)

GCD (array of integer type)

Returns the greatest common divisor from the integer type array of arguments.The return value is an integer type.

Example: The following expression returns "6".

GCD(ARRAY_INT(12,18))

Example: Target repeated component container item cont/value.Type is integer type only.

GCD(${cont/value})

LCM (Repeat item)

LCM (repeating container item)

LCM (foreign key child model item)

LCM (array of integer type)

Returns the least common multiple from the integer type array of arguments.The return value is an integer type.

Example: The following expression returns "6".

LCM(ARRAY_INT(2,3))

Example: Target repeated component container item cont/value.Type is integer type only.

LCM(${cont/value})

ARRAY_INT (integer type number ...)

ARRAY_LONG (numerical value of 8-byte integer type ...)

ARRAY_FLOAT (4 byte floating point number ...)

ARRAY_DOUBLE (8 byte floating point number ...)

ARRAY (character string ...)

Generate an integer type array from an integer type numeric argument (multiple entries can be described with a comma delimited). MAX, MIN By combining with a function, it is possible to return the maximum value and the minimum value.

If there is no argument, it creates an empty array.ARRAY_INT (), ARRAY_LONG (), ARRAY_FLOAT (), ARRAY_DOUBLE (), ARRAY () are available.R7.10.3

Example:

MAX(ARRAY_INT(10, 20, 30))

You can also convert a string item to an array of strings.

Example:

ARRAY(USERNAME())
ARRAY(${item1},${item2},${item3})

SORT (numerical type array)

SORT (string type array)

Sorts the array of arguments in ascending order.The return type is of the same type as the argument.

REVERSE (numeric array)

REVERSE (string type array)

Sorts the array of arguments in descending order.The return type is of the same type as the argument.

CONTAINS (numeric array, number)

CONTAINS (string type array, character string)

It returns whether the value of the second argument is included in the array of the first argument.The return value is boolean (true/false).

ISEMPTY (array)

Returns whether or not the array of the first argument is empty.The return value is boolean (true/false).

ADD (array, value)

At the end of the array of the first argument, create a new array with a value added.The return value is an array.It can be used with numeric type, string type, date type.

ADD(${array},1)

If the item array has the value "1, 0" in the repeating item, an array of "1, 0, 1" is newly generated.

GET (array, subscript)

Returns the "what" value of the array of the first argument.Array subscripts are counted from 0.If the subscript of the second argument is inappropriate (eg subscript is negative, subscript exceeds array size), subscript is treated as 0.That is, it returns the 0th value of the array.

INDEXOF (array, find value)

If the value specified by the second argument is included in the array of the first argument,What number is included from the top.Array subscripts are counted from 0.If not found, it returns -1.

LASTINDEXOF (array, find value)

If the value specified by the second argument is included in the array of the first argument,What number is included from the end.Array subscripts are counted from 0.If not found, it returns -1.

ISEQUALS (array, array)

It checks whether the value of the array of the first argument is the same as the value of the array of the second argument.The return value is boolean (true/false).It can be used with numeric type, string type, date type.

ISSAMELENGTH (sequence, sequence)

It checks whether the array of the first argument is the same as the number of elements of the array of the second argument.The return value is boolean (true/false).It can be used with numeric type, string type, date type.(If the array is null, compare it as 0 elements.)

REMOVE (array, subscript)

Returns an array from which the number of elements (numerical values ​​expressed as subscripts) are deleted from the array of the first argument.Array subscripts are counted from 0.It can be used with numeric type, string type, date type.

REMOVEELEMENT (array, value to delete)

From the array of the first argument, returns the array excluding the "deletable value" found first.If the specified value is not included, the same array as the array of the first argument is returned.Only one element is deleted.

For example, if the array element is "1, 2, 3, 2, 1" and the value to be deleted is "2", the return value array will be "1, 3, 2, 1".

REMOVEELEMENTALL (array, value to delete)

From the array of the first argument, returns an array that excludes all found values ​​to be deleted.If the specified value is not included, the same array as the array of the first argument is returned.

For example, if the elements of the array are "1, 2, 3, 2, 1" and the value to be deleted is "2", the array of return values ​​is "1, 3, 1".

UBOUND (array)

Returns the size (size) of the array.The return value is an integer type.

AGCOUNT ("item name")

"Output control> List screen> Aggregate value> Number"Specify the item name.You can acquire the "number" value obtained by the tabulation function of the list display screen.The return type is an 8-byte integer (long).

Example:

AGCOUNT("kosuu")

If the item name does not exist, or if the summary function on the list display screen is not set, -1 is returned.

AGSUM ("item name")

"Output control> List screen> Total value> Total value"Specify the item name.You can acquire the "total value" value obtained by the tabulation function of the list display screen.The return type is an 8-byte integer (long).

Example:

AGCOUNT("goukei")

If the item name does not exist, or if the summary function on the list display screen is not set, -1 is returned.

AGAVG ("item name")

"Output control> List screen> Total value> Average value"Specify the item name.You can acquire the "average value" obtained by the tabulation function of the list display screen.The return type is 8 bytes floating point (double).

Example:

AGAVG("heikin")

If the item name does not exist, or if the summary function on the list display screen is not set, -1 is returned.

AGMAX ("item name")

"Output control> List screen> Aggregate value> Maximum value"Specify the item name.You can acquire the "maximum value" value obtained by the tabulation function of the list display screen.

Example:

AGMAX("saidai")

Since the return type is java.lang.Object, explicit type conversion is necessary when using it.For example, for date type, it is as follows.

(java.util.Date)AGMAX("salesday")

For numeric type (integer), it is as follows.

(Integer)AGMAX("salestotal")

If the item name does not exist, or if the tabulation function of the list display screen is not set, null is returned.

AGMIN ("item name")

"Output control> List screen> Aggregate value> Minimum value"Specify the item name.You can obtain the value of "minimum value" obtained by the tabulation function of the list display screen.

Example:

AGMIN("saisyou")

Since the return type is java.lang.Object, explicit type conversion is necessary when using it.For example, for date type, it is as follows.

(java.util.Date)AGMIN("salesday")

For numeric type (integer), it is as follows.

(Integer)AGMIN("salestotal")

If the item name does not exist, or if the tabulation function of the list display screen is not set, null is returned.

You can specify the ID part and the content part of the model reference contained in the container. When simply specifying the item name, its "content" is the target.

Items to be specified in the condition part such as COUNT, SUM

Determined by value of child model

When handling COUNT, SUM, etc. of the foreign key child model, specifying the prefix "child model name _ lp." For the item specified in the condition part will be included in the "child model".

For example

SUM(${support_lp.kingaku}, "${support_lp.type#id}==1")

In case of the type field described in the condition part, use the one included in the child model side (support).The condition judgment at this time is done for each data of the child model.

Determined by value of parent model

If you do not give a prefix, use the items contained in the "parent model".

For example

COUNT(${child_lp}, "${child_lp.status#id}==${status#id}")

, Only child models matching the value of the status item of the parent model are eligible.

As you can see in this example, if the prefix "child model name _ lp." Is omitted and the item of the condition part is included in both the parent model and child model, the parent model item is used.
If the child model name is not specified and the designation item (status item in the above example) does not exist in the parent model and exists in the child model, the prefix "child model name _ lp." Automatically exists It is interpreted as.That is, it is judged by the item status of the child model.
  • Repeat item is an object of calculation, container, does not exist or foreign key fields, or sum, the case shall meet the conditions specified in the count operator did not have one, the calculation result is not stored.(The display area is blank.) If you forcibly set "0" even in such a case, set the item describing the calculation formula to "RequiredPlease say that.
  • Aggregate functions can not be used to set initial values ​​of search conditions.
  • "Input method> User defined checkAggregate functions can not be used.
  • You can not use aggregate functions for "job definition" - "start condition" in "Send/Receive Mail by Time Specification" or "Receive Mail by Time Designation (Data Acquisition)".
  • Aggregate functions can not be used for "form> file name".
  • Aggregate functions (COUNT, SUM, etc.) can not be included in "item part" and "condition part" specified by aggregate function.For example, the following expression produces an error.
    JOIN(JOIN(${item1}, ","), ",")
    A JOIN function is specified in the item part of the JOIN function. In such a case, please calculate each item in separate items.