1 Reply Latest reply on Jan 25, 2013 7:38 AM by philmodjunk

    Auto complete field from external file field



      Auto complete field from external file field



           I have two databases...

           TREE INFO                                                                                                    SITE DATA

           (A database of trees and the species related info i.e. textbook)           Site tree survey (site data record by me of individual trees)

           - Botanical name                                                                                               - Botanical name

           - Common name                                                                                               - Common name

           - Mature height                                                                                                  - Dimensions (of a specific tree)

           - Colour                                                                                                              - Location

           - etc                                                                                                                      - etc


           When I am filling out the SITE DATA file, I have "Auto complete using existing values" turned on.  I still have to type in the Botanical name and common name at least once before this becomes a benefit. 

           How do I link the Botanical and Common name fields in the SITE DATA file to the Botanical and Common name fields in the TREE INFO file, so that, when I type the first letter of a tree, lets say "E" into SITE DATA, all of the Botanical name records from TREE INFO are displayed to choose from?

           Secondly, from reading through the help and forums, I see now that filemaker can automatically select the Common name when I as a user choose a Botanical name.  Would this be able to continue across files in the manner above?  

           One issue I can see is that the two databases don't have an inherent common field. 



        • 1. Re: Auto complete field from external file field

               You should link these tables in a relationship. Neither the botanical nor the common name is the best option to use as a match field in the relationship.

               Define an auto-entered serial number field in Tree Info: __pkTreeID

               Link it to a number field, _fkTreeID in Site Data

               Tree Info::__pkTreeID = Site Data::_fkTreeID

               Remove the common and botanical names from Site Data, you do not need them. With this relationship, you can simply add these names fields from Tree Info to your Site Data layout.

               Format a value list for _fkTreeID to list __pkTreeID as the first field and either the common or the botanical name as the second field. Selecting a value from this value list can then be done by name, but the value list enters the ID number. When you select a tree from this value list, the name fields from Tree Info will automatically display the names for that tree.

               This is a "beginner level" solution. Other options exist that make working with large numbers of names easier to manage than you get with an ID number based value list.