Editing Tables |
Top Previous Next |
The Edit Table Definition dialog is shown when Adding or Editing a Table from Tables Setup.
When adding a new table, you will enter the identifying information for it, select whether it's a Pick List or some other table, and start adding Data Fields to it. If you're Editing an existing table, the only things you can change are the Unique Identifier Field, Notes, and Data Field Definitions.
Displayed Name -- This would be the user-friendly name for the table, used when editing records of the table, etc. This would typically be something like "Membership Type", using capitalization and spaces between words in order to be consistent with the rest of the system.
Internal Name -- This is the name used within the system (and whenever using Advanced Customization functions for the table), so it must be unique. We also don't recommend using spaces in it. So for instance it may be something like "MemberType" to be consistent with other tables in the system, or even just "MT" to make it easier to use in expressions.
Short Name -- This is a unique 4-letter abbreviation for the table (e.g. "Memb"). While this is not currently used for the user-defined tables, there may be functionality added later that uses it for expressions, etc.
Pick List? -- Check this box if the table is to be used as a Pick List, rather than a general-purpose table. There are some differences in the way Pick Lists are handled, for instance when adding them to a dialog for user selection (see below). Also, when a Pick List is created, all of the standard fields required for Pick Lists are automatically added.
ID -- This is a unique ID number for the table. It must be a 3-digit number not used for any other table. It's primarily used to identify the record type in things like the Audit Trail.
Unique Identifier Field -- Once you have fields defined, you can select a field here to be a unique identifying field for each record. For instance if it's a Pick List, this should be "Selection Name". It means that this field must be different for every record in the table, and that this field can be used to uniquely identify the record. Note that this is not the same as an "index" in database terms. The Record ID is always used as the record index for instance when linking to the record from another table (see Relationships). The Unique Identifier should be a user-friendly text field. Note that this field is also used if Importing records of this type to see if it should enforce unique record "names".
Data Field Definitions -- This button opens the typical Data Field Definitions dialog where you can add and edit the fields of your new table. Note that if you're adding a new table, clicking this button will immediately "create" the table and make it a real system table, so no further editing of the name, ID, etc is possible.
Edit Raw Data -- You can use this to enter (or edit) data in the table, for example to set up a user-defined Pick List (like standard Pick List editing) or manage any table's contents, just like the Raw Data Table functions for standard tables. Note that if you're adding a new table, clicking this button will immediately "create" the table and make it a real system table, so no further editing of the name, ID, etc is possible.
Print -- This will simply print the fields shown on this dialog, for reference.
Special Handling of Pick Lists
In most respects a Pick List is the same as any other table, but is generally only used to present a fixed list of values/options for a particular data field, e.g. the Site Type of a Site, or the Category of a Transaction. This makes it easy to use and eliminates errors (the user doesn't have to type it every time), saves space in the database (only the item's record ID is stored, not the whole text), and makes it simpler to do filtering and reporting based on the pick list options.
When you have a Pick List table and its associated Relationship set up, you would refer to it by adding a field with the type "Pick List item (Enum)" to some other table, e.g. Customers. This allows the system to automatically know how to handle this kind of field, relationship, and table combination. One particular place this happens is when adding the new pick-list field to a custom Dialog Definition. Using the Quick-Add Field function, it will automatically add all of the code necessary to create a drop-down selection list on the dialog with all of the enabled pick list items in it, so you can use it just like any other pick-list in the system. (If you use the generic "Link to Record" field to refer to it, then you will only get a read-only text control showing the current value, so the special Pick List functionality is lost.)
Also, a Pick List has a specific set of required fields, such as Selection Name, Report Heading, Enabled, etc, that the system uses for various functions. Those fields will be added to your new table automatically if it has the "Pick List?" option selected. You can add any further fields you want, but of course it would be up to your custom code to utilize any extra fields. For more details on Pick List fields and managing Pick Lists, see that section in the documentation.
In order to manage the items in the Pick List, use the Edit Raw Data (on Tables Setup), or you can create your own Menu selection to use the ShowRawData function.
Additional Topics:
Advanced Customizations Overview & other topics