Clarion Community Help

This section teaches you how to:

·Create new table definitions.

·Create a pool of column definitions from which new column definitions can be easily derived.

·Create additional column definitions for all tables.

Defining New Tables

After copying and modifying the three tables defined in the Getting Started dictionary, you're ready to add a new table from scratch.

Starting Point:

The LCLesson.DCT should be open.

Create the Phones File

Specify the label, prefix, and description

1.Make sure that the selection bar is in the DCT Explorer Tables section, and press the Add Table button.

The Add Table dialog appears.

2.Type Phones in the Label entry, then press TAB.

The Label column only accepts a valid Clarion label, which uniquely identifies the data structure. A label may only contain letters, numbers, and the underscore(_) or colon (:) characters, and must begin with a letter or underscore. Executable code statements use this label to refer to the table.

After pressing the tab key, “Pho” automatically appears in the Prefix entry. The prefix is one way to uniquely identify columns of the same name in different tables. For example, Pho:CustNumber is the CustNumber column in the Phones table while Cus:CustNumber is the CustNumber column in the Customers table. You can also uniquely identify columns by using Field Qualification syntax (discussed in the Language Reference).

3.Type Customer phones table in the Description field.

This description appears next to the table label in the Dictionary dialog list. If you select the Comments tab, you can type in a long text description. A description of what the table is for can be very helpful for when you return to the table for maintenance programming.

Choose the table driver

1.Verifiy that TOPSPEED is visible in the Driver dropdown list.

This declares the table format for the table as the TopSpeed table format. This is the newer of the two proprietary table formats that SoftVelocity has developed for use in Clarion (the older is “Clarion”).

The Database Drivers topic/PDF documents all the available table drivers and provides information about what data types each one supports, plus other useful information such as the default table extensions for data and/or index files. It also provides tips and tricks for choosing the right driver for the job, such as which drivers are best when your application must handle a very large database which is frequently updated, or which drivers are best when the quickest query time is the foremost concern.

2.Press the OK button to close the Add Table dialog.

You can accept the defaults for all other options in the dialog. The dialog box closes, and the Dictionary dialog lists the Phones table, with “Customer phones table” listed next to it (If that Dictionary Option is enabled).

 

Name the Detail and Products Tables

Create the Detail table

1.Make sure that the selection bar is in the DCT Explorer Tables section, ,and press the Add Table button.

2.Type Detail in the Label entry.

3.Type Order detail table in the Description entry.

4.Type DTL in the Prefix entry.

By customizing the default prefix (changing it from “DET” TO “DTL”), you can make your code more readable. Three characters is the convention for table prefixes, but you are not limited to that.

5.Choose TOPSPEED from the Driver dropdown list.

Accept the defaults for all other options in the dialog.

6.Press the OK button.

Create the Products table

1.Make sure that the selection bar is in the DCT Explorer Tables section, ,and press the Add Table button.

2.Type Products in the Label entry.

3.Type Products for sale in the Description entry.

4.Type PRD in the Prefix entry.

5.Choose TOPSPEED from the Driver dropdown list.

6.Press the OK button.

Save your work

From the IDE menu, choose File Save, or press the Save button on the IDE tool bar.

Defining the Columns

Define a Column Pool

At this point, we'll define several columns that will become the linking columns between the tables in our database. We'll use a feature of the Data Dictionary called a “Column Pool” to ensure that all tables that need these columns always define them exactly the same way.

1.Making sure that the Pools section is selected in the DCT Explorer, press the Add Table button.

This time you should see the Pool Properties dialog:

Column Pools are treated just like a table in the Data Dictionary, even though they do not generate any code into applications. You may have as many Column Pools in your data dictionary as you choose, but there is usually no need for more than one.

2.Type ColumnPool in the Label field.

3.Type POOL in the Prefix field.

4.Press the OK button.

Open the Column / Key Definition windows

1.Highlight the ColumnPool “table” in the DCT Explorer list, and double-click to open the Entity Browser:

This window displays a list of all the columns defined in the Pool “table.” Since this is a new table, there is nothing to display. We could simply start adding columns, but instead, we'll start by copying a column from the Customer table.

2.Highlight the Customer table in the DCT Explorer , and double-click to open the Entity Browser for this table.

Select the column and copy it

1.In the Entity Browser, locate the CustNumber column in the Columns section.

