Need Display Solution for Side-By-Side Column Groups
Hi everyone. I've posted a couple of perplexing FM requirements in the past and you've guided me to solutions. I'm hoping for the same kind of outcome here. (Sorry for the lengthy post. I figured more info was better than not enough.)
BACKGROUND: I've built a solution that comprises multiple related tables and even more layouts to help my wife with the extraordinary administrative and paperwork demands being heaped upon her and her fellow teachers. The goal is to help her speed through these kinds of tasks so she has more time to actually teach her students.
REQUIREMENT: My wife has a metal Scheduling Board on a wall in her classroom where she places student names using magnets in two groups of columns, the AM group and the PM group. Students who attend in the morning are grouped in the AM columns and those attending in the afternoon are grouped in the PM columns. The Scheduling Board also displays the days each student attends (Monday and Wednesday, abbreviated M-W, for example), as well as the month and day of the month each student comes due for an annual evaluation. As you'd expect, this takes a lot of time to update and maintain this board, and it's time she needs to spend on other, more important things.
She wants me to design a FM layout that mimics the physical Scheduling Board and that builds itself in FM with the click of the mouse. Additionally, this layout must give her the ability to sort each AM/PM group by clicking just above each of the group's columns (I've seen this done in List View in other solutions). Lastly, some of the text needs to be conditionally formatted with various colorations. The weekly time savings of 10-30 minutes derived from such a feature seems small but in the context of a school year the time savings would amount to days!
The example below shows a couple of AM student records stacked in List View similar to the way they appear on her board. In reality there are more than a dozen students that presently show in the AM column group, one record stacked on top of the other with no blank rows in between.
APPROACH: So far, the primary table is a Student Records table. This table has other tables related to it to perform specialized tasks, etc. Based on what I've seen others do, I figured it would be best to set this up in a List View that looks at the Student Records table. I created calc fields that look at the session each student attends (AM or PM) and place the appropriate names, attendance days and evaluation date in those fields. Then, I placed those calc fields in the body of a List View layout with the AM fields strung together on the left and the PM students on the right.
PROBLEM: The problem I am encountering and haven't been able to solve is what to do with empty rows that show up in the List View for each record that does not meet the particular calc criterion.
The example below shows AM students with gaps in between visible records where the PM student would normally appear but are prevented from doing so because they fail to meet the conditional requirement of the calc field. Interestingly, the PM column group to the right of the page (not shown here) places the PM students immediately adjacent to where the gaps appeared in the AM column group.
The AM group and the PM group both populate down their respective column groups, as expected, but they are full of holes where particular student records fail to meet the AM/PM calc requirement.
SOLUTION IDEAS ATTEMPTED OR CONSIDERED: I can create a found set of records in the Student Records table for the AM students and that totally fixes the problem in the AM column group but, of course, the PM column group goes completely blank. Alternatively, I tried sorting all of the student records by session and the result was all the AM students stacked neatly on the left side of the page with no gaps in-between records but nothing but blank rows in the PM fields on the right side of the page. That is, they're blank until the last AM student and then the PM students fill in from that point downward on the right side of the page. What to do?
Furthermore, I thought about just stitching together the appropriate AM and PM students and their related data into two separate text lists using a looping script structure and then throwing them into side-by-side, Plain-Jane text fields. That works beautifully except I am unable to sort the lists or surgically apply conditional formatting, as my wife needs.
Additionally, I even thought about creating two new tables (AM and PM) and relating them to the Student Records parent table which would then automatically populate the two child tables by way of scripting with AM kids going to one table and PM kids going to another, and then pulling field data from these to populate the side-by-side AM/PM column groups back in the parent table List View. My thinking tells me this might work but it also seems overly elaborate. There's got to be a simpler way...
Finally, my understanding is that filtering a portal may solve this problem; i.e. setting up two portals side-by-side and filtering one for AM students and the other for PM students. I've never actually done this (I'm building the solution using FM 10 which doesn't have that feature but my wife has FM 11 at school which I have read does provide for portal filtering). I understand that were I to use portals in a Student Records layout, then the source data would have to come from a related table or tables, or perhaps a self-join table. Correct me if I'm wrong here.
CONCLUSION: Have any of you ever encountered this kind of situation, and if so, how did you solve it?
By the way, I consider myself to be a FileMaker novice working daily to achieve "intermediate" status. I'm using FM Pro Advanced 10.0v1 on an Intel-based MacBook Pro. My wife's using an Intel-based iMac at school.