Report Writing Issue
FMP 10, served from FileMaker Server 10 (1 copy of FMPA)
All Macs with a majority of 10.5.x and a few 10.6.x
Once upon a time a heavy MS Access user; now FMP only from version 2 on.
This is a large, shared student information database with dozens of tables. A user has requested a report that will cleanly export to Excel that contains a student ID number (the primary key) and that student's 2 most recent scholarship entries. A desired output would be ID 1234 Fall 2009 $5, Spring 2010 $5 in one line. The report may contain 1000 of these lines each time it is run.
The scholarship table has the following relevant fields: ID, Semester, Year, Amount and Modification Date.
I have already set the system so that it "knows" the current year and semester, so retrieving that information is easy. I am hitting the wall on getting the second most recent information.
My idea so far is to create a layout that is sorted by ID number then by Modification date. Then create a script that will look at that table, get the first line for an ID number, return and get the second line, and then move to the next ID number. This data would be placed on another "final" report layout in table view that could export to Excel. My problem is I'm not sure where to even start with this.
I tried using a portal, but that just gives me everything when I export it (this could easily be 8 lines for a senior student).
I also tried creating a calculation field in the table that would contain both sets of data, but I am unsure how to get it to go to one more record and stop.
Any suggestions would be appreciated.