2 Replies Latest reply on Mar 8, 2011 8:43 AM by philmodjunk

    selected details from list

    BruceBono

      Title

      selected details from list

      Post

      I have a school database going with tables for Students and Teachers. I have another table to store Notes.

      I wonder if there is a way to show the "list" of the people that have a Note associated with them  AND

      I would like this listing of the people to "expand" to show Notes for only the selected person.

      In other words, I have a list of names and when I click on one of them, underneath this persons name, I would like to see the various Notes concerning them. All other Notes remain "hidden". 

      Is this possible?

      Thanks!

        • 1. Re: selected details from list
          james.gould

          You could add a calculation field to the Students and Teachers:

          Student::noteCheck (Calc, Number) = not ( IsEmpty ( Note::id ) )

          If there is a related record from the notes table (and thus this person has at least one note), the Student::noteCheck field would return 1 and a 0 if there isn't a related record. You could then perform a find for noteCheck = 1

          It is very important you choose a field from the note table (e.g. Note::id) that will always contain some data for each note - otherwise the field may comeback as empty even if a related note is found.

          With regards to 'expanding' your selected record... I would use a list view with a single line for each person displaying name/id etc... and maybe the heading or first line of their the related note - anyway just some basic details. I would then put a button (or turn all the fields into a button, since you probably don't want data entry on a search results layout anyway, that takes you to another layout that displays some further details with a portal for containing all their notes. Finally a back (Go to Layout[Note Search]) button on the note layout would be required to take them back to the search results.

          Finally if you wanted to see students/teachers with a specified number of notes you could use Student::noteCheck = Count ( Note::id ) and this would return the number of related records from the note table. You could then use  noteCheck ≠ 0 to find records with notes and noteCheck ≥ 5 for records with, you guessed it, 5 or more notes.

          • 2. Re: selected details from list
            philmodjunk

            I'd use the list view layout where you click to see a notes layout for that person like James Gould suggests, but...

            Say you have these relationships:

            Students::StudentID = StudentNotes::StudentID

            Teachers::TeacherID = TeacherNotes::TeacherID

            On a layout based on Students, you could enter find mode and place an * in the StudentNotes::StudentID field, then perform the find. The records found will be all students with at least one record in StudentNotes.

            Note the similarity between the two relationships. It's possible to merge student and teacher tables so that you have 2 tables instead of 4:

            People::PeopleID = Notes::PeopleID

            An additional field in People, can then identify a given record as that of a "student" or a "teacher".