Tutorial 2 - Duplicate Records, to Index or not to Index

Indexing is "expensive" and should be used sparingly. While small tables don't justify an index, one of the by-products of indexing (the ability to designate a field or combination of fields to be Unique in order to avoid duplicate records) may be highly desired. If duplicate records is a concern and indexing is not justified, there is an alternative: using a Procedure. During this tutorial, you'll set up an index to show how it is done, so that you know how to do it when it is appropriate, and then you will delete it and create a Procedure to protect against duplicate records. While you are at it, don't get the wrong idea about Procedures, just because we suggest using one for this simple task. Procedures are powerful tools and will handle almost anything you can conceive.



Exercise 2 - 1, Creating an Index
When all fields are de-selected, the Inspector displays the Index view.

1. Click on the Add button in the Inspector's Index view.
This will insert Index1 into the Indices for City area.
Click on Index1.
This will display all available fields in the template.
Drag the City, St/Pr, Country above Drag fields above this line in the Indexed fields area of the view, and set the Unique switch to On.
If you want something more specific than Index1 you can type City into the Name field. However, this should rarely be necessary as the index names are not used in configuration.

This Unique index will assure that each City - St/Pr - Country combination occurs only once and attempts at duplication will be refused. However, the price is too high so let's not do it.

2. Click on the Drop button in the Inspector's Index view.


THINK ABOUT: Adding intelligence to a template
You can add intelligence to templates through the use of Procedures. Procedures are executable flowcharts which are attached to Templates and executed according to a number of pre-defined conditions. The use of Procedures is optional. During this tutorial series you are working with Global templates only; however, the rules you are learning here also apply to templates for Transactions (Common or Extra) and Subledgers - Primary or Cloned, and you will learn more about these later on.

Procedures allow you to carry out many tasks, such as:

interrogate the database, down-load information and insert it "as is"; or
use it to calculate new data for insertion into the record during data entry; or
validate the accuracy, relationship, or selection of predetermined data.

Each template can have a number of Procedures which are activated (triggered) under a variety of conditions:

Field-based procedures can be attached to database fields (local or linked) and are fired automatically when their contents is edited. Trigger-based procedures can be attached to View fields that are styled as buttons and will be triggered when the button is pressed.

Thus the Configurator may
create Procedures for automating and/or controlling various processes utilizing such features as:

database retrieval row processing options
offering a SQL Expert alternative to the GUI-controlled setup
broad calculation capabilities
attention panels, with multiple-choice response options
imbedded functions
user-defined resources
validation against and importation from data in Global tables.
You will learn about all of these features as you progress through the various stages of the Tutorial.



Exercise 2 - 2, Creating a Procedure
During this exercise, you will create a Procedure that will get triggered when you try to save a new record. Before the Save instruction is carried out, this Procedure will interrogate the City database table and attempt to locate an already saved record with the same City, St/Pr, and Country as that which appears in the record being saved. If it finds a match (e.g. an identical record already exists) it will then display an Attention panel with a suitable message. The Attention panel "demands your attention" in that it must be dismissed before you can carry out any further instructions. However, the OK button which you must click to dismiss the Attention panel will be "instructed" by you, as part of your configuration effort, to Abort saving. This will protect you from creating duplicate records. So let's configure the first Procedure.

1. Launch the Global Procedures editor.
This will display the Open Procedure panel showing you all Global Templates that have been created; in your case all you will see is City (the template that you just created).

Click on City in the Template column.
This will display in the Procedure column all trigger options, both Template-based and Field-based, that are available for this template.

Double-click on the Saving record option under Template-based.
This will display the basic three windows that you are already familiar with from working with the template: Palette, Workspace, and Inspector. The Workspace comes preloaded with a Start and Finish object. The name is City Procedure Workspace (Trigger: saving record). In preparation for working with the Workspace and laying out a neat looking procedure (a personal preference for neat freaks like us), you may want to equip the Workspace with a Grid.

Type an explanation into the Inspector's Description view:
e.g. Validate new record against other records already saved in the database and, if a match is found, give notice through an Attention panel and abort saving.

2. Drag the Retrieval icon from the Draggable objects area of the Palette and drop/position it into the Workspace just below the Start object.
The Retrieval object has a header field and a single Get Field, and is surrounded by Control Points.

Click on the bottom center Control Point and drag down until a total of three fields are shown in the Get Field area.

With the Retrieval object selected, click on City in the Source column of the Palette.

This will add City into the header field of the Retrieval object and display, in the Palette's Fields column, the database fields available for retrieval from this database table.

Click on City, St/Pr, and Country in the Fields column of the Palette.
This will load the selected field names into the Get Field area of the Retrieval object (you can also drag them from the Palette and drop them into the fields of the Retrieval box).

Click on Single row in the Query returns area of the Inspector's Retrieval view.

Select the Inspector's Search tab and drag City, St/Pr, and Country above the Drag fields above this line.

Each dragged row will now be equipped with a pop-up list of available Comparison Operators, a run-time switch, and a value field. The default setting of the run-time switch (the center of the switch is white) is set for hard-coded values (you type your data into the value field).

Keep the default = in Comparison Operators

Click on each of the three run-time switches.

This will change the value fields into receptacles for resources to be supplied at run-time. You will notice that the centers of the run-time switches are now gray and the value fields are grayed over and display the message Drag variable / field here. The run-time resources can be selected from the Local column; or from Fields (after a database table name has been selected under Source).

