6 Replies Latest reply on Oct 20, 2011 2:07 PM by mgores

    manual data entry in related table field

    LucyKenyon

      Title

      manual data entry in related table field

      Post

      If my related table B doesn't have the data I need, can I enter it manually into the main table A? I'd like to populate one and the same field EITHER with data from a related table B, OR type it in directly to my main table A. Is this possible? Or do I have to 

      1/ create a new field I can use for manual data entry  in main table A

      or 

      2/ go back and edit the related table B? 

      Thanks.

        • 1. Re: manual data entry in related table field
          philmodjunk

          Can you describe this in more detail what you are trying to do. (and use real names in place of "table A" and "table B")

          I think a looked up value setting on a field in Table B would do what you want, but need more detail to be sure.

          • 2. Re: manual data entry in related table field
            LucyKenyon

            Dear Phil Mod Junk,

            Thanks so much for your time and patience: I'm new to Filemaker and the manual isn't giving me all the answers. 

             I have a main table called Contacts - names, addresses-- etc that I imported from Outlook.  I have a second table, called Specialties, which I imported from Excel. The contacts' specialties were not in the Outlook database.  (Prob. TMI, but i work for an architecture firm so the specialties are civil engineer, lighting designer, that kind of thing).

            The two tables, Contacts and Specialties, are related by Company Name, and the Specialty field I created in the Record Detail layout in Contacts pulls in its data from he "Specialties" table.   NOW, if I want to add or change a specialty (the original Excel table is  incomplete / wrong in parts)  can I just type directly into the Specialties field, which is populated by data in the related table? Or do I have to choose a field type- EITHER this data comes in from the related field, OR I enter in manually? And if I have to choose, what's a smart way to avoid to having two specialty fields in the same form? 

            Was that as clear as mud? 

            • 3. Re: manual data entry in related table field
              philmodjunk

              The two tables, Contacts and Specialties, are related by Company Name

              So you have this relationship defined in Manage | Database | Relationships:

              Contacts::CompanyName = Specialties::CompanyName

              and the Specialty field I created in the Record Detail layout in Contacts pulls in its data from he "Specialties" table.

              How does it "pull in its data"? Is this a value list? or something else such as a looked up value setting?

              I'm not sure I see the purpose behind the specialties table when defined like this. Is this so you can show that the company "acme design" has contacts with the specialties "Civil Engineer" and "lighting designer" while "pinnacle design" only has "Civil Engineer"?

              As a side note, I don't recommend linking records by a name field such as the CompanyName, Such names are not unique, companies change their name and data entry errors can take place which require correcting them once you spot the error. If you base the relationship on a name, all of those events cause problems for you data. It's better to base the relationship between tables on a serial number fields instead so duplicate names and name changes don't mess up the links to your data.

              • 4. Re: manual data entry in related table field
                LucyKenyon

                So you have this relationship defined in Manage | Database | Relationships:

                Contacts::CompanyName = Specialties::CompanyName

                Exactly right. 

                How does it "pull in its data"? Is this a value list? or something else such as a looked up value setting?

                Just by virtue of the relationship. the data is text, not a list. 

                I'm not sure I see the purpose behind the specialties table when defined like this. Is this so you can show that the company "acme design" has contacts with the specialties "Civil Engineer" and "lighting designer" while "pinnacle design" only has "Civil Engineer"?

                the tables are defined like this because that's how my raw data was grouped - -some stuff in outlook, some in a bunch of different Excel workbooks.  A mess. I'm trying to bring it all together in FileMaker. I often need to find, say, marine engineers in Florida, but did not have those two criteria in one easily searchable database. 

                As a side note, I don't recommend linking records by a name field such as the CompanyName, Such names are not unique, companies change their name and data entry errors can take place which require correcting them once you spot the error.

                Makes sense. Really helpful. thanks so much. 

                • 5. Re: manual data entry in related table field
                  philmodjunk

                  Getting back to your original question, you shouldn't need to enter the data from Specialties into fields in Contacts except for the key field that matches to records in Specialties unless you have very specific reasons for doing so. You can just add the fields from Specialties to your Contacts layout and the data from the matching record in Specialties will appear on the layout just as though these fields are part of that same contact record. The catch here is that this only works for one related record in specialties. If you have more than one, you'd use a portal to display them in most cases.

                  • 6. Re: manual data entry in related table field
                    mgores

                    If there was only one specialty per contact, it may be better to just add a specialty field to the contact table and not have a separate Speciality table.

                    If there is more than one specialty possible per contact, a separate table and the use of portals as suggested by Phil makes the most sense.