4 Replies Latest reply on Sep 29, 2016 4:51 PM by keywords

    Challenges with portals and circular references

    heatherwilliams

      My apologies for asking a question similar to ones that others have posted on the forum, but I can't seem to apply the solutions that I have read about to make our FileMaker Pro 12 database work the way that we want it to.

       

      Background: I have been asked to re-build a database that was originally set up to track student participation and completion in various research programs. Some programs allow students to apply multiple times and they get a new record with each application. We now need to more closely track the participation and success of faculty members by pulling data from the student application records. I know that portals and reports can do most of this work, but I am having issues getting the data for the faculty records to correctly pull information from the various program tables (red, purple, and green tables in the attached screen shot).

       

      Problem: When I try to pull similar information for faculty advisors related to Soph Jr Research, it does not pull all of the records for the faculty member, even if I try to filter by a field that exists in both tables and has the same data. It is strangely also pulling data from student records who may have also applied multiple times and with a different faculty members each time.

       

      What works: I set up a dummy database and re-upload the data so that serial numbers would be assigned to the records and I created a new layout with portals to pull the relevant data for a particular faculty member by program. The portal for the Pres Scholar records seems to be working fine.

       

      What doesn't work: I've tried creating various join tables (see bottom left of attached relationship graph), but maybe I'm not setting them up correctly. I've tried linking multiple occurrences of a table (e.g. faculty data) to the individual programs. I tested a theory that the issue was because there are multiple records in Soph Jr Research for the same student with different faculty advisors, so I created a duplicate student record in Pres Scholar and gave it a different faculty advisor. The faculty portal for Soph Jr Research still pulled records for other faculty members while the Pres Scholar one did not.

       

      I imagine that I am missing something simple, but I've spent a lot of time trying to figure this out. Please help!

        • 1. Re: Challenges with portals and circular references
          philmodjunk

          I suggest explaining the two different ID fields in the student table:

          Student_DartID

          __studentIDpk

           

          How are each defined? How does each get a value?

           

          Looking at the "connector' to the student table that matches to Student_DartID, I too would suspect that you have multiple records in the student table with the same value in this ID field. You can perform a find on that field with ! specified and then sort by that field to group records to see if this is indeed the case.

           

          Problem: When I try to pull similar information for faculty advisors related to Soph Jr Research, it does not pull all of the records for the faculty member, even if I try to filter by a field that exists in both tables and has the same data. It is strangely also pulling data from student records who may have also applied multiple times and with a different faculty members each time.

          I suggest describing what you mean by "pull". You set up a portal to what table occurrence on a layout based on which table occurrence? Or maybe you did something completely different?

           

          From a layout based on either Student or Soph Jr. Research, a portal to Faculty Data 2 will show you data linked to Soph Jr. Research, but a layout based on student will include all faculty data records linked to any Soph Jr. Research records that are linked in turn to the current student record. A portal to Faculty Data 2 placed on a Soph Jr. Research layout, on the other hand, will show only faculty data records that link to the current Soph Jr. Research record. Form the relationship connectors shown, that could also be more than one record as the Faculty_dartId is also not set up to be constrained to a unique value. So you might search this table for duplicates as well.

           

          If you hover the mouse over the upper left corner of the three table occurrences that start with "Faculty" on the right hand side of your graph, do they all show the same data source table? I would assume so, but let's be sure.

          • 2. Re: Challenges with portals and circular references
            heatherwilliams

            Hi philmodjunk,

             

            Thank you for your quick response! Student_DartID is an institution assigned identification number unique to an individual. The __studentIDpk is a serial number assigned by FM upon entry of the a new record. In essence, each student should only have one record in the Student table with both a Student_DartID (assigned the by the institution) and a __studentIDpk (assigned by FM). The program tables in our database were originally set up to retrieve related data (e.g., firstname, middle name, last name, class year) from the Student Data table if the Student_DartID matched.

             

            We already had a main layout for each of the student records that includes demographic information as well as details regarding participation in our programs (e.g. applied yes/no, project title, academic year, advisor name, status), so I created a separate table and layout for the faculty data records that is similar to the student one described above. Again, the idea was that each advisor would have an advisor_dartID (insitution assigned) that would be the linking factor from the various program tables and also an __advisorIDpk (FM assigned).

             

            The portals on the Faculty Data layout are supposed to retrieve data from the program tables when the advisor_dartID matches. For the Soph Jr tab it is supposed to retrieve related records from the Soph Jr Research table. For the Pres Scholar tab it retrieves records from the Pres Scholar table. There are some more screen shots attached for reference.

             

            The three table occurrences for "Faculty" all have a data source of "Faculty Data."

             

            I'm looking forward to your response.

            Student data layout.pngFaculty data layout.png

            • 3. Re: Challenges with portals and circular references
              philmodjunk

              To get a clear view of the Soph Jr Research data, you should set up a layout based on Faculty Data 2, not Faculty Data with a portal to the Soph Jr Research table occurrence. (Table Occurrences are the "boxes" found in your relationship graph.)

               

              Because your Faculty Layout is based on Faculty Data, the "path" to Soph Jr Research is an indirect path from Faculty Data to PreScholar to Student Data to Soph Jr Research.

               

              And because you are linking data by an identifier that is imported/entered from an outside source with no validation in place to ensure unique values, it is distinctly possible that you have duplicate values in the Faculty and Student data tables.

              • 4. Re: Challenges with portals and circular references
                keywords

                Rather less analytical than the replies already posted by philmodjunk, but here are a few observations for what they are worth:

                 

                1.     I note that you are directing all your relationships through the institution-assigned DartID. This is bad technique. The whole purpose of creating internal FM-assigned ID fields is (1) you can ensure that they are unique (serial or UUID) and meaningless for any user purpose, and (2) you use them to drive all your relationships. You should regard the DartID as just another piece of data and not use it to drive relationships or somewhere along the line it will trip you up—and perhaps has. I suggest, on a copy of your file, you alter all your relationships to use the FM IDs; that alone may resolve the problems you are having.

                 

                2.     As already suggested, I suspect you have duplicate data in one or more DartID fields. Search for that to check.

                 

                3.     A join table's purpose is to break down many-to-many relationships (students can belong to more than one faculty, faculties have many students) into one-to-many (this student belongs to X faculty—one join record; this student also belongs to Y faculty—another join record; etc). To function, the join table needs only to have the studentID and the facultyID, but it can also have other data specifically relevant to that single enrolment; it should be given a descriptive name so that you can see at a glance what it does, e.g: student_JOIN_faculty.

                 

                4.     It appears to me that you need to pay closer attention to your data model and perhaps revise it somewhat. Three things stand out:

                (1) you have TOs called Faculty Data, Faculty Data 2 and Faculty Data 3—this immediately sets off an alarm bell. What is the difference between these? You might remember now why you made them but will you still remember in a month? six months? Or perhaps you've already forgotten. Always try to make TO names meaningful.

                (2) you have similar multiplication in field naming, e.g. advisor_confirm1, …2, …3; student_email, student_email_new, student_email_old. Wherever you run across this kind of need it suggests the data model needs attention.

                (3) it appears that the TOs PreScholar and Soph Jr Research may contain some duplicate field sets; there is also some apparent duplication across other tables. Is it just the names that are the same/similar? Or are these fields really duplicating data?

                The whole project may become clearer to you and easier to manage if you address these issues.

                 

                Hope that's of some help.