Solutions

Dynamic Menu – Part 1

Here are the details of the main presentation from the Sept 24, 2010 meeting – Dynamic Menus.

As you know,  FileMaker is a database platform that combines a robust user interface with database tables.  If all you’ve ever used is FileMaker, this fact may not be obvious to you.  If you’ve worked with databases such as Microsoft SQL or MySQL, you know that there is no interface for the end users to manage data in a meaningful way.  You have to create a separate environment using authoring tools such as Visual Basic or PHP and a web server.

Think of the most common “database” – a spreadsheet.  The interface for the information in the spreadsheet is a grid.  There aren’t many tools for structuring the information.  There are tabs for different worksheets, basic cel formatting, unintuitive formulas for calculations, and commands buried in the menus for sorting.  Spreadsheets are a cumbersome environment for designing forms that link data to other parts of the database.

By contrast, FileMaker offers a native method for structuring information.  It’s called the Layout.  Layouts display any information that you choose to place on the layout.  Layout A can be a form that forces the user to enter information in a specific order, and Layout B can be a list of all the forms and the information that was entered.  The information on each form is the same, just displayed differently for different purposes.

That fact that the raw data is displayed on layouts in FileMaker is what makes it an attractive platform for department managers and non-technical users who need a fast learning curve.  It’s also what drives database purists crazy about FileMaker.  SQL guys like the data “abstracted” from the interface so it can be used in any interface authoring environments.  FileMaker data can be used in other interface authoring environments, but that’s a subject for later.

In this group we accept that FileMaker is a pure database engine tied to an integrated user interface system.  Still, we can go a little “layout crazy” when Sally wants a layout that only shows her information and Bob wants a layout only for his information.  We find ourselves making dozens of layouts displaying the same data simply to meet the work flow and security rules.  There’s no way around that if we want to make our co-workers and clients happy.  Why would we force users to interpret information when it’s so easy to make a layout that renders a pleasantly formatted set of fields with buttons to take specific actions from that point in the work flow?  We wouldn’t.

But that doesn’t mean we have to struggle with a long list of layouts and update every previous layout with a new button that navigates to new layouts we’ve placed in the database.  There is a way to create a universal layout navigation menu that automatically updates itself when you add new layouts to the system.  The Dynamic Menu can also change depending on who is logged in.  The purpose of the Dynamic Menu is to save you time as the developer and promote consistency for the users.

Please watch the following video to see how the Dynamic Menu works.

Dynamic Menu – Part 2

Dynamic Menu works by storing the names of the layouts in a global repeating field, and then displaying the numeric instance of the repeating field to generate a series of buttons with titles that match the layout names.  Clicking the button passes the contents of the field repetition to a script as a parameter.  The scripts reads the parameter and goes to the corresponding layout by calculation.

The heart of Dynamic Menu is a script that runs at login to determine what layout names to populate in the repeating menu field.  The layout names that populate the menu names field are filtered by a couple of conditions.  a) does the current user’s privilege set allow them access to the layout.  b) does the name of the layout begin with special characters that prevent it from populating the layout menu.

Let’s get started.

If you don’t already have a table in your FileMaker database for storing system wide data such as preferences, you’ll need to make one.  I like to use the table name “sys” or “prefs”.  In the example I use the table name “!menu” and it contains fields only for managing the dynamic menu.

The menu table contains several fields, but the critical one is “menu”.  It is a global text field with storage options to allow up to 60 repetitions.  The reason that is is global is so that the menu names can be displayed on any layout regardless of the layout’s table.  The reason it is a repeating field with up to 60 occurrences is so that you can have up to 60 layout navigation buttons.  You can have as few or as many as you like.

Now build the display of your menu.  Make a layout called “~menu”.  The tilde will hide the menu layout from the menu.  You’ll see how when we study the login script that filters layout names. Place the “menu” field on your “~menu” layout and use the field format command or inspector (FM11) to show repetitions 1 to 1.  Duplicate the field and move it below the first field and format it to show repetition 2 to 2.  What you are doing is creating buttons that will be titled automatically from the layout names.  You’re using a repeating field so that you don’t have to create fields for every layout name. Repeat these steps until you have a grid of fields organized in order of the field repetitions.  You can align the fields vertically in a single column, multiple columns, or horizontally (as in the header style menu shown in the video).  Just be sure your field repetitions are sequential or else the menu order won’t match the layout order.

I’ll finish this solution tutorial Oct 22-23.

Dynamic Menu – Part 3

The heart of Dynamic Menu is the script that populates the repeating fields with the names of the layouts.

#_________________________________________clear menu
Set Variable [ $MenuCount; Value:60 ]
Set Variable [ $MenuNo; Value:1 ]
Loop
 Set Field [ !menu::menu[$MenuNo]; "" ]
 Exit Loop If [ $MenuNo = $MenuCount ]
 Set Variable [ $MenuNo; Value:$MenuNo+1 ]
End Loop
#_________________________________________set defaults
Set Variable [ $LayList; Value:LayoutNames("DynamicMenu") ]
Set Variable [ $LayCount; Value:ValueCount($LayList) ]
Set Variable [ $LayNo; Value:1 ]
Set Variable [ $LayCurrent; Value:GetValue($LayList; $LayNo) ]
Set Variable [ $LayCurrentMask; Value:Left($LayCurrent; 1) ]
If [ Get(CurrentPrivilegeSetName)="[Full Access]" ]
 Set Variable [ $Masks;Value:"0¶1¶2¶3¶4¶5¶6¶7¶8¶9¶~¶`¶!¶@¶#¶$¶%¶^¶&¶*¶(¶)¶_¶=¶+¶[¶]¶}¶¶|¶;¶:¶'¶,¶<¶.¶>¶/¶?" ]
Else
 Set Variable [ $Masks;Value:"0¶1¶2¶3¶4¶5¶6¶7¶8¶9¶~¶`¶!¶@¶#¶$¶%¶^¶&¶*¶(¶)¶_¶=¶+¶[¶]¶}¶¶|¶;¶:¶'¶,¶<¶.¶>¶/¶?¶{" ]
End If
Set Variable [ $MaskCount; Value:ValueCount($Masks) ]
Set Variable [ $MaskNo; Value:1 ]
Set Variable [ $MaskCurrent; Value:GetValue($Masks; $MaskNo) ]
Set Variable [ $MenNo; Value:1 ]
#_________________________________________start building the menu
Loop
 Set Variable [ $LayCurrent; Value:GetValue($LayList; $LayNo) ]
 Set Variable [ $LayCurrentMask; Value:Left($LayCurrent; 1) ]
 Set Variable [ $MenCurrent; Value:GetValue($LayList; $LayNo) ]
 Set Variable [ $MaskNo; Value:1 ]
#_________________________________________
  Loop
   Set Variable [ $HideLayout; Value:0 ]
   Set Variable [ $MaskCurrent; Value:GetValue($Masks; $MaskNo) ]
   If [ $LayCurrentMask = $MaskCurrent ]
    Set Variable [ $HideLayout; Value:1 ]
   End If
 Exit Loop If [ $MaskNo = $MaskCount or $LayCurrentMask =$MaskCurrent ]
 Set Variable [ $MaskNo; Value:$MaskNo+1 ]
 End Loop
#_________________________________________
  If [ $HideLayout=0 ]
   Set Field [ !menu::menu[$MenNo]; $MenCurrent ]
   Set Variable [ $MenNo; Value:$MenNo+1 ]
  End If
Exit Loop If [ $LayNo = $LayCount ]
Set Variable [ $LayNo; Value:$LayNo+1 ]
End Loop