A Rev DB App, in 3 Secs or Less - #3
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
AND/OR Record Search
Found Set Record Navigation
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.
This array has additional keys added once a find has been done for the records of a particular card.
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.]
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
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.
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.
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: