Skip to main content

Setting examples and output results of calculation-formulas and edit-formulas (text field/numeric field)

This section describes how to set basic calculation-formulas and edit-formulas for text fields and numeric fields, and explains the behavior of the field buffer in each case.

For text fields

Examples with the following conditions are used to explain how to set basic calculation-formulas and edit-formulas in a text field, and to explain the field buffer.

Specify data by enclosing it in double quotation marks (" ").

Prerequisite

When the following data are entered from the higher-level application

  • "John Smith" is entered in the "NAME" field

  • "1" is entered in the "KBN" field

A dummy field (text field) "DUM" is created as a reference field.

Input data only

To print the input data in the "NAME" field without making any changes

  • Usage examples

    • Input data: "John Smith"

    • Calculation-formula

    • Edit-formula

    • Field buffer: "John Smith"

    • Printing result: "John Smith"

Input data+Calculation-formula (input field)

To edit the input data using the calculation-formula, and print the result

  • Usage examples

    • Input data: "John Smith"

    • Calculation-formula: "Mr. "+NAME

    • Edit-formula　

    • Field buffer: "Mr. John Smith"

    • Printing result: "Mr. John Smith"

Input data+Edit-formula (input field)

To edit the input data using the edit-formula, and print the result

Reference

Because the result of an edit-formula does not replace the field buffer value, "John Smith" is stored in the field buffer.

  • Usage examples

    • Input data: "John Smith"

    • Calculation-formula

    • Edit-formula: "Mr. "+NAME

    • Field buffer: "John Smith"

    • Printing result: "Mr. John Smith"

Input data+Calculation-formula (using edit functions)

To edit the data in the field "NAME" with the edit function

  • Usage examples

    • Input data: "John Smith"

    • Calculation-formula: IF(KBN="1","Mr. "+NAME,"")

    • Edit-formula

    • Field buffer: "Mr. John Smith"

    • Printing result: "Mr. John Smith"

Input data+Edit-formula (using edit functions)

To edit the data in the field "NAME" with the edit function

  • Usage examples

    • Input data: "John Smith"

    • Calculation-formula

    • Edit-formula: IF(KBN="1","Mr. "+NAME,"")

    • Field buffer: "John Smith"

    • Printing result: "Mr. John Smith"

Input data+Calculation-formula (using edit functions)+Edit-formula

To edit the data in the field "NAME" with the edit function

  • Usage examples

    • Input data: "John Smith"

    • Calculation-formula: "Mr. "+NAME

    • Edit-formula: IF(KBN="1",NAME,"")

    • Field buffer: "Mr. John Smith"

    • Printing result: "Mr. John Smith"

Specify the output data as a fixed value (calculation-formula)

To specify the output data in the "DUM" field using the calculation-formula

  • Usage examples

    • Input data: ""

    • Calculation-formula: "Calculation-formula"

    • Edit-formula

    • Field buffer: "Calculation-formula"

    • Printing result: "Calculation-formula"

Specify the output data as a fixed value (edit-formula)

To specify the output data in the "DUM" field using the edit-formula

  • Usage examples

    • Input data: ""

    • Calculation-formula

    • Edit-formula: "Edit-formula"

    • Field buffer: ""

    • Printing result: "Edit-formula"

Specify the output data as a fixed value (calculation-formula+edit-formula)

To specify the output data in the "DUM" field with the calculation-formula and the edit-formula

Reference

As "Calculation-formula" is specified in the calculation-formula of the "DUM" field, "Calculation-formula" will be stored as the value of the field buffer. However, as "Edit-formula" is specified in the edit-formula, the value of the edit-formula will be output as the printing result.

However, if you refer to the "DUM" field from another field, "Calculation-formula" will be retrieved as the value, since the value of the field buffer will be referenced.

  • Usage examples

    • Input data: ""

    • Calculation-formula: "Calculation-formula"

    • Edit-formula: "Edit-formula"

    • Field buffer: "Calculation-formula"

    • Printing result: "Edit-formula"

Refer to the output data from other fields (calculation-formula)

To refer to the "NAME" field data from the "DUM" field and print the data

  • Usage examples

    • Input data: ""

    • Calculation-formula: NAME

    • Edit-formula

    • Field buffer: "John Smith"

    • Printing result: "John Smith"

Refer to the output data from other fields (edit-formula)

To refer to the "NAME" field data from the "DUM" field and print the data

  • Usage examples

    • Input data: ""

    • Calculation-formula

    • Edit-formula: NAME

    • Field buffer: ""

    • Printing result: "John Smith"

Output date

Retrieve the output data from edit functions (calculation-formula)

To output the system date in the "DUM" field as text-type data

  • Usage examples

    • Input data: ""

    • Calculation-formula: DTOC(DATE())

    • Edit-formula

    • Field buffer: "2010/01/10"

    • Printing result: "2010/01/10"

Retrieve the output data from edit functions (edit-formula)

To output the system date in the "DUM" field as text-type data

  • Usage examples

    • Input data: ""

    • Calculation-formula

    • Edit-formula: DTOC(DATE())

    • Field buffer: ""

    • Printing result: "2010/01/10"

Retrieve the output data from edit functions (calculation-formula)+edit-formula

To acquire the system date in the "DUM" field using the calculation-formula, and output after converting it to text-type data by using the edit-formula

Reference

As DATE is specified in the calculation-formula, the calculation result of DATE "20100110" will be saved as the field buffer value of the "DUM" field.

So, you can output the value as text-type data in YYYY/MM/DD format (such as "2010/01/10") by referring to the "DUM" field as a DTOC function parameter in the edit-formula.

  • Usage examples

    • Input data: ""

    • Calculation-formula: DATE()

    • Edit-formula: DTOC(DUM)

    • Field buffer: "20100110"

    • Printing result: "2010/01/10"

For numeric fields

Examples with the following conditions are used to explain how to set basic calculation-formulas and edit-formulas in a numeric field, and to explain the field buffer:

Prerequisite

When the following data are entered from the higher-level application

  • "1000" is entered in the "UNIT_PRICE" numeric field

  • "5" is entered in the "QUANTITY" numeric field

  • "1" is entered in the "KBN" text field

A dummy field (numeric field) "DUM" is created for calculation.

Reference

In the edit-formula, the calculation results must be specified as text-type data. Therefore, when specifying numeric data, use the FORMAT function to convert to text-type data.

Input data only

To print the input data in the "UNIT_PRICE" field without making any changes

  • Usage examples

    • Input data: 1000

    • Calculation-formula　

    • Edit-formula　

    • Field buffer: 1000

    • Printing result: "1000"

Input data+Calculation-formula (calculated using the input field)

To edit the input data using the calculation-formula, and print the result

  • Usage examples

    • Input data: 1000

    • Calculation-formula: UNIT_PRICE*2

    • Edit-formula

    • Field buffer: 2000 (1000x2)

    • Printing result: "2000"

Input data+Edit-formula (calculated using the input field)

To edit the input data using the edit-formula, and print the result

Reference

Because the result of an edit-formula does not replace the field buffer value, the entered value of "1000" is stored in the field buffer.

  • Usage examples

    • Input data: 1000

    • Calculation-formula

    • Edit-formula: FORMAT(UNIT_PRICE*2,"Z,ZZ9")

    • Field buffer: 1000

    • Printing result: "2,000" (1000x2)

Input data+Calculation-formula (calculated by referring to data in a different field)

To print the calculated values in the "UNIT_PRICE" field by using the input data in the "QUANTITY" field

  • Usage examples

    • Input data: 1000

    • Calculation-formula: UNIT_PRICE*QUANTITY

    • Edit-formula

    • Field buffer: 5000 (1000x5)

    • Printing result: "5000"

Input data+Edit-formula (calculated by referring to data in a different field)

To print the calculated values in the "UNIT_PRICE" field by using the input data in the "QUANTITY" field

  • Usage examples

    • Input data: 1000

    • Calculation-formula

    • Edit-formula: FORMAT(UNIT_PRICE*QUANTITY,"Z,ZZ9")

    • Field buffer: 1000

    • Printing result: "5,000" (1000x5)

Input data+Calculation-formula (calculated by using edit functions)

