3 Replies Latest reply on May 29, 2014 11:23 AM by philmodjunk

    Creating relations and look up help please



      Creating relations and look up help please


           Our non-profit has two databases that were already set up when I arrived.  I'm not well versed in FileMaker Pro 10. We have one database that list all members and their information.  Every month we have exhibitions that members can enter.  I'm trying to link our Member DB with the Show DB (no info in this DB other than the fields), so that the Show DB automatically fills in when you start typing in a name. The field names I want to link match in both DBs - First Name, Last Name, Phone and Email.

           I've created the relationship between the 4 items.  I then specified the Look-up for each of these fields.  However, when you start typing in the Show DB no info is transferred from the Member DB.

           I've followed each step in the sections for creating the relationship between the two DBs and to define a lookup, I must be missing something.  I'm fairly sure that when the DBs were created they were not set up or structured to be related. Can these two DBs be related and set for look-up without being recreated from scratch?

           Thank you


        • 1. Re: Creating relations and look up help please

               You shouldn't match by all four fields like this. With all four fields specified as match fields, you'd need to enter perfectly matching data into all 4 fields before the record you are editing can match to to a record in the related table.

               And matching by names is not a good idea. Two people with exactly the same first and last name is a more common occurrence than you might think. If you match by name alone, entering the name can match to the wrong member if you get two members with identical names.

               Ideally, you should be matching by a member ID number--a unique value assigned to each member record and never changed (where people do change their names) There are then a number of methods possible for selecting or entering a member's name such that it links your two tables by ID number.

               Here's the "beginner level" approach to get you started. If you can get it to work (It won't allow you to start typing a name and get an auto-complete), we can look at more sophisticated methods that make working with larger groups of members easier to manage (and some of these options do support auto-complete)

               Define a text field in your members table with this auto-complete calculation:

               Last Name & ", " & First Name

               Select the Unique values validation field option for this field.

               Use the method in this thread to update your existing records so that they get the needed text into this field: Updating values in auto-enter calc fields without using Replace Field Contents

               Link your two tables like this:

               MSA Member Database::__pkMemberID = MasterShowTemplate::_fkMemberID

               Yes, you may need to add an auto-entered serial number field to use for a memberID if you do not already have one.

               Now define a value list with the "use values from a field" option.

               Select __pkMemberID from MSA Member Database as the value for the first field, then select the new auto entered full name field as field 2. Select the Show only values from second field option to hide the ID numbers that would otherwise appear in this value list.

               Go to your MasterShowTemplate layout and format _fkMemberID as a drop down list or pop up menu that uses this value list.

               When you select a member from this value list, you select a member by name, but it enters the id number in order to link the two tables. You can then add the Name, Phone and Email tables from MSA Member Database to the MasterShowTemplate layout and they will display that data once you select a member from the value list.

               Once that's working we can look at enhanced options that support auto-complete.

               PS. the fields listed in your MasterShowTemplate table suggest that you really need an additional related table in place of "first entry, second entry...."

          • 2. Re: Creating relations and look up help please
                 Thanks.   I did try linking with only using one unique identifier, namely email address.
                 I was hoping for a much easier solution - such as those listed in the "help" sections for creating relationships and look-up.
                 I'm a volunteer with the organization and happen to have a little more tech knowledge that most other members, that's why I'm in charge of the data entry. However, as I mentioned before, I really don't know FileMaker.
                 I'm currently the web master and I'm in the process of creating a new website, for the organization, using Drupal and CivicCRM, which is eating a lot of my time.
                 Thanks for your feedback and when I have the time I'll give this a try.
            • 3. Re: Creating relations and look up help please

                   I would not use the email address field to link these two tables. People do change their email addresses from time to time and that will create problems for you if you link by it. Use the auto-entered serial number method instead.