3 Replies Latest reply on Mar 16, 2014 8:29 AM by PatrickLollis

    Create a new record with information from two other tables

    PatrickLollis

      Title

      Create a new record with information from two other tables

      Post

           Thanks ahead of time for help with my newbie question!!

           I am a music teacher creating a database to manage performance information for my nearly 500 students. My database currently has three tables.

           Table 1 - Student information: This table has all the regular information you'd expect: name, grade, teacher, class, birthdate, student ID, etc..

           Table 2 - Music Standards: Each record contains: Category (performance / literacy / critical), Skill Ladder (ladders of related skills within each category, such as "Literacy - Rhythm", and "Performance - Sing"), Standard Name, Description, Grade Level, and Standard ID Number.

           Table 3 - Music Notes (related to Table 1 with Student ID and Table 2 with Standard ID). This table will bring together notes about the student's performance toward a specific skill, such as Student ID, Standard ID, Notes, Mastery (yes/no), Date, score, and container.

           To begin entering a new note, I'm currently starting on the student side with a "New Note" button. This runs a script that opens a data entry layout based on Table 3. It sets the student ID value to the currently selected student, brings over the first and last name, and sets the current date (which can be changed if I'm behind). Now, I need to "lookup" the standard ID from table 2 for which I'd like to take notes. I imagine the easiest way (for the user) would be to check one of three radio buttons to select the category, then use a dropdown menu populated with the skill ladders from the chosen category, then use another dropdown populated with the standards from that skill ladder. Finally, the matching standard ID would be set in the form, and notes can be entered.

           How do I do this?

           Thank You Again!!!

           Note: I could (and may) go the other direction, starting from the skill, but I would face a very similar situation getting the student ID from Table 1 (Grade, Class, Student, ID).

        • 1. Re: Create a new record with information from two other tables
          philmodjunk
               

                    This runs a script that opens a data entry layout based on Table 3. It sets the student ID value to the currently selected student, brings over the first and last name, and sets the current date

               But there is no need to bring over the first and last name. Since this data is already in the student table, you can use your relationship to access that data without needing to copy the data into the Notes table.

               

                    I imagine the easiest way (for the user) would be to check one of three radio buttons to select the category, then use a dropdown menu populated with the skill ladders from the chosen category, then use another dropdown populated with the standards from that skill ladder. Finally, the matching standard ID would be set in the form, and notes can be entered.

               What you are describing is a hierarchical series of conditional value lists.

               Here are some links on the subject:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

          Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Create a new record with information from two other tables
            PatrickLollis

                 I misspoke about the student first and last name. I merely display them on the data entry form so I can visually make sure I'm working on the correct student's record.

            Hierarchical Conditional Value lists looks like EXACTLY what I need. Thanks so much!!

            • 3. Re: Create a new record with information from two other tables
              PatrickLollis

                   Thanks again for the excellent help. The form (and a couple of others I made yesterday) work perfectly. :-)