To edit the input data in the "UNIT_PRICE" field using edit functions

  • Usage examples

    • Input data: 1000

    • Calculation-formula: IF(KBN="1",UNIT_PRICE*QUANTITY,0)

    • Edit-formula

    • Field buffer: 5000 (1000x5)

    • Printing result: "5000"

Input data+Edit-formula (calculated by using edit functions)

To edit the data in the "UNIT_PRICE" field using edit functions

Reference

In the edit-formula, the calculation results must be specified as text-type data. Therefore, when specifying numeric data, use the FORMAT function to convert to text-type data.

  • Usage examples

    • Input data: 1000

    • Calculation-formula

    • Edit-formula: IF(KBN="1",FORMAT(UNIT_PRICE*QUANTITY,"Z,ZZ9"),FORMAT(0,"Z,ZZ9"))

    • Field buffer: 1000

    • Printing result: "5,000" (1000x5)

Input data+Calculation-formula (using edit functions)+Edit-formula (using the buffer value itself)

To edit the input data in the "UNIT_PRICE" field using edit functions

  • Usage examples

    • Input data: 1000

    • Calculation-formula: IF(KBN="1",UNIT_PRICE*QUANTITY,0)

    • Edit-formula: FORMAT(UNIT_PRICE,"Z,ZZ9")

    • Field buffer: 5000 (1000x5)

    • Printing result: "5,000"

Specify the output data as a fixed value (referenced using calculation-formula)

To specify the output data in the "DUM" field using the calculation-formula

  • Usage examples

    • Input data

    • Calculation-formula: ROUND((UNIT_PRICE*1.05),0)

    • Edit-formula

    • Field buffer: 1050

    • Printing result: the value of "1050" (1000x1.05) rounded to a whole number

Specify the output data as a fixed value (referenced using edit-formula)

To specify the output data in the "DUM" field using the edit-formula

Reference

Because the result of an edit-formula does not replace the field buffer value, the default value of "0" will be stored when a calculation-formula is not specified.

  • Usage examples

    • Input data

    • Calculation-formula

    • Edit-formula: FORMAT(ROUND((UNIT_PRICE*1.05),0),"Z,ZZ9")

    • Field buffer: 0

    • Printing result: the value of "1,050" (1000x1.05) rounded to the first decimal place

Specify the output data as a fixed value (calculation-formula+edit-formula)

To specify the output data in the "DUM" field with the calculation-formula and the edit-formula

Reference

As "QUANTITY" * "UNIT_PRICE" is specified in the calculation-formula of the "DUM" field, 5000 (QUANTITY * UNIT_PRICE) will be stored as the value of the field buffer. However, as "UNIT_PRICE" * 1.05 is specified in the edit-formula, the calculation results specified in the edit-formula will be output as the printing result.

If you refer to the "DUM" field from another field, the value of 5000 (QUANTITY * UNIT_PRICE) will be retrieved as the value, since the value of the field buffer will be referenced.

  • Usage examples

    • Input data

    • Calculation-formula: QUANTITY*UNIT_PRICE

    • Edit-formula: FORMAT(ROUND((UNIT_PRICE*1.05),0),"Z,ZZ9")

    • Field buffer: 5000 (1000x5)

    • Printing result: the value of "1,050" (1000x1.05) rounded to the first decimal place

Specify the output data as a fixed value (calculation-formula+edit-formula)

To specify the output data in the "DUM" field with the calculation-formula and the edit-formula

Reference

If the name of the field itself is specified in the edit-formula, calculations will be done based on the field buffer value of the field itself.

As "QUANTITY" * "UNIT_PRICE" is specified in the calculation-formula of the "DUM" field, 5000 (QUANTITY * UNIT_PRICE) will be stored as the value of the field buffer.

As the name of the field itself is specified in the edit-formula, calculations will be done based on the calculation results specified in the calculation-formula (field buffer value) and "5,250" will be printed as a result.

  • Usage examples

    • Input data

    • Calculation-formula: QUANTITY*UNIT_PRICE

    • Edit-formula: FORMAT(ROUND((DUM*1.05),0),"Z,ZZ9")

    • Field buffer: 5000 (1000x5)

    • Printing result: the value of "5,250" (5000x1.05) rounded to the first decimal place

In this section: