A Rev DB App, in 3 Secs or Less - #4
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 portals and subforms are converted into Rev Data Grid objects.
Portal/SubForm to Data Grid Conversion
Most desktop database applications include a way to display records related to a parent record on the same screen as the parent record detail info. FileMaker Pro implements this feature within an object called a portal, and Microsoft Access uses a subForm. These Portals and SubForms are converted into Rev Data Grid form objects automatically. The Data Grid form more accurately represents the way portals are implemented. Objects can be located anywhere within the portal row within FileMaker Pro portals, therefore the Data Grid form implementation represents a good way to implement the same functionality in Rev. The relationships gathered from the source database are used by SQL Yoga to retrieve the related records, which are then written into the Data Grid each time the refreshFields handler runs.
Even though FmPro Migrator automatically includes its own Data Grid template object within the template stack file, the code used behind the scenes to implement the actual operation of the Data Grid comes from within the Rev IDE's own folder structure. So this means that as improvements are made to the Data Grid within newer versions of the Rev IDE, you don't have to worry because you will automatically get the latest version of the Data Grid code. If you have a Data Grid object in your stack, the standalone builder automatically pulls code from the IDE and embeds it in your compiled project to manage the Data Grid whenever you compile a standalone app.
Configuring TruncateTail for Text Fields
FmPro Migrator builds code into the Data Grid which automatically uses the Data Grid TruncateTail helper command to visually truncate long field text which exceeds the bounds of the field. Double-clicking within the field displays the non-truncated info for editing.
Note: In this screen shot, the tops of the letters are cut off when editing, but they display Ok in the Data Grid row when browsing. Since some objects require a little more space when they are converted into a Rev card, it can be useful to add a little more space around the objects in the original database form or layout. Since the conversion process is automated, you can quickly convert all of the layouts of a database and visually determine how everything looks after the conversion. Then you can know if you need to fine tune the spacing of any of the objects within the original database prior to making your final conversion.
The TruncateTail command gets called from within the FillInData handler within the Data Grid's Behavior Script.
If you want to change this code, just comment out the TruncateTail line within the FillInData handler.
-- This message is sent when the Data Grid needs to populate -- this template with the data from a record. -- pDataArray is an array containing the record data. on FillInData pDataArray set the uText of field "fld__maintenance_record__asset_id" \ of me to pDataArray["asset_id"] set the text of field "fld__maintenance_record__asset_id" of \ me to pDataArray["asset_id"] TruncateTail the short id of field \ "fld__maintenance_record__asset_id" of me, "..." set the uText of field "fld__maintenance_record__date" of me \ to pDataArray["date"] set the text of field "fld__maintenance_record__date" of me \ to pDataArray["date"] TruncateTail the short id of field \ "fld__maintenance_record__date" of me, "..." set the uText of field \ "fld__maintenance_record__maintenance_id" \ of me to pDataArray["maintenance_id"] set the text of field \ "fld__maintenance_record__maintenance_id" \ of me to pDataArray["maintenance_id"] TruncateTail the short id of field \ "fld__maintenance_record__maintenance_id" of me, "..." set the uText of field "fld__maintenance_record__notes" of \ me to pDataArray["notes"] set the text of field "fld__maintenance_record__notes" of me \ to pDataArray["notes"] --TruncateTail the short id of field -- "fld__maintenance_record__notes" of me, "..." \ -- This line commented out put pDataArray["maint_picture"] into image \ "maintenance_record__maint_picture" of me set the label of button \ "menu__maintenance_record__maint_condition1" \ of me to pDataArray["maint_condition1"] set the label of button \ "menu__maintenance_record__maint_condition2" \ of me to pDataArray["maint_condition2"] set the checkedButtons of group \ "chkbx_maintenance_record__maint_condition3" \ of me to pDataArray["maint_condition3"] set the hilitedButtonName of group \ "radio_maintenance_record__maint_condition4" \ of me to pDataArray["maint_condition4"] end FillInData
More Than Just Text Fields
Data Grid forms can display a lot more than just text fields. FmPro Migrator includes support for converting: text field, image field, pull down menu, pop-up menu, checkbox group, radio button group, rectangle, rounded rectangle, oval/circle, button, and image objects. Information is automatically updated in checkbox and radio button groups and clicking the objects changes the data within the record of the related table. If you want some of these fields to be read only fields, you can easily comment out the code within the object which updates the database.
Checkbox groups represent one of the more challenging object types which have been implemented within the main card and also within Data Grid Row Templates. Within FileMaker Pro, a checkbox is just a single field object which has its display type set to checkbox which gets its values from a centrally located value list. To implement checkboxes in Rev, each element of a custom value list is created as a separate checkbox button, and all of the checkbox buttons are enclosed within a group. You can see this structure within the Application Browser screen shot shown below.
Each checkbox button has a name derived from the original Tablename::Fieldname within the original database along with the name of the custom value list value. Rev radio buttons automatically include code to manage the setting of only one radio button at a time. But to implement a checkbox functionality, there are 43 lines of code within the checkbox group.
on mouseUp -- get the hilited checkbox buttons global gMode local tCheckedList if the hilite of button \ "chkbx_maintenance_record__maint_condition3_New" of me\ is true then put "New" & return after tCheckedList if the hilite of button \ \ \ "chkbx_maintenance_record__maint_condition3_Excellent" of me \ is true then put "Excellent" & return after \ tCheckedList if the hilite of button \ "chkbx_maintenance_record__maint_condition3_Good" of \ me \ is true then put "Good" & return after tCheckedList if the hilite of button \ "chkbx_maintenance_record__maint_condition3_Poor" of \ me \ is true then put "Poor" & return after tCheckedList if the hilite of button \ "chkbx_maintenance_record__maint_condition3_Scrap" of \ me \ is true then put "Scrap" & return after tCheckedList lock messages -- avoid triggering setProp handler set the checkedButtons of me to tCheckedList unlock messages if gMode = "Browse" then put the dgIndex of the dgDataControl of me into tIndex put the dgDataOfIndex[tIndex] of the dgControl of me into \ tPortalRecordArray put tCheckedList into tPortalRecordArray[the dgColumn of \ me] sqlrecord_update tPortalRecordArray put the result into tError if tError is not empty then errorHandler \ "Error updating row#" & tIndex & space & tError end if -- end of check for browse mode end mouseUp setProp checkedButtons pCheckedList -- set the proper checked buttons when the checkedButtons -- custom property is set. local tButtonName,tLine -- clear all buttons first set the hilite of button \ "chkbx_maintenance_record__maint_condition3_New" of \ me to false set the hilite of button \ \ \ "chkbx_maintenance_record__maint_condition3_Excellent" \ of me to false set the hilite of button \ "chkbx_maintenance_record__maint_condition3_Good" of \ me to false set the hilite of button \ "chkbx_maintenance_record__maint_condition3_Poor" of \ me to false set the hilite of button \ "chkbx_maintenance_record__maint_condition3_Scrap" of \ me to false -- set only the specified buttons repeat for each line tLine in pCheckedList put the short name of me & "_"& tLine into tButtonName try set the hilite of button tButtonName of me to true catch e end try end repeat -- pass the setProp message to allow the property to be set pass "checkedButtons" end checkedButtons
There are two handlers generated for the checkbox group.
mouseUp - The mouseUp handler figures out which of the checkboxes in the group have been selected, and adds them to a list. It then passes this return separated list to the checkedButtons setProp handler which insures that all of the buttons really did get checked. Then it updates the data within the row of the Data Grid array and updates the record for the related database table.
setProp checkedButtons - The checkedButtons setProp handler gets called by the mouseUp handler of the checkbox group when a button is clicked and it also gets used by the refreshFields handler when fields get filled on the card. First, it clears all of the buttons, and then only sets the buttons which have been passed in with the pCheckedList parameter.
There are a couple of caveats to be aware of in regards to this implementation.
(1) What is a return character? - For an embedded single-user database this probably won't be much of an issue. But for a multi-user database you may have users from multiple platforms putting data into the same database field. MacOS X and Windows operating systems use different characters as return characters. A few lines of code could be added to the checkedButtons handler to replace linefeed and carriage return line feed combinations with just return characters. This would be done just before looping thru all of the lines of the pCheckdList parameter. Normally you don't have to worry about making these kinds of translations within data stored within a Rev field because the change is made for you automatically whenever the stack is opened within the IDE for the first time on a different platform. And those changes stay with the stack when it is built into a standalone application for each platform where you perform a build. But when the data is stored outside of the Rev stack in a SQL database, it may become an issue.
(2) FileMaker Pro Line Feed characters. - When the FileMaker database puts checkbox data into its fields, each value is separated by a linefeed character. FmPro Migrator doesn't change this data when it performs the data transfer into the SQL database, it just faithfully copies each byte into the destination database server. But when you first look at the data in the variables tab of the debugger after it is retrieved from the SQL database, the individual values will appear to be run together with no separator. Once again, it is not an issue for the FileMaker database engine because it knows that regardless of the client platform, if it is a checkbox group then the data will be separated by linefeeds so it can separate the data and display it properly. Once again, using a couple lines of code like the following could handle this situation:
replace LF with return in pCheckedList replace CRLF with return in pCheckedList
Adding a Row to a Data Grid
FileMaker Pro portals are designed to enable a user to click in an empty row to add a new record. The Rev Data Grid works differently because it doesn't give the user the opportunity to do anything with a row which doesn't contain data. Therefore FmPro Migrator adds a New Record button represented as a green (+) symbol near the upper right corner of the Data Grid. Clicking this button creates a new record in the related table and automatically fills the Foreign Key column with the Primary Key value from the current parent record. This graphic image can be changed globally throughout the stack by changing the add_32_32.png image on the Images card. If you want the Data Grid to provide read only access to the related records, then you can delete this button entirely.
Deleting a Row in a Data Grid
Within FileMaker Portals, it is common to have a button object or an image object with an embedded "Delete Portal Row" script script. Clicking the button will delete the selected row of the portal. FmPro Migrator implements this same functionality within Rev Data Grid objects by converting the "Delete Portal Row" script step into code which deletes the selected row of data in the Data Grid (and in the related database table). This feature has been implemented within the "Delete Row" button object and the image button which looks like an image of a red minus sign in the image shown above.
on mouseUp global gMode local tIndex,tPortalRecordArray,tError if gMode = "Browse" then put the dgHilitedLines of group "Portal1" into tIndex put the dgDataOfIndex[tIndex] of the dgControl of me into \ tPortalRecordArray sqlrecord_delete tPortalRecordArray put the result into tError if tError is not empty then errorHandler \ "Error deleting row#" & tIndex & " of Portal1: " & tError DeleteLines tIndex if tIndex = 1 then set the dgData of group "Portal1" to \ empty -- last row requires special handling RefreshList end if -- end of check for Browse mode end mouseUp
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 you can use the Fmig_Preferences.xml file to customize the way FmPro Migrator generates your stack file including substituting the use of your own customized template stack file.
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: