Create Rule Using a Text Line
Use a text line to create a rule that has one or more conditional expressions.
- On the bottom of the Create New Rule dialog, click the Text Rule button.
-
On the text line that appears, enter a conditional expression that has the
following format:
<columnoperand> <operator> <value_or_values>
-
<columnoperand> is either a column name or function on one or more column.
Examples of column expressions with string columns include First_Name of the Row + Last_Name of the Row or concatenate(First_Name of the Row,Last_Name of the Row).
Row in this syntax refers to a single row in the worksheet, or one record.
Examples of column operands with number columns include Weight of the Row / Height of the Row or Hours of the Row * Rate of the Row.
For more information about functions, see the Related Information.
-
<operator> is one of the operators listed in the topic in the Related Information.
-
<value_or_values> must be the same data type as the <columnoperand>.
NoteYou can take advantage of the suggestion lists that automatically appear when you start typing parts of the conditional expression. For example:-
If you type Zi, you can select Zipcode of the Row from the list of columns that appears.
The suffix of the Row is added to a column name to distinguish it from a column in a data domain. You can export worksheet rules and import them into data domains.
-
Hit the space bar, and a suggestion list of operators appear. If you start typing is not, the suggestion list changes to only a subset of operators starting with those words.
-
Hit the space bar, and a suggestion list of column names and keywords such as null and concatenate appears. If you type nu, you can select null from the list that appears.
If the column is a string data type, either enter a value surrounded by single quotation marks or select <'string'> from the suggestion list..
-
-
If you want to add another rule expression, enter one of the following before you enter
another conditional expression.
Option Description and If the record must comply with all conditions or If the record must comply with one or more conditions For example, to define a rule that Zipcode, State, and City must not be null, enter the following conditional expressions in the Rule Text. A record will fail this rule if any of the three columns is null.
Zipcode of the Row is not equal to null and State of the Row is not equal to null and City of the Row is not equal to null
-
Click outside of the text line to start rule validation.
If the rule expression is not valid, a message appears indicating the error. In this case, fix the error and validate the rule again.
If the rule expression is valid, the Save button is enabled.
- Click Save.
-
You can now take one of the following actions:
- Click the Run All Rules icon on the right bottom of the Rules screen to show the number and percentage of records that failed each rule.
- Export rules to a file or rule collection.
- Click the Data Table icon in the top left corner to return to the worksheet where you can define a filter to show the records that passed or failed each rule.