revUp - Updates and news for the Revolution community
Issue 92 | May 14th 2010 Contact the Editor | How to Contribute

A Rev DB App, in 3 Secs or Less - #3
Use FmPro Migrator Platinum Edition to unlock the power of SQL Yoga and the Rev Data Grid

by David Simpson

This article is a continuation of the article series showing how to create Rev database applications using FmPro Migrator Platinum Edition. In this article I will show how the query by example feature is implemented along with the technique used to convert tab controls.

Query by Example & Found Set Record Navigation

Users expect to have an easy way to search records in a SQL database just as they have been accustomed to searching within a desktop database. A lot of work has gone into creating the same type of query by example searching technique within the generated stack file. A user can select Find Mode from the View menu, the fields and portals will be cleared, allowing the user to enter their search criteria into the various fields on the form. It is important to note that allowing users to search every field of a form may cause performance problems for large multi-user databases. The search performance can be improved by allowing users to search only the most important subset of the fields on the form. Limiting the fields which can be searched can be done easily in the Rev stack by setting the lockText property for any fields which you want to omit from searching. This property could be set within the clearFields handler. This handler could be copied to the card script, and specific fields could be set to have their lockText property set to true within Find mode. The user won't be able to enter these fields or add search criteria when the lockText property is true. The property could be disabled with another handler which would run when selecting the Find All Records menu item.

For a multi-user database is it also important to build indexes for the fields which need to be searched. Unlike FileMaker Pro, large SQL database servers generally impose limitations upon the number of columns or the combined width of the columns which can be added to a single index. Adding indexes will impose a performance penalty when inserting and updating records in the database. And unlike FileMaker Pro, SQL databases don't add indexes automatically to a table the first time you perform a search.


on findRecords
   -- find records based upon field contents
   global gQuerySearchString, gFieldDataArray,gFindType,\
   \
          gCardRefreshFlagArray,gFoundSetPKArray_asset_management2
          local tError,tCurrentCardName,tFoundRecordsCount,\
          tFoundSetPKQuery_asset_management2
          
          put 0 into tParameterCount
          put the short name of this card into tCurrentCardName
          -- get current record id
          put gCardRefreshFlagArray[tCurrentCardName]["recID"] into \
                 tCurrentRecordID
          put the FmPro["fieldList"] of this card into tFieldsList
          
          put sqlquery_createObject("asset_management2") into \
                 tFoundSetPKQuery_asset_management2
          sqlquery_set tFoundSetPKQuery_asset_management2,"select \
                 clause","asset_management2.asset_id"
          
          -- get the search conditions specified by the user
          findConditions
          
          -- set the search conditions
          sqlquery_set tFoundSetPKQuery_asset_management2, \
                 "conditions" \
                 ,gQuerySearchString, gFieldDataArray
          put empty into gFoundSetPKArray_asset_management2
          sqlquery_retrieveAsArray tFoundSetPKQuery_asset_management2, \
                 gFoundSetPKArray_asset_management2
          put the result into tError
          if tError is not empty then 
             errorHandler "Error finding records: " & tError
             exit findRecords
          end if -- end of query error check
          put the number of lines in the keys of \
                 gFoundSetPKArray_asset_management2 into \
                 tFoundRecordsCount
          if tFoundRecordsCount = 0 then
             answer error "No records match this find request." with \
                    "Cancel" or "Modify Find"
             switch it
                case "Cancel"
                   -- clear FS info - return to Browse mode
                   put 0 into \
                          \
                          gCardRefreshFlagArray[tCurrentCardName]["recCountFS"]
                   put 0 into \
                          \
                          gCardRefreshFlagArray[tCurrentCardName]["recIDFS"]
                   put empty into gFoundSetPKArray_asset_management2
                   switchToBrowseMode
                   exit findRecords
                   break
                case "Modify Find"
                   -- do nothing - stay in find mode
                   exit findRecords
                   break
             end switch
          end if -- end of 0 records found check
          -- set refresh flag to refresh the data in the fields
          put 0 into gCardRefreshFlagArray[tCurrentCardName]["rFlag"]
          put 1 into gCardRefreshFlagArray[tCurrentCardName]["recIDFS"]
          put tFoundRecordsCount into \
                 gCardRefreshFlagArray[tCurrentCardName]["recCountFS"]
          switchToBrowseMode
          refreshFields
end findRecords 

Search Criteria Symbols Menu

Find Mode Symbols Menu

Search criteria symbols are implemented within a pop-up menu, which inserts the requested symbol into the currently selected field.

Once the user clicks the Find button, the search conditions are gathered from each of the fields and a SQL Yoga query is run against the SQL database.

The findConditions stack handler is used to gather the search criteria entered into the fields by the user. The first character of each search conditions is checked to see if it matches one of the symbols, and this info is assembled together into a search query within the gQuerySearchString global variable.



