Tips_tutorials   >   Studio102   >   Studio 102 (All Contents)

Introduction

Welcome to the Studio 102 tutorial.

This tutorial is provided to StudioTips Members only. If you are not currently a StudioTips Member please go to www.studiotips.net and click the link provided for becoming a StudioTips Member. The cost of membership is minimal and helps to cover the time and expense of creating these tutorials and maintaining the studiotips.net website.

This tutorial continues from where we left off in the Studio 101 tutorial. In this tutorial we will:

  1. Add a Stateprov table to the database. The Stateprov records will be linked to the Country records using a foreign key.
  2. Create a qStateprovList query class to join the Stateprov child records with their Country parent record in a single list.
  3. Create a wStateprovList window class to add/edit Stateprov records and link them to a parent Country record.
  4. Add a Towncity table to the database. The Towncity records will be linked to the Country records and optionally linked to the Stateprov records since some countries do not have states or provinces.
  5. Create a query class and window class for the Towncity records.

In the process of doing the above we will create a tBase superclass table class with subclass tables classes for each table in the database.

The Studio 101 tutorial gave you very explicit directions for each operation you needed to complete. Knowing that you have been through the Studio 101 tutorial, the Studio 102 tutorial is less explicit when giving directions. For example, the Studio 101 tutorial gave you explicit directions like:

F2 Browser > select Contacts library > double-click tCountry table class.

The Studio 102 tutorial will give you abbreviated instructions like:

Double-click the tCountry table class.

When entering code for this tutorial you will need to create variables as you write the code. The StudioTips Naming Conventions for variables tell you the variable type and scope based on the variable name. Here are a few naming convention hints to help you:

There are more hints in the Conventions Used in this Tutorial section of the Studio 101 tutorial.

State/Provinces

In this section we will add a Stateprov table to the database with a foreign key column which will be used to link each Stateprov record to a parent Country record. We will then create a query class to join the Stateprov child records to their Country parent records. Finally we will create a window class which will allow us to add/edit Stateprov records and join them to a Country record.

Create the Stateprov Table

In this section we will add a Stateprov table to the database. A foreign key column, Country_fkey, will be included in the Stateprov table. The foreign key column will be used to store the primary key of the parent Country record.

In SQL terms, the Country table is called the referenced table, and the Country_pkey column is called the referenced column.

  1. Open the Contacts.lbs library which you created in the Studio 101 tutorial.
  2. F2 Browser > SQL Browser > select CONTACTS_01 session > click Interactive Sql
  3. Enter the following SQL script in the Interactive SQL window:

    CREATE TABLE Stateprov (Stateprov_pkey INTEGER NOT NULL,StateProvName VARCHAR (30) NOT NULL,StateProvAbbrev VARCHAR (5),Country_fkey INTEGER NOT NULL)

  4. Click the Run button to execute the SQL script.
  5. Enter the following SQL script in the Interactive SQL window to create a unique index on the primary key column:

    CREATE CASE SENSITIVE UNIQUE INDEX Stateprov_pkey ON Stateprov (Stateprov_pkey)

  6. Click the Run button to execute the SQL script.
  7. Enter the following SQL script in the Interactive SQL window to create a unique index on the CountryName column:

    CREATE CASE SENSITIVE UNIQUE INDEX StateProvName ON Stateprov (StateProvName)

  8. Click the Run button to execute the SQL script.
  9. Enter the following SQL script in the Interactive SQL window to create a non-unique index on the Country_fkey foreign key column:

    CREATE INDEX Country_fkey ON Stateprov (Country_fkey)



    Adding an index to the foreign key column helps to speed up selects in the Omnis data file. You would not need to do this for other RDBMSs.
  10. Click the Run button to execute the SQL script.
  11. F2 Browser > SQL Browser > select CONTACTS_01 session > Tables > select Stateprov > click Modify Table. Check to make sure the table columns and indexes have been correctly added.
Note

The above SQL scripts used to create the unique indexes is specific to the Omnis data file. For creating unique index constraints on other databases check the documentation specific to the RDBMS.

Insert Stateprov Records

I find it helpful to have some test data in the database when developing an application so lets insert a few Stateprov records using the Interactive Sql window.

  1. F2 Browser > SQL Browser > CONTACTS_01 > Interactive Sql
  2. Enter the following SQL script:

    INSERT INTO Stateprov VALUES (1001,'Ontario','ON',1002)
  3. Click the Run button in the Interactive SQL window.
  4. Enter the following SQL script:

    INSERT INTO Stateprov VALUES (1002,'Quebec','QC',1002)
  5. Click the Run button in the Interactive SQL window.
  6. Enter the following SQL script:

    INSERT INTO Stateprov VALUES (1003,'Florida','FL',1004)
  7. Click the Run button in the Interactive SQL window.
  8. Enter the following SQL script:

    INSERT INTO Stateprov VALUES (1004,'California','CA',1004)
  9. Click the Run button in the Interactive SQL window.

To test the join between the Stateprov and Country records:

  1. Enter the following SQL script:

    SELECT StateProvName, CountryName FROM Stateprov,Country WHERE Country_fkey = Country_pkey
  2. Click the Run button in the Interactive SQL window.
All going well the Stateprov records should appear in the Interactive SQL window list joined to the correct Country records.

Create the Stateprov Schema

In this section we will create a schema class which maps to the Stateprov table in the database.

  1. Create a new schema class named, sStateprov, in the Contacts library.
  2. Set the Server table or view field to Stateprov
  3. Add the following columns to the sStateprov schema class:

    Column Name - Stateprov_pkey
    Type - Number
    SubType - Long Integer
    Primary Key - kTrue
    No Nulls - kTrue

    Column Name - StateProvName
    Type - Character
    SubType - 30
    No Nulls - kTrue

    Column Name - StateProvAbbrev
    Type - Character
    SubType - 5

    Column Name - Country_fkey
    Type - Number
    SubType - Long Integer
    No Nulls - kTrue
  4. Close the schema class.

Create the Stateprov Table Class

We need a to create a table class which is mapped to the sStateprov schema class. We could duplicate and modify the tCountry table class, but that will result in code duplication. The custom table class methods of tStateprov will be identical or only slightly modified. We are also going to need table classes for Towncity and Contact. Duplicating the table class over and over is going to result in lot of duplicate code. Not a good thing.

A situation like is where you want to use a superclass, and create subclasses which inherit properties and methods from the superclass.

  1. Rename the table class, tCountry to tBase. tBase will be our superclass.
  2. Clear the $sqlclassname property of tBase so that the superclass property is empty.
  3. Right click tBase > select Make Subclass.
  4. Name the subclass, tCountry.
  5. Right-click the $sqlclassname property of tCountry and select Overload Property.
  6. Set the $sqlclassname property to sCountry.
  7. Right click tBase > select Create Subclass.
  8. Name the subclass, tStateprov.
  9. Right-click the $sqlclassname property of tStateprov and select Overload Property.
  10. Set the $sqlclassname property to sStateprov.

We now have a superclass table class, tBase, and two subclass table classes, tCountry and tStateprov. All of the methods and properties of tBase, except the $sqlclassname property, is currently be inherited by the subclasses.

We now need to look at the tBase methods to check if they will work for all of the subclasses. If possible, we will modify the methods to make them generic so that they will work for all the subclasses.

  1. Double-click tBase to go to the methods.
  2. Looking at the $dowork method it appears that it should work for any subclass.
  3. Looking at the $getAllRecords method subclasses are going to have a problem with the line of code Calculate OrderBy as "ORDER BY CountryName". The line of code is specific to the sCountry schema class.
  4. We will use the approach of creating a property method which returns the order by clause. This will allow subclasses to override the property method and return an order by clause which makes sense for that subclass. To differentiate between action methods and property methods I use the naming conventions of a $: prefix for any property methods.
  5. Add a new method, $:DefaultOrderBy, to the tBase class methods.
  6. Enter the following code in the $:DefaultOrderBy method.

    Quit method ''

  7. In the $getAllRecords method change the line of code

    Calculate OrderBy as "ORDER BY CountryName"

    to: Calculate OrderBy as $cinst.$:DefaultOrderBy
  8. There is a problem with the $setPrimaryKey method in that it has Country_pkey and Country hard coded into the method.
  9. Add a new method, $:PrimaryKeyColName, to the tBase class methods.
  10. Enter the following code in the $:PrimaryKeyColName method.

    ; Assume that the first column in the schema or query class is the primary key.
    Calculate ColName as $cinst.$cols.1.$name

    ; If the column name does not include the suffix '_pkey' report an error and set the colname variable to null.
    If pos('_pkey',low(ColName))=0
       OK message [sys(85)] (Icon) {Unable to find the primary key in the [$cinst.$sqlclassname] SQL class.}
       Calculate ColName as #NULL
    End If

    Quit method ColName



    For the schema classes we have created, and the naming convention we are using, the above generic code should work.
  11. Select the $setPrimaryKey method and change the line of code

    Calculate ColName as 'Country_pkey'

    to: Calculate ColName as $cinst.$:PrimaryKeyColName

    The ColName variable will be calculated as the return value from the $:PrimaryKeyColName method.
  12. There is also problem with the $setPrimaryKey method in that it has Country hard coded into the method.
  13. Add a new method, $:BaseTableName, to the tBase class methods.
  14. Enter the following code in the $:BaseTableName method.

    ; In a query class, there can be multiple table names.
    Calculate Scratch as $cinst.$servertablenames

    ; Parse the first server table name as the base table name.
    Calculate TableName as strtok('Scratch',',')

    Quit method TableName

  15. Select the $setPrimaryKey method and change the line of code

    Calculate TableName as 'Country'

    to: Calculate TableName as $cinst.$:BaseTableName

    The TableName variable will be calculated as the return value from the $:BaseTableName method.

Create the Stateprov Query

In this section we will create a query class which joins the Stateprov records to the Country records.

  1. Create a new query class named, qStateprovList, in the Contacts library.
  2. Double-click qStateprovList.
  3. Click the dropdown list button in the Schema name column of the query class and select sStateprov.
  4. Leave the Column name empty. Omnis Studio includes all of the columns from a schema class if the Column name in the query class is left empty.
  5. F9 Catalog > Schemas tab > sCountry. Drag the CountryName column from the F9 Catalog to column 2 of row 2 of the qStateprovList query class.

    Tip

    You can select and drag multiple columns from the F9 Catalog onto a schema class.

  6. At the bottom of the query class window there is a Text appended to queries field. This is the $extraquerytext property of the query class. Enter the following SQL text in the $extraquerytext field.

    WHERE Country_fkey = Country_pkey
  7. Close the query class.
Tip

I have run into situations where the Omnis data file was finicky about the order of the foriegn key and primary key. The join would fail if the primary key was first. To avoid this problem I always put the foriegn key ahead of the primary key in the $extraquerytext. To help you remember think of it as putting them in alphabetic order, fkey come before pkey.

