Description
Determines if the specified expression is valid for a specified picklist field. The picklist field is indicated by specifying the database, table name, and field name. The parent value is only used if the picklist field has a parent.
Syntax
ISPICKLISTVALUE ( SourceValue, "connection", "table", "picklist_field", ParentValue )
| Parameter | Description |
|---|---|
| SourceValue | The value to test. |
| connection | The name of the connection where the picklist table resides. If the connection is Scribe Internal Database, the SCRIBE user must have at least SELECT privileges on the picklist table. |
| table | The name of the object or table for the picklist. |
| picklist_field | The name of the picklist field. |
| ParentValue | The optional parent value of the SourceField. The parent value parameter is optional and only used if the picklist field has a parent. |
Remarks
This function is useful for testing the validity of picklist values against the target to help with setting a default or to skip the row. See examples below.
Note: You can use Field Properties in the Workbench to view the list of available picklist choices. ISPICKLISTVALUE validates against the list you see in Field Properties.
Some adapters have a feature that allows you to turn on picklist validation by selecting a check box. See your adapter documentation for details.
If your site uses a picklist that contains numeric data on both sides of the picklist, Insighte reads
Examples
Example: Validity of a PickList Value
The following example uses an object named CONTACTS in the target database. The following table shows the values and descriptions for the acceptable picklist choices for the TITLE field.
| Value | Name/Description |
|---|---|
| 1 | Owner |
| 2 | President |
| 3 | Manager |
| 4 | Executive Director |
| 5 | Principal |
| 6 | Unknown |
As shown here, some adapters will display a value and a name/description in the Field Properties.
ISPICKLISTVALUE can validate either a picklist value or description. Consider the following formula:
ISPICKLISTVALUE(S2, "Contacts”, "CONTACTS", " TITLE ", "")
Testing for a description, the function returns TRUE when S2 = "President” and FALSE when S2 = "CEO”:
Testing for a picklist value, the same function returns TRUE if S2 = 3 and false if S2 = 7.
Example: Testing to Set a Valid Default
In this example you are working with restricted picklist fields, which will reject the row if the value is invalid. This formula allows you to set a valid default value and avoid the rejected row by first testing the validity of the value. This formula sets the output value to "Unknown" if the value passed in from the source is not a valid picklist choice.
IF( ISPICKLISTVALUE(S5, "Target", "Account", "Type", ""), S5, "Unknown" )
Example: Testing to Skip the Row
In this example you are working with unrestricted picklist fields, which would allow your row to go in with a value that is not on the target application’s list. It may be an undesirable situation to add new records that have new and unexpected picklist choices. This formula allows you to reject the row if the value passed in from the source is not a valid picklist choice.
IF( ISPICKLISTVALUE(S5, "Target", "Account", "Type", ""), S5, FAILROWMSG("Invalid picklist choice"))
See also