Page tree

Pick List

Pick list on fields supersede and enhance former "Discrete set of values". List of values can be bound to a Standard field. Those will be used as a template when editing or searching the field. Typical use case is when one field value is heavily used on multiple records through the database. This is exactly what you would expect from discrete set of values. But there are other advanced options to be setup on a field.

The pre-defined values can be selected from the list if you query on the field in both, Query builder and Form based query. Also a value can be easily selected when editing or inserting a row.

The Pick list is configurable in schema editor for a selected field:

Under Edit Pick List... button you will find a dialog with several options.

Pick List Options

Field's existing values

This is the easiest option. Pick list will access all the possible values in the field and make them available for selection. You need to load the available values from the database by clicking the "Load" button.

As it would be too tedious to pick the value from the list while for example building a query, Pick List functionality also supports context searching for the values by simply typing.

Constant list of values

This option enables to set up only a discrete set of values to choose from. For example, setting up a Pick List for Acceptors field, which contains about 90 different numerical values, only those defined will be available in the Pick List.

Values loaded by Groovy script

Setting up the Pick List with values loaded by Groovy give us more possibilities. One of the neat features is "aliasing" the values. See the example Groovy below.

loadValues = { field, pickList ->
    return [
        [1, "one"], 
        [2, "two"], 
        [3, "three"], 
        [4, "four"], 
        [5]
    ]
}

As you can see in the example, which was used for Acceptors field in the Demo Database, the code assigns only 5 values to be available (in analogous way to Constant List of values option). First four will be available in the Pick List by their string alias. The last one will be available as the value itself (in this case 5).

The interface also offers a possibility to validate your Groovy script by clicking the "Test script" button.

Other groovy examples are simple loading of values, which basically supplies the functionality of Constant List of Values

// 1. Just simple list of values - numbers for integer fields OR Strings for text fields
loadValues = { field, pickList ->
    return [ 1, 2, 3, 10..20, 30 ]
    // Or Strings for text fields:
    // return [ "one", "two", "three", "four", "five" ]
}

 

Next groovy example also allows to create obsoleted items. The createItem method is also an alternative way to create "aliased" values as seen in the first groovy example.

// 3. Using create method - allows to create also obsoleted items
loadValues = { field, pickList ->
    return [ 
        pickList.createObsoletedItem(0, "zero"), // Only for querying, but not allowed in inserting rows
        pickList.createItem(1, "one"), 
        pickList.createItem(2, "two"), 
        pickList.createItem(3, "three"), 
        pickList.createItem(4, "four"),
        pickList.createItem(5)
    ]
}

 

The last example shows a way how to load values from other field from different or the same entity. This can be useful if you have an entity with a field used only to store the list of values for pick list. By this, you can also share the same pick list definitions in various different entities simply by loading the values from one centralised place by using this script

// 4. Load values from other field from other or the same entity. See comments below.
//    a. Fill correct names of entity and field below
//    b. In the case of the same entity just change the 2nd line to:
//          def entity = field.entity
loadValues = { field, pickList ->
    def schema = field.entity.schema
    def entity = schema.entities.items.find { it.name == 'Wombat activities' } // Fill the correct entity name
    // def entity = field.entity // If the field is from the same entity just use this line instead
    def field2 = entity.fields.items.find { it.name == 'BIO.SPECIES' } // Fill the correct field name
    def edp = DFEntityDataProviders.find(entity)
    return edp.retrieveDistinctValuesForField(field2.id)
}

 

 Other highlight of the functionality is that when "In list" query is used, the Pick List changes to checkboxes enabling multiple selection.