How To Use Pick Lists

This document discusses how to modify or create a pick list. Pick lists show predefined choices for any field. This speeds up data entry and minimizes errors. Virtually any field in the system can have a pick list attached to it.

To view the pick list file for a field:

You must have access rights to form and field properties. These rights depend on your user level.

User level 99 does not display pick lists when you open forms, except for internal pick lists created by Collect!. Please sign in as a user level other than 99 to see how pick lists will be displayed to the end user.

WARNING: Do not modify System pick lists. System pick lists are identified by having an underscore in front of the name and no .pck extension. These lists will automatically regenerate during the upgrade process.

1. Tab into the field or click in it with your mouse.

2. Press Alt+F1, or Right click the field, and choose Properties from the popup menu. Or, select System from the top menu bar and then select Field Properties from the drop-down choices. Either way, the Access Rights form will open.

3. To make sure you have accessed the right field, look in the Name field of the Access Rights form. It must contain the name of the field whose pick list you are about to modify.

4. Look in the Pick List File field. You will see the name of the pick list file that is attached to this field. (For example, 7.pck or field1) All legacy pick lists in Collect! have the *.pck extension as they used to be stored as files. As pick lists are stored in the database, the name can be any value as long as there is a corresponding record that in the Pick Lists list. If there is no pick list on the field, Collect! will automatically generate a unique name and create the Pick List record.

5. Tab into the Pick List field and press F2 to view the contents of the pick list file, or select the small folder icon to the right of the field. The pick list Editor will open with information about the data type of the field and a list of choices formatted in a special way. When you add to this list, or modify an entry, please be sure to enter the same type of data in the same format. See details below under "Entering Choices In Your Pick List."

To create a new pick list on a field:

1. Follow Steps 1 through 3 above to open the Access Rights form and ensure that you are on the right field.

2. Select the small folder icon to the right of the Pick List File field to open the pick list Editor. You will see information about the data type of the field and a few sample entries showing you how to format your entries for pick list choices. Please be sure to enter the same type of data in the same format. See details below under "Entering Choices In Your Pick List."

To remove a pick list from a field:

1. Follow the steps above to open the Access Rights form for the field.

2. Click into the Pick List File field and use the Backspace key to delete the *.pck filename. This removes the pick list from the field, but it does not delete it. You would do this if you wanted to disassociate a pick list with a field, but keep it in the database for other fields.

3. Press OK to exit the form.

To delete a pick list file:

WARNING: BE CAREFUL as this actually deletes the pick list from the Collect! database. It will be gone.

1. Follow the steps above to open the Access Rights form for the field.

2. Select the "X" icon next to the pick list field.

3. Select the OK button when the prompt displays if you are sure that you want to DELETE the file.

4. Press OK to close the Access Rights form.

If you are not sure about actually deleting the file, you can simply remove it from the field by backspacing as described above. This leaves the file in the pick list folder but removes it from the field.

For information about using the shortcut buttons next to the Pick List File field, please see below under "Buttons On The Pick List Field."

Entering Choices In Your Pick List

1. Use any method above to navigate to a Pick List record.

2. Click in to the Entries list and either create a new entry or select an existing one.

3. The Order field will pre-fill for new entries. Edit it, if required.

When creating a new pick list, you can create an entry with a blank Key and Text. This will allow users to type in their own text instead of the pre-defined choices in the pick list.

WARNING: When using a blank entry, keep in mind that the value entered still needs to match the data type for the field. Please see below for more information.

4. Fill in the Key field. This is the value that will be stored in the database for this field.

5. Fill in the Text field. This is the text that will display to the end user on the screen when this item is selected.

You can leave this field blank and Collect! will display the Key value to the end user. This is useful, particularly in string fields where the value stored in the database will be the value displayed to the end user (Example: Yes or No). As a note, using numbers in the database is faster when doing SQL queries. Please see below on field data types for information on what can be stored in a field.

