[Omnis-Newsletter] Omnis Technical Newsletter

[omnis-news-admin@omnis.net]

 January 9th, 2002

========================================

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 and HAPPY NEW YEAR!

Welcome and thank you for subscribing to the 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.

In the first article in this newsletter, Geir Fjaerli continues his tutorial
by adding a list control to the application Task window and implementing a
Search to respond to user clicks. In the second article, David Swain
discusses how you can create your own custom data types in Omnis Studio. 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 part 15: Implementing the data entry interface, part 4., by Geir
Fjaerli
-Omnis News: A New Omnis Conference in March, and the launch of The
datApprentice Program.
-New 2002 Training Dates
-Custom Data Types, by David Swain
-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, now available for Mac OS X. Geir is a regular contributor to the
annual Omnis Developer conferences in the US and Europe, speaking about
Object-Oriented programming in Omnis and SQL development, amongst other
things.

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. Latterly, David has
appeared at Omnis Developer conferences providing his own Studio 101
introductory course to Omnis programming and application development.


========================================

Building part 15: Implementing the data entry interface, part 4.
By Geir Fjaerli, Sunshine Data
Email: geir@sunshinedata.net
Web: www.sunshinedata.net

Hi, and welcome back to our Omnis Studio tutorial. I hope you have all had a
merry Christmas holiday.

Here is our work so far:
Part 1: Hello World, our first little Studio application
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, the Property Manager and the Catalog.
Part 4: The Data Structure. Including basic terminology.
Part 5: Creating schemas and database tables.
Part 6: Adding table classes and using our schemas in application windows.
Part 7: What did the wizard build, and a tour of the method editor.
Part 8: The logon window and object class.
Part 9: The task window.
Part 10: The task window continued.
Part 11: Designing a proper data entry interface.
Part 12: Implementing the data entry interface.
Part 13: Implementing the data entry interface, part 2.
Part 14: Implementing the data entry interface, part 3.
You should have those handy, and your "tasks.lbs" library file.

As usual, I strongly suggest that you refer to these parts if you haven't
already, or if you feel uncertain about the details. From part 5 onwards,
each new part will be based on programming done in previous parts, so you
have to follow them all. (For back issues, please go to
www.omnis.net/newsletter and click on the Newsletter Archive link.)

WHERE ARE WE?
In our last issue we continued implementing our data entry interface. That
issue was spent defining and explaining the where clauses for the Find
function. At the end we had a functional method able to handle finds with
only one match. Today we are going to handle multiple match finds, by
implementing a list in our task window.

THE RESULT LIST.
You will remember that our Find method in wTask actually built a list from
the select table of our query. We defined a where-clause according to the
users input, and then we performed a $select using that where-clause. So our
Find already builds the list. Today we will add the two missing bits:
1. The list field in the window that will display the found records, and
2. The logic behind this list that handles user selections.

So, to add the list open the wTask window in design mode. We have already
added a tab pane control, and placed our details fields on the first tab.
The list is going on the second tab. So click on the second tab (currently
labelled "Tab 2") to select it. We want to name this tab "List", so with the
tab selected, go to the Property Manager and select the Pane tab, and set
the tabcaption property to List.

Now this second tab, or page, on the tab pane control is currently empty. We
want to add a list to allow the user to see and select from the list of
matching records built by our find. Studio however offers a number of
different list controls, each specialised for a different purpose. So lets
us quickly run through them and get familiar with their look and use.

Note that they all have a number of properties and features besides those
mentioned. My co-writer David Swain has written about some of these in
detail in previous issues, please refer to these for more info. Here is the
quick overview:

THE LIST BOX: This is the standard list box in Omnis. It is really simple,
it just displays single line rows of text data. It has no columns, instead
you have to set up how many characters to allow for each column using the
jst() function. Since the columns are based on number of characters, you
have to use a mono-spaced font (that is a font where all characters have the
same width, like Courier) for this one. The list box does not have a header.

THE CHECK LIST: The check list is pretty similar to the list box, but adds
one useful feature: All lines have a checkbox in front. This allows you to
make multiple choices that are persistent, that is they remain selected
regardless of other actions.

THE HEADED LIST BOX: This adds two main features to those of the list box:
1. Columns. The headed list box lets you set up real columns of desired
width. This means you can use any font. The columns can have dividers, and
may be resized by the user.
2. Header. As the name implies, the headed list box has a header section,
which contains text for each column, usually descriptive column names. The
header can be enabled to accept click, e.g. to trigger sorting on the
selected column.

THE STRING GRID: The main feature of this one is that it allows the user to
type directly into the list, by clicking a column the cursor will be placed
there ready for typing. Note that all the enterable grid types share one
behaviour, that is you cannot select a line while it is enterable. You must
disable data entry to allow "standard" click and double click behaviour.

