Learning Clarion (Part 1)

The Getting Started section briefly introduced you to Clarion programming at its highest level. Learning Clarion now teaches you how you can use all the rest of the tools Clarion provides to create real-world applications. It contains two parts, on two very different levels:

• A series of Application Generator lessons, which familiarizes you with all the tools in the Clarion development environment.
• A Clarion Language lesson, which introduces the Clarion programming language and familiarizes you with the type of code generated for you by the development environment.

Lessons 1 through 12 introduce all the main Clarion development environment tools. We start at the application planning stage, walk you through creating the data dictionary with the Dictionary Editor, and then walk you through creating a complete application with the Application Generator. By the end of these lessons you’ll have created a complete order-entry application.
You’ll use the Application Generator and work with Procedure, Control, and Code templates to produce an Order Entry application. You’ll work with the Window Designer to design windows. You’ll work with the Report Designer to design reports. You’ll use the Text Editor to embed Clarion language source code into the code generated by the templates.
Lesson 13 introduces the Clarion programming language at the hand-coding level. It starts at "Hello World", then walks you through creating simple forms of the most typical types of procedures used in Clarion applications, all while explaining the functionality of the hand-code you’re writing and relating it to the type of code you’ll see generated for you by the Application Generator.
Please review the section entitled "Anatomy of a Database" in the help file. We covered it in the Introduction to Clarion blog entry, where you read it in the "Learning Clarion" PDF file. This time it's in the help file, so it should already be familiar territory.

In summary:

• A database is a collection of information (data) in a system of columns (fields), rows (records), and tables.
• Columns (fields) can store many different types of data, but each individual column is specified to hold only one type.
• Each data item should be stored in only one place.
• One or more columns makes up a row. One or more rows make up a table. A collection of related tables make up a database.
• Clarion programs can access many different table systems through the use of table drivers.
• Keys and indexes declare sort orders other than the physical order of the rows within the table, and can contain more than one column, allowing sorts within sorts.
• Range Limits enable you to process a subset of rows, which reduces processing time.
• Tables are related through common columns containing identical data, which allows you to eliminate redundant data.
• Table names and column (field) names should be singular, not plural, in almost all cases.
• A primary key is a column, or combination of columns, that uniquely identifies each row in a data table.
• A foreign key is a column, or combination of columns, in one table whose value must match a primary key’s value in another related table.

Lesson 1: Planning the Application

As a general rule, every minute you spend planning your application beforehand saves you ten minutes later. This topic informally describes the planning process for the application you’ll create in the subsequent lessons. In the real world, you’ll probably create a bona fide functional specification for your important applications. This informal description defines:

• The tasks the application performs.
• The data the application maintains, and how it stores it.

As a starting point, this Application Generator lesson application uses the data dictionary from the applications you created in the Getting Started manual. It extends the concept to a simple Order Entry system, using the database for keeping track of customers and what they ordered.

Defining Application Tasks

This application will maintain the customer and billing tables for a manufacturing company. The first task in planning just what the application will do is to assess what the company expects it to do. For the subsequent lessons, the application we’ll create is a simple order entry system. Customers typically phone in orders for one or more products at a time. A salesperson takes the order. The billing department prints an invoice that night.
The application therefore must provide:

• Entry dialogs for taking the order, or modifying the data in it later.
• Access to the customer list from within the order entry dialogs. The customer list is the one you created in the Getting Started lessons, stored in the Customer table.
• Access to the list of part numbers (items) that the company manufactures, from the order entry dialogs.
• Browse windows for listing sales transactions.
• Procedures that will maintain the Product list and Customer information.
• Printed reports.

Designing the Database

The first task in planning the table structure is to assess what data the application needs, and how to store it with the minimum amount of duplication. Good database management maintains separate data tables for each "entity" or group of discrete data elements. The data "entities" this application maintains are:
Customer
Customer name and address data that changes only when a customer moves. Created in the Getting Started lessons, along with its related Order table.
Phone
In the communication age that we live in, it is probable that a customer may have more than one contact phone number.
Order
Basic information needed for assembling the data needed to print an invoice. It "looks up" information from the other tables, such as the customer name and address. When a sales person takes a new order, they add a row to this table.
Order Detail
Product, price, and quantity ordered for an item on a given invoice: the variable information for each order. Though this duplicates price information in the Product table, you must maintain the price at the time of the sale here. Otherwise, when you increase the price in the Product table, it would cause the balance in the Detail table to change.
Product
Information on the products sold by the company, including product number, description and price. This data changes only when a price changes or a new product is added.
State
The list of states in the USA where the customer is located.

The Customer Table

The Customer table stores "constant" data such as customer names and addresses. It’s most efficient to store this data in one place, allowing for a single update when the information changes. This also saves space by eliminating redundant customer information in the Order table; otherwise, if there were 1000 orders for company XYZ, the address information would be repeated 1000 times. Reducing storage requirements by storing the data only once is called normalization.
The customer data requires a column to uniquely identify the customer. The company name is unsuitable because there could be duplicates, or it may change. There may be, for example, multiple rows for a customer called "Widget Depot" if it has multiple locations. The CustNumber column is a good candidate for an auto-number key which automatically creates and stores unique customer numbers.
The CustNumber column also serves as the primary key for the data table. Any other data tables which are related to the Customer table must declare the CustNumber column as a foreign key.
Because there may be many orders for each customer number, the relationship between the Customer table and the Order table will be a one to many (1:Many) relationship. We say the Customer data table is the parent table, and the Order data table is the child table.

