3 Replies Latest reply on Jul 8, 2014 4:09 PM by philmodjunk

    Lost creating my first database

    mprusse

      Title

      Lost creating my first database

      Post

           I'm trying to learn FileMaker and getting a bit lost creating a simple database. In my database in Table 1 I want two fields (among other simple ones) that hold names. I would like to populate another table (Table 2) with just names so I can pick them when in the name field on Table 1. I've been able to do this with using a Lookup to Table 2. What I don't understand is how can I add names to Table 2's names database by entering them in those name field in Table 1 when the name doesn't exist in Table 2.

           Perhaps I've set up my tables wrong and I need to use relationships but those are confusing to me as well. Maybe a nice soul can walk me through this.

           Thank you so much!

           Mike Prusse

        • 1. Re: Lost creating my first database
          philmodjunk
               

                    What I don't understand is how can I add names to Table 2's names database by entering them in those name field in Table 1 when the name doesn't exist in Table 2.

               From the design that you describe, this would require a script that checks for a matching record and adds one if no match is found.

               But matching records by name gets messy very quickly due to the fact that names are not unique, people change their names and names can easily be entered into a table incorrectly due to the idiosyncratic way people spell their names.

               Thus, it is usually best not to link records by a name, but by an ID number that is assigned to the record for a person and never changed.

          • 2. Re: Lost creating my first database
            mprusse

                 Excuse my ignorance but how do you select a name from the table? You have to remember the ID number?

            • 3. Re: Lost creating my first database
              philmodjunk

                   You would not have to remember the ID number. The user need not even know that there is an ID number. But you have to walk before you can run.

                   Say that you define this relationship between two tables::

                   Contacts-----<Invoices

                   Contacts::__pkContactID = Invoices::_fkContactID

                   You can then open Manage |  Value lists and create a value list where you specify the "use values from a field" option.

                   You can set it up to display values from Contacts where __pkContactID is the "field 1" source of values and a Name field from Contacts is the second field source of values.

                   If you set up _fkContactID as a popup menu or drop down list that uses this value list, you can select a person by name, but the value list enters the Field one value, the ID number into the field in order to link the two tables in a relationship. Once this value has been entered into the _fkContactID field, any field from Contacts that you have added to the Invoices layout will display data from that selected contact.

                   Please note that this is the "beginner level" value list set up for linking records by ID. Much more sophisticated, script assisted options that can handle duplicate names and the automatic addition of new names can be used that are much more user friendly--especially when the list of contacts becomes large.