Tips_tutorials   >   Studio102   >   Town/Cities

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.

StudioTips Documentation - Copyright 2006 Vencor Software