8 Replies Latest reply on Mar 11, 2014 3:04 PM by keywords

    auto completing a second field based on the first field input

    billblitz

      First of all sorry for such a newbie question...

       

      I have a table that contains 2 fields employee & id#. In my program (FileMaker13 pro advanced btw) I have a drop down to select Teacher Name. I wish to automatically fill in Teacher ID based on the Teacher Name selection.

       

      My database has the following relationship;

       

      Teacher Name = employee & Teacher ID = id#

       

      All are defined as Text fields

      thanks in advance,

      Bill

        • 1. Re: auto completing a second field based on the first field input
          ErikWegweiser

          Hi, Bill. Welcome. Don't be afraid to ask. We're all noobs now and then.

           

          It sounds like you're working with an Employee (teachers, etc.) table, to which you have a relationship from some other table, such as Class. In both tables, you have the Teacher Name field and Employee ID / Teacher ID field. When you choose a Teacher Name in the Class table record, you want the Employee table's Employee ID to automatically populate in the Teacher ID field in the Class record.

           

          Your relationship from Class to Employee need only be Teacher Name = Employee. The Teacher ID field in the Class table is then defined as a lookup based on that relationship, copying the Employee ID field to the Teacher ID field.

          • 2. Re: auto completing a second field based on the first field input
            billblitz

            Erik,

                 Thank you. This worked perfectly. I guess I tried to make it too complex.

            Bill

            • 3. Re: auto completing a second field based on the first field input
              keywords

              "This worked perfectly" … except that you are headed for trouble! This approach will break as soon as your database contains two teachers with the same name—how will FM know which ID to choose?

               

              A better solution would be to build your relationship using the ID field, which IS unique (or should be, else what is its purpose?). Then create a value list using the teacherID field from the employee table and also showing teacher name, and check the "show only the second field" option. Attach this list to the teacherID in the other table; when you enter that field the list will show teacher names, but when you select from the list the data entered will be teacherID. To then display the name just place a related name field on your layout.

              • 4. Re: auto completing a second field based on the first field input
                ErikWegweiser

                Quite correct. I didn't bother to elaborate on this point, partly because its a whole other thread regarding use of pop-up lists vs. drop-down menus and various techniques for overalpping fields, etc. when using this kind of value list.

                • 5. Re: auto completing a second field based on the first field input
                  billblitz

                  yes you are right. Know that I understand the technique, I will drive off the ID is is UNIQUE. Thank you again.....Bill

                  • 6. Re: auto completing a second field based on the first field input
                    billblitz

                    Erik,

                    I hate to impose on you again, but you were so knowlegable  I have to ask.


                    I have a table (ScoreCurve) with 3 columns


                    Points, Score, Rating


                    4.0, 27, highly effective

                    .

                    .

                    .

                    .

                    .

                    1.0, 0.6, Ineffictive


                    I would like to call into the table ScoreCurve with a value for Points and get back Score and Rating. My variable for Points is calculated in my Table APPR Teacher Points. I would like to insert returned Score into APPR field Teacher Score and Rating into APPR field Teacher Ratings


                    I tried the relationship  APPR Teacher Points = ScoreCurve Points, but cannot get anything back in the Lookups. I think I am missing something fundamental. Google has poor (for me) examples in the Filemaker search returns for FileMaker VLookup. I hate to return to the FileMaker forum so quickly, as to not seem to be an annoyance. Would you point me in the right direction?

                    Thanks in advance,

                    Bill

                    • 7. Re: auto completing a second field based on the first field input
                      billblitz

                      yes you are right. Know that I understand the technique, I will drive off the ID is is UNIQUE. Thank you again.....Bill

                      • 8. Re: auto completing a second field based on the first field input
                        keywords

                        Bill, your explanation is a bit unclear to me, but as I understand it you wish to enter data into the Points field and then have the Score and Rating fields display appropriate values based on that. If that is right, then it would seem to me to be best achieved by having all three fields in the same table. Points field can be a Number field into which you enter the points. The other two fields can be calculation fields which reference the Points field.

                         

                        For the Score field I assume you will have some formula which delivers a different score in a range from 0.6 … 27 for different values in a range from 1.0 points up to 4.0. I suggest you use the Let( ) function to build your formula. The calculation result will need to be number.

                         

                        The Rating field may even use the same formula, but produce a different result—a range of words from Ineffective … Highly Effective. In this case the calculation result will need to be text.

                         

                        If you need to then display these fields or transfer them to other table for some reason, this can be done via relationships. For example, if the points/score/rating fields are in student records, you may wish to display all the students of a given teacher, together with their points/score/rating in a portal on the teacher record.