IQ Bot 11.x: Formulas

For calculative or comparative validation, in the design view, specify a formula as part of validation options for a selected field or table column.

Prerequisites

You can specify formula validation only when format of field or table column is a number.

The formulas you specify in the Designer are saved and carried over to the Validator as well. Opening a bot in the Validator will let you see and use the formulas associated with the bot.
  • In the Designer, in Preview (See extraction results) view, the formula validation flags errors as expected, to process the training documents.
  • Upload the same documents to production. The documents get processed and failed due to the same formula validation.
  • In the Validator, the same formula validation continues to flag errors to process the documents.

To specify a formula for validation, perform the following steps.

Procedure

  1. Select the validation type as Formula from the Validate drop-down menu.
  2. Type the formula of your choice and click Define to save the formula.
  3. To write a formula, you can use basic arithmetic, comparative, logical, and functional operations. Validation rules can be built by combining these operations with different fields and table columns.
  4. Fill in the fields in the form, as appropriate.
    Table 1. Mathematical Operators
    Operations Description Syntax
    + Addition Field/Column_Name1 + Field/Column_Name2
    - Subtraction Field/Column_Name1 - Field/Column_Name2
    * Multiplication Field/Column_Name1 * Field/Column_Name2
    / Division Field/Column_Name1 / Field/Column_Name2
    Table 2. Comparative Operators
    Operations Description Syntax
    == Equal To

    [Current Field/Column Name] == [expression comprised of one or more field/column name or fixed numeric values]

    For example, AMOUNT == MUL(QUANTITY, UNIT_PRICE)
    >= Greater than or Equal To

    [Current Field/Column Name] >= [expression comprised of one or more field/column name or fixed numeric values]

    For example, TOTAL_BILL_AMOUNT >= AMOUNT_PAID
    <= Less than or Equal To

    [Current Field/Column Name] <= [expression comprised of one or more field/column name or fixed numeric values]

    For example, AMOUNT_PAID <= TOTAL_BILL_AMOUNT
    > Greater Than

    [Current Field/Column Name] > [expression comprised of one or more field/column name or fixed numeric values]

    For example, BILL_AMOUNT > 0
    < Less Than

    [Current Field/Column Name] > [expression comprised of one or more field/column name or fixed numeric values]

    For example, DISCOUNT_PERCENTAGE < 100
    != Not Equal To

    [Current Field/Column Name] != [expression comprised of one or more field/column name or fixed numeric values]

    For example, DEBIT_AMOUNT != 0
    Table 3. Logical Operators
    Operations Description Syntax
    &&

    And: Field/Column is valid if all conditions are true

    <Current Field/Column Name> <operator 1> <expression 1> && <Current Field/Column Name> <operator 2> <expression 2>

    For example, DISCOUNT_PERCENTAGE >= 0 && DISCOUNT_PERCENTAGE <=100

    ||

    Or: Field/Column is valid if any one of the given conditions is true

    <Current Field/Column Name> <operator 1> <expression 1> || <Current Field/Column Name> <operator 2> <expression 2>

    For example, DISCOUNT_PERCENTAGE >= 0 || DISCOUNT_PERCENTAGE == 'Net'

    !

    Not: Converts a true expression to false and also the other way round

    !<expression>

    For example, !(AGE < 18) ==> valid when AGE is not less than 18

    Table 4. Functional Operators
    Operations Description Syntax
    SUM

    Summation: gives result of addition of one or more field/column/fixed-number values

    SUM(n1,n2,....,nN)

    For example, SUM(SUB_TOTAL, SERVICE_TAX, EDUCATION_CESS) ==> equivalent to SUB_TOTAL plus SERVICE_TAX plus EDUCATION_CESS
    SUB

    Subtraction: gives result of subtraction of one or more field/column/fixed-number values from the first specified value

    SUB(n1,n2,....,nN)

    For example, SUB(SUB_TOTAL, TOTAL_DISCOUNT, ADJUSTMENTS) ==> equivalent to SUB_TOTAL minus TOTAL_DISCOUNT minus ADJUSTMENTS
    MUL

    Multiplication: gives result of multiplication of one or more field/column/fixed-number values

    MUL(n1,n2,....,nN)

    For example, MUL(QTY_IN_BOX, UNIT_PRICE, SHIPPED_BOXES) ==> equivalent to QTY_IN_BOX multiply-with UNIT_PRICE multiply-with SHIPPED_BOXES
    DIV

    Division: gives result of division of one or more field/column/fixed-number values from the first specified value

    DIV(n1,n2,....,nN)

    For example, DIV(AMOUNT, SHIPPED_BOXES, UNIT_PRICE) ==> equivalent to AMOUNT divide-by SHIPPED_BOXES divide-by UNIT_PRICE
    COLSUM

    Sum of a given Column in a table: gives result after evaluating given expression for each row of specified table and adding them all together

    COLSUM("<table-name>", "<expression to evaluate for each row of specified table-name>")

    For example, FINAL_TOTAL == COLSUM("LINE_ITEMS", "MUL(QTY, UNIT_PRICE)")

    Say there are 3 rows for table LINE_ITEMS, then FINAL_TOTAL should be equal to MUL(QTY1, UNIT_PRICE1) + MUL(QTY2, UNIT_PRICE2) + MUL(QTY3, UNIT_PRICE3)

    where, QTY1 is QTY value in row 1, similarly UNIT_PRICE1 is UNIT_PRICE value in row 2, and so on for other rows as well.
    Tip: We recommended using functional operators instead of mathematical operators when validating fields and tables for better accuracy.
    When formulating an expression remember the following:
    • All function names must be in capital letters.
    • All formulas must result in either a true or false validation.
    • Field and column names are case sensitive when used within formulas. If field is defined as Qty in design, using qty or QTY in formula results in an invalid formula.
    • For variable declaration or manipulation, ensure not to use certain keywords that are reserved for formulating an expression.
SUM, SUB, DIV, MUL, COLSUM, IF.

Next steps

If the expression evaluated is false, a validation error is raised.
Tip: Ensure you do not write "=" (single equal) where you intend to use "==" (double equal). For example, AMOUNT == MUL(QUANTITY, UNIT_PRICE) is valid. But AMOUNT = MUL(QUANTITY, UNIT_PRICE) is not a valid resultant formula.