6 Replies Latest reply on Feb 23, 2015 10:01 PM by philmodjunk

    Auto fill species database based on another column, no unique ID

    IngridKnapp

      Title

      Auto fill species database based on another column, no unique ID

      Post

      Hi I'm trying to revamp my labs sample collection database and would like to add some columns which will fully auto complete based on another column (no drop down menus, I have 1000's of records!).

      The data is a collation of collected genetic tissue samples. So there are a number of samples with the same species names and I want to auto populate what phylum, and eventually what genus, they are in so it's easier to search the database in the future. I've been reading a lot of forums/watching tutorials and I still can't seem to do it. 

      I have one main table (A) with all the sample information (incl. species name, location, collector, unique ID etc) and another table (B) with species names and the phylum they belong to (I'll deal with genus if I can get this to work). I want the phylum column in the main table A to autofill based on the phylum info in the species name table B. Is this possible?

      I've created relationships between the 2 tables (in every way I can think of, including ticking the "Allow creation of records in this table via this relationship" button"). I looked into portals, but I wasn't sure if I should pursue that method more amongst other methods.

      I'd like to be able to do this for a number of variables, so it would be exceptionally useful to work this out. I feel like it should be an easy fix, but I just can't seem to work it out. Also a lot of the original files were in filemaker pro 9 so this is the version I'll be using presently (but I can mostly work out version differences). 

      If further information is needed please let me know and I'll be happy to provide it.

      Many thanks

      Grid

        • 1. Re: Auto fill species database based on another column, no unique ID
          philmodjunk

          I'm not sure I understand the question. Is this what you want to do?

          You enter (or select from a value list--the fact that you ave 1000's of records doesn't mean that you cant use a value list) the species and the other taxonomic data, such as Phylum appears automatically--looked up from a table of this info.

          If so, a simple relationship that links the two tables by the Species field, or possibly genus and species field if the species name alone is not specific enough should be sufficient for your system to display this information from the related table.

          See this thread for more on the subject: Auto Fill

          (no drop down menus, I have 1000's of records!)

          Just because you have 1000's of records, this does not mean that you can't use value lists--including drop down menu formats. Such a drop down list can use auto-complete to "filter down" the list of values and you can also set up a conditional value list to trim the list down to size, for example, you could select the genus in field 1 and the field 2 can deploy a drop down list of species, but only those for the genus selected in field 1. Plus there are value selection methods that go beyond what a simple value list can do when you need to quickly and accurately select from a very long list of values.

          You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Auto fill species database based on another column, no unique ID
            WBSimon

            I have a degree in Botany, so I speak your language. 

            Do you have a drop box link available so I can take a look at the file?

            • 3. Re: Auto fill species database based on another column, no unique ID
              IngridKnapp

              Hi Phil, thanks for your reply, I really appreciate the quick response and thanks for the link to the other forum. It was the main one I came across to try and sort out my issue, but I thought (and I could very well be wrong) that to do it this way (which I tried) each row would need a unique ID, which I don't have as I want to autofill the phylum based on the species name, which is the same for 100's of samples. Is this a problem or should it still work? I'm fairly new to filemaker pro, so apologies for my naivety in the subject, however from what I've learnt in the last week with this software it will be pretty useful.

              Just to clarify my question here's a little example of what these 2 tables look like, with Species, Phylum, order and genus information (sorry yes I did ad on order this time round) (I've attached them as well as hopefully embedded them in this response).

              Table 1 (Main table)

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              SampleIDSpecies NamePhylumGenusOrder
              Av1Arca ventricosa   
              Av2Arca ventricosa   
              Av3Arca ventricosa   
              Av4Arca ventricosa   
              Av5Arca ventricosa   
              FS1Fungia scutaria   
              FS2Fungia scutaria   
              FS3Fungia scutaria   
              FS4Fungia scutaria   
              FS5Fungia scutaria   
              Pp1Panulirus penicillatus   
              Pp2Panulirus penicillatus   
              Pp3Panulirus penicillatus   
              Pp4Panulirus penicillatus   
              Pp5Panulirus penicillatus   
              PL1Porites lobata   
              PL2Porites lobata   
              PL3Porites lobata   
              PL4Porites lobata   
              PL5Porites lobata   

               Table 2 (Taxonomic Information)

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              Species NamePhylumGenusOrder
              Arca ventricosaMolluscaArcaArcoida
              Fungia scutariaCnidariaFungiaScleractinia
              Holothuria whitmaeiEchinodermataHolothuriaAspidochirotida
              Panulirus penicillatusArthropodaPanulirusDecapoda
              Porites lobataCnidariaPoritesScleractinia

               

              Also thanks for the links to the Adventures in FileMaking series, but I have v9, so it won't open the v12 files I'm afraid, I did a quick google to see if there were earlier version but I couldn't find any. I'm going to try out your suggestions, but if have more to add please feel free as I'm not sure I explained myself very well the first time, but hopefully seeing the tables will help.

              Also thanks Brent, I might take you up on that offer if I can't get this to work, but I like to try and solve things myself first ;) 

              Many Thanks

              Ingrid

              • 4. Re: Auto fill species database based on another column, no unique ID
                philmodjunk

                You need a means of correctly identifying the information you want to look up. This does indeed require some form of ID that matches correctly to the data you want to lookup. Usually, you set up a table with one record for each such possible value to look up. The key to look up this data would need to be unique, it might even be a pair of fields such as Genus and Species--which is actually what you have in your species name field. But this "unique value" is only used in the table from which you look up data, which seems to be exactly what you have set up in table 2. So if you are looking up data from Table 2 into fields of Table 1, you can use Species Name as your match field.

                Please note that such lookups will work even if the match field values in the table from which you are looking up data are not unique, it's just not the ideal set up as the lookup will always copy data from the first such matching record as controlled by the sort order (if any) specified for the relationship.

                As to "Adventures In FileMaking", you do need at least FileMaker 12 to open the files. If you want, you can download the 30 day free trial copy of FileMaker 13 in order to open and read these files. Most of the techniques, not all, could be used in Filemaker 9.

                • 5. Re: Auto fill species database based on another column, no unique ID
                  IngridKnapp

                  Hi Phil, so I finally worked out what I was doing wrong. In the relationships I was making duplicates of the value tables, not the main data table. Well I think that what wasn't working, it seems to work now so I'm happy. Anyway I thought I'd write a brief description of what I did so you can have a look through it if you wanted to and so other people can maybe use it. I also wanted to attach the filemaker pro file, but wasn't sure how to.

                  Many Thanks for all your help

                  Best Wishes

                  Ingrid

                  What I did to make a taxonomic autofill work for me:

                         
                  1. Setup 2 tables:
                         
                  1. Data table with information filled out for SampleID and Species Name columns and empty fields: Genus, Order and Phylum, which you want to auto-populate with values from Table B.
                  2.      
                  3. Values table with taxonomic information including: Species Name, Genus, Order and Phylum.

                   

                  Species Name will have to be used as the “unique value” or match field, even though it’s not unique it is the information I wanted the other data to be auto-populated from.

                   

                         
                  1. Set-up relationships in File|Manage|Database|Relationships by dragging and linking the labels between the boxes like this:

                  DataTableA::SpeciesName = ValuesTableB::SpeciesName

                  And

                  Then duplicate the Main DataTableA, rename the duplicates (DataTableA_Phylum, DataTableA _Order and  DataTableA _Genus) and link ValuesTableB to each of the duplicates ie

                  DataTableA_Phylum::Phylum = ValuesTableB::Phylum

                  DataTableA _Order:: Order = ValuesTableB:: Order

                  DataTableA _Genus:: Genus = ValuesTableB:: Genus

                   

                  So you have multiple Main tables and still 1 values table. (I was originally duplicating the Values table and linking it to the one Data table, this didn’t work). I’m not sure if this is completely necessary as it gets messy in your relationships window, but it worked for me so I’m sticking with it!

                  Also in the edit relationship window (found easily if you click the = sign which now links the 2 boxes) don’t forget to tick the “Allow creation of records in this table via this relationship” box for the table you want to see filled out.

                   

                         
                  1. Alter the “Field/Control Setup”, found in the layout view by clicking on the box with your field name in e.g. “Genus”. In the setup I left the control style as Edit Box as I don’t want drop down options. Then in the “Display Data From” drop-down menu change it to look from ValuesTableB and select Genus. Repeat appropriately for Order and Phylum. This should auto-populate the fields in Table A based on the values in Table B.

                   

                  • 6. Re: Auto fill species database based on another column, no unique ID
                    philmodjunk

                    Species Name will have to be used as the “unique value” or match field, even though it’s not unique it is the information I wanted the other data to be auto-populated from.

                    But species name SHOULD be unique, but only in the values table from which you are looking up information when you select a species name.

                    and empty fields: Genus, Order and Phylum, which you want to auto-populate with values from Table B.

                    Don't really see a need for all those fields defined in Table A. Once you have the species name to link to a record in Table B, you do not have to copy over all the other data from Table B into Table A. On a layout based on Table A, the relationship makes it possible to add fields from Table B to your table A layout and then you don't need to put the same data into both tables--which can create problems for your database.

                    And I don't quite see the need/purpose for so many occurrences of the values table with so many different match fields.

                    Didn't you just want to enter/select a species name and then see the taxonomic levels for that specified species name automatically appear? That should only require one relationship between your two tables with species name used to match records.

                    On the other hand, a series of such relationships could be used to set up a hierarchical value list where you start with the highest order taxonomic level and the value selected for it limits the values listed for the next lower taxonomic level in a "chain" of conditional value lists. That's quite possible and is demonstrated in several different ways in "Adventures in FileMaking #1 - Conditional Value Lists", but wasn't what I understood you to want here.