We need to create a table class mapped to this query class. We can either create another subclass of tBase or just copy the tStateprov table class and change the $sqlclassname property to qStateprovList.

  1. Right click tStateprov > select Duplicate.
  2. Name the duplicate, tStateprovList.
  3. Set the $sqlclassname property to qStateprovList.

Test the Schema and Query Classes

We can test the schema and query classes using the Programmer Test Method.

  1. Enter the following code in the Programmer Test Method.

    ; Define the list binding it to the table class.
    Do List.$definefromsqlclass('tStateprov')
    If List.$colcount=0
       OK message [sys(85)] (Icon) {The list has zero columns after $definefromsqlclass('tStateprov')}
    Else
       
       ; Set the session object.
       Do List.$sessionobject.$assign($ctask.dbsessionobj)
       
       ; Get all the records.
       Do List.$getAllRecords() Returns FlagOK
       
    End If

  2. Test the sample code. All going well at the end of the method the List variable will contain the 4 records we inserted into the Stateprov table.
  3. Change the line of code at the beginning of your test code.

    Do List.$definefromsqlclass('tStateprov')

    to: Do List.$definefromsqlclass('tStateprovList')
  4. Test the sample code. All going well at the end of the method the List variable will contain the 4 records we inserted into the Stateprov table and the correct parent CountryName for each Stateprov record.

Stateprov Window

The wStateprovList has the added complication that for each Stateprov record, the user needs to be able to select a parent CountryName record and link the Stateprov record to the Country record.

When the user selects a Country record, we will need to set the foreign key, Country_fkey, in the Stateprov record to the primary key, Country_pkey, of the Country record.

Another complication is that we cannot issue a $dowork against the tStateprovList table class because it includes columns from more than one table. The reason for this is that when a record is added to the list or updated, how does Omnis Studio decide that it should insert/update a Stateprov record, but not insert/update a Country record? In this situation we are only joining two tables, imagine a query that joins 4 or 5 tables. It would be a logistics nightmare to figure out which tables to insert, update, or delete records. Therefore, the Omnis Studio built-in $dowork will not execute for query classes which include columns from more than one schema class.

It is possible to override the built-in $dowork method and come up with your own technique for looping through the smartlist and inserting/updating/deleting records for a multi-table query class. We use this technique in the StudioWorks framework to simplify our application code.

For this tutorial we will be using the tStateprovList table class (which points to qStateprovList) to fetch the records, but then merge those fetched records into a list defined from tStateprov (which points to sStateprov). Before merging the tStateprovList records we will add a CountryName column to the tStateprov defined list. Columns added to a list defined using $definefromsqlclass are not included in the insert/update/delete SQL generated by Omnis Studio.

There are numerous user interface techniques we could use. For this example:

  1. We'll use the complex grid object as was used for the wCountryList window class.
  2. For the CountryName field we'll use a kComboBox field with some $event code to assist the user and to set the Country_fkey foreign key when the user leaves the field.

Create the Stateprov Window