THE DATA GRID: The data grid adds two main features to those of the string
grid:
1. Support for multiline text and other data types, including pictures. In
the data grid you can display up to 5 lines of text for each list line, you
can have pictures and you can set columns to be drop down lists for preset
selections.
2. Each column may be handled specially, with its own justification and
colour etc, by setting the user defined property to true. Note that the data
grid has some unresolved issues still in its event handling.

THE COMPLEX GRID: Aptly named, this control allows you to place other
controls (entry fields, buttons etc) inside the grid. So it basically allows
you to build a custom grid type yourself. In Omnis 7 it was the only
alternative to a simple list box, and a lot of people still swear by it. I
never use it, and it is not the first control I would advise a newcomer to
try out. You will find most of what you need in the other grid types.

THE ICON ARRAY: The icon array displays icons, as the name suggests. It can
be set to display big (48x48) or small (16x16) icons, with or without text.
The Browser in Studio (in large or small icons view) is a good example of an
icon array.

THE TREE LIST: This allows you to build a hierarchical view to your data,
with multiple levels of child data nodes that can be expanded or collapsed
as you drill down into your data. The Windows Explorer or the folder list in
Outlook (for you Windows users) is a good example of a tree list. The Studio
field list is another, as we saw in the last issue.

THE POP UP LIST: The pop up list is a special list type that normally only
shows a single line, that is the current line of the list. When you click on
it, it pops up to display the rest of the lines (or as many as there is room
for). The pop up list is a standard Macintosh interface control available on
all platforms: see note below.

THE DROP DOWN LIST: This Windows control behaves like its Macintosh cousin
the pop up list. It looks different, and when clicked the list "drops down"
more like a menu.

Note that Studio implements both the pop up and the drop down lists on all
platforms. I urge you to use the appropriate one for the platform though. In
our task window we use the pop up/drop down list for our Employee selection
list.

THE COMBO BOX: This Windows control is the last one I will mention. It looks
like the drop down list, but with one important distinction. It is actually
a combination between an entry field and a drop down list. So the user can
actually type into the field as well as use the list to select.

OK, having browsed through he available list/grid types, it is time to make
our choice. The purpose of our list was to display matching records and let
the user select the correct one. This sounds like a job for the list box or
the headed list box. We will use the headed list, as it looks much nicer and
more professional.

ADDING THE HEADED LIST BOX.
So in your wTask window, select the second tab (the one we named List) if it
is not already selected. Drag a headed list box from the Component store on
to the tab. You will notice that when the cursor enters the tab page, a
rectangle will appear to indicate that we are within the area where we can
place controls. Now drop the list box in the upper left corner of the tab
page, and then use the mouse and/or the arrow keys on your keyboard to
position and resize it until it fills most of the tab. You will notice that
the tab has a small margin on each side, we cannot get all the way to the
edge.

The next task is to decide which columns to display. A list control doesn't
have to display all the columns in our list variable, we can select the ones
that are of interest to the user. In our case I would say the date planned,
the task type and the task title would be useful to identify a task. So we
will add these in that order. Another column we would like to have is the
employee. However, the name of the employee belongs to another table, we
don't get that when we build our list, just the number. So we will leave
that one for now. Later we shall see how we can use a query class to combine
information from several tables with a so called "join".

With the list control selected, go to the Property Manager. On the top of
the General tab there are three properties that interest us:

Name: This is the name of the control, as we have discussed earlier this
property is used to give it a meaningful name for the developer. We can name
ours tasklist.

Dataname: This is the name of the variable that the control is bound to. In
our case that is the list variable iTaskList, so type that into the dataname
property. As usual, you can use the Catalog (F9) to find the variable, it
will be under Instance vars.

Calculation: The calculation property tells Omnis which columns to display,
in our case cDatePlanned, cType and cTask. Now calculation properties are a
bit tricky, because the syntax is different for the different list controls.
For the headed list box we use column1name,tab,column2name,tab,column3name
and so on. So the column names are tab separated. Now the calculation
property doesn't actually take a tab, so we cannot simply hit the tab key on
our keyboard. Instead we have to insert a tab character value. That is
character 9, or chr(9) in the character set. So that would be something like
column1name,chr(9),column2name,chr(9),column3name. Recent versions of Studio
also has a constant for that, called kTab.

Now each column belongs to the list, so we use the syntax
listname.columnname in the calculation. The tab separated column list then
will look like this:
iTaskList.cDatePlanned,chr(9),iTaskList.cType,chr(9),iTaskList.ctask, or
iTaskList.cDatePlanned,kTab,iTaskList.cType,kTab,iTaskList.ctask

