A Rev DB App, in 3 Secs or Less - #4

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

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.

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.

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

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

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.

(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

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.

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: