6 Replies Latest reply on Jan 21, 2016 1:31 PM by Ent_Lab

    Database Assistance

    JosephMilanovich

      Title

      Database Assistance

      Post

      Hello, I am looking to create a simple database that includes an overall site and associated data.  In particular, for each site I would like to include variables with subsections.  For example, for Site 1 I would like to collect data from several traps.  In each trap, I would like to 1) select what species is in the trap (from a drop down list) and 2) how many numbers of individuals of that species are in that particular trap.  Aside from this issue, the creation of the database is straightforward enough.

      I would appreciate any assistance provided.

        • 1. Re: Database Assistance
          philmodjunk

          And what level of experience and expertise do you bring to this project? (that makes a big difference in what level of detail should be provided and how sophisticated a suggested design might be described and still give you a decent chance of implementing it.)

          I'd guess that this may be your very first attempt at creating a database, but that's just a guess.

          • 2. Re: Database Assistance
            JosephMilanovich

            Hello, yes, this is my first database.  My initial thought was to set up a single table where I would include site, environmental variables, etc, and then buttons for each trap.  Within each button I would like to select a species (out of several) from a drop down list and then enter the number of individuals for that species at that trap.  Is that possible? 

            Again, thank you.

            • 3. Re: Database Assistance
              philmodjunk

              Possible, but not a good idea. What you have are three "lists" of distinctly different information. One is a list of sites. Then for each site you have a list of traps and for each trap, you have a list of things caught in that trap. you might even need more than one list of things caught if you check the trap multiple times and record what's in the trap each time....

              Each of these different types of lists needs its own database table so to get things started, you'd set up 3 tables like this:

              Sites-----<Traps-----<SpeciesTrapped

              Sites::__pkSiteID = Traps::_fkSiteID
              Traps::__pkTrapID = SpeciesTrapped::_fkTrapID

              For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              With this basic set up, you can put a portal to Traps on a Sites layout to list all traps located at that site. You can put a portal to SpeciesTrapped on a Traps layout to list all species found caught in that trap.

              That's all a very simple basic set up. There are ways, for example to select a trap in your portal on the sites layout and see the list of species found in that trap, but first things first as we'll need to keep this as simple as possible at first to make sure you have a good chance at getting this to work.

              • 4. Re: Database Assistance
                JosephMilanovich

                Hello,

                This was very helpful.  I have created the tables, created/labeled fields within each table (for example, within the Traps table I created 20 traps to denote each trap).  Next, I created relationships with fields within my main table I would like transferred over (e.g,. site, date, etc) to each trap field, and then each trap field to a Species field that includes a drop down list of every species we will encounter.  I also created portals as described above. 

                At this point I would like to inquire about how to work on my layout.  This will be used with iPads, but it does not seem like I can move the Portals and I would like to create buttons to move in-between the tables as the data collection is complete.  Also, I would like to write script to begin a new set of data collection for all the above variables once we finish with a particular site and move onto another site, or some way of identifying that a new site is being worked on.

                Thanks again.

                • 5. Re: Database Assistance
                  philmodjunk

                  If by

                  within the Traps table I created 20 traps to denote each trap

                  If you mean that you created 20 different fields in the same record--one for each trap, then you have not correctly set this up. If you mean that you set up 20 different Traps records all linked to the same Site record, then you have successfully followed my design recommendations. I don't think that's what you did here as you would first set up the relationship and the layout with a portal, then you would enter data to create related Traps records. Note that this method allows you to have any number of traps at a given site, from 1 to hundreds and the system will support it and this is not the only reason for using a portal to a related table instead of individual fields.

                  With the current simple design, you would have a portal to Traps on the sites layout and a portal to species on the traps layout. Buttons would move you from one to the other by changing layouts. Once that works, we can talk about how to put a button inside the portal row in the traps portal that allows you to both change layouts and pull up a list of just those Traps records so that you can see a portal to species for each Trap record.

                  • 6. Re: Database Assistance
                    Ent_Lab

                    Hello everyone,

                     

                    My lab also works with insect trapping, sites, and species counts. My reply has two parts:

                     

                    1) We use FileMaker 11, using seven databases. All data is pulled from the same Excel file. AllSites is relational to Collector Codes and Treatments. LepMASTER is relational to AllSites, LepTaxa, SurveyMethod, and PlantSpecies. LepMASTER then is the primary database with ALL of the trapping information. This is also where we track species and number of samples.

                     

                    2) This is where I am struggling. We have created a digital reporting form to track trapping data at the end of the summer.  Our Excel file has headings with trap number and specific species names. Currently, the way the form is set up, I cannot get the different species to match up to it's coordinating trap number. Here is a screen shot of the Excel file:

                    Screen Shot 2016-01-21 at 12.04.41 PM.png

                    Here is a screen shot of the form:

                    Screen Shot 2016-01-21 at 11.54.03 AM.png

                     

                    # male is Species Count1, Species Count2, Species Count3. How do I change my form so I can successfully pull both the species and count? I attempted to add each species in my Value List, but that didn't work either.

                     

                    Thanks in advance. --Angela

                    JosephMilanovich