Finally, the calculation property expects a single string value, so we must
enclose our string with a con() function:
con(iTaskList.cDatePlanned,kTab,iTaskList.cType,kTab,iTaskList.ctask)

An important note here: In this tutorial I will refer to comma separated
functions, as that is the standard in an English Omnis. However, in
localized versions this may be different. Why? Because in many countries,
including my own, we use comma as the decimal separator, instead of the
decimal point. And then we cannot use comma as a function separator too. Say
if (in the English Omnis) in a function I wrote con('Geir',1.2), that would
concatenate Geir with the number 1.2. But in a Norwegian version that would
be con('Geir',1,2), and that would confuse Omnis because it would not know
if I meant to concatenate Geir with 1 and 2, or with the decimal number 1,2.
So we have to use another function separator, usually the semi colon (;). So
if you have a localised version, keep this in mind and substitute as needed.

Type the above expression into the calculation property.

Now we have set up the relation between the control and the variable. We
just need to do a few adjustments to the layout.

First of all you will notice that the control still only has two columns,
even if it is defined with three. And the column header shows "Column 1" and
"Column 2". This we can easily corrected. Go to the Appearance tab in the
Property Manager. There you will find the following two properties to set:
Designcols: Set this to 3 to make the control show 3 columns in design mode.
Columnnames: This is the column titles to be displayed in the header, and
takes a simple comma separated list. Set it to "Date,Type,Task" (without the
quotes).

Finally, if you are not satisfied with the column widths, you can change
these by clicking on the column divider in the header and dragging to
increase or decrease the column width. This of course will be easier to do
once we have tested the list with some actual data and see how they fit in
the columns.

So, that was the layout of the headed list box. Next we need to add some
code. As discussed above, there are two things we need to do here:
1. To make the list display all records if more than one was found, and
2. To allow the user to click in the list to select the correct record.

THE FIND METHOD.
Go to the method editor for the wTask window and select the Find method.
When we stopped in the last issue, it was actually 95% complete. It sets up
the where clause and performs the select and fetch, and it handles the two
cases where either zero or 1 record was found. The select and fetch is done
on the iTaskList, so if multiple records were found they are actually
already in the list. All we have to do is redraw and display it.

So the following three lines will do, if you place them just above the last
End if. So select the End If method line and press Ctrl-I to make room
between it and the Do $cinst.$redraw() above it, then enter the following
code:

  Else
    Do $cinst.$objs.tasklist.$redraw()
    Calculate $cinst.$objs.TabPane.$currenttab as 2

This code does the following if the else clause is triggered:
1. Redraw the task list ($cinst.$objs.tasklist). Earlier we have use redraw
on $cinst to redraw the whole window. Here we redraw only one of the
controls in the window. This is the only one that will have changed, so
there is no reason to redraw the rest. Note that technically, there is
another level of objects here, since the tasklist is placed on a tab pane.
So the full notation path would have been
$cinst.$objs.TabPane.$objs.tasklist. But Omnis allows us to use the simpler
notation string with redraws.

2. Set the current tab of the tab pane so that it displays the list if it
didn't already.

The method lines following the $fetch now should look like this:
  If iTaskList.$linecount=0
    OK message No matching rows {No rows where found to match your search.
Please try again.}
  Else If iTaskList.$linecount=1
    Calculate iTaskRow as iTaskList
    Do $cinst.$redraw()
  Else
    Do $cinst.$objs.tasklist.$redraw()
    Calculate $cinst.$objs.TabPane.$currenttab as 2
  End If

Now we should be ready to try our find with the list. Open the window in
runtime mode (Ctrl/Cmnd-T remember) and click the Find button. Now you can
enter the data you want to find on, and then hit the save button to perform
the find. If you just click Find and Save without typing anything, all our
fields will be empty or zero, and therefore no where clause will be built.
And a select without a where clause will return all rows.

One little note here: We named our OK button Save because that made sense
when inserting or updating data. For Find it is less appropriate. Later we
shall see how we can use notation to change the text of a button at runtime,
so that the OK button can reflect the current mode.

OK, so now we have a list that displays the matching rows. But what about
selecting. We can indeed click in the list, but that doesn't do us much good
yet. If you try selecting a line in the list and then go back to the details
tab, you will see that the record has not been loaded. We need code to do
this. This code we will add to the headed list box itself.

THE HEADED LIST BOX EVENT CODE.
You will remember from earlier that Studio controls have a special method
called $event which is triggered by user actions on that control. We used
that to set the employee ID when we selected an employee from the drop
down/pop up list. All events on the control are sent to this method, then in
the method we use the On statement to test which event we are receiving. The
event received depends on two things:
1. The user action. Did he/she tab, type, click...
2. The type of control. Different controls react to different events, and
sometimes two controls even report different events for the same user
action.

The built in controls will come with the relevant event filters already
added to their $event method. This is true for the headed list box as well.
Open the method editor for the list box (if you double-click the list box it
will take you directly into its event method.) You will notice that the
event method has the following two lines of code:
  On evClick     ;; Event Parameters - pRow ( Itemreference )
  On evDoubleClick     ;; Event Parameters - pRow ( Itemreference )

These are the most usual events that a list box will have to handle. The
user clicks in the list, and something happens. In our case we will load the
selected row into our details fields. Now do we want that happen on the
click or on the double-click?

Well, this of course is something that, while in reality we are free to
decide for ourselves, the user will expect it to behave similar to other
programs. My little rule of thumb for list boxes is:
Use single clicks to load and redraw data, and little else.
Use double-clicks to perform any action apart from that.

Note that Studio just like most tools cannot separate between a single click
and the first click of a double-click, and when you double-click it will
therefore report both a click and a double-click. Some people find this
annoying. But if you think about it, this is the way it has to be.

After all, a double-click is just two single clicks coming close enough
together. So there is nothing different between a single and the first of a
double-click. It is how fast the next click comes that decides. So the only
way we could prevent the single click is if Omnis delayed acting on the
first click to see if a second one was coming. That of course is no good.
Nothing irritates the user more than an unresponsive application.

So we use this to our advantage, and split the code accordingly, into a load
and an action. That suit us well. We do want to load and redraw the row
selected, and we have an action to perform, that is to set the current tab
to the Details tab.

So under the evClick event, we would want the following code:
  Calculate iTaskRow as iTaskList
  Do $cinst.$redraw()

The first line is a little Studio shortcut, when we use iTaskList like this,
without specifying the line number, Omnis will assume that we mean the
current line, that is the line the user clicked on.

However, the observant reader will notice two things about this:
1. This code does load the task, but it will not display the employee,
because that is a drop down list and we have to find the correct line to
display.
2. We already have exactly the same code in our Find method, when a single
line was returned by our query.

Now whenever the same code exists in multiple places, it makes sense to move
them both into a common method, a subroutine somewhere. In this case the
subroutine should be a class method in the window. So add a new method
(right-click on class methods and select "Insert new method"). Name the new
method LoadRow. Add our two method lines to this new method:
  Calculate iTaskRow as iTaskList
  Do $cinst.$redraw()

Then go back to the $event method for the list and just below the On evClick
statement add the following:
  Do method LoadRow

Go back to the Find method, and find the same calculation and redraw there,
and replace with
  Do method LoadRow
there as well. The last part of the Find method should then look like this:

  If iTaskList.$linecount=0
    OK message No matching rows {No rows where found to match your search.
Please try again.}
  Else If iTaskList.$linecount=1
    Do method LoadRow
  Else
    Do $cinst.$objs.tasklist.$redraw()
    Calculate $cinst.$objs.TabPane.$currenttab as 2
  End If

So what about displaying the employee. Well, I think that should be done in
the LoadRow method too. In fact, a single line of code will do it for us:
  Do iEmployeeList.$search(iEmployeeList.cID=iTaskRow.cEmployeeID,
    kTrue,kFalse,kFalse,kFalse)

A quick explanation: We are doing a List.$search, that is a search in the
list. $search takes 5 parameters:
1. The search criteria, in this example
"iEmployeeList.cID=iTaskRow.cEmployeeID". That is, select the line(s) where
the ID in the employee list matches that in our details row var.
2. From start (We may already have done a search so that the current line is
a bit down the list, and want to continue searching from there.)
3. Only selected (Allows to only test the lines selected by a previous
$search or user selection.
4. Select matches. If true multiple lines may be selected.
5. Deselect non-matches. If true any previously selected lines that does not
match this search will be deselected.

In our case, we are only looking for one line, and we know it will be unique
because we search on the primary key, so we set From start to true and the
rest to false.

The $search statement should be inserted before the redraw, as the list must
be redrawn too. So then our LoadRow method will look like this:

  Calculate iTaskRow as iTaskList
  Do iEmployeeList.$search(iEmployeeList.cID=iTaskRow.cEmployeeID,
    kTrue,kFalse,kFalse,kFalse)
  Do $cinst.$redraw()

That leaves us with one thing to do: When the user double-clicks we want to
go to the details pane to display the selected record. So go back to the
event method for the headed list box, and add the following statement under
the evDoubleClick event:

  Calculate $cinst.$objs.TabPane.$currenttab as 1

And that completes our Find function, and our lesson for today. Now you can
insert, update and find as much as you want.

Only one major bit (the Delete button) and some rough edges remain in the
task window. That will be part of the next lesson. A bit further down the
line we shall return to the wTask window and enhance it.


========================================

Omnis News

SIGN UP BY JANUARY 18th AND SAVE $200
Raining Data is delighted to announce and lend support to a new developer
conference in March 2002. The cost of the conference is US$999 if you sign
up on or before Jan 18, 2002, while after Jan 18 the cost will be US$1199.
Read all about the conference in the Omnis News.
NEW EDUCATIONAL PROGRAM
We are delighted to feature a new venture by David Swain, longtime Omnis
developer and trainer, and co-author of this technical newsletter. Back in
December 2001, David's consulting firm Polymath Business Systems announced a
new educational program for high school and college students that teaches
them the business of IT consulting in a real-world and professional context.
Read about it in the Omnis News:
www.omnis.net/news
(plus you can always read the back issues of our news page at:
www.omnis.net/news/backissue.html)


========================================

New 2002 Training Dates
We have just released dates in 2002 for training in Europe. Please go to the
appropriate page on our web site for your region:
UK dates: www.omnis.net/training/uk-train.html
Germany: www.omnis.net/training/gereng.html
Switzerland: www.omnis.net/training/zurichschedeng.html
Benelux: www.omnis.net/training/benelux.html


========================================

Custom Data Types
By David Swain, Polymath Business Systems Inc
Email: dataguru@polymath-bus-sys.com
Web: www.polymath-bus-sys.com

------------------------------------------------------------------

Occasionally we need to deal with variable values that do not exactly fit
the usual Omnis Studio data types. These are usually values that are made up
of composite parts, being somewhat numeric in nature - but not exactly
decimal. Date-time values made up of day, month, year, hour, minute, second,
etc. parts are this type of data. Omnis Studio (fortunately!) gives us a set
of data types to handle date-times since they are so commonly required, but
that is where our luck ends.

There are many similar kinds of values we may encounter in our work for
which there is no direct provision in most database products. Consider
latitude/longitude values in degrees, minutes and seconds or video time code
values in hours, minutes, seconds and frames (with many different frame
rates to choose from) to name just a couple. Even common measurements in
feet and inches, points and picas or some other non-decimal composite units
may appear in projects. Many fields of endeavor have specialized data types
like these. Though not as common as date-time values (or strings or
numbers), we must still find ways to work with them.

There are many facets to the problem of dealing with such values. First, we
must determine how to store and retrieve them. There may also be
manipulations (usually arithmetic operations, but there may be others) we
must perform on them. We may be called upon to convert these values to some
other basis (degree-minute-second values to decimal degrees, for example).
Finally, we need to consider how to display the values on windows and
reports and how to capture them during data entry - complete with range
validation checking or alternate format acceptance for the various parts of
the composite value (like Omnis Studio automatically does for date-time
entry).

There are many aspects to this problem, and those aspects may differ
slightly for different types of data. Let's explore this in general terms
first using short date values as a model, then apply the principles we
derive to the specific examples cited above.

Initial Examination

If we think of this problem as being a game (isn't that what database
application development is all about?), our first task is to determine the
characteristics of the game board or playing field. This will give us some
guidance in how to handle the data type. Here are some things to consider:

1. What are the parts of our composite value and what do we call them?
2. For each part, what is its usual nature (character, number or other)?
3. What range of values is appropriate for each part?
4. What is the standard way such values are displayed?
5. Are there alternate display styles (and does our client use them)?
6. How are such values ordered (in case we must index them)?
7. Is there some common denominator (non-composite value) we can reduce such
values to?
8. What operations are performed with such values?
9. How should such values be entered from the keyboard?

Imagine for a moment that we did not have a built-in data type for simple
date values. How would such a value be viewed in the light of these
questions?

First we break a typical value down to its component parts. We discover that
there are at least three basic parts: day, month and year. But on
examination of the fourth question above, we learn that the year has two
parts of its own (century and year-within-the-century), one of which
(century) is not always displayed in common usage. We also learn from the
fourth and fifth questions that there are many display styles possible for
date values and that the month part can be displayed as either a number
(sometimes padded to two digits with a leading zero) or a character string
(with full name and abbreviation variations). In addition, we learn that
some display modes for a date value may transpose the positions of the day
and month parts, so we must take that into account in how we handle these
values.

Question number two also gives us some additional interesting information.
We learned above that the month part of a date value may be displayed as
either number or character. Further examination reveals that it is basically
numeric in nature, but that common usage assigns a name to each month for
easier recognition. We will have to deal with this at some point, but the
fact that the month value is basically numeric is useful, as we will see.

A slight complication arises when we realize that the range of valid values
for the day part depends on the current value of the month part (and the
year parts in the case of February). We will also need an algorithm to deal
with this issue. At least the lower end of the range is 1 for all months!
The month part can have values from 1 to 12 and the year and century parts
can have values from 0 through 99.

There are some basic operations that are performed on date values. We can
increase or decrease a date value by a number of days, months or years. We
may also need to determine the number of days, months or years between two
dates. Further consideration of these facts indicates that the date value
itself can be treated numerically as a number of days from some established
(though arbitrarily selected) zero point. Date values are ordered along this
"time line".

Finally, we consider how these date values should be entered from the
keyboard. We might want to allow for certain shortcuts or data entry
options, but convert the entered value to a common display format.

Having analyzed some representative examples of values for this data type
and determined the rules such values must follow, we now need to make some
decisions about how to handle them.

Storage and Retrieval

The first thing to do is consider how such values might be most conveniently
(and compactly) stored in our database. For most data types, we have three
options: store the values as strings, reduce the values to numeric
equivalents, or convert them to some binary form and store them in a binary
field. Each option has good and bad points. The final choice is influenced
by a number of factors.

First consider what is most convenient or most direct. In the case of a date
value, it might at first seem most convenient to store the value as a
character string. In fact, many early database products did just that. Of
course, this would limit us to only one display and data entry mode if we
want to keep it simple. Indexing such a character column would also give us
undesirable results, since ASCII-based ordering would not put our date
values in the logical order for their nature.

Staying with the idea of using a character data type, we could spend a
little effort to convert such values to and from a "standard" storage format
that would sort properly. This would mean putting the century, year, month
and day parts in that order from left to right within our composite value
after converting the month part to a numeric equivalent.

Of course, we also have to consider how the process is to be reversed, That
is, how can we extract values stored in this way and convert them back into
a displayable format? This will depend on how the values are stored in the
first place. We need to be able to distinguish among the four parts of our
composite value. We have two basic choices in how to display such values:
store a delimiting character between each adjacent part of the composite or
pad each part of the composite to a fixed length so that we can simply pick
the value apart for display conversion without storing delimiting
characters. The first technique will generally require more space for our
date values, so the second seems more desirable.

But what if we take a different approach and store the values in a numeric
field? We could choose a zero date (like December 31, 2000) and have all
date values stored as the number of days from this date (so that 1/1/2001
would be day number 1, for example). We could use the "Long integer" data
type that only requires four bytes per value for storing the derived number.
Our indexing problem vanishes, as the "time line" now becomes an integer
"number line" stretching to about two billion days in the positive and
negative directions from our zero date. All we need is an algorithm that
converts between a date value and its numeric equivalent using this zero
date. The only difficulty we might encounter is in incrementing a date value
by month or year since the length of a month or year can vary from one to
the next. Still, we should be able to find (or develop) an algorithm to deal
with this. The numeric approach seems promising.

But what about using a binary field? We can use ranges of bits rather than
ranges of characters to store the component parts of a date value. We could
use four bits to store the month part (value range 0-15), five bits for the
day part (value range 0-31), and seven bits each for the year and century
parts (value range 0-127). If a byte is eight bits, we can store any date
value in three bytes (with a bit to spare!) using this technique, saving 25%
of storage space over the numeric technique.

There's only one problem with using a binary field for this in Omnis Studio:
binary fields cannot currently be indexed in the native database. Although
this is the technique Omnis itself uses to store date values, we are missing
an important tool in emulating this behavior. This is probably not a
limitation with most SQL databases, however, so we should be able to emulate
this behavior using those back ends.

Fortunately, we don't have to worry about this for date-time values since
Omnis Studio (and many versions of Omnis before it) gives us so many tools
to deal with these complex problems. But the point of the exercise was to
examine the three basic storage options.

Conversion to Other Data Types (Bases)

Many of the composite values we will encounter can be reduced to a numeric
value. This will usually (but not always) be expressed in terms of the least
significant component. For example, feet and inches will easily reduce to a
total number of inches. But it may also need to be expressed in decimal
number of feet. We may have chosen to store our value as a delimited string,
but still be required to perform numeric calculations. Or we may have chosen
to store the values numerically, but we still have to display them as a
delimited string. In either event, some type conversion will be required
somewhere along the way.

The different component parts of composite values usually have a scaled
relationship to one another. There are always twelve inches in a foot, for
example. Converting to some numeric basis is simply a matter of choosing the
basis and dealing with the scaling factors up or down for the other
composite parts. Consider the value 2 gallons 1 quart 1 cup. We can express
this as:

Calculate decimalGallons as gallons+quarts/4+cups/16 -> 2.3125 gallons
Calculate decimalQuarts as gallons*4+quarts+cups/4 -> 9.25 quarts
Calculate totalCups as gallons*16+quarts*4+cups -> 37 cups

If we had to convert this to other units (milliliters, for example), we
would simply have to incorporate another conversion factor.

We may also have to perform the opposite conversion. This means picking the
basic value apart into its components using combinations of the int() and
mod() functions and combining those results using a con() statement. To
convert our decimalQuarts value back to GQC format, we might use the
following method:

Calculate gallons as int(decimalQuarts/4)
Calculate quarts as int(decimalQuarts-gallons/4)
Calculate cups as mod(decimalQuarts-gallons/4,1)*4
Calculate GQCvalue as con(gallons,' gallons ',quarts,' quarts ',cups,'
cups')

There may be additional formatting manipulations required if one or more
component parts must be expressed in an alternate string format (like month
names).

This may seem like a lot of work without much reward, but there are other
things we may need to do with these converted values.

Arithmetic Manipulations

With many composite data types, we may often need to determine the "distance
between two points" of that data type or other similar values based upon two
or more initial values. Data types requiring this manipulation will reduce
to a numeric equivalent, but the result will usually need to be expressed in
the original format. For example, we may need to determine the length of a
video clip knowing the "in" and "out" points of the clip expressed in time
code. Each of these values will easily reduce to a number of frames, so we
can simply subtract one from the other to yield a number of frames as a
result. But the user will relate to this result better if it is expressed
back in time code format (hours:minutes:seconds:frames), so we need to
convert to a numeric basis, perform our calculation, and then convert back
to our original format.

Conversion to an integer or decimal numeric basis and then back again is an
essential part of performing most manipulations on composite values. But
sometimes, dealing with the transfer from storage to display may require
this process as well.

Display and Data Entry

If our chosen storage format is not exactly the same as our display format,
we will have to perform some kind of conversion. We may have chosen to
simply not store delimiting characters, so we must strip them before storage
and replace them for display. We may also want to remove unwanted zeros used
as padding characters for fixed length storage formats.

On the other hand, we may have chosen to use masked entry fields for display
and data entry, so our fixed length storage format may be directly useful in
the entry field with no conversion required. Time code values, for example,
can be stored in an eight character field using two character positions for
each of the four component parts. We could use a display format for time
code values like:

"@@:@@:@@:@@"

and an entry mask like:

">>##:##:##:##"

Note that the display format is only displaying whatever characters the
value contains and placing a colon character between each pair. The entry
mask on the other hand limits data entry to numerals only.

Omnis Studio masked entry fields are incredibly useful for displaying
composite values stored in this way, but they may still be awkward for
accepting data entry for some data types where range checking is required.

Data Validation and Range Checking

The problem is that the range of valid values will often be different for
each component part in a composite value. In time codes, the hours can range
from zero to 99 so any numeric value entered into the first two character
positions is valid. But the minutes and seconds can only range from zero to
59, and the frames component is dependent on the frame rate and will usually
be even more restricted that minutes and seconds. So a user could easily
enter invalid values for any but the hours component of the time code value.

If we want to perform data validation on each component part, we can either
do some very fancy event programming on a keystroke-by-keystroke basis or we
can break the entry field into separate fields for each component part.
Neither choice is better than the other, it just comes down to what is more
comfortable in a specific case.

It might get a bit cumbersome copying all the algorithms for data validation
and other operations from one field to another. Perhaps there is a way to
centralize many of these operations.

Data Type Rules Objects

To really handle a custom data type in an elegant manner, it helps to create
an object class to hold all the rules and value manipulation methods for the
data type. The choice of scope for instantiating this object will vary
depending on need and personal preference, but only one instance should need
to be within scope at any time.

The full set of methods this object class should contain will vary as well,
but there are a certain minimum number of methods that nearly any custom
data type will require:

$packdata - method to perform conversion from display format to storage
format

$unpackdata - method to perform conversion from storage format to display
format

$validateA, $validateB, etc. - methods for validating values for each
component part

We instantiate the object at some appropriate level of scope and name it in
a way that is easy to remember. We can then use the name of the object dot
the name of the method as though it were a function. So if we name a time
code object "tcode", we could prepare our display value using:

Calculate displayValue as tcode.$unpackdata(storageValue)

We would include in our data type rules object all the conversion routines,
display rules, data validation and manipulation algorithms we require for
that data type. As we improve our techniques, we only have one place to
modify the code.

Now let's examine our two examples in more detail.

DMS Example

There are many fields that employ degree-minute-second (DMS) notation. Any
field dealing in latitude and longitude works with such values. These
include aviation, navigation, geologic surveys, global positioning systems,
astronomy...the list goes on. Some of these fields of endeavor are making
the conversion to decimal systems, but many still use DMS.

The standard format is as follows: an integer number of degrees followed by
the degree symbol and space, then an integer number of minutes followed by
the single quote character (ASCII character 39) and space, and finally
either an integer or a decimal number of seconds followed by the double
quote character (ASCII character 34). Unfortunately, the degree symbol is in
the extended ASCII character set, so it is a different character number on
Macintosh (161) and Windows (176) platforms. This is handled most easily if
a rules object contains an instance variable for this character that is
populated with the appropriate value in its $construct() method.

Instance var degreeSymbol (Character)

Calculate degreeSymbol as pick(sys(6)='M'|sys(6)='X',chr(176),chr(161))

We can then poll the object instance to get the proper symbol for formatting
DMS values on our windows and reports:

$getdegreesymbol  ;;  provides degree symbol for formatting

Quit method degreeSymbol

If we choose to store our delimited string directly in the database as a
character value, we should still substitute a more universal delimiter for
the degree symbol since it is not the same ASCII value on all platforms. A
forward slash ("/") character, the letter "d" or some other symbol that is
stored consistently across platforms will do as well. We just have to
remember to perform the substitution when storing and retrieving values of
this data type. So our unpacking method might contain a line like:

Calculate displayValue as replace(storageValue,'/',degreeSymbol)

We may occasionally need to convert DMS values to decimal degrees and vice
versa. Converting to decimal is the more complex of these operations in that
we must construct each component value separately. Converting to DMS only
requires that we perform one (somewhat complex) calculation.

$convtodecimal  ;  converts degrees, minutes and seconds angle to decimal
Parameter dmsString (Character)
Local var delimiters (Character)
Local var degreeValue (Long integer) ; range 0-359
Local var minuteValue (Short integer) ; range 0-59
Local var secondValue (Number floating dp) ; can be fractional

Calculate delimiters as con(degreeSymbol,chr(34,39))
Calculate degreeValue as strtok('dmsString',delimiters)
Calculate minuteValue as strtok('dmsString',delimiters)
Calculate secondValue as strtok('dmsString',delimiters)
Quit method degreeValue+minuteValue/60+secondValue/3600

$convtodms  ;  converts decimal angle to degrees, minutes and seconds
Parameter decimalDegrees (Number floating dp)

Quit method
con(int(decimalDegrees),degreeSymbol,chr(32),int(mod(decimalDegrees*60,60)),
chr(39,32),mod(decimalDegrees*3600,60),chr(34))

We may need to provide other methods for more complex manipulations as well.
DMS values may be used to provide great circle distances and heading between
two latitude/longitude pairs, for example.

Time Code Example

More and more people are doing video projects today with the great tools
that Apple Computer has developed (FireWire, QuickTime, Final Cut Pro, etc.)
to make the job easier. This means that sooner or later you may encounter a
job that requires storage and manipulation of video time code values.

The standard format for this data type contains hours, minutes, seconds and
frames component parts that are each two place numerals separated by colon
characters. The hours component can technically take on any value from zero
to 99, but the minutes and seconds components are restricted to the 0-59
range and the frames component's range depends on the frame rate. There are
standard frame rates for NTSC video (29.97 frames per second, but often
rounded to 30 for convenience), PAL (25 frames per second), and others, but
computer animation and video sequences can take on many other frame rate
values for special purposes. So the frame rate is an important auxiliary
value for the time code value.

We can store such values conveniently in a character string, padding each
component part to two digits. Such values will index and sort appropriately,
so there is no problem with taking the easy route to data storage.
Display-only fields can use a display format that inserts colons in
appropriate places within this string as demonstrated earlier in this
article.

Data entry for a time code value is most effectively accomplished by
splitting the value into its component parts, then dealing with each of part
in separate data entry fields. This allows for the easiest data validation
on a component basis. The components can then be assembled into the full
time code storage value as the cursor leaves each component entry field
after the data validation test is passed. For example, if the minutes value
is represented by the instance variable "minVal", the event method for the
entry field might look like this:

On evAfter
    Do tcode.$validateMinutes(minVal) returns OktoContinue
    If OktoContinue
        Calculate storageValue as
jst(hrsVal,'-2P0',minVal,'-2P0',secVal,'-2P0',frmVal'-2P0')
    Else
        OK message {Value is out of range.}
        Quit event handler (Discard event)
    End if

For determining the length of a clip between to marker points, we need to
know the frame rate (frames per second) on which the time code is based. We
must reduce the beginning and ending time code values to number of frames,
subtract the larger from the smaller, and then convert the result back to
time code format. If we are working in a consistent time code environment,
we can set up a variable at some global level of scope to act as a constant
for our frame rate and use that throughout the application. But if the frame
rate can vary from one clip to the next, we need to store the frame rate
with each clip record. (We can assume that the frame rate must be consistent
within the clip.)

See if you can't find a half dozen data types that require some sort of
special treatment over the next few days and determine how you might best
deal with them. Practice now can save you lots of time later...


========================================

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 Technical 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 2002. 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