2.Choose Edit Copy (or press CTRL+C), or you can right-click on the column and select Copy from the popup menu:

3.Click back on the ColumnPool tab to reopen its Entity Browser.

4.In the Columns section of the Entity Browser, select the Columns entry, and choose Edit Paste (or press CTRL+V).

The Column Properties dialog appears.

5.Press the Save and Close button to close the Column Properties dialog, and the ColumnsPool table..

This is the column that will be the linking column for the relationship between the Customer and Orders tables. Linking columns in separate tables are always defined the same, so copying the column definition is one way to get the existing column's definition into the Column Pool.

Derive the existing column

1.The Customer table should still be opened. If the CUSTNUMBER properties is not displayed, double-click on the CUSTNUMBER column to open it.

2.Type POOL:CustNumber in the Derived From entry (or, you could press the ellipsis button to the right of the entry, and select the CustNumber field from the ColumnPool table). Press the TAB key.

The Set Freeze Checkbox dialog pops up. Press Don't Freeze to allow changes in the Derived Column to cascade changes in the ColumnPool column if they are made.

This means that the CUS:CustNumber column is now derived from the POOL:CustNumber column. The term “derived from” means that the POOL:CustNumber column's definition is the “parent” and all “children” columns which are “derived from” that column automatically share all the attributes of the parent.

Deriving column definitions from existing columns gives you the ability to make changes in only one place, then cascade those changes to all derived columns. For example, if the definition of the CustNumber column needs to change in all tables using it, simply make one change to the POOL:CustNumber column definition, then cascade that change to all the derived CustNumber columns in all tables.

3.Press the Save and Close button to close the Column Properties and Customer table Entity Browser.

Add the rest of the columns to the Column Pool

Just to show you a different approach, let's use the QuickView at this time to complete the ColumnPool table.

1.In the DCT Explorer, highlight (select) the ColumnPool table.

2.In the Quick View, press the Add button to open the Column Properties dialog.

Once you begin the process of defining new columns, an empty Column Properties dialog automatically appears after you add each successive column. This speeds up the process of adding multiple columns. After adding your last column, you just have to press the Cancel button on an empty Column Properties dialog to return to the Quick View.

3.Type OrderNumber in the Column Name entry.

This will be the linking column between the Orders and Detail tables.

4.Choose LONG from the Data Type dropdown list.

This specifies a four byte signed integer. See the Language Reference in the core help for more details.

5.Type @n_6 in the Screen Picture entry.

6.Press the OK button.

A new Column Properties dialog opens again.

7.Type ProdNumber in the Column Name entry.

This will link the Detail table to the Products table.

8.Choose LONG from the Data Type dropdown list.

9.Type @n_6 in the Screen Picture entry.

10.Press the OK button.

The Column Properties dialog re-appears.

11.On the next Column Properties dialog, press the Cancel button.

12.Press the Save button in the IDE toolbar to save your work to this point.

Define the columns in the Phones Table

At this point, go back to the Phones table and prepare to define its columns.

Derive the CustNumber Column

1.Highlight the Phones table in the DCT Explorer.

2.In the Quick View Fields pane on the right, press the Add button to open the Column Properties dialog.

3.Type CustNumber in the Column Name entry.

4.Press the ellipsis (…) button to the right of the Derived From entry.

A Select window appears containing tree lists of all the columns already defined for all the tables in the dictionary. You can derive new columns from any existing column'whether that column is in a table definition, global data, or a column pool.

5.Highlight the CustNumber column in the ColumnPool table then press the Select button. You may need to expand the ColumnPool table to see the columns under it.

The Set Freeze Checkbox dialog pops up. Press Don't Freeze to allow changes in the Derived Column to cascade changes in the ColumnPool column if they are made.

The new column automatically becomes a perfect copy of the column from which it was derived'right down to the prompts and window control type. The button with the right arrow icon right next to the ellipsis (…) button allows you to refresh the derived column from its parent's definition.

6.Press the OK button to close the Column Properties dialog.

This is the column that will provide the link between the Phones and Customer tables.

A new Column Properties dialog now opens.

Define the PhoneType Column, and Add Special Characteristics

This defines the type of phone number (Home, Work, etc.). We will also add a special Validity Check, Default Value, and Window Control to this column.

1.Type PhoneType in the Column Name entry.

