Procedure: Creating tables

To define a new table, point to new in the Explorer menu and choose Table. The Table Definition window will appear.

  1. Name the table.

Three names are required for the table.

The name entered in the Table Name field is the table name that is used in scripts. The display name is how the table name will be displayed on the screen in the application. The physical name is the name under which the table is stored by the operating system or database. By default, this name can be up to eight characters long.

If you’re developing an application for an operating system that supports long file names, and you’ve marked the Allow Long Physical Names option in the Options window, you can enter table physical names up to 28 characters long.

  1. Select the table series.

The table series allows you to group related tables in a Dexterity application together using series categories like Sales, Financial and System. You will use these series groupings when you use the Report Writer, set up pathnames, and perform table maintenance routines.

  1. Select the database type.

The database types specifies how Dexterity will store the table. You can choose memory-based tables, which act just like c-tree tables, with the exception that they are stored in memory, rather than written to a physical disk.

If you are unsure which database type to use, or your application will be used on different platforms, choose Default; your application will use the default database type. The default database type is specified by the DatabaseType defaults file setting, the Table_SetDBType() function, or the command line option included when Dexterity or the runtime engine is started.

  1. Insert the fields that will be stored in the table.

In the Global Fields list, highlight each field you want to include in the table and click Insert. The field names will be displayed in the Table Fields list in the order they will be saved in the table you’re defining. The order isn’t critical, but to maximize performance, group string fields next to each other in the Table Fields list.

  1. Define table keys.

A table key is the field or group of fields that are used to sort and locate specific records in the table. The table keys are always composed of fields from the table. Each table must have at least one key and can have several keys. The fields in the keys specify how you want to sort and retrieve information.

For example, if a table contains the fields Name, Address, City, State and ZIP Code, one table key could be Name. Using this key, a specific address could be retrieved by name. Another key could be the ZIP Code. This key would allow you to retrieve records from the table based upon the ZIP Code.

To create a key, click New in the Table Definition window. The Key Definition window will open, as shown in the following illustration:

Enter the name of the key. This name will be used in scripts, so you may want to substitute underscores for spaces.

In the Table Fields list, highlight each field you want to include in the key and click Insert. The key segment will be inserted after the item currently selected in the Key Segments list. If no item is selected in the Key Segments list, the new segment will be inserted as the first item.

If the key has more than one segment, insert the key segments in the order you want the information in the table to be sorted. Records will be sorted primarily by the first key segment. Records that are identical for the first key segment will be sorted again by the second key segment, and so on.

Select the options for the key. The key options you can set depend upon the database type you specified in the Table Definition window. If the database type for the table is c-tree or Pervasive.SQL, you can specify P.SQL/c-tree key options. If the database type is SQL, you can specify SQL key options. If the database type is Default, you can specify both sets of options. Refer to Key options for a detailed description of the key options you can specify.

If necessary, select options for key segments. The following table lists the common segment options.





Sorts records in descending rather than ascending order for this segment.

Ignore Case

Sorts records without regard to case.

Use the No Length Byte and No Combining options only if you’re creating an application that integrates with Great Plains Accounting files. These options are not intended for use in other Dexterity-based applications.

Click OK to save the key. The Table Definition window will re-appear.

  1. Specify options for the table.

Typically, the only option you specify for the table is whether to allow active locking. Mark the Allow Active Locking option in the Table Definition window to allow active locking. If you don’t mark this option, any change or edit table statements that reference this table and include the lock keyword will produce errors when you attempt to compile them.

We strongly recommend that you avoid using active locking in your application. Application performance can degrade when active locking is used, especially when your application uses a SQL database.

Changing the default settings of the Use 4 byte header and Use Restriction options is necessary only if you’re integrating with Great Plains Accounting files. We strongly recommend that you don’t unmark the Use 4 byte header option or mark the Use Restriction option for your Dexterity-based application.

  1. Define any table relationships.

Table relationships are created between tables that have fields in common. These relationships allow the Dexterity Report Writer to select fields from all of the related tables and use them on a single report. For more information about creating table relationships, refer to Table relationships.

  1. Encrypt fields (optional).

Encryption is used for fields such as password fields to prevent users from viewing passwords by looking at the table with a file editing utility. To encrypt a field, select it in the Table Fields list and mark the Encrypt option.

Click OK to save the table definition and close the Table Definition window.

Documentation Feedback