4 Replies Latest reply on Nov 24, 2009 4:59 PM by mmcconl

    Report Writing Issue



      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.


      The issue:


      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. 

        • 1. Re: Report Writing Issue
             What if a student has only one or zero entries in the Scholarships table? Should they be included in this report?
          • 2. Re: Report Writing Issue

            Sorry, left out a step.


            The user will first perform a find of students who have valid data (i.e. find all students who have a record in Fall 2009).


            Given that, zero entries will not be an issue. Single entries, however will exist for all first-year students and should be included in the report. 

            • 3. Re: Report Writing Issue
                 Well, there are several ways that come to mind, none of them too easy (or fast). The main difficulty here is the "one-line per student" requirement. This means that (1) you must export from the Students table, and (2) you must add some calculation fields to this table.

              Anyway, if you sort the relationship so that the Scholarships records are in descending chronological order (I assume you know how to do this), you can use the GetNthRecord() function to retrieve data from the first two related records, e.g.:

              GetNthRecord ( Scholarships::Amount ; 2 )

              will return the amount of the student's second most recent scholarship (this calculation must be unstored).

              • 4. Re: Report Writing Issue

                You are correct, it was not a fast solution, but it was in fact a solution that worked!


                I didn't even consider that calculation.


                For those playing at home, I had to create 8 non-stored calculations to make this work (4 fields with first and second occurrences).


                After getting it all together, I even located some previously unknown data entry errors (I know, not really that shocking in a multi-user environment).


                Thank you for the excellent suggestion!