[Omnis-Newsletter] Omnis Tech Newsletter
omnis-news-admin@omnis.net
omnis-news-admin@omnis.net
Wed, 16 May 2001 15:42:29 +0100
Omnis Tech Newsletter May 16th, 2001
========================================
UNSUBSCRIBE OPTIONS: You have been sent this email because you have directly
signed up for, or expressed an interest in receiving a technical newsletter
when you downloaded an evaluation of Omnis Studio, or registered the Lite
version of Omnis Studio. If, however you feel you have received this email
in error you can unsubscribe as well as change your subscription options at
www.omnis.net/newsletter.
N.B. If you subscribed by checking a box on one of our forms, you will not
have received a password. You will need to submit your email address at
www.omnis.net/newsletter and select the 'email me my password' option on the
next page in order to receive this.
========================================
WELCOME!
Welcome to this the fifth issue of our Omnis Technical Newsletter. Published
fortnightly, it is intended for Omnis developers of all levels and
experience, for those people evaluating Omnis Studio, or for developers
moving from a similar tool. We think you'll find the content both
interesting and useful for your Omnis development needs and hopefully it
will help you become more productive in Omnis application design.
The first article in this newsletter is the next part of Geir's tutorial for
those just starting out in application development and Omnis in particular;
this issue he deals with the data structures required to fully implement
relational database access in Omnis. The second article, by David Swain,
describes how you can tightly control data entry using so-called input
masks, and similarly how you can affect the display of database values using
special display formatting. You may find it easier to work through the
exercises and examples in this newsletter by printing it out before you
begin.
CONTENTS:
-About the Authors
-Building Your First Omnis Application: Part 4, The Data Structure, by Geir
Fjaerli
-StudioTIPS, a learning tool for Omnis developers
-Masked Entry Field Techniques, by David Swain
-About the Omnis Studio manuals
-Copyright and Unsubscribe details
========================================
About the Authors
The Omnis Technical Newsletter contains high quality content from two
leading and well respected Omnis developers, Geir Fjaerli and David Swain.
Geir Fjaerli is based in Norway and has been an Omnis professional developer
for many years as well as a Regional Sales Manager for Omnis. He is
currently working freelance again developing a range of products (in Omnis
of course) including his Prophet5 sales and customer relationship management
solution, soon to be released for Mac OS X.
David Swain is the founder and president of Polymath Business Systems, for
many years a leading provider of Omnis training. His expertise in Omnis
programming and his ability to make complex concepts understandable are
recognized throughout the worldwide Omnis community.
========================================
Building Your First Omnis Application: Part 4, The Data Structure
By Geir Fjaerli, Sunshine Data
Email: geir@sunshinedata.net
Web: www.sunshinedata.net
----------------------------------------------------------------------
Welcome to the next installment of our Basic Omnis Studio tutorial. After
building our first basic application in the first chapter, we started
looking at the details of an Omnis application. They included:
Part 2: The Studio Classes. The classes are the building blocks of an
application, and include windows, reports, menus etc.
Part 3: The Omnis Integrated Development Environment. Here we looked at the
Component store, which lets us add classes to our library, fields to our
reports and windows, and so on. We looked at the Property Manager which
displays and allows us to change the values of an object's properties, e. g.
the background color of a window, the text on a button etc. And we looked at
the Catalog, which lists all variables, functions, constants and more, for
easy drag & drop or double-click access.
I strongly suggest that you refer to these two chapters if you haven't
already, or if you feel uncertain about the details. (If you want the back
issues of this newsletter, go to www.omnis.net/newsletter and click
'Newsletter Archive' in the left margin.)
-- THE DATA STRUCTURE --
In this issue we are going to look closer at the data structure of our
application. This is not coincidental, the data structure is the foundation
of our application. Just like when you are building a house: First draw the
floor plan, making sure you have got the piping and cabling figured out.
Then dig and build the foundation before you start with the rest of the
house. If later you find the color of the house is wrong, just repaint it.
Much worse if the foundation is wrong.
Let us begin by describing how the data structure is built. This will be a
basic intro only, one of many books on the subject is strongly recommended.
NOTE: While many of the concepts discussed are generic, this chapter is
directed at the relational database model and the SQL language, which is the
basis for Omnis functionality. Other models exist, including the built-in
Omnis DBMS (DataBase Management System) which - while you can access it
relationally using SQL - is a hierarchical/network type database. These and
other database models and the "native" Omnis database language will not be
discussed here.
-- TABLES, COLUMNS and ROWS --
The data structure is usually built on Tables, Columns and Rows. (Though
they may be named differently, say Files, Fields and Records.) A table is a
collection of columns:
* A table is a collection of information that describes something: A
customer (name, address), an order, a stock item, a support call, anything
that you want to record information about.
* A column is one of these bits of information. "Last name" would be one of
the columns in the employee table, "Part number" would be a column in the
stock items table.
* If we store several items in a table, we call each of them a row.
So in terms of columns and rows, a Birthday database may look like this:
# Name Birthdate
1 Geir 20-04-1957
2 Pete 03-07-1923
Our table definition then has three columns: "Number", "Name" and
"Birthdate", and we have stored 2 rows: One for Geir and one for Pete.
A column is usually (but not always) typed (has a data type), that is we
decide whether the column holds text, numbers, dates, pictures or others.
Some column types take other information, such as the length of the text,
the number of decimals. The information stored depends on the type of
database. Omnis applications usually work with relational databases, so we
are going to assume that we are for this tutorial. In our birthday database
then the table definition might be:
Column1: Number Integer
Column2: Name Text
Column3: Birthdate Date
-- PRIMARY KEYS --
Our example has a column called "Number". This does not really hold
information that interests us at all, so why do we include it? Well, it may
not be interesting to us, but it is vital to the database. Our database
operations include finding rows, updating, deleting etc. When interacting
with the database this way, we need to identify the data to work on. Imagine
telling the database to "Delete Geir". If we have more than one Geir in the
database, how does the database engine know which one we mean. A human may
be able to figure it out based on other available information ("one of the
Geirs moved to Iceland, so I assume it is him"), with databases you have to
be more specific. So we add a "primary key", a column that holds a unique
value for each row. Only Geir has number = 1, so if instead of saying
"Delete Geir", we say "Delete the guy with number = 1" the database engine
understands.
We call it relational because not only does it define the tables for each
type of data, and the columns it consists of, but also how these tables are
related. An order is linked to the customer that ordered it, an employee is
linked to the company they work for. Very often such relations are
hierarchical, (aka. parent/child relations), but they don't have to be. An
example of a hierarchical structure would be, a sales rep has several
customers, from each customers he may have several orders, each order may
have several order lines.
So, why do we create these complex relations, why not simply store all
information in one "table". There are several reasons for this, e. g. using
the example above, every order would need to have the customer name and
address, and the sales rep's info. This would mean that if a customer had
say ten orders, we had duplicated the customer info ten times. That takes up
room in our database, and creates a nightmare when we want to update the
customer info, as we have to do it not only once, but ten times.
How you want to split your data is the question. At one point the management
issues becomes greater than the advantages. Compare to traffic: You can
stuff everybody into buses, but people may have to wait for ages while the
bus stops at other places on the way to your destination. Put everyone in
their own car, and they can all go directly to their destination, but may
not get there any faster, as they will be stuck in a giant traffic jam.
-- FOREIGN KEYS and JOINS --
In a relational database, we link two tables together by using information
in a column in each. In our hierarchical Customer/Order example, we would
use the primary key of the customer, as it is unique. So we would add a
column to our order table called say "CustomerID". This is referred to as a
"Foreign key", because it stores the primary key of another table. In that
column we would store the ID of the customer that placed that order.
So our data structure might be:
Customer:
ID Integer
Name Text
Address Text
etc...
Order:
ID Integer
Date Date
CustomerID Integer
etc...
And our database may look like this:
Customers:
11 SofTech London
12 HardTech Paris
Orders:
101 20-04-2001 11
102 21-04-2001 12
103 21-04-2001 11
In this example, SofTech has two orders, 101 and 103, while HardTech has
one.
When we operate with relational tables, we must inform the database how to
present the data. If we say "Give me order 101, we will get that order, but
not the related customer info. We have to say "Give me order 101 and the
customer who's ID equals the orders CustomerID". This is called a join, as
we "join" the two tables, and the last part is called a "Where-statement".
Note: While the relational link is usually done on the primary key, it is
possible to perform joins on other columns as well. This often results in
multiple results, as the column may not be unique.
-- SQL --
For the database engine to understand what we say, we need to use a language
that it understands. The common language for relational databases is called
SQL, (Structured Query Language), and defines a strict syntax for the
various operations. So rather than the sentence above, if I wanted the
database engine to understand me I would have to say: "Select from Customer,
Order where Order.ID = 101 and Customer.ID = Order.CustomerID". The last
part is called the "Where"- clause, as it defines which rows the database
will return from the (in this example) two tables.
As we shall see, Omnis Studio shields the beginner from much of the SQL
syntax issues. Still I recommend that you do some study on the general
database concepts. Again a number of books are available.
-- OMNIS SQL CLASSES --
So, now that we know how the database is structured, how do we access it
from our Omnis Studio application?
In part 2 we grouped our classes into definition classes, visual classes and
non visual classes. Omnis has three types of classes dedicated to making
your database coding life easier. Two of them are definition classes and one
is a non visual class:
The Schema:
The schema is our link to the database structure. Inside the database is a
data dictionary, which holds the table definitions for our tables. By
defining schemas we can keep a "copy" of that definition in our application,
which allows us to program against the structure without being connected to
the database, and which allows us to use our application with a number of
different databases.
A schema holds the table definition for a particular database table, using
Omnis data types. These are slightly different from the SQL database ones.
So our Birthday definition may look like this in the database:
Number INT 4
Name VARCHAR 100
Birthdate DATETIME
And in the Omnis Schema:
Number Number Long Integer
Name Character 100
Birthdate Date Time D M Y
Omnis handles the type conversions for us in most cases, so at this stage we
do not have to worry too much about them.
The Query:
A query is a class holding selected columns from (usually) two or more
schemas, together with a statement to join the tables referenced by these
schemas. We shall look at queries in more detail later.
The Table:
I am sorry for the unavoidable confusion: The table class in Studio is not
related to the database table, even if they share the same name. Instead the
Studio table class holds the methods to access and update the database
table. The table class is linked to the database through a schema. Since the
schema holds the database table definition, by linking to it the table class
knows what the database table looks like.
The table class comes with a number of built-in methods. These includes:
$select: To select one or more rows matching our "where"-statement, which is
passed as a parameter.
$fetch: Fetches the rows selected by $select from the database into our
application.
$insert, $update, $delete: Inserts rows into, updates or deletes them from
our database.
There are more built-in methods, and you can add your own or customize the
built-in ones.
So basically, the schema knows how the database table looks, and the table
class knows how to work with it. Together they form a logical unit that can
be used in your application, by a number of different windows and reports,
to access the database table. So all your definitions and logic will be in
one place, rather than scattered throughout your libraries.
-- NEXT CHAPTER --
By now you are familiar with some basic database concepts, including tables,
columns and rows, and primary keys and joins. We have introduced the three
class types in Studio which handle relational database access. Next time we
shall see how we link our application to the database, and how we use the
classes in our application.
========================================
StudioTIPS, a learning tool for Omnis developers
Raining Data have teamed up with Vencor Software to offer Omnis Studio
developers the opportunity to download StudioTIPS Lite for FREE. StudioTIPS
is a learning and resource tool written in and accessed in Omnis Studio,
providing code examples, explanations, demos and tutorials. To find out more
about StudioTIPS and to download a copy of the Lite version, please go to:
www.omnis.net/downloads/studiotips.html
For more details about StudioTIPS and to purchase the full version of
StudioTIPS, please visit the Vencor Software web site at:
www.vencor.on.ca
========================================
Masked Entry Field Techniques
By David Swain, Polymath Business Systems Inc
Email: dataguru@polymath-bus-sys.com
Web: www.polymath-bus-sys.com
----------------------------------------------------------------------
A window field type that offers a lot of possibilities is the Masked Entry
Field. It allows us to apply special display formatting to variable values
to make them more readable or meaningful. It also allows us to guide or
structure the entry of such values so that the data is stored either without
additional formatting characters or with consistent and appropriate
formatting characters included.
There are two distinct aspects of a Masked Entry Field: the Display Format
and the Input Mask. These aspects are entirely independent of each other,
although there are also many occasions where they can work well together.
The special properties of this field type are described on pages 179-180 of
the Using Omnis Studio manual and the mechanics for assigning values to
these properties are given on pages 183-190 of the Omnis Programming manual.
Beyond what the manuals provide, there are some useful techniques and a
caveat of which you might wish to be aware in using this field type:
Display Formats
By far the more flexible and widely useful aspect of a Masked Entry Field is
the Display Format. It is used to impose a special "look" to the variable
value it presents while not affecting the value itself.
As the manual explains, there are different formats that can be applied
based upon the data type of the variable associated with the field. This
data type must be expressed using the formatmode property, which can take on
one of four values: kFormatBoolean, kFormatCharacter, kFormatDate or
kFormatNumber. The formatstring property then recognizes certain characters
as "placeholders" with special meanings depending upon the formatmode
setting. The programmer can either select from up to 30 predefined formats
per data type (which are stored in the System Tables named #BFORMS, #TFORMS,
#DFORMS and #NFORMS respectively) or manually set the format string.
The application of a text color is another (optional) part of a display
format. This will usually be applied when different "sections" are used in
the format string to further emphasize the variable value. For example, we
might wish to show positive numeric values in black and negative ones in
red. The color is applied by enclosing the name of the color in square
brackets within the appropriate section of the formatstring property value.
Any of the sixteen system colors can be used. The "name" of each color is
the name of its color constant without the leading "k".
Input Masks
Input masks are a bit more rigid and will be less widely used than display
formats. They are primarily useful for guiding or structuring the input of
formatted character values, but other data types can occasionally make use
of them. For example, we can restrict entry of an integer value to a single
digit by using an input mask if we need to allow only values from zero
through 9.
Input masks are an "all-or-nothing" proposition. If a mask is applied to a
field, it must be completely filled before Omnis will allow the focus to
leave the field. The field can only be left empty if the associated
allowempty property value is set to kTrue. If you are still using an early
version of Omnis Studio, the allowempty property for a masked entry field
may not be available to you (and you should really upgrade your copy of
Studio!), but there is a useful workaround that can have other utility as
well:
Consider an entry field for a US telephone number. Such values have a
consistent structure and so are good candidates for the use of a masked
entry field. Using both an input mask and a corresponding display format, we
can store just the critical part of the number (the digits one would dial)
while enforcing a consistent format for display and data entry that enhances
readability. In some cases, we may not have a value to enter into such a
field. Before the introduction of the allowempty property, this posed a
serious problem, but even now there can be benefit from the workaround. Put
the following code in the $event() method of such a field:
On evBefore
If not(len(fld($cfield.$dataname)))
Calculate [$cfield.$dataname] as '0000000000'
Do $cfield.$redraw()
End If
Quit event handler (Pass to next handler)
On evAfter
If fld($cfield.$dataname)='0000000000'
Calculate [$cfield.$dataname] as ''
Do $cfield.$redraw()
End If
Quit event handler (Pass to next handler)
This supplies a dynamic default value for the variable associated with the
field and has the (albeit slight) advantage of reducing the number of
keystrokes required to enter a telephone number that ends in one or a number
of zeros. This works for us because the zeros in the default string act as
placeholders (replacing whatever our original formatstring value uses). It
also works because field properties must be satisfied before event handling
methods come into play. So even if the user simply tabs out of the field
without typing a value, there is still a "complete" value in the field and
the cursor is allowed to leave. The "On evAfter" block of the event handling
method then cleans up the residue. Notice that this is written in a very
generic way, so it can be applied to any field with an input mask requiring
ten characters (and we can easily change the number of default characters
for other needs as well).
Dynamic Application of Display Formats and Input Masks
There may be times when a display format and/or input mask must change for a
specific field. In fact, there may be times when that field needs to NOT
impose an input mask. Consider the example from the last issue of this
newsletter where we set up a dropdown list to choose a payment type for an
invoice. In the real world there will most likely be an associated payment
details field for entering check or credit card numbers or other details
about the payment. Some of these will require free text entry while others
will require various input and display masks.
There is currently a problem here. Certainly we can set an input mask and a
display format using notation, but when we remove an input mask by this
means (setting its value to an empty string), we can no longer enter data
into the field (at least on my copy of Omnis Studio 3.0.1). This means that
we need a separate field with the same dataname for the free text entry
cases (cash, check and account). Let's name these two fields
"payDetailsMask" and "payDetailsFree". Our associated variable, the dataname
for each of these fields, will be named "payDetails".
To simplify the creation and alignment of these two fields so that they
appear to be one to the end user, we can employ the following technique:
Create the first of the fields, giving it all the necessary property values
including the proper size and position. (We can start with either field.)
Now introduce the second field to the window, placing it anywhere for the
time being. Again select the first field (the one in the proper position)
and copy its properties using the Copy Properties command from the context
menu of the Property Manager. Finally, select the second field and align it
with the first using the Paste Properties command from the context menu of
the Property Manager. The property values from all properties the two fields
share in common (including size, position and dataname) will be given to the
second field in a single step!
As a payment type is selected from our list during data entry, we need the
$event() method of the dropdown selection list to make a number of
decisions. First, it must set the appropriate details field as visible and
make the other invisible. If the masked field is to be visible, the method
must set the appropriate set of formatstring and inputmask values and clear
the value of the details variable. If the free text entry field is to be
visible, the method needs to set an appropriate default value for the
details variable. (The properties of the invisible field won't matter until
it becomes visible again.) In either event, the focus should then be set to
whichever details field was made visible.
Here is an example of such an event handling method. Let's examine it a
section at a time:
On evClick
Calculate Invoice file.payMethod as payTypeList.$line-1
Switch Invoice file.payMethod
Case 0 ;; check
Hide fields {payDetailsMask}
Show fields {payDetailsFree}
Calculate Invoice file.payDetails as '#'
Queue set current field {payDetailsFree}
Queue keyboard event {Right}
Case 1 ;; cash
Hide fields {payDetailsMask}
Show fields {payDetailsFree}
Calculate Invoice file.payDetails as 'Received on delivery'
Queue set current field {payDetailsFree}
Case 2,3,5 ;; Visa, MasterCard, Discover
Hide fields {payDetailsFree}
Show fields {payDetailsMask}
Calculate $cinst.$objs.payDetails.$formatstring as '@@@@ @@@@
@@@@ @@@@'
Calculate $cinst.$objs.payDetails.$inputmask as '>>#### ####
#### ####'
Calculate Invoice file.payDetails as ''
Queue set current field {payDetailsMask}
Case 4 ;; American Express
Hide fields {payDetailsFree}
Show fields {payDetailsMask}
Calculate $cinst.$objs.payDetails.$formatstring as '@@@@ @@@@@@
@@@@@'
Calculate $cinst.$objs.payDetails.$inputmask as '>>#### ######
#####'
Calculate Invoice file.payDetails as ''
Queue set current field {payDetailsMask}
Case 6 ;; Account
Hide fields {payDetailsMask}
Show fields {payDetailsFree}
Calculate Invoice file.payDetails as 'Due on receipt of invoice'
Queue set current field {payDetailsFree}
End Switch
First, the common credit cards: Visa, MasterCard and Discover. These all
have the same structure for their numbers: 16 characters in groups of four
separated by a space. Their payMethod values are 2, 3 and 5 respectively and
we can deal with them in the same Case block.
American Express has a slightly different credit card number structure, so
it requires a separate Case block. Again, matching input mask and display
format values must be supplied.
For all of the credit card variations using the masked field, we clear the
value of the payment details variable. In this way, when the focus is placed
in the masked field, the cursor will highlight the first placeholder
character ready to accept data entry.
The payment types requiring free text entry each set an appropriate default
value. For "account" we offer the string "Due on receipt of invoice" (or
some other string involving the terms for that customer). For "cash" we
offer "Received on delivery". For either of these options, the entire value
will become selected when the entry field receives the focus in anticipation
that the user may need to overwrite it.
For "check" our need is slightly different. Here we want to place a "#"
symbol in the field and then place the cursor immediately behind it in
anticipation of the user entering a check number. Since we know that the
value "#" will be highlighted once the entry field receives the focus, we
simply queue a "right arrow" keyboard event to occur after the field
receives the focus. This yields the desired result.
If the behavior of a masked entry field with a notationally emptied
inputmask value changes in some future version of Omnis Studio so that it
works the same as one with a default empty value, the method above will
simplify and we will only need the one field. We would then simply have to
set the formatstring and inputmask values to empty for the free text entry
payment types. All other operations would remain the same.
Custom Components
Commonly used Masked Entry Fields with specialized formats and custom
methods are ideal candidates for storing on a special page in the Component
Store. Some tips on doing this in the next issue.
========================================
About the Omnis Studio manuals
Full and comprehensive user documentation for Omnis Studio is provided in
PDF format on the product CD and to download from the Omnis web site. In
particular, the 'Getting Started with Omnis Studio' manual is aimed at
newcomers to application development and newcomers to Omnis Studio and
describes how to develop a software application using Omnis Studio. For more
information about this and the other Omnis Studio manuals, please go to:
www.omnis.net/products/studio/manuals.html
To download the Omnis Studio manual PDFs, please go to:
www.omnis.net/downloads/manuals.html
Printed copies of the main manuals are available from Trafford Publishing,
an 'on-demand' publisher based in Canada. Full details at the above links.
========================================
I hope you've found this issue of the Omnis Tech Newsletter both interesting
and informative. Please send me your comments and feedback, and include
suggestions for future articles if you like. We would like to hear from
you...
Regards,
--Andrew Smith.
Omnis Tech Newsletter Editor
Email: editor@omnis.net
========================================
No part of this newsletter may be reproduced, transmitted, stored in a
retrieval system or translated into any language in any form by any means
without the written permission of Raining Data.
(c) Copyright Raining Data, Inc., and its licensors 2001. All rights
reserved.
Omnis(r) is a registered trademark and Omnis 7(tm), and Omnis Studio are
trademarks of Raining Data UK Ltd. Other products mentioned are trademarks
or registered trademarks of their corporations.
========================================
To unsubscribe from this newsletter or change your subscription options,
please go to:
www.omnis.net/newsletter