AnsweredAssumed Answered

Self Join Stopped Working

Question asked by clayhendrix on Jan 21, 2017
Latest reply on Jan 21, 2017 by JackRodges

I have a self-join relationship to enable a portal on a layout to display a list of active students. This was working and has stopped. I cannot remember enough about the cartesian join to troubleshoot the error and correct it. Any help is appreciated.


Records are Students in the table SIS_Student. There is a global field SIS_Student::@active_i which is a global field with the value of "I". Just now, I realized that it was NO longer a global field, but I recalled that it had been and my field nomenclature "@" indicated that it once was. For the purposes on this layout, I use a TO of SIS_Student called "SIS_Student 3"


There is another TO of SIS_Student called Student_List_Sidebar. SIS_Student 3 and Student_List_Sidebar are related by two fields (images below):

1.) SIS_Student 3::_id_student_pk (which is the key field) and Student_List_Sidebar::_id_student_pk (same field in each TO) with a cartesian relationship (X)


2.) Student_List_Sidebar::@inactive_i and SIS_Student 3::'c_status with a not equal relationship (slashed equal sign)

Screenshot 2017-01-21 09.29.56.png  Screenshot 2017-01-21 09.32.42.png

On a layout based upon SIS_Student 3, there is a portal based upon Student_List_Sidebar and the goal is for all active students (records with a value in the field 'c_status of any value besides "I") to be listed in the portal (see image below showing TO upon which layout is based, and layout dialog box some information redacted) Users, can click on the portal row and are taken to that record in the same layout. It worked. It stopped.

Screenshot 2017-01-21 09.34.05.png


I have changed @inactive_i to be a global field again and it currently has the value of "I". However, records for ALL students are showing in the portal and not just active students.


Also, SIS_Student 3::'c_status is a calculated field that finds the first value of the student status field in a related table in which students have multiple records regarding their enrollment status during various academic terms (see images below of calculation details). The relationship is sorted so that records in the table with enrollment records with status of "A" are listed first, therefore, if a student has an "I" for previous academic terms, they would not be excluded from the sidebar mentioned above and their status displayed using this calculated field would show "A" or active if they had any enrollment record with an "A" status. This works still for many reports and other layouts. I was skeptical as to whether using a calculated field would work to eliminate inactive students from the sidebar portal, but it worked when I set it up.


Screenshot 2017-01-21 09.43.49.png  Screenshot 2017-01-21 09.43.57.png


Thank you for reviewing my problem and any help you can provide is appreciated, greatly!