Finding records with equal values in different fields

You can find records in which different fields in a record have the same value. For example, you want to find all orders for which the paid amount is the same as the billed amount. Use a calculation field or a loop in a script to compare the values of the two fields in each record.

To find records with equal values using a calculation field:

1. In the table containing the fields you want to compare, create a calculation field whose result type is Number. See Defining calculation fields.

   For example, create a calculation field named CompareFields.

2. In the Specify Calculation dialog box, enter an expression that compares the two fields.

   For example, the expression FieldA = FieldB sets the CompareFields field to 1 only if both fields have the same value; otherwise, the CompareFields value is 0.

3. Add the calculation field (CompareFields) to the layout.

   See Placing and removing fields on a layout.

4. Start a find request, type 1 in the calculation field (CompareFields), then click Perform Find.

   See Making a find request.

The found set is all records for which the calculation field CompareFields equals 1—that is, all records for which FieldA has the same value as FieldB.

To find records with equal values using a script:

1. Create a script.

   See Creating and editing scripts.

2. Add the following steps to the script, where Table::FieldA and Table::FieldB are the two fields you want to compare on a particular layout (in this case, "My Layout").
   Copy

   Go to Layout [ "My Layout" (Table) ]
   Show All Records
   Go to Record/Request/Page [ First ]
   Loop [ Flush: Always ]
       If [ Table::FieldA = Table::FieldB ]
           Go to Record/Request/Page [ Next ; Exit after last: On ]
       Else
           Omit Record
       End If
   End Loop

3. Perform this script.

The script shows all records and goes to the first record. Then for each record, it compares the two fields and omits the records where FieldA is not equal to FieldB.

Note  Instead of using an equal sign in the calculation field, or in the If script step above, you can use any comparison operator or logical operator to compare fields. For example, use Table::FieldA > Table::FieldB to find records in which FieldA is greater than FieldB. See Comparison operators and Logical operators.