AX1348

About picklist tables

Picklist tables hold lists of items from which users can make selections. For example, you might want users to select from a list of defined categories or status codes, and these lists can be defined as picklist tables. Picklist tables are a special type of reference table, specially designed to be used as picklists.

Picklist tables have the following advantages over using regular reference tables for the same purpose:

Picklist tables can be easily created and edited using the Web Client Table Manager. This makes it easier for non-technical users to manage picklist tables and their values.
Refresh variables provide special support for selecting values from picklist tables. This feature makes it easy to present users with custom text values for selection, but save those selections back to the database using standard integer codes.
File groups have a special picklist variable type, allowing enhanced control over how the picklist can be used within the file group.

Additionally, some systems may have been using document reference tables to maintain picklist-style data, in order to leverage the easy-to-use spreadsheet interface for editing picklist values. Picklist tables also provide an easy-to-use interface, and additionally picklist tables can be used as lookup tables for other columns (whereas document reference tables cannot).

Picklist column structure

Picklist tables have a predefined structure of three columns. All of these columns are created by default when the picklist table is created, and cannot be deleted.

Name	Column Properties	Description
Code	Integer Key Column	Required. Defines a numeric code for each record. Each code must be a unique positive integer. When users select items from a picklist, items are returned and stored using this numeric code.
Value	String (50) Alternate Key Column	Required. Defines a text value for each record. Each text value must be unique. Users select items from the picklist using these values.
Description	String (1023)	Optional. Defines a description or explanatory text for each record. When users select items from a picklist, you can optionally display this text to help users choose the correct values.

Name

Column Properties

Description

Code

Integer

Key Column

Required. Defines a numeric code for each record. Each code must be a unique positive integer.

When users select items from a picklist, items are returned and stored using this numeric code.

Value

String (50)

Alternate Key Column

Required. Defines a text value for each record. Each text value must be unique.

Users select items from the picklist using these values.

Description

String (1023)

Optional. Defines a description or explanatory text for each record.

When users select items from a picklist, you can optionally display this text to help users choose the correct values.

The main column properties of picklist columns cannot be modified, such as column name, data type, key column status, etc. Only minor column properties such as read-only status can be modified.

Picklist table editor

Picklist tables have a special editor in the Web Client, designed to make it easy for authorized users to create new picklist tables and to edit picklist values. For more information, see Managing picklist tables.

Picklist tables can also be created and edited within the Desktop Client if needed, using the normal table creation and editing features.

When using the Web Client editor, only the three required columns of Code, Value, and Description are visible and editable. Although it is possible to add other columns to a picklist table using the Desktop Client, these columns have limited usefulness and must be populated using Open Table in Spreadsheet or Save Type 1 instead of using the Web Client editor.

Using picklist tables

Picklist tables have a set structure that combines an integer code key column with a string value column. You can define the string value as needed while maintaining the standard integer code. When users select items from the picklist, you can display the text values but save back the selection as the integer code.

In order to display picklists to users, you can use any selection feature that is available to reference tables. In additional, several features provide special support for picklist tables:

ComboBox and Grid refresh variables support special syntax to specify a picklist table as the source of data, to make it easy to set up and use refresh variables with picklist tables.
File group picklist variables support optional properties to store useful information about the use of the picklist table in the file group. You can associate the picklist with a column in the plan code table, flag the picklist as required, and conditionally enable the picklist based on other related values. Using the functions GetFileGroupVariableProperty and GetFileGroupVariableEnablement, you can return this information into a template or other file, and then use this information to dynamically configure features in the template (such as custom save validation for a required picklist).

Picklist table restrictions and limitations

Picklist tables follow the same general rules of reference tables, with some additional restrictions. In addition to the pre-set column structure, the following limitations and restrictions apply to picklist tables:

Picklist tables cannot be plan code tables.
Picklist tables cannot have column hierarchies, column sequences, or calculated fields.

When filtering a list of tables by table classification, picklist tables will display along with the other reference tables, unless they are being omitted from the list as invalid selections.

Generally speaking, whenever a feature says a reference table is valid for use, a picklist table can be used as well. Any exceptions are noted in the specific feature documentation.

Picklist table security

By default, non-admin users do not have access to the data in picklist tables. When a new picklist is created, you must configure read-only or read / write access to the table as needed (unless the picklist is assigned to a table type to inherit permissions).

Note the following special considerations for picklist table security:

Because picklist tables are comprised of simple lists and do not normally contain sensitive information, you may want to configure read-only access on the Everyone role to simplify security. Make sure to enable Specify custom write access and then leave the filter blank so that users do not have read / write access, because all users with read / write access can modify picklist values using the Web Client Table Manager. Read / write access should be restricted to a small subset of users who define and administer the picklist values.
Picklist tables can belong to picklist table types, for purposes of easily granting full read or write access to a set of related tables. You might create different table types to hold picklist tables for different types of planning (Capital, Budget) or for different facilities or entities. You can then grant access at the table type level, to be inherited by all tables in the table type.
A special security permission, Administer Picklists, is available to give users rights to create and manage picklists in the Web Client Table Manager. Users with this permission can do the following:
- Create new picklist tables as needed. Note that these picklists must be assigned to table types that the user has access to, so that the user will have permission to the new table once it is created.
- Modify picklist table properties and delete picklist tables. The user must have at least read-only access to the table in order to perform these actions.
- These special permissions only apply to the Web Client Table Manager. Administer Picklists users cannot create or manage picklist tables in the Desktop Client.
Any user with read / write access to the table can edit picklist table values using the Web Client Table Manager. This is different behavior from other tables, where having read / write access to a table does not automatically give the user an interface to view and edit the table values. Because of this difference, you should not give a user read / write access to a picklist table unless you want them to be able to edit the values in the table.