Drag City from Local in the Palette and deposit it into the value field of the City row and repeat the process for St/Pr and Country.

3. Drag the Condition icon from the Draggable objects area of the Palette and drop/position it into the Workspace just below and to the right of the Retrieval object.
It comes with a Yes/No Exit orientation.
Click on the No/Yes Exit orientation in the Inspector.
Resize (make larger) the Condition object and double-click it to make the rectangular area in the center of the object editable.
You will fill this editable area with the following text: City.City == City AND City.[St/Pr] == [St/Pr] AND City.Country == Country by loading all components from the Palette's Main resources:

With the City still selected in the Source column:
click on City in the Field column;
click on == in the Operators and dividers area;
click on City in the Local column;
click on AND in the Operators and dividers area.

Repeat this process for St/Pr and Country.

4. Drag the Alert icon from the Draggable objects area of the Palette and drop/position it into the Workspace to the right of the Retrieval object.
The Alert icon becomes an Alert panel with a Heading and Body area, and three Title buttons. For this Procedure a single button is appropriate.

Set to "one button".

Double-click the Alert panel to make it editable:
place the cursor into the Body area and:
click on City in the Local column of the Palette;
type a , (comma) and a space;
click on St/Pr in the Local column of the Palette;
type a , (comma) and a space;
click on Country in the Local column of the Palette;
type already exists;
type/replace Default in the button with OK .

5. Drag the Exit/Finish icon from the Draggable objects area of the Palette and drop/position it into the Workspace below the Alert object.
With this Finish object selected click on the Abort saving of record switch (checked) in the Inspector.
6. Drag the Finish object from the bottom of the Workspace and position it above or below the No exit of the Conditional object.
Connect all objects in the order of the execution flow in the same manner as you connected the fields in the template:
Start to Retrieval, to Conditional, Yes exit of Conditional to Alert, to Finish (below the OK button), No exit of Conditional to Finish.

7. Click on the Check Syntax button (with a tick mark image) in the tool bar. If an error is noted, click on the Edit again button and make the necessary corrections.
The Edit again button is the same button that you clicked to Check Syntax; however, when an error is detected, it changes its image to a little wrench key.

Select Save menu item.
This will mark the Template name and Trigger option with a checkmark in the Open Procedure window, indicating that a Procedure has been saved and is operational (in the event that a saved Procedure has errors, the checkmarks (both template name and trigger option) will be broken and the text strings will be shown in bold).


Exercise 2 - 3, Testing the Procedure
Before committing a Procedure into production, it should be tested. However, the Procedure which we have just created can't be tested unless we have at least one record in the actual database against which the Duplicate Record test can be carried out. Therefore, you will have to add the first record to the database table. In our example, we used Calgary, AB, Canada.

1. Launch the General Data Entry function.
This will display the Open Table panel.
Double-click on City.
This will display the City input window.
Enter the first record with City = Calgary; St/Pr = AB; Country = Canada and Save.
2. Return to the Procedure.
Click on the Test button.
This will display the City input window.
Complete the record with City = Calgary; St/Pr = AB; Country = Canada; and click on Save.
This will display the Test window.
Click on the Step button in successive steps (you can also use the Return key).
Watch the progress in the Test window and in the Workspace (the object whose instructions are currently being carried out is shown with a green border:

Since the condition specifications have determined a Yes response, the Alert object will be the next target which will then carry out its instructions by displaying the Attention panel:


THINK ABOUT: Optimizing the database design by eliminating data redundancy
You have now completed building a small template and given it some intelligence by providing a Procedure which prevents duplicate data from being accumulated. The next step is to think about optimizing the database design by eliminating data redundancy; this can be achieved with linked fields while shielding the user from the intricacies of the design of the relational database (this is part of a process that is called "normalization" in the database design).

The source of linked data can only be a Global Template. More than one linked field can be brought into a General Data (Global or Subledger) Template from the same source. Frequently repeated items (such as city of residence in a Name table, where the same city may apply to most of the customers and vendors) can be moved into one table (e.g. the City template that we have already completed), and linked from that table into those tables that make use of them.

Linked fields also provide a convenient way of centralizing and instantly distributing changes in data throughout the database. For example, a change in the cost of an inventory item can take effect in all tables that reference this item, if the inventory record, including the amount field, is linked into those tables. Linked fields can also be brought into a General Data Template from more than one source table.

Links can also be inherited through the hierarchy of templates. That is, the Name template can itself serve as a source for other templates that make use of its fields. In this case, the native fields of the Name template will become the links of the first order, while the linked fields (City, St/Pr, and Country) will be the links of the second order (if brought into those templates). STEP FORWARD will hide the real origin and nature of the linked fields from the recipient templates, posing them as native fields in the source, regardless of their true location.

However, the user should realize that multi-level linking of fields from one template into another, while providing a smooth flow of data, can tax the database server to the point where searches on the tables with links become increasingly slow. The exact threshold of the number of individual links within the template and the level of nesting of links, where the loss of performance becomes noticeable, depends on the design of the particular hierarchy of templates/links, and will vary from one RDBMS to another. As a rough rule, the flatter the structure of the links (i.e. the fewer levels of reference) the better the performance. For example, our tests show that Sybase 11 can handle up to 4 levels of nesting of linked fields, without significant performance penalty.

Before utilizing linked fields, let us introduce you to some Accessories, because we want to include some of those features along with the linked fields in a Name template.

Go To

  Tutorial 3 - Defining Accessories
  Index