The Phone Table

The Phone table stores telephone numbers; each customer could have several. Each row includes a CustNumber column to relate back to the Customer table.
The Phone table also includes a text column in which we can indicate whether the phone number is an office, fax, mobile or home number. Using the data dictionary, we’ll specify that the control for entering data for this column should be a drop-down list with the choices already loaded.

The Order Table

The Order data table gathers information for each sales transaction from all the other data tables (such as the customer data). Because much of the basic data in this table prints in the "header" area of the invoice, this is sometimes called the Order Header.
Every sales transaction requires one row in the Orders table. The row relates to the customer information by referencing the unique customer number. Because some order rows may reference one product, and others may reference ten, you’ll create a separate Detail table which relates back to a unique order number. This creates a one-to-many relationship, with the Order table as parent and Detail as child. The actual products ordered are identified by their product codes, in the Detail table.
The Order row thus holds a customer number to relate back to the customer data (the foreign key), and a unique order number to relate to the Detail. You’ll create a multi-component primary key on the two columns so that you can easily create a browse sorted by customer and invoice number.

The Order Detail Table

The Detail table stores the products ordered by their product codes (a foreign key into the Product table), their individual prices, the quantity of each, and the tax rate. An additional column holds an invoice number, which relates back to the Order table in a many-to-one relationship.
The Detail table duplicates the price information with the columns in the product table; this is because prices may change. It’s important to store the price column within the detail table row because if the price increases in six months, today’s "paid in full invoice" would reflect a balance due.

The Product Table

The Product table stores unique product numbers, descriptions, and prices. When the sales person looks up a product by name, the application inserts the product number into the Detail row. The product code is the primary key: no two items can have the same code, and every product must have a code. An additional column contains the tax rate for the product.

The State Table

The State table is a list of the two-letter codes for the states in the USA where the customer is located. Since there are only two fields in the table, this is often referred to as a "lookup table".

Referential Integrity

Referential Integrity refers to the process of checking all updates to the key column in a given table, to ensure that the validity of parent-child relationships is correctly maintained. It also refers to ensuring that all child table rows always have associated parent rows so that there are no "orphan" rows in the database.
Because the data for a given transaction resides across several tables, this application must enforce referential integrity. This is critical, yet many database application development tools require you to hand code procedures to enforce this. The Application Generator’s templates implement this automatically in your generated source code when you select a few options in the Data Dictionary.
It is essential that the application does not allow an update to a row that leaves a blank or duplicate value in a primary key column. For example, we need to restrict the ability of the end user to update a row in a way that could cause a duplicate Customer number. If two different companies shared a duplicate Customer number, you could send a bill to the wrong company.

The Complete Database Schematic

The schematic below is supposed to provide an overview of the entire database.

In my opinion, this diagram is a confusing mess. The boxes are too close together, and the arrangement is jumbled, so it takes a while to figure out what is related to what. Also, some table names are in ALL CAPS and many are plural words, which makes things confusing.
If you are going to spend time designing the database correctly, spend a little more organising the data diagram properly too. Try to put the "one" tables on the left, and the "many" tables on the right. This immediately makes everything more easy to read. Sloppy thinking leads to errors: always aim to be clear and precise.

This diagram shows more clearly what is going on. I have highlighted the Primary Key (PK) columns in yellow. This immediately shows up two design flaws in the database design.
Firstly, there is no PK in the Phone table. Secondly, the Detail table has problems. If we make the PK OrderNumber + ProdNumber, then we can only sell the product once in a given order. What about selling 10 items at the special price and 2 items at the standard price? Also, the Products will not show up on the Order in the same sequence as ordered by the Customer. This could cause problems in the real world. It is best to resolve these issues at the design stage, and not wait until after you have shipped the application to the customer.
The schematic above provides an overview of the entire database. If you look at it from the perspective of the sales agent taking a phone order, the Order table stores who’s ordering, the Detail stores what they’re ordering, and the Customer and Product tables store constant information about the customers and products.
The item code looks up the description and price. The customer code looks up the customer’s name and address. Other data, such as the transaction date, fill in automatically (by looking up the system date, for example).
The lessons will create a brand new data dictionary, and you will copy and paste the tables that Getting Started defined for you into the new dictionary.
As for the actual application you create, because the lessons are a teaching tool more concerned with showing what Clarion can do for you, it won’t create a full-scale order entry system. However, you will find that some parts of the application will be very "showy," so that you can quickly learn how to do equivalent procedures in your applications.

Application Interface