2.Choose STRING from the Data Type dropdown list.

3.Type 6 in the Characters spin control.

4.Select the Validity Checks tab.

The Validity Checks tab allows you to set numeric ranges for number columns, specify that a column value must match another column in a related value, must be true or false, and in this case, that the column value must be in a list you specify in this dialog.

5.Select the Must be in List radio button.

6.Type the following in the Choices box:

Home|Work|Fax|Cell|Mobile|Other

A vertical bar ( | ) must separate each choice.

This defines the actual list of allowable choices. In this case, the dictionary specifies that only these types of phone values are acceptable.

7.Set a default value. Select the Attributes tab.

8.Type 'Home' in the Initial Value column (including the single-quote marks).

This specifies that anytime the control appears, its default value will be “Home.” Initial values can be time savers for the end user; in this case, if most customers call from “Home”, it saves picking it from the list each time a new customer phone number has to be added. The single-quote marks are necessary because you can also name a variable or function as the initial value of a column in a table (be aware that there are slightly different rules for initial values of memory variables). In this case, the initial value is a string constant, as identified by the single quote marks around it.

9.Specify a default window control. Select the Controls tab.

When you specify a Must be in List option, the default window control for the column is an OPTION structure with RADIO buttons. These appear by default in the Window Controls list.

10.Select LIST from the Control Type list box.

The Window Controls list now updates to show only a PROMPT and a LIST control with a DROP attribute.

11.Press the OK button to close the Column Properties dialog.

A new Column Properties dialog now opens.

Define the OutsideUSA Column, set a Validity Check

This column identifies a phone number as domestic or foreign. This will be used as a flag to control the formatting of the phone number column.

1.Type OutsideUSA in the Column Name entry.

2.Choose BYTE from the Data Type dropdown list.

3.Select the Validity Checks tab.

4.Select the Must be True or False radio button.

This will create a check box for this column by default.

5.Press the OK button to close the Column Properties dialog.

A new Column Properties dialog appears.

Define the PhoneNumber Column

1.Type PhoneNumber in the Column Name entry.

2.Choose STRING from the Data Type dropdown list.

3.Type 20 in the Characters spin control.

This seems a little large for a normal phone number, but we need to account for a possible international number that could be larger.

4.Type the following in the Screen Picture prompt:

@P(###)###-####P

This will set a “mask” for domestic phone numbers. We'll use the OutsideUSA column in our application to change the picture later to accommodate international phone numbers.

5.Press the OK button to close the Column Properties dialog.

A new Column Properties dialog appears.

Define the Extension Column

1.Type Extension in the Column Name entry.

2.Choose STRING from the Data Type dropdown list.

3.Type 10 in the Characters spin control.

4.Press the OK button to close the New Column Properties dialog.

A new Column Properties dialog appears.

Save Your Work!

All the columns are defined, and a blank Column Properties dialog should be active.

1.Press the Cancel button to close the Column Properties dialog.

2.Press the Save button in the IDE toolbar to save your work.

Define the columns for the Detail Table

At this point, we'll define the columns for the Detail table.

Derive the linking column definition

1.Highlight the Detail table in the DCT Explorer list.

2.In the Quick View Fields pane on the right, press the Add button to open the Column Properties dialog.

3.Type OrderNumber in the Column Name entry.

4.Press the ellipsis (…) button to the right of the Derived From entry.

5.Highlight the OrderNumber column in the ColumnPool table then press the Select button. The Set Freeze Checkbox dialog pops up. Press Don't Freeze to allow changes in the Derived Column to cascade changes in the ColumnPool column if they are made.

6.Press the OK button to close the Column Properties dialog.

This is the column that will be the link between the Orders and Detail tables.

A new Column Properties dialog appears.

Derive the ProdNumber column

This column allows you to relate this table and the Products table.

1.Type ProdNumber in the Column Name entry.

2.Press the ellipsis (…) button to the right of the Derived From entry.

3.Highlight the ProdNumber column in the ColumnPool table then press the Select button. The Set Freeze Checkbox dialog pops up. Press Don't Freeze to allow changes in the Derived Column to cascade changes in the ColumnPool column if they are made.

4.Press the OK button to close the Column Properties dialog.

A new Column Properties dialog appears.

Define the Quantity column

This stores the number of each product ordered.

1.Type Quantity in the Column Name entry.

2.Choose SHORT from the Data Type dropdown list.

3.Press the OK button.

A new Column Properties dialog appears.

Define the ProdAmount column

This stores the unit cost of the product as it was at the time of the order.

1.Type ProdAmount in the Column Name entry.

2.Choose DECIMAL from the Data Type dropdown list.

3.Type 5 in the Characters spin control.

4.Type 2 in the Places spin control.

5.Press the OK button.

A new Column Properties dialog appears.

Define the TaxRate column

1.Type TaxRate in the Column Name entry.

2.Choose DECIMAL from the Data Type dropdown list.

3.Type 2 in the Characters spin control.

4.Type 2 in the Places spin control.

5.Press the OK button.

A new Column Properties dialog appears.

Close the dialogs, save your work!

All the columns are defined, and a blank Column Properties dialog should be active.

1.Press the Cancel button to close the Column Properties dialog.

2.Press the Save button in the IDE toolbar to save your work.

Define the columns for the Products Table

At this point, we'll define the columns for the Products table. This is the last table. We'll also show you an alternate way to add columns here.

Derive the linking column definition

1.Highlight the Products table in the DCT Explorer list.

2.In the Quick View Fields pane on the right, press the Add button to open the Column Properties dialog.

3.Type ProdNumber in the Column Name entry.

4.Press the ellipsis (…) button to the right of the Derived From entry.

5.Highlight the ProdNumber column in the ColumnPool table then press the Select button. The Set Freeze Checkbox dialog pops up. Press Don't Freeze to allow changes in the Derived Column to cascade changes in the ColumnPool column if they are made.

6.Press the OK button to close the Column Properties dialog.

This is the column that will be the linking column for the relationship between the Detail and Products tables.

A new Column Properties dialog appears.

Define the ProdDesc column

This allows for a product description.

1.Type ProdDesc in the Column Name entry.

2.Choose STRING from the Data Type dropdown list.

3.Type 30 in the Characters spin control.

4.Press the OK button.

A new Column Properties dialog appears.

Define the ProdAmount column

This stores the unit cost of the product.

1.Type ProdAmount in the Column Name entry.

2.Choose DECIMAL from the Data Type dropdown list.

3.Type 5 in the Characters spin control.

4.Type 2 in the Places spin control.

5.Press the OK button.

A new Column Properties dialog appears.

Define the TaxRate column

1.Type TaxRate in the Column Name entry.

2.Choose DECIMAL from the Data Type dropdown list.

3.Type 2 in the Characters spin control.

4.Type 2 in the Places spin control.

5.Press the OK button.

A new Column Properties dialog appears.

Close the dialogs and save your work

All the columns are defined, and a blank Column Properties dialog should be active.

1.Press the Cancel button to close the Column Properties dialog.

2.Choose File Save, or press the Save button on the tool bar to save your work up to this point.

All of the tough work is done! Hang in there, we are almost there!

Clean up of the Orders table import

Before we start the next section, let's clean up a couple of items from the Orders table that we imported in the Getting Started lesson.

Derive the CustNumber column

This provides a unique identifier for each order

1.Highlight the Orders table in the DCT Explorer, and double-click on the CustNumber column in the Quick View Fields list.

2.Press the ellipsis (…) button to the right of the Derived From entry.

3.Highlight the CustNumber column in the ColumnPool table then press the Select button. The Set Freeze Checkbox dialog pops up. Press Don't Freeze to allow changes in the Derived Column to cascade changes in the ColumnPool column if they are made.

4.Press the Save and Close button to close the Column Properties dialog.

Derive the OrderNumber column

This provides a unique identifier for each order

1.Highlight the Orders table in the DCT Explorer, and double-click on the OrderNumber column in the Quick View Fields list.

2.Press the ellipsis (…) button to the right of the Derived From entry.

3.Highlight the OrderNumber column in the ColumnPool table then press the Select button. The Set Freeze Checkbox dialog pops up. Press Don't Freeze to allow changes in the Derived Column to cascade changes in the ColumnPool column if they are made.

4.Press the Save and Close button to close the Column Properties dialog.

Close the dialogs

1.Press the Save button in the IDE Toolbar to save your work so far.

OK, What Did I Just Do?

Here's a quick recap of what you just accomplished:

Now we'll go on to add keys and table relationships.

Click here to jump to the next lesson