6. If desired, fill in the Red Green Blue (RGB) fields with the applicable RGB values of a color.

7. Click OK on the Pick List Entry form to save the entry and click OK on the Pick List form to save the list.

WORDS OF CAUTION

* * *

Each field has limitations regarding the kind of information it holds. If you are unsure of the fields data type, please refer to the Printable Field Help page. Find the field in the list and look at the Record Type and Field Length columns.

Text, AN means that any alphanumeric value can be stored, up to the value in the Field Length column.
Text, N means that only whole numbers can be stored in the field.
Currency means that a floating point number, up to 2 decimals can be stored.
Percent means that a floating point number, up to 3 decimals can be stored.
Date is a special case. You can place a pick list on a date field as long as the values in the Key field are dates.

In reports, when you output a field (Example: @de.ty), Collect! will display the Text value of the pick list. If you want to output the Key value, you can place it in a variable using the @PV command. See the Printing Pick Lists section below.

When running SQL queries on fields with pick lists, the result you get back will always be the database or KEY value. Your query, or post query process, will need a translation table to convert the database value into the pick list value if you want it displayed in a report.

You can join the pick list table in SQL queries to return the text value of a field. This is an advanced reporting topic.

When importing to a field with a pick list, ensure that the data you are loading will fit in the field. Also, if you are using FILL VALUE, only fill the value that will be stored in the database. For example, if you use fill value on the Mode field when importing new accounts, use a fill value of A, not ACTIVE.

* * *

Examples

1. For large text fields:

Key:
"First Choice "
"Second Choice"
"Third Choice "
" "

In this example, the choices of First Choice, Second Choice and Third Choice would appear in the drop-down list. The example also allows for users to enter their own choice. See the Words of Caution above.

If First Choice were selected, then First Choice would appear in the field. Its value would be entered in the database as well.

2. For small fields using an identifier:

In this example, the choices of "First Choice," "Second Choice," and "Third Choice" would appear in the drop-down list. If First Choice were selected, then it would display in the field and 01 would be placed into the database.

Also, in the second example, no other option could be entered because the empty entry is missing from this example. This is an easy way to ensure that only valid entries are entered in any given field.

Buttons On The Pick List Field

In the Access Rights form, the pick list field has four shortcut buttons to the right of it. Use these buttons to quickly do the following.

• Open the pick list record
  
  If the Pick List field already contains the name of a pick list, click this button to open the pick list Editor. Then you can modify the file if needed
  
  If the Pick List field is empty, selecting the Open button will open a new pick list for you.
  
  
• Print a pick list record
  
  To Print the displayed pick list select the Print button.
  
  
• Delete a pick list record
  
  To delete the pick list, select the button with the stylized X on it. This prompts you to delete the record. CAUTION!! This actually deletes the record from your Collect! database.
  
  
• Select a pick list record
  
  To attach an existing pick list to the field, click the Magnifying Glass button. Collect! will open a dialog displaying the list of pick lists in the database. NOTE -- You should already know which list you want to select.

Setting User Level Pick Lists

The Form Rights Pick List field gives you the ability to assign a special pick list to a particular user level. This is very useful for limiting the operator's choices. For instance, you can create one pick list for your system level users and another for your collectors. An example would be the Debtor Status field. Perhaps you want your collectors to be able to change the Status, but limit them to a few choices. This is where you could benefit from setting the pick list in Form Rights for the user level you have assigned to your collectors.

Printing Pick Lists

The @PV command enables you to populate a variable with the database value of a pick list. This command is useful if you need the database value for an export or SQL query.

Use @PV to:

• Return the database or numeric value of a pick list to a variable.

Syntax: @varReturn* = @PV()

Examples:

1. @varReturn* = @PV(@de.st)

Sample pick list on the state field:
"CA" California
"OR" Oregon
"WA" Washington

In normaly use, @de.st would return the readable text (California, Oregon, Washington).

@varReturn* = @PV(@de.st) will return the first column to the @varReturn variable (CA, OR, WA).