7 Replies Latest reply on May 25, 2011 2:37 PM by philmodjunk

    multiple names assigned to same record

    Nerico_1

      Title

      multiple names assigned to same record

      Post

      I have a table for a schedule of lectures.  Each records has the information for a particular lecture including title, date, time, location...  Each lecture record can have up to 3 lecturers assigned to it.  There is a separate table with the info on each lecturer.  Right now, I have three fields where I store the IDs for each of the 3 lecturers from the faculty table.  FacID1, FacID2 and FacID3.  I want to have three corresponding calculated fields that will display the name of each lecturer,  FacName1, FacName2, FacName3.

      My question is that I don't know how to set up or code the calculations to display the correct name.

        • 1. Re: multiple names assigned to same record
          philmodjunk

          What you have set up requires three different relationships between lectures and lecturers:

          LectureSchedule::FacID1 = LectureFaculty1::FacultyID
          LectureSchedule::FacID2 = LectureFaculty2::FacultyID
          LectureSchedule::FacID3 = LectureFaculty3::FacultyID

          LectureFaculty1, LectureFaculty2 and LectureFaculty3 are all table occurrences of the same data source table.

          There is a better approach, however and one that won't limit you to a maximum of three lecturers:

          LectureSchedule----<Schedule_Faculty>-----Faculty

          LectureSchedule::LectureScheduleID = Schedule_Faculty::LectureScheduleID
          Faculty::FacultyID = LectureSchedule::FacultyID

          You then place a portal to Schedule_Faculty on your lecture schedule layout.

          Put Schedule_Faculty::FacultyID plus faculty name and any othe fields from Faculty that you need to list the lecturers assigned to that LectureSchedule record. Format the Faculty ID field as a drop down list or pop up menu of Faculty IDs and names so you can use it to assign lecturers from the faculty table.

          • 2. Re: multiple names assigned to same record
            Nerico_1

            Thanks Phil.  I will try that.

            Are there any major structural advantages to the third table method other than the ability to have more than 3 lecturers?  I mean for scripting and reporting purposes? I did consider that approach but I have so many different related tables and portals for many other types of data that one more felt a bit overwhelming.  I'm pretty certain that there will never be a 4th lecturer, 2 is not so uncommon but 3 is extremely rare.  Actually, when I though about reporting, the 3 fields seemed a better option. 

            • 3. Re: multiple names assigned to same record
              philmodjunk

              The flexible number of lecturers that become possible is an additional benefit, not the main reason for doing this. Note that you need three different relationships for your three fields so the join table approach is simpler than three different relationships. (2 table occurrences with the Join table method and 3 with the separate field method.)

              Now consider if you want to see all lectures scheduled for "John Smith". With three fields, you have to search all three fields. With the portal, you need only search one.

              If you want to print out a report listing all of John Smith's scheduled lectures, you can easily do so from a layout based on the join table--in fact you can list a report for all lecturers, with scheduled lectures listed under each person's name. Can't do either of those when you have three separate fields.

              If your Relationship map is getting too busy, you might investigate using the Anchor Buoy method for bringing some order to the chaos.

              • 4. Re: multiple names assigned to same record
                Nerico_1

                That's true, I hadn't really been thinking about reporting by faculty.  Most of the reporting is on the lecture table and those have several relationships (to an objectives table, a teaching unit table, keywords table, exam questions table, etc...  So I was trying to put as much of the the lecture specific stuff on the lecture table since I can't report, for example, on both the relationship between lecture and question and between lecture and faculty on the same report.  Any ideas?

                Also,  can you double check the Anchor Buoy link?  I'm getting a 404 error.

                • 5. Re: multiple names assigned to same record
                  philmodjunk

                  Try the link now, I've fixed it. (Should have double checked what actually got pasted in to the link editor.)

                  I don't see why you can't report on lecture, question and faculty in the same report.

                  If you have these relationships:

                  Lecture::LectureID = Questions::LectureID

                  Lecture::LectureID = LectureSchedule::LectureID

                  Faculty::FacultyID = LectureSchedule::FacultyID

                  Then you can base your report on either Questions or Faculty and list either the Faculty or Questions records in a portal with LectureSchedule fields added directly to the layout.

                  • 6. Re: multiple names assigned to same record
                    Nerico_1

                    Really?  I was under the impression that portals did not work for reports.  How do you make them shrink or grow?  

                    I think maybe my FM book is not that good... Yell

                    Would appreciate any links to useful threads or tutorials on using portals in reports.

                    If that works. I think I will go with the third table approach. 

                    As usual, thanks for all your expertise! 

                    • 7. Re: multiple names assigned to same record
                      philmodjunk

                      Portals are a good thing to avoid in reports, but sometimes you can't easily avoid them.

                      Portals can indeed shrink/slide up in a report as a unit, but fields inside the portal cannot slide up. Thus, you can put a 10 row portal on your layout and have it shrink to a 5 row portal if you only have 5 records in the portal, but you can't make a two line field inside of the portal slide and shrink to only one line height if there's only one line of text in the field.

                      Thus, a small portal listing your faculty should work pretty well here, but a portal of Exam Questions might not.

                      It's possible to do away with the portal, but with the relationships that we have here, you end up creating a temporary report table and importing data into it just so you can list all the data in correctly grouped format.

                      This issue does not occur when you have these relationships:

                      Parent----<Children---<GrandChildren

                      When it's a linear one to many from table to table like this, you can base your layout on GrandChildren and then include fields from Children and Parent on the same layout--often in grand summary, sub summary, header, and/or footer layout parts.