The next major task before coding is to plan the user interface. For a business application like this, it’s crucial that a salesperson quickly locate the data they need, so that they can record the sale and move on to the next phone call. Therefore, the application should put all the important data "up front" by default, and no entry or maintenance dialog should be more than a button or menu command away.
Additionally, the company uses many other Windows applications; therefore, it’s especially important that the application have a standard Windows "look and feel." End users learn a familiar interface more quickly.
To implement the tasks the application must execute consistently with our guidelines, we can plan for the items listed below. Though the following is no substitute for a real program spec, it should suit us for the upcoming lessons.

• Because the application will handle the maintenance for the customer, item, and billings tables on different forms, the Multiple Document Interface (MDI) is necessary.
• The application should have a toolbar with buttons to load forms and browse windows, and to control the browsing behaviour.
• To maintain a consistent "look and feel," the main menu choices will be File, Edit, View, Window, and Help. The File menu accesses the printing and exit procedures. The Toolbar buttons call the form dialogs for editing a current row (if highlighted in a browse) or adding/deleting rows, and for browsing through the tables. The Browse menu calls the procedures for browsing tables. Window and Help perform standard actions.
• When adding new orders, the sales people should be able to pick customers and products from scrolling lists. Pertinent data in the order dialog (addresses, item descriptions and prices) should automatically "fill in" as appropriate.

OK, What Did I Just Do?

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

• You defined the tasks the Application must accomplish.
• You designed the database that will allow the application to accomplish those tasks.
• You specified the user interface the application will use.

Now that the application description is fairly complete, we’re ready to begin work. The first step is to create the data dictionary.

Lesson 2: Creating the Data Dictionary

Start the Clarion IDE, and go to the "Dictionaries" tab in the "Start Page". Click on "New Dictionary". Save the file as "LCLesson.dct" in the following folder: "C:\Users\Public\Documents\SoftVelocity\Clarion11\Lessons\LearningClarion"

From the "Dictionary" menu, click on "Show Properties Dialog".

Click on the "Add" button and type in "Learning Clarion Lessons Dictionary" as the comment. Click "OK". Then click on the "Versions" tab and note that this is the initial version. These two features (comments and versions) allow you to make notes and keep track of changes to the dictionary over the life of the application. Click "OK".

Copying Tables From One Dictionary to Another

You can use the standard Windows copy and paste commands to copy table definitions from another dictionary (or to copy columns from one table to another). In other words, once you’ve defined it once, why bother to re-define it when you can just copy what you’ve already done?
There is also a better and recommended way to copy table definitions from one Clarion Dictionary to another, by using the "Table Import Wizard". We will use this better method in this exercise.

Click on the "Add Table" button in the DCT Explorer, and select "Import Tables". Click "Yes" if prompted to save changes to the existing dictionary.

Make sure that "Select Server" says "Clarion Dictionary". Click on the ellipsis button next to the "Select Dictionary" field, and navigate to the "Getting Started" folder to find "GSLesson.dct". Click "Open".

Click on the "Add All" button, and then click "Finish". All the table and field definitions are imported, including their relationships. By contrast, using the Windows clipboard to copy and paste doesn't transfer the relationships, only the table structures.

Note that in the Properties section of the Customer table, the "Full Path Name" is still showing the old "Getting Started" path. Just remove this name entirely.

Last time we changed the description fields for each column to mixed case. This time we are going to change the Column Names as well, as shown above.

When you get to the "State" field, go to the "Attributes" tab as well, and choose "Uppercase" and set the initial value to 'FL', including the quote marks. This will default the state code to Florida for new customers. Don't forget to change the case of the "ZIPcode" field too. Then click on the "Save" button.
The initial value in this case is a string, hence the single quotes. You can also use variable names (Preceed with an exclamation mark) or functions.

Repeat the process of removing the full Path Name and changing the case of the column names in the "Order" table, as shown above. Then go to the "OrderDate" field and set the "Initial Value" as the function TODAY() as shown.
Click on the green "Save and Close" button to return to the "Start Page".

Instead of clicking on the LCLesson file, click on the path to the right, as shown. This will open the "LearningClarion" folder.

Please rename "STATES.TPS" to "STATE.TPS" and "ORDERS.TPS" to "ORDER.TPS". This is so the TopSpeed file names match the table names in the dictionary. Close the folder and return to the "Start Page".
Make a backup of your work.

OK, What Did I Just Do?

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

• You created a new, empty data dictionary (.DCT file).
• You imported existing table definitions from one data dictionary to another (the easy way to work: never re-invent the wheel).
• You added an initial value for one of the columns in the database.

In the next lesson, you’ll learn how to add a table to the data dictionary, starting totally "from scratch". It's quick and easy it is to do even without using a wizard.

More lessons to follow in Learning Clarion (Part 2).

[ Learning Clarion (Part 7) ]
[ Learning Clarion (Part 6) ]
[ Learning Clarion (Part 5) ]
[ Learning Clarion (Part 4) ]
[ Learning Clarion (Part 3) ]
[ Learning Clarion (Part 2) ]
[ Learning Clarion (Part 1) ]
[ Backing up your Clarion projects ]
[ Getting Started with Clarion (Part 2) ]
[ Getting Started with Clarion (Part 1) ]
[ Introduction to Clarion 11 ]
[ Installing Clarion 11 on Windows 10 ]