5 Replies Latest reply on Nov 18, 2011 9:50 AM by philmodjunk

    Display records from one table in another table/layout?

    iMakeFilez

      Title

      Display records from one table in another table/layout?

      Post

      I have 1 database with 2 tables inside, Members and Students. The Members table has fields such as Name, ID#, Age, etc., and the Students table has a Grade field as a dropdown list. I want to set up a Student layout so that it can show me data from the Members table, such as Names, ID, Age, and the dropdown list for the Grades from the Students table. So far I've been able to get the Grades field to show me the data in the dropdown list, but am unable to get the Name, ID, Age, etc. to show up. I set up the table so that they are linked together via the Serial#.

        • 1. Re: Display records from one table in another table/layout?
          philmodjunk

          You need this relationship:

          Members::ID# = Students::ID#

          If you intend to use a portal to Students placed on the members layout to create new students records, double click the relationship line and enable the "allow creation of records via this relationship" option for the students table.

          From there, I have to stop. I don't know how you want to set up your layouts. Where have you located this drop down? What field is it? How is the value list defined?

          If you have a valid relationship linking the two tables, and your records contain the correct values in their ID# fields, you can place the fields from members on your Students layout and they will display the correct data from the related members record.

          • 2. Re: Display records from one table in another table/layout?
            iMakeFilez

            How do I achieve the Members::ID# = Students::ID# setup?

            I placed the fields from Members into the Student layout, but nothing shows. I wanted to set it so that the Student Layout would be populated with the data already in the Members table, and set it so that you cant create new records from the Students layout, only from the Members table.

             

            This is what I have so far.

            • 3. Re: Display records from one table in another table/layout?
              philmodjunk

              The relationship you show:

              Members::Serial# = Students::Serial#

              should work for you, bu tyou have to enter a value into Students::Serial# that matches the serial# field for a record in members before you will see any values.

              To repeat:

              I don't know how you want to set up your layouts.

              Where have you located this drop down? (what layout?)

              What field is it? How is the value list defined?

              If you set up the Students::Serial# field as a drop down list of Serial numbers from Members, you can create a new record on the Students layout and select a member from this drop down. Fields on the layout from the Members table should then display data from that selected record.

              Set up a value list for this drop down using the specify field option in manage Value lists. Specify Members::Serial# for field 1 and Members::cFullName for field 2. (cFull Name would be a calculation field you define in Members that combines the first name and last name fields.)

              With this value list, you can select a member by name, but the value list enters the corresponding serial# and then data from that related member record will appear in the member fields on the layout.

              • 4. Re: Display records from one table in another table/layout?
                iMakeFilez

                Isnt there a way to have the fields populate without me inputting anything?

                 

                I used the Inspector Value list > Manage Value List to create the dropdown list and set the Grade field as the dropdown list.

                 

                This is how I have the layout setup for Students.

                • 5. Re: Display records from one table in another table/layout?
                  philmodjunk

                  You would set the Serial # field as your drop down list. You can certainly set up the grade field as a drop down list of grades, but this doesn't link it to any records in the members table.

                  There are several ways you can automate the linkage of a new record in students to a record in members. The easiest, if this is all the data you need to enter in the students table, is to place a portal to students on your members layout. If you double click the relationship line linking these two table (occurrences), you can specify "allow creation of records via this relationship". Then you can enter data in the bottom blank row of the portal and a new related record in students is created for you with the matching value in the Serial # field intertered into this new student record for you.

                  If you want to switch from the members layout to the students layout and have any new records automatically linked to the currrent members record shown on the members layout, do it this way:

                  Use an OnRecordLoad script trigger (specified in layout setup), on the members layout to run this script:

                  Set Variable [$$IDnumber ; value: Members::Serial #]

                  Open manage | Database | Fields, find the Serial # field in Students and double click it.

                  Select the auto - enter tab and click the calculation option.

                  enter this calculation:

                  $$IDnumber.

                  Now, any new record in students will auto-enter the matching serial number of the current record on the members layout. This will fail, however, if you open the file and go to the students layout without first accessing the members layout. In that case, the $$IDnumber global variable will be empty and no matching number will be entered into the Serial # field of any new Students record you create. You might want to use File Options to specify that the file always open to your members layout to make sure that this cannot happen.