5 Replies Latest reply on Mar 30, 2012 10:06 AM by philmodjunk

    Creating an iPad wild plant checklist for Filemaker Go



      Creating an iPad wild plant checklist for Filemaker Go



      I'm the botanist at a large Park District that manages over 50 parks. I'm trying to create a simple wild plant checklist to use on an iPad with Filemaker Go. I'd also like to create a comparable wildlife checklist that can be used by the wildlife biologists on staff.

      Currently I do surveys in more than 50 parks using a traditional printed alphabetical checklist of all the species that may occur in a particular park. The paper form for a plant survey looks roughly like this (with '.....' indicating many additional rows):

      Date: __March 26, 2012__ Park Name: _Wildcat Canyon______

      Ferns or fern-like:
      Adiantum aleuticumFIVE-FINGER FERN 
      Adiantum jordanii    CALIFORNIA MAIDENHAIR FERN
      Grasses or grass-like:
      Agrostis avenacea   PACIFIC BENTGRASS
      Achillea millefolium  YARROW
      Acer macrophyllum   BIG-LEAF MAPLE

      The printed plant checklist is currently 26 pages long, with a total of more than 1,300 species. I check off each plant I see in the field, and back in the office I manually update my Microsoft Access database with the results.

      I would like to switch to FileMaker Pro with FileMaker Go on the iPad, but first I have to see if a functional digital checklist can be produced. I’m using the trial version of FileMaker 11 to test that now.

      My goal is to create a database running on an iPad where:

      1) 'Date' could be entered manually (or automatically).
      2) 'Park Name' could be chosen from a list (a drop down list?).
      3) The plant list (or wildlife list) could be easily scrolled or searched with a text entry.
      4) Observed species could be easily checked off (with a checkbox or radio button?).
      5) The results could easily be transferred to a master database back in the office.

      For lookup tables, I've created: 
      1) Parks: ParksID (autonumber), ParkName (text) - 66 records
      2) Plants: PlantID (autonumber), PlantName (text), PlantNameCommon (text), Family (text), Form (text), Origin (text), Longevity (text), and HabitatBloom (text) - 1,359 records. The fields after PlantNameCommon would be used to filter a search or display helpful ID information on the data entry form. 

      At this point, I'm stuck. 

      Should I use a blunt force approach: Add 'Date', 'ParkName' and 'Check' fields to tblPlants and just edit a big table? I think I could set that up, but I'd prefer a more elegant solution.

      Or should I create a normalized database with SurveyDetails junction table linked to the Survey table (SurveyID) and Plant Names table (PlantID), with a "Seen" field for checking off plants? See the screen shot below.

      That sounds like the right approach, at least in the Access world, but , but I currently don't know how to get there. Do I need other fields? And how should the data entry form be designed? 

      My apologies for any confusion in my descriptions and questions. I’m new at using FileMaker Pro and I’d appreciate any suggestions that would allow me to move this paper checklist to FileMaker Go.


        • 1. Re: Creating an iPad wild plant checklist for Filemaker Go

          The data structure and relationships you have created seem quite reasonable.

          You have a number of options you can use here. I would enable "allow creation of records via this relationship for SurveyDetails in the Plants to SurveyDetails relationship. I'd then set up a list view layout based on Plants with a button in the body of this list that creates a related record in SurveyDetails when it is clicked. I probably would not use the Seen field as only those species that are observed will get a related record in the SurveyDetails table.

          Prior to starting the survey, I'd create the needed Sruvey record and load a global field or variable with the SurveyID. An autoenter calculation defined for the SurveyDetails::SurveyID field would then copy the current value of this field or variable. That way the script to log observation of a species could be simply this:

          Set Field [SurveyDetails::PLantID ; Plants::PlantID ]

          With the "allow creation" option I mentioned earlier enabled, that's all the button has to do to log the observation of a plant. If you need to record more details than just whether or not it is seen, you can either add those fields also to this layout (and then you don't necessarily need the button) or the button can pop up a dialog box or temporarily change layouts to records additional details about the observed species.

          Conditional formatting can change the appearance of such a button and even the color of the plant name field to provide quick feedback as to which species have already been observed.

          Oh yes, and with a sub summary part, and the correct sorting, you can get the general categories listed under subheadings like you show in your example. In fact, a drop down list could be added to the layout that filters the list down to just species of that category. (And you can use more than one such list--much like a simplified field guide or dichotomous identification key to quickly find the species for which you want to log the species observed.)

          • 2. Re: Creating an iPad wild plant checklist for Filemaker Go


            I've set up the relationship and the layout you suggested. It looks good. I love the button idea, especially with conditional formatting, but I haven't gotten that far yet. 

            In your third paragraph, you said:  

            Prior to starting the survey, I'd create the needed Survey record and load a global field or variable with the SurveyID. An autoenter calculation defined for the SurveyDetails::SurveyID field would then copy the current value of this field or variable. 

            How do I set up the global field? Do I add a global field to the Survey table (SurveyIDGlobal) or a new table? I've even tried setting up SurveyID as a global field. And how do I "create the needed Survey record"? I've tried placing the Survey table fields , including a global SurveyID, in the List View Header, but I'm only allowed to enter the SurveyID number. I'm not allowed to select the other two fields (Surveyor and a Park Name drop down list).

            Sorry for the dumb questions....



            • 3. Re: Creating an iPad wild plant checklist for Filemaker Go

              A global field is a field that stores a single value instead of a possibly different value for each record in the table. It often can be used in place of the "text box" you might put on an Access database form. You can define it in any table and it still works. I often set up a separate table for all globals not used in relationships to make them easier to keep track of in my database system.

              You don't want SurveyID to be global or each new survey will disconneect from previous recorsd. (I'd use the same table structure here for your main database where you wil merge data from the FM Go devices.)

              how do I "create the needed Survey record"?

              It's up to you. You can switch to a layout based on Survey and select New Record from the records menu or you can run a script like this:

              Freeze Window
              Go to Layout [survey (survey)]
              New Record/Request
              Set Field [Globals::gCurrentSurveyID ; Survey::SurveyID ]
              Go to layout [original layout]

              You can then open Manage | database | Fields, find and double click the SurveyID field in SurveyDetails and enter:


              As its auto-enter calculation. That way, each new record in the SurveyDetails table is automatically linked to the same record in the Survey table. If you were to manage more than one survey at a time, you can put the gCurrentSurveyID field on any layout and format it with a value list of SurveyID plus other data from the Survey table so that you can select the survey that you are currently working with.

              Hmmm, but if you do find you need to work with more than one survey at a time, we'd then need to move gCurrentSurveyID into the Plants table and update the relationship to be:

              Plants::PlantID = SurveyDetails::PlantID AND
              Plants::gCurrentSurveyID = SurveyDetails::SurveyID

              You'll need to decide if that's a possible situation for how you plan to manage these surveys.

              • 4. Re: Creating an iPad wild plant checklist for Filemaker Go

                Thanks again!

                I was able to watch some tutorial video on the topics you suggested, and suddenly it all makes sense. Wow! Now I see why so many people love working in FileMaker Pro.

                I still have some details to work out, but my data entry tables are working with pop-up lists for parks, conditional formatting for the plant list buttons, and pop-up calendars for dates. Plus doing a search in the plant table is a delight. After grinding through Access for years, I feel like a kid in a toy store! Databases are fun again.

                Thanks once more for helping me over the hump. I've attached a screenshot showing the results of searching PlantNameCommon for "Oak", and how the three Oak species I selected show up in the SurveyDetails table, as you suggested. Now I can work on the appearance and adding buttons w/scripts to improve the ease of use, and turn the Family and Form fields into separate, linked tables.

                • 5. Re: Creating an iPad wild plant checklist for Filemaker Go

                  Sounds like you have discovered one the key differences between Access and FileMaker. In Filemaker, you have a built in capability to do an "off the cuff" search of the records in your database directly from a layout--pulling up a set of records matching the criteria you specify.

                  In access, you can do such a search from the query manager, but this keeps you separate from the forms and reports where you have the data arranged/formatted in a user friendly format and you quickly end up wrestling with SQL expressions to find the records you want--especially when there is more than one table involved.

                  Access developers can and do create controls and pop up forms that enable the user to search the database and see the results on a form or report, but the developer must create that capability--it's not built in and if the developer fails to anticipate the precise type of query you want to use to research data in the database, you can't do it.