12 Replies Latest reply on Apr 30, 2010 1:55 PM by philmodjunk

    Auto-fill multiple names and phone numbers

    m3m3m3

      Title

      Auto-fill multiple names and phone numbers

      Post

      Hi,

       

      I am relatively new to FileMaker 11.  I have a database that has 8 different drop down menus for names.  I need phone numbers to match up and automatically be filled in next to each person's name when the it's selected.  

       

      Reading through the forums and various websites, I've tried lookups and creating relationships to no avail.  This has been a very frustrating process so far.  

       

      If anyone can PLEASE PLEASE spell this out for me, like talking to someone who has never used a computer before, that would be very helpful and greatly appreciated.

       

      Thank you!

        • 1. Re: Auto-fill multiple names and phone numbers
          philmodjunk

          Please help us to help you.

           

          Please describe the tables you've set up to do this, then we can step your through the process of linking them in relationships and setting up looked up value field options that will work for you.

           

          8 different name drop downs seems a bit unusual, it may be that there's a design change that can simplify things so you don't have so many different drop downs.

          • 2. Re: Auto-fill multiple names and phone numbers
            m3m3m3

            Essentially, it is a job assignment sheet.  It has the job name, number, who is assigned to what position, machinery used, and dates.  I would like for their phone numbers to pop up next to their name.  Usually there are up to eight people working on a job at once--hence the 8 necessary fields.  So unfortunately, that part cannot change.

             

             

            I have set up two tables - the main one which will contain all the information for the job: number, project, location, machinery used, dates, position and personnel.   In hopes to create a relationship I created a second table that contains strictly names in one field and phone numbers in the next. 

             

            In the personnel section I have used value lists so that I could make a pop up menu for each.  There are 3 different value lists because not every employee is able to work in every position.  Could this be the problem?  

             

            I hope this is enough information and my problem can be solved.

             

            Thank you in advance to everyone who contributes to this thread.

            • 3. Re: Auto-fill multiple names and phone numbers
              philmodjunk

              m3m3m3 wrote:

              Usually there are up to eight people working on a job at once--hence the 8 necessary fields.  So unfortunately, that part cannot change.

               


               

              Actually that can change and probably should be. Using a portal and a join table, you can list these in a portal so that you only have one field and one relationship to link to your personnel table. That makes it possible to place the phone number field from the personnel table next to your drop down. When you select a person in the drop down, their phone number then automatically appears.

               

              With 8 separate fields, you'd need 8 separate relationships one for each drop down field. This can be done but is very tedious--requiring you to define 8 different table occurrences of the same personnel table.

               


              • 4. Re: Auto-fill multiple names and phone numbers
                m3m3m3

                Thank you.  How do I go about making a portal and a join table? 

                • 5. Re: Auto-fill multiple names and phone numbers
                  philmodjunk

                  For starters on Portals, look that term up in the Filemaker help table--it's got a pretty good introduction on the subject.

                   

                  THen you can enter "many to many" in the search box above and click search to find any number of threads that give examples of setting up such a join table.

                   

                  If you still have questions after that, post back here and I'll see what I can do.

                  • 6. Re: Auto-fill multiple names and phone numbers
                    m3m3m3

                    I vaguely understand the Portal concept.  When I use a value list does that interfere with the lookup? - example:  I want to be able to choose a name from a pop up menu (attached to a value list) and then have the person's phone number appear in a field right next to the name. 

                     

                    I hope that makes sense.

                    • 7. Re: Auto-fill multiple names and phone numbers
                      philmodjunk

                      No, that's one of the most frequently used implementations of this technique.

                      • 8. Re: Auto-fill multiple names and phone numbers
                        m3m3m3

                        OK great.  How do I go about implementing it?

                        • 9. Re: Auto-fill multiple names and phone numbers
                          philmodjunk

                          There are two basic methods. Which is best for you depends on what you need.

                           

                          One method copies the data from another table. Changes to the data in this other table don't automatically update existing values in the current table. That's useful for things like a table of prices in an invoice system where you don't want a price change to affect the invoices your created prior to the price change.

                           

                          The other method simply displays the field from the related table. With this approach, a change in the other table automatically updates existig values.

                           

                          Which is the option you need?

                           

                          In either approach it starts with the right relationship.

                           

                          Let's say your tables are called Main, Team and Personnel. Main is the table your layout is based on. You have a portal to Team and Personnel stores the phone numbers.

                           

                          You have a relationship for your portal

                          Main::MainID = Team::MainID

                           

                          You have a relationship for your phone numbers:

                          Team::Name = Personnel::Name     (I usually use ID numbers for this)

                           

                          To copy the phone number:

                          Add a field in Team for the phone number, In field options, select looked up value and select the Phone Number field from Personnel.

                          Place this field in your portal next to your Team::Name field that you've formatted as a drop down list or pop up menu.

                          When you select a name, the number is copied into the phone number field.

                           

                          To just display the number:

                          Simply place the phone number field from Personnel in the portal next to your Team::Name field.

                          When you select a name, the matching phone number is displayed in the the Personnel:: PhoneNumber field.

                          • 10. Re: Auto-fill multiple names and phone numbers
                            m3m3m3

                            Thank you!  It worked for one position.  Do I need to make separate tables for each position? 

                            • 11. Re: Auto-fill multiple names and phone numbers
                              philmodjunk

                              You shouldn't need to. Using my previous tables for the example, each additional position would be a new Team record joining the two tables. You'd just add a field to the team table for identifying the person's position.

                              • 12. Re: Auto-fill multiple names and phone numbers
                                philmodjunk

                                Here's a demo file of the concept: http://www.4shared.com/file/E9D5IMLK/MultipleNamePhone.html

                                I added two fields for describing a team member's "role". Role is specific to the join table, Team. Selecting a role in this field identifies a person's role for the specific Job Record. Job Desc is specific to the person's personnel record. It will show the same Job Description for all instances where a given person is added to a Job Record's team.