1 2 Previous Next 23 Replies Latest reply on Feb 19, 2014 9:19 AM by philmodjunk

    Display a 2 dimensional array of values from a narrow table

    GregLeibel

      Title

      Display a 2 dimensional array of values from a narrow table

      Post

           I have been trying unsuccessfully to develop a report for classroom attendance like this:
            
           Class Subject
           Class Timeslot (eg. Mondays@9:30)
            
                          Date1 Date2 Date3 Date4 etc.
           Name1    Att1.1 Att1.2 Att1.3 Att1.4 etc.
           Name2    Att2.1 Att2.2 Att2.3 Att2.4 etc.
           Name3    etc...
            
           The relationships are:
           Person---<Attendance>---ClassDate>---ClassTimeslot>---ClassSubject
            
           So a person belongs to a class by attending all of the dates, and the Attendance table registers the person and their attendance records for those class dates.
            
           Could you please help me? I can't seem to work out how to make the report trigger the next Attendance field horizontally across the page with the Date as the heading. Printing the Dates using the Name as a subheading is easy, but displays vertically, rather than horizontally, so it wastes a lot of the page.
            
           The Attendance table, it is very narrow, with only the ClassDate, Name and Attendance being critical to this report.
            
           Just to further complicate things, while a person usually attends all dates, it is possible for a person to only attend one date, so the other columns could be blank. Alternatively, I could have a script populate them with blanks. I'm easy either way.
            
           Any help would be greatly appreciated!
            
           Many thanks,
            
           Greg Leibel

        • 1. Re: Display a 2 dimensional array of values from a narrow table
          philmodjunk

               You can use a row of one row portals that are set, via relationship or filter calculation, to display a single record from attendance for the specified date and classTimeSlot to show this attendance data.

               You may find the links shared by DavidAnders in this similar thread helpful: Can FM Advanced Pro 13 produce Excel-like pivot-tables?

          • 2. Re: Display a 2 dimensional array of values from a narrow table
            GregLeibel

                 Hi PhilModJunk,

                 Thanks very much for your prompt answer. It is very much appreciated.

                 I will give the portals thing a go, and post back my findings. 

                 I'm quite new to FMP and this seems like a clunky way about this task, but maybe I'm just not used to it. 

                 I had a look at DavidAnders' recommended Google search queries, but couldn't find anything that sounded like this problem. Is there a specific link you were trying to direct me to?

                 Cheers,

                 Greg

            • 3. Re: Display a 2 dimensional array of values from a narrow table
              philmodjunk

                   What you are describing is called a "cross tab" report. There are several ways to set up such and the one row portals is one such method.

              • 4. Re: Display a 2 dimensional array of values from a narrow table
                GregLeibel

                     Hi PhilModJunk,

                     Success! The problem for me was that I was not sorting the rows successfully, and I ended up creating an extra table, called Enrolments, which means that each person is only listed once. Enrolments simply ties the class to the person.

                     Each row now displays the person_ID from Enrolments, then the one-line portal trick that you mentioned where enrolments::person_id = attendance::person_id

                     Thanks for pointing me on the right path.

                     Cheers,

                     Greg

                • 5. Re: Display a 2 dimensional array of values from a narrow table
                  GregLeibel

                       With this method, am I correct in thinking that the layout is a fixed width, rather than be based on the actual number of columns (in this case Dates) in the table?

                       Also, I guess that I have to have a person have the right number of dates in the attendance table, and only populate the relevant ones, for example, if someone just attends a makeup class on one date, if I gave it just the one date, it would appear in the first column. Am I mistaken? 

                       I guess it's possible to add another field, such as ColNum, or ClassNum to determine which column it goes in, then display one line portals filtering on that field. In that way I could also ensure that the dates and attendance were always in the right order, even when new records were added to the middle (for example, if a new class was added).

                  • 6. Re: Display a 2 dimensional array of values from a narrow table
                    philmodjunk
                         

                              With this method, am I correct in thinking that the layout is a fixed width, rather than be based on the actual number of columns (in this case Dates) in the table?

                         That is correct. This will be the case with any method that you use with FileMaker to organize this data into columns. I could set up repeating fields or a large number of individual fields in one record to show this data, but I'll still have a fixed number of columns on the layout.

                         

                              Also, I guess that I have to have a person have the right number of dates in the attendance table, and only populate the relevant ones, for example, if someone just attends a makeup class on one date, if I gave it just the one date, it would appear in the first column. Am I mistaken?

                         That does not necessarily need to be the case. Each portal can use either a match field or a filter expression to select for only a specific date.

                    • 7. Re: Display a 2 dimensional array of values from a narrow table
                      GregLeibel

                           Thanks so much for your help. Very much appreciated!

                           Cheers,

                           Greg

                      • 8. Re: Display a 2 dimensional array of values from a narrow table
                        GregLeibel

                             I am so close! I have the portal rows working correctly, but as you rightly pointed out, I can use a filter expression and only show the first of one row that matches that row and column. 

                             In my case, each portal cell has the value:

                             (Attendance::ClassDate_ID = $$Column1)
                             and 
                             (Person Enrolment::ID = Attendance::Person_ID)
                              
                             My (hopefully!) last problem is that I can't make $$Column1 take the value of the ClassDate::ID in the first column header portal, which is sorted by ClassDate::Date. The headers come out correctly in date order, even though the ClassDate::IDs are not sequential, but the cells take on the subset sorted by ClassDate::ID. I can't for the life of me find out why.
                              
                             I have a script triggered by OnLayoutEnter:
                             Sort Records by Field [Ascending; ClassDate::Date]
                             Sort Records by Field [Ascending; Enrolment::Class_ID] (which is used to sort and give nice subheadings)
                             Set Variable [$$Column1; Value:GetNthRecord ( ClassDate::ID ; 1 )]
                             etc
                              
                             Can anyone help me get the nth value as per the portal headers? I've been working on it for a long time, and I feel that it's a really simple solution.
                              
                             Many thanks,
                              
                             Greg
                        • 9. Re: Display a 2 dimensional array of values from a narrow table
                          philmodjunk

                               Your script looks correct, but I doubt that your filtered portal is updating to filter on the value newly assigned to this variable. Try doing a Refresh Window [Flush Cached Join Results] script step after the set variable [$$Column1... step and see if you now see portals updating as they should, but after what could be a long delay waiting for all the portals to update on your layout.

                               If that works, you can replace the variable with a global field and include it in your relationship as a match field. Then you will no longer need to use Refresh window [flush... to update the portal.

                          • 10. Re: Display a 2 dimensional array of values from a narrow table
                            GregLeibel

                                 Thanks (as always!) for your assistance, PhilModJunk. I would be utterly frustrated without it, and I really appreciate it.

                                 I like your idea of the Refresh Window [flush... but alas, it didn't work.

                                 Just for fun (in a weird sort of way), I also tried the global fields as you suggested, but they give the same answers as the variables, which is kind of refreshing to my little brain, as I didn't see why they should be different!

                                 My OnLayoutEnter script now includes:

                                 Set Field [Temp::Column1; GethNthRecord ( ClassDate::ID ; 1)]

                                 Set Field [Temp::Column1; GethNthRecord ( ClassDate::ID ; 1)]

                                 etc.

                                 When I include these variables in the column headings, they are the same as the $$Column1... etc variables, sorted by ID, not by date.

                                 Any other ideas? I'm really stymied!

                            • 11. Re: Display a 2 dimensional array of values from a narrow table
                              philmodjunk

                                   Which suggests that you should be pulling over a date instead of an ID number with your set field step.

                              • 12. Re: Display a 2 dimensional array of values from a narrow table
                                GregLeibel

                                     Hi PhilModJunk. Thanks again...

                                     I thought I'd try your suggestion of sorting by date, so as a test I created a (many row) portal instead of a cell, with Attendance::ClassDate_ID and ClassDate::ID. These have a relationship, being 

                                     Attendance::ClassDate_ID >------ ClassDate::ID

                                     I have filtered where the Person_ID is correct to only display the rows relating to that person.

                                     Strangely, the portal looks like:

                                     Attendance::ClassDate_ID / ClassDate::ID

                                     9 / 9
                                     10 / 9
                                     11 / 9
                                     12 / 9
                                     13 / 9
                                     ... etc ...

                                     I would have expected these to be the same, given that they have the relationship. Instead, all of the dates appear the same, so I can't order them.

                                     I've disabled my OnLayoutEnter sorting, so that shouldn't be affecting it (although it shouldn't have anyway!)

                                     AAAAARGH!

                                     Sorry. That's better.

                                     I had a look at the Google searches that you linked to, and I have already seen them before posting the initial query, unless there is something in particular that I've missed.

                                     Am I outlasting my welcome?

                                • 13. Re: Display a 2 dimensional array of values from a narrow table
                                  philmodjunk

                                       I didn't post any google searches.

                                       Sorry but I'm home sick with the flu and can't wrap my brain around this at the moment. I will point out, however that:
                                       Attendance::ClassDate_ID / ClassDate::ID

                                       You have two different table occurrences specified. Only one might be the same as your portal's reference to a table occurrence and that probably explains why you are getting 9 in every portal row.

                                       But what I suggested was to set up a portal filter that filters by date instead of by date ID.

                                  • 14. Re: Display a 2 dimensional array of values from a narrow table
                                    GregLeibel

                                         Hi PhilModJunk,

                                         thanks for your help. I managed to do as you suggested and it works! My problem was that I was choosing the portal to read from Attendance, as it was what I wanted to display, which meant that I couldn't sort it. I changed that to read from ClassDate, and show related records from Attendance, and it's good!

                                         Thanks so much for your help! I have really appreciated it!

                                         Cheers,

                                         Greg

                                         (PS. The Google searches I was referring to were the only comment on the article you linked to on January 14.)

                                    1 2 Previous Next