Since the wStateprovList window is going to be similar to the wCountryList window, we can save some work by duplicating the wCountryList window and then modifying the duplicate.

  1. Right-click wCountryList and select Duplicate.
  2. Name the duplicate, wStateprovList.
  3. F6 Property Manger > General tab. Set the $title to States/Provinces.
  4. Select wStateprovList and press F8 to go to the class methods.
  5. Insert a new class method and name it buildLists.
  6. Add the following variables:

    List - local variable, type List
    iList - instance variable, type List
    iCountryList - instance variable, type List
  7. Enter the following code in the buildLists method.

    You will first need to enter the

    ; Define and build the list variables used in this window and fetch records as applicable.
    Do method retDefinedList ('tStateprovList') Returns List
    If List.$colcount=0
       Calculate FlagOK as kFalse
    Else
       
       ; Get all the Stateprov records joined to their parent Country records.
       Do List.$getAllRecords() Returns FlagOK
       If FlagOK
          
          ; Define a list based on the sStateprov schema.
          Do method retDefinedList ('tStateprov') Returns iList
          If iList.$colcount=0
             Calculate FlagOK as kFalse
          Else
             
             ; Add a column to the Stateprov list for the country name.
             Do iList.$cols.$add('CountryName',kCharacter,kSimplechar,100)
             
             ; Merge the query based list into the schema based list.
             Do iList.$merge(List,kTrue) ;; (Match column names)
             
             ; Make the list into a smartlist.
             Do iList.$smartlist.$assign(kTrue)
             
             Do method retDefinedList ('tCountry') Returns iCountryList
             If iCountryList.$colcount=0
                Calculate FlagOK as kFalse
             Else
                ; Get all the records in the Country table for use by the CountryName lookups.
                Do iCountryList.$getAllRecords() Returns FlagOK
                
                ; Set the list to be a smartlist so that we can save any changes to the list.
                Do iCountryList.$smartlist.$assign(kTrue)
                
             End If
          End If
       End If
    End If
    Quit method FlagOK

  8. Insert a new class method and name it retDefinedList.
  9. Add the following variables:

    List - local variable, type List
    pTableClassName - parameter variable, type Character
  10. Enter the following code in the retDefinedList method.

    ; Define a main list to be used for displaying Stateprov records joined to Country records.
    Do List.$definefromsqlclass(pTableClassName)
    If List.$colcount=0
       OK message [sys(85)] (Icon) {The [pTableClassName] list has zero columns.}
    Else
       
       ; Set the session object in the list variable so that the SQL statements will be issued to that session's database.
       Do List.$sessionobject.$assign(dbsessionobj)
       
    End If
    Quit method List

  11. Remove the $construct method code and enter the following code in the $construct method.

    ; Define and build the list variables used in this window.
    Do method buildLists Returns FlagOK
    Quit method FlagOK

  12. Press F3 to open the window class editor.
  13. Select the complex grid and set the $columns property to 4 under the Appearance tab.
  14. Ctrl/Opt-drag the Country Name label from column 1 to column 2 and again to column 3 of the complex grid header to copy the label to columns 2 and 3.
  15. Double-click the Country Name label in column 1 and change it to State/Prov Name.
  16. Double-click the Country Name label in column 2 and change it to State/Prov Abbrev.
  17. Ctrl/Opt-drag the CountryName entry field from column 1 to column 2.
  18. Change the $dataname property of the entry field in column 1 to iList.StateProvName.
  19. Change the $name property of the CountryName entry field in column 1 to StateProvName.
  20. Change the $dataname property of the entry field in column 2 to iList.StateProvAbbrev.
  21. Change the $name property of the entry field in column 2 to StateProvAbbrev.
  22. Drag a Combo Box field from the Component Store and drop it in the column 3.
  23. Set the following properties of the kComboBox field in column 3.

    General tab
    $calculation - iCountryList.CountryName
    $dataname - iList.CountryName
    $listname - iCountryList
    $name - CountryName

    Appearance tab
    $edgefloat - kEFposnClient
    $listheight - 20
  24. Double-click the CountryName combo box entry field to get to the field's methods.
  25. Select the $event method and enter the following code.

    On evBefore

    ; Find the matching country in the countries list.
    Do iCountryList.$search($ref.Country_pkey=iList.Country_fkey,1,0,0,0)

    On evClick ;; Event Parameters - pRow ( Itemreference )

    ; Set the foreign key to match the selected country's primary key.
    Process event and continue
    Calculate iList.Country_fkey as pRow.Country_pkey
    Calculate iList.CountryName as pRow.CountryName
    Do $cfield.$redraw()

    On evAfter

    Do method event_evAfter

  26. Right-click on the $event method and select Insert New Method.
  27. Name the new method, event_evAfter.
  28. Select the event_evAfter method and enter the following code.

    ; Preset the flag to true.
    Calculate FlagOK as kTrue

    ; Make sure the CountryName entered by the user matches an entry in the Countries list.
    If iCountryList.$search(low($ref.CountryName)=low(iList.CountryName),1,0,0,0)
       
       ; Update the foreign key in the Stateprov list.
       Calculate iList.CountryName as iCountryList.CountryName
       Calculate iList.Country_fkey as iCountryList.Country_pkey
       
    Else
       
       ; If not, ask the user whether or not they want to add the country.
       Process event and continue (Discard event)
       No/Yes message [sys(85)] (Icon) {The Country Name entered does not match a country in the countries list.////Do you wish to add [$cobj.$contents] as a country?}
       If flag true
          
          ; Add a line to the countries list.
          Do iCountryList.$add()
          Do iCountryList.$line.$assign($ref.$linecount)
          
          ; Set the country name.
          Calculate iCountryList.CountryName as $cobj.$contents
          
          ; Use $dowork to inserted the new country into the database.
          Do iCountryList.$dowork() Returns FlagOK
          If FlagOK
             
             ; Update the foreign key in the Stateprov list.
             Calculate iList.Country_fkey as iCountryList.Country_pkey
             
          End If
       End If
    End If
    Quit method FlagOK

Note

Phew! That was a lot work. Better take a break and treat yourself to a coffee!

Add a Stateprov Menu Line

In order to open the wStateprovList window we need to add a menu line to the Contacts menu.

  1. Double-click mMainMenu in the Contacts library.
  2. Add a new menu line below the Countries menu line and set the properties as follows:

    $name - StateprovList
    $text - States/Provinces...
  3. Double-click the StateprovList menu line to get to the $event method.
  4. The code is similar to the $event method of the CountryList menu line. You can copy and modify the code from that method. (Mmm... duplicate code, sounds like we should add an object class and generalize the code, but we'll leave that for another time.)
  5. The finished code should be as follows.

    ; Find and open an instance of the state/prov list window class.
    Calculate ClassName as 'wStateprovList'
    Do $clib.$windows.$findname(ClassName) Returns rClass
    If isnull(rClass)
       OK message [sys(85)] (Icon) {Unable to find the window class '[ClassName]'.}
    Else
       Do rClass.$openonce('*') Returns rWin
       If isnull(rWin)
          OK message [sys(85)] (Icon) {Unable to open an instance of the window class [ClassName].}
       End If
    End If
    Quit method rWin

  6. Close the method editor and the menu class editor window.
  7. Close and reopen the Contacts library Startup_Task to reinstall the Contacts menu. (Right-click on Startup_Task > Close Task, then Right-click > Open Task.)

Test the Stateprov Window

We are ready to test the wStateprovList window.

  1. Contacts menu > States/Provinces to open an instance of the wStateprovList window class.

    All going well the window will be opened and the Stateprov records which we manually inserted will be listed with their correct parent Country records.
  2. Tab past the end of the list to add a new line to the complex grid. Enter a new Stateprov record as follows:

    State/Province Name - Michigan
    State/Province Abbrev - MI
    Country Name - USA
  3. Click the Save button. All going well the record will be inserted into the Stateprov table with the correct Country_fkey.
  4. Close and reopen the States/Provinces window. Michigan should now be included in the list of Stateprov records with its Country Name set to USA.
  5. Experiment with adding and editing some more States or Provinces and using the droplist button on the combo box field.
  6. Try entering a Country Name that is not currently in the Country table.

There are many user interface approaches you can use for this window. One of the more popular approaches is to auto-suggest a list of countries by opening a droplist of countries which match what the user has typed in the CountryName field. If the user types the letter U, a droplist displaying UK and USA is opened.

Another form of auto-suggest is type-ahead which types ahead of the user in the entry field the first matching record found in the database. If the user types the letter C, the country name Canada is entered for the user in the entry field for the user. The user can continue typing a different country name, e.g. Cambodia, and when they type Cam, if Cambodia is in the database, type-ahead will enter Cambodia in the entry field for the user.

Type-ahead and auto-suggest can be combined.

A helpful feature is to remember the last country the user selected beginning with the first letter they type, and default to selecting that country. For example if they last selected USA, the next time they type the letter U, pre-select USA rather than UK.

Creating an auto-suggest type-ahead widget which defaults to the last selection for each letter of the alphabet is beyond the scope of this tutorial. The StudioWorks framework includes an auto-suggest type-ahead widget which defaults to the last selection for each letter of the alphabet for each user.

Town/Cities

In this section we will add a Towncity table to the database, insert some records, create the schema class, query class, and window class.

If all of our contacts were only located in Canada and the USA we could just link each Towncity record to a Stateprov record and then rely on the Stateprov record to join the Towncity to the correct Country.

However, not all countries have states or provinces, so we must include foreign keys in the Towncity table to the Stateprov table and the Country table.

The Stateprov_fkey foreign key in the Towncity table needs to be optional for towns which are located in a country that does not have states or provinces.

The correct way to do this in the SQL world is to enter a null value in the Stateprov_fkey and then use an outer join to select the Towncity/Stateprov/Country records.

Unfortunately the Omnis Studio data file performance for outer joins is very slow. Using outer joins in your SQL text adds some complexity to your SQL scripts. To overcome these issues I use a technique which may cause some SQL gurus to shudder. The technique is to create an empty parent record with its primary key set to zero (0). I then set the optional foreign key value in the child record to zero (0), thereby making a valid join to the empty parent record, negating the need for using outer joins in the SQL scripts. The downside on using the empty record technique is that you need to remember to exclude the empty records when selecting records from tables which contain emtpy records.

It is up to you whether you use a null value foreign key and outer joins, or a zero value foreign key and an empty record. For this tutorial we'll use the empty record technique.

Create Towncity Table

In this section we will add a Towncity table to the database. The foreign key columns, Country_fkey, and Stateprov_fkey will be included in the Towncity table.

  1. F2 Browser > SQL Browser > select CONTACTS_01 session > click Interactive Sql
  2. Enter the following SQL script in the Interactive SQL window:

    CREATE TABLE Towncity (Towncity_pkey INTEGER NOT NULL,TownCityName VARCHAR (30) NOT NULL,Stateprov_fkey INTEGER NOT NULL,Country_fkey INTEGER NOT NULL)

  3. Click the Run button to execute the SQL script.
  4. Enter the following SQL script in the Interactive SQL window to create a unique index on the primary key column:

    CREATE CASE SENSITIVE UNIQUE INDEX Towncity_pkey ON Towncity (Towncity_pkey)

  5. Click the Run button to execute the SQL script.
  6. Enter the following SQL script in the Interactive SQL window to create a non-unique index on the TownCityName column:

    CREATE CASE SENSITIVE INDEX TownCityName ON Towncity (TownCityName)

  7. Click the Run button to execute the SQL script.
  8. Enter the following SQL script in the Interactive SQL window to create a non-unique index on the Stateprov_fkey foreign key column:

    CREATE INDEX Towncity_Stateprov_fkey ON Towncity (Stateprov_fkey)



    Adding an index to the foreign key column helps to speed up selects in the Omnis data file. You would not need to do this for other RDBMSs.
  9. Enter the following SQL script in the Interactive SQL window to create a non-unique index on the Country_fkey foreign key column:

    CREATE INDEX Towncity_Country_fkey ON Towncity (Country_fkey)



    Adding an index to the foreign key column helps to speed up selects in the Omnis data file. You would not need to do this for other RDBMSs.
  10. Click the Run button to execute the SQL script.

The above indexes do not prevent the user from entering the same TownCityName twice. Different countries could have the same TownCityName so we can not make this a unique index. To prevent the user from entering a TownCityName twice for the same country we will add a composite unique index on the TownCityName and Country_fkey.

  1. Enter the following SQL script in the Interactive SQL window to create a unique composite index on the TownCityName and Country_fkey columns:

    CREATE CASE SENSITIVE UNIQUE INDEX TownCityName_Country_fkey ON Towncity (TownCityName,Country_fkey)

  2. Click the Run button to execute the SQL script.
  3. F2 Browser > SQL Browser > select CONTACTS_01 session > Tables > select Towncity > click Modify Table. Check to make sure the table columns and indexes have been correctly added.
Note

The above SQL scripts used to create the unique indexes is specific to the Omnis data file. For creating unique index constraints on other databases check the documentation specific to the RDBMS.

Insert Towncity Records

I find it helpful to have some test data in the database when developing an application so lets insert a few Towncity records using the Interactive Sql window.

  1. F2 Browser > SQL Browser > CONTACTS_01 > Interactive Sql
  2. Enter the following SQL script:

    INSERT INTO Towncity VALUES (1001,'Toronto',1001,1002)
  3. Click the Run button in the Interactive SQL window.
  4. Enter the following SQL script:

    INSERT INTO Towncity VALUES (1002,'Montreal',1002,1002)
  5. Click the Run button in the Interactive SQL window.
  6. Enter the following SQL script:

    INSERT INTO Towncity VALUES (1003,'Orlando',1003,1004)
  7. Click the Run button in the Interactive SQL window.
  8. Enter the following SQL script:

    INSERT INTO Towncity VALUES (1004,'Hollywood',1004,1004)
  9. Click the Run button in the Interactive SQL window.

Test the join between the Towncity, Stateprov, and Country records.

  1. Enter the following SQL script:

    SELECT TownCityName,StateProvName,CountryName FROM TownCity,Stateprov,Country WHERE Stateprov_fkey = Stateprov_pkey AND Towncity.Country_fkey = Country_pkey

    Note

    The reason that Country_fkey is prefixed with Towncity in the above SELECT is that there is a Country_fkey column in the Towncity table and the Stateprov table. Both tables are included in this select, so we must specify Country_fkey column's table in the SELECT.

  2. Click the Run button in the Interactive SQL window.
All going well the Towncity records should appear in the Interactive SQL window list joined to the correct Stateprov and Country records.

Insert Empty Stateprov Record

To allow a Towncity record to use zero (0) in the Stateprov_fkey field, we need to add a Country record with the Country_pkey set to zero (0) and a Stateprov record with the Stateprov_pkey set to zero (0) and the Country_fkey set to zero (0).

  1. F2 Browser > SQL Browser > CONTACTS_01 > Interactive Sql
  2. Enter the following SQL script:

    INSERT INTO Country VALUES (0,'')
  3. Click the Run button in the Interactive SQL window.
  4. Enter the following SQL script:

    INSERT INTO Stateprov VALUES (0,'','',0)
  5. Click the Run button in the Interactive SQL window.

To avoid selecting the empty records we need to modify the $getAllRecords method in tBase.

  1. Double-click tBase.
  2. Select the $getAllRecords method.
  3. Modify the method as follows.

    ; Prepare the ORDER BY text.
    If len(pOrderBySQL)
       Calculate OrderBy as pOrderBySQL
    Else
       Calculate OrderBy as $cinst.$:DefaultOrderBy
    End If

    ; Prepare the SQL text to exclude the empty zero(0) primary key record.
    Calculate ColName as $cinst.$:PrimaryKeyColName
    If pos("WHERE ",upp($cinst.$extraquerytext))
       Calculate SQLText as con("AND ",ColName," <> 0")
    Else
       Calculate SQLText as con("WHERE ",ColName," <> 0")
    End If

    Calculate SQLText as con(SQLText,' ',OrderBy)

    ; Select all the records in the table.
    Do $cinst.$select(SQLText) Returns FlagOK
    If not(FlagOK)
       OK message [sys(85)] (Icon) {Flag false after $cinst.$select([SQLText])}
    Else
       
       ; Fetch all the records in the table.
       Do $cinst.$fetch(kFetchAll) Returns FetchStatus
       If not(FetchStatus)
          Calculate FlagOK as kFalse
          OK message [sys(85)] (Icon) {Flag false after $cinst.$fetch(kFetchAll)}
       Else
          
          ; Set the current line to the first line.
          Do $cinst.$line.$assign(1)
          
       End If
    End If
    Quit method FlagOK

  4. Close the method editor.

Create Towncity Schema

In this section we will create a schema class which maps to the Towncity table in the database.

  1. Create a new schema class named, sTowncity, to the Contacts library.
  2. Set the Server table or view field to Towncity
  3. Add the following columns to the sTowncity schema class:

    Column Name - Towncity_pkey
    Type - Number
    SubType - Long Integer
    Primary Key - kTrue
    No Nulls - kTrue

    Column Name - TownCityName
    Type - Character
    SubType - 30
    No Nulls - kTrue

    Column Name - Stateprov_fkey
    Type - Number
    SubType - Long Integer
    No Nulls - kTrue

    Column Name - Country_fkey
    Type - Number
    SubType - Long Integer
    No Nulls - kTrue
  4. Close the schema class.

Create Towncity Table Class

We need a to create a table class which is mapped to the sTowncity schema class.

  1. Right click tBase > select Create Subclass.
  2. Name the subclass, tTowncity.
  3. Right-click the $sqlclassname property and select Overload Property.
  4. Set the $sqlclassname property to sTowncity.
  5. Double-click tTowncity to go to the methods.
  6. Right-click the $:DefaultOrderBy and select Overrride Method.
  7. Enter the following code in the $:DefaultOrderBy method.

    Quit method 'ORDER BY TownCityName'

  8. Close the method editor.

Create Towncity Query

We will create a query class which joins the Towncity table to the Stateprov table and to the Country table.

  1. Create a new query class named, qTowncityList, in the Contacts library.
  2. Double-click qTowncityList.
  3. Click the dropdown list button in the Schema name column of the query class and select sTowncity.
  4. Leave the Column name empty so that Omnis Studio will include all the columns of the sTowncity schema class.
  5. F9 Catalog > Schemas tab > sStateprov. Drag the StateProvName column from the F9 Catalog to column 2 of row 2 of the qTowncityList query class.
  6. F9 Catalog > Schemas tab > sCountry. Drag the CountryName column from the F9 Catalog to column 2 of row 3 of the qTowncityList query class.
  7. At the bottom of the query class window there is a Text appended to queries field. This is the $extraquerytext property of the query class. Enter the following SQL text in the $extraquerytext field.

    WHERE Stateprov_fkey = Stateprov_pkey and Towncity.Country_fkey = Country_pkey
  8. Close the query class.

We need to create a table class mapped to this query class. We can either create another subclass of tBase or just copy the tTowncity table class and change the $sqlclassname property to qTowncityList.

  1. Right click tTowncity > select Duplicate.
  2. Name the duplicate, tTowncityList.
  3. Set the $sqlclassname property to qTowncityList.

Test Schema and Query Classes

We can test the schema and query classes using the Programmer Test Method.

  1. Enter the following code in the Programmer Test Method.

    ; Define the list binding it to the table class.
    Do List.$definefromsqlclass('tTowncity')
    If List.$colcount=0
       OK message [sys(85)] (Icon) {The list has zero columns after $definefromsqlclass('tTowncity')}
    Else
       
       ; Set the session object.
       Do List.$sessionobject.$assign($ctask.dbsessionobj)
       
       ; Get all the records.
       Do List.$getAllRecords() Returns FlagOK
       
    End If

  2. Test the sample code. All going well at the end of the method the List variable will contain the 4 records we inserted into the Towncity table.
  3. Change the line of code at the beginning of your test code

    Do List.$definefromsqlclass('tTowncity')

    to: Do List.$definefromsqlclass('tTowncityList')
  4. Test the sample code. All going well at the end of the method the List variable will contain the 4 records we inserted into the Towncity table and the correct parent Stateprov and the correct parent CountryName for each Towncity record.

Create Towncity Window

Since the wTowncityList window is going to be similar to the wStateprovList window, we can save some work by duplicating the wStateprovList window and then modifying the duplicate.

  1. Right-click wStateprovList and select Duplicate.
  2. Name the duplicate, wTowncityList.
  3. F6 Property Manger > General tab. Set the $title to Towns/Cities.
  4. Select wTowncityList and press F8 to go to the class methods.
  5. Select the buildLists method.
  6. Enter the following code in the buildLists method.

    ; Define and build the list variables used in this window and fetch records as applicable.
    Do method retDefinedList ('tTowncityList') Returns List
    If List.$colcount=0
       Calculate FlagOK as kFalse
    Else
       
       ; Get all the Stateprov records joined to their parent Country records.
       Do List.$getAllRecords() Returns FlagOK
       If FlagOK
          
          ; Define a list based on the sStateprov schema.
          Do method retDefinedList ('tTowncity') Returns iList
          If iList.$colcount=0
             Calculate FlagOK as kFalse
          Else
             
             ; Add columns to the Towncity list for the stateprov name and country name.
             Do iList.$cols.$add('StateProvName',kCharacter,kSimplechar,100)
             Do iList.$cols.$add('CountryName',kCharacter,kSimplechar,100)
             
             ; Merge the query based list into the schema based list.
             Do iList.$merge(List,kTrue) ;; (Match column names)
             
             ; Make the list into a smartlist.
             Do iList.$smartlist.$assign(kTrue)
             
             Do method retDefinedList ('tStateprovList') Returns iStateProvList
             If iStateProvList.$colcount=0
                Calculate FlagOK as kFalse
             Else
                ; Get all the records in the Country table for use by the CountryName lookups.
                Do iStateProvList.$getAllRecords() Returns FlagOK
                If FlagOK
                   
                   Do method retDefinedList ('tCountry') Returns iCountryList
                   If iCountryList.$colcount=0
                      Calculate FlagOK as kFalse
                   Else
                      ; Get all the records in the Country table for use by the CountryName lookups.
                      Do iCountryList.$getAllRecords() Returns FlagOK
                      
                      ; Set the list to be a smartlist so that we can save any changes to the list.
                      Do iCountryList.$smartlist.$assign(kTrue)
                      
                   End If
                End If
             End If
          End If
       End If
    End If
    Quit method FlagOK

  7. Press F3 to open the window class editor.
  8. Double-click the column 1 header label and change it to Town/City Name.
  9. Double-click the column 2 header label and change it to State/Province Name.
  10. Select the StateProvName entry field in column 1 of the complex grid and set the field properties as follows.

    General tab
    $dataname - iList.TownCityName
    $name - TownCityName
  11. Select the StateProvAbbrev entry field in column 2 the complex grid and press the Delete key to delete the entry field.
  12. Ctrl/Opt-drag the CountryName combo box field from column 3 to column 2 to copy it to column 2.
  13. With the copied field selected in column 2 set the field properties as follows:

    General tab
    $calculation - iStateProvList.StateProvName
    $dataname - iList.StateProvName
    $listname - iStateProvList
    $name - StateProvName
  14. Double-click the StateProvName combo box entry field to get to the field's methods.
  15. Select the event_evAfter method and enter the following code.

    ; Preset the flag to true.
    Calculate FlagOK as kTrue

    ; Make sure the CountryName entered by the user matches an entry in the Countries list.
    If iStateProvList.$search(low($ref.StateProvName)=low(iList.StateProvName),1,0,0,0)
       
       ; Update the foreign key in the Stateprov list.
       Calculate iList.StateProvName as iStateProvList.StateProvName
       Calculate iList.Stateprov_fkey as iStateProvList.Stateprov_pkey
       
       Calculate iList.CountryName as iStateProvList.CountryName
       Calculate iList.Country_fkey as iStateProvList.Country_fkey
       
    Else
       
       ; If not, ask the user whether or not they want to add the country.
       Process event and continue (Discard event)
       No/Yes message [sys(85)] (Icon) {The State/Province Name entered does not match a State/Province in the state/provinces list.////Do you wish to add [$cobj.$contents] as a State/Province in the country [iList.CountryName]?}
       If flag true
          
          ; Insert a record in the StateProv table.
          Do method retDefinedList ('tStateprov') Returns List
          If List.$colcount=0
             Calculate FlagOK as kFalse
          Else
             
             Do List.$smartlist.$assign(kTrue)
             Do List.$add()
             Do List.$line.$assign($ref.$linecount)
             
             Calculate List.StateProvName as $cobj.$contents
             Calculate List.Country_fkey as iList.Country_fkey
             
             Do List.$dowork() Returns FlagOK
             If FlagOK
                
                ; Rebuild the state/prov list.
                Do iStateProvList.$getAllRecords() Returns FlagOK
                If FlagOK
                   
                   ; Make sure the CountryName entered by the user matches an entry in the Countries list.
                   If iStateProvList.$search(low($ref.StateProvName)=low(iList.StateProvName),1,0,0,0)
                      
                      ; Update the foreign key in the Stateprov list.
                      Calculate iList.StateProvName as iStateProvList.StateProvName
                      Calculate iList.Stateprov_fkey as iStateProvList.Stateprov_pkey
                      
                      Calculate iList.CountryName as iStateProvList.CountryName
                      Calculate iList.Country_fkey as iStateProvList.Country_pkey
                      
                   End If
                End If
             End If
          End If
       End If
    End If
    Quit method FlagOK

  16. Select the $event method and enter the following code.

    On evBefore

    ; Find the matching country in the countries list.
    Do iStateProvList.$search($ref.Stateprov_pkey=iList.Stateprov_fkey,1,0,0,0)

    On evClick ;; Event Parameters - pRow ( Itemreference )

    ; Set the foreign key to match the selected country's primary key.
    Process event and continue
    Calculate iList.Stateprov_fkey as pRow.Stateprov_pkey
    Calculate iList.StateProvName as pRow.StateProvName
    Calculate iList.Country_fkey as pRow.Country_fkey
    Calculate iList.CountryName as pRow.CountryName
    Do $cinst.$redraw()

    On evAfter

    Do method event_evAfter

  17. Select the $event method of the ComplexGrid object and enter the following code.

    On evExtend

    Process event and continue

    ; Copy the previous row value to the new row.
    Do iList.$assignrow(iList.[iList.$line-1],kTrue) ;; (Match column name)

    ; Clear the primary key and the TownCityName columns.
    Calculate iList.Towncity_pkey as #NULL
    Calculate iList.TownCityName as ''

    ; Redraw the window to display the copied values.
    Do $cinst.$redraw()

Add Towncity Menu Line

In order to open the wTowncityList window we need to add a menu line to the Contacts menu.

  1. Double-click mMainMenu in the Contacts library.
  2. Add a new menu line below the States/Provinces menu line and set the properties as follows:

    $name - TownProvList
    $text - Town/Cities...
  3. Double-click the TownProvList menu line to get to the $event method.
  4. The code is similar to the $event method of the CountyList menu line. You can copy and modify the code from that method. (Mmm... more duplicate code the object-oriented police are going to be after us.)
  5. The finished code should be as follows.

    ; Find and open an instance of the state/prov list window class.
    Calculate ClassName as 'wTowncityList'
    Do $clib.$windows.$findname(ClassName) Returns rClass
    If isnull(rClass)
       OK message [sys(85)] (Icon) {Unable to find the window class '[ClassName]'.}
    Else
       Do rClass.$openonce('*') Returns rWin
       If isnull(rWin)
          OK message [sys(85)] (Icon) {Unable to open an instance of the window class [ClassName].}
       End If
    End If
    Quit method rWin

  6. Close the method editor and the menu class editor window.
  7. Close and reopen the Contacts library Startup_Task to reinstall the Contacts menu. (Right-click on Startup_Task > Close Task, then Right-click > Open Task.)

Test Towncity Window

We are ready to test the wTowncityList window.

  1. Contacts menu > Towns/Cities to open an instance of the wTowncityList window class.

    All going well the window will be opened and the Towncity records which we manually inserted will be listed each with their correct parent Stateprov and Country records.
  2. Tab past the end of the list to add a new line to the complex grid. Enter a new Towncity record as follows:

    Town/City Name - Detroit
    State/Province Name - Michigan
    Country Name - USA
  3. Click the Save button. All going well the record will be inserted into the Towncity table with the correct Stateprov_fkey and Country_fkey.
  4. Close and reopen the Towns/Cities window. Detroit should now be included in the list of Towncity records with its State/Province Name set to Michigan and Country Name set to USA.
  5. Experiment with adding and editing some more Town/City records and using the droplist button on the combo box field.
  6. Try entering a Towncity record with a State/Prov Name that is not currently in the Stateprov table.

Summary

Well that wraps up the Studio 102 tutorial. I hope that this tutorial has helped you understand foreign key relationships and how to implement them in Omnis Studio using query classes, $extraquerytext, and how to create a user interface for setting the foreign keys when the user is adding/editing records.

You now have the basics but there's much, much, more to learn! Omnis Studio is a powerful development tool with many more features.

In the Studio 103 tutorial you will continue developing the Contacts application and do the following:

The Studio 103 tutorial is only available to StudioTips Members. Omnis Studio developers are encouraged to become StudioTips Members. The cost of membership is well worth the benefits of being a member.

Visit www.studiotips.net to find out more and to become a StudioTips Member. Your support is greatly appreciated!

If you this tutorial has been helpful please send an email to (doug@vencor.ca) It's always encouraging to hear from developers who have benefited from the Studio 102 tutorial. Be sure to include any suggestions for improvements or additional topics you would like to see covered.

Happy coding!

Doug Kuyvenhoven
Vencor Software

StudioTips Documentation - Copyright 2006 Vencor Software