AND/OR Record Search

Find Mode AND/OR Search Menu

Unlike FileMaker Pro databases, SQL databases easily support AND as well as OR search criteria in a straightforward manner. The converted Rev application provides for AND/OR searching via a pop-up menu.



Found Set Record Navigation

Find Mode Record Navigation

In found set mode, the number of records found out of the total number of records in the table are displayed (2/3).

The Next/Previous buttons and the scrollbar can be used to move to another record in the found set. Or a record number can be entered in the Record box to move directly to the requested record.

The gCardRefreshFlagArray is used to store information regarding the number of records being browsed, the current record id and whether the field data is current or needs to be refreshed.

gCardRefreshFlagArray

This array has additional keys added once a find has been done for the records of a particular card.

gCardRefreshFlagArray - Find Mode

The recCountFS key holds the number of records in the current found set for the card. The recIDFS key contains the value of the key in the gFoundSetPKArray_asset_management2 array for the current record being displayed. Also notice that the Browse mode recCount and recID values are unchanged, so that the user will be viewing their previous record when returning from found set record navigation mode back to the regular Browse mode. [Errata: Actually it would work this way if I had not forgotten to add the instruction:

put the short name of this card into tCurrentCardName 

within the showAllRecords stack handler. I will show you how to modify the template Stack used by FmPro Migrator in a future article so you can add your own customizations.]

 

gCardRefreshFlagArray After Show All Records

After the Show All Records menu is selected, the value for the recCountFS key is set to 0, which indicates that found set navigation mode is no longer active. The array of primary keys for the card's table is maintained during and after found set navigation has been exited, so that it isn't necessary to put additional load on the SQL database server. And only 1 complete record of data for the card's table is stored internally for a given table in the database in order to reduce local memory usage, network bandwidth and the load on the server.

Tab Control to Rev Tab Panel Group Conversion

Tab  Control 1

Tab Control 2

Tab Control 3

Tab Controls within databases like FileMaker Pro or Microsoft Access work a little differently compared to how we usually implement a Tab Panel within Rev stacks.

Within FileMaker Pro or Access databases, clicking a tab of the control simply shows the information contained within that particular tab. However when you click on the tab you are still viewing the same form or layout.

Within Rev stacks, we usually go to a different card when clicking on the tab of a tab panel. To simulate this same functionality within Rev, FmPro Migrator converts each individual tab of a Tab Control into a Tab Panel object within a group having the same name as the Tab Panel. Each object within the particular Tab is created within this group.

revTalk menuPick code is placed into each group to automatically hide the current Tab Panel group and show the next group when a different Tab Panel tab has been clicked. Hiding or showing the enclosing group automatically hides or shows each of the objects created within the group, thus simulating the graphical interface of the original database. Tab controls can contain most other types of objects including fields, checkboxes, radio buttons, graphics and images.

The menuPick code embedded within each tab panel group is very simple. This is the code within the Tab1 tab panel object:

on menuPick pItem
   switch pItem
      case "Tab1"
         -- do nothing
         break
      case "Tab2"
         set the visible of group "Tab2" to true
         set the visible of group "Tab1" to false
         lock messages
         set the menuHistory of me to 1
         unlock messages
         break
      case "Tab3"
         set the visible of group "Tab3" to true
         set the visible of group "Tab1" to false
         lock messages
         set the menuHistory of me to 1
         unlock messages
         break
   end switch
end menuPick 

If you want to do something else when the user clicks on a different tab panel, then you can easily update this code within the menuPick handlers of the tab panel objects.

Conclusion

The Database to Rev Conversion feature built into FmPro Migrator Platinum Edition enables Rev developers to quickly convert popular database files into functional Rev database front end application stack files. A working application can be created in seconds, without manual coding for commonly requested database features. In the next article, I will show the code which updates the Data Grid, and show you how you can customize the Data Grid behavior code.

[Special Offer]

Order a copy of FmPro Migrator Platinum Edition for $599 and we will include a copy of SQL Yoga at no extra cost. The way this works is that you add FmPro Migrator Platinum Edition and SQL Yoga to your order, use coupon code FMPROMAY and you will receive both products for the price of FmPro Migrator Platinum Edition.

FmPro Migrator Platinum Edition includes license keys for the following features:

  • PHPToRevTalk License Key (Qty = Unlimited)
  • BasicToRevTalk License Key (Qty = Unlimited)
  • AccessToFmPro License Key (Qty = 250)
  • FmProToAccess License Key (Qty = 250)
  • DBToRev License Key (Qty = 250)

About the Author

David Simpson is the president of .com solutions inc, and a Revolution developer of database applications since 2001.

Main Menu

What's New

Buy RunRevLive.09 DVD