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:
- 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.
- In the Specify Calculation dialog box, enter an expression that compares the two fields.
For example, the expression
FieldA = FieldBsets the CompareFields field to 1 only if both fields have the same value; otherwise, the CompareFields value is 0.
- Add the calculation field (CompareFields) to the layout.
- Start a find request, type
1in the calculation field (CompareFields), then click Perform Find.
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:
- Create a script.
- 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 ]
If [ Table::FieldA = Table::FieldB ]
Go to Record/Request/Page [ Next ; Exit after last: On ]
- 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.