State/Provinces

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.

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.

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.
   
   
   

   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.

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

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.

StudioTips Documentation - Copyright 2006 Vencor Software