3 Replies Latest reply on Jan 12, 2009 1:27 PM by ninja

    getting latest data when there are multiple entries

    samxli

      Title

      getting latest data when there are multiple entries

      Post

      I have a table that displays the graduation dates of students from different institutions.

       

      I am creating a script that checks whether a student has graduated within a certain date range, but some students might have graduated from multiple institutions. For example, one student graduated from 3 different institutions and the dates are 9/15/2008, 11/14/2007, and 11/29/2005. When I run my script to get the graduation date, it only returns the 11/14/2007 date. How do I make it so that it returns either the latest date or the earliest date?

       

      Note: I tried using the Max() function but it did not return a proper date value.

        • 1. Re: getting latest data when there are multiple entries
          ninja
            

          Howdy samxli,

          Thanks for the post.

           

          I use Max(datefield) and Min(datefield) to do what it seems you're after.  I think you're on the right track...what does it mean that it didn't return a proper date value?

           

          Did you confirm that your "Graduation Date" field is set to date format?

          Did you confirm that your "Most Recent Grad Date" calculation field is set to be in date format?

          Did you confirm that the calculation must result in a 'Date'?

           

          For the little info you gave, it looks to me like a field format problem.  One slip...even in a script step...and the date format could be turned into something else...walk it through every step.

           

          If not a format issue...please give more detail and/or more info.

          • 2. Re: getting latest data when there are multiple entries
            samxli
              

            Hi Ninja,

             

            Thanks for the reply.

             

            My problem is that each student has multiple entries in this table. So my script would find the specific student in this table, which produces a result of 3 entries for this student since he has graduated from 3 different institutions. The graduation date field is in Date format.When using the Max() function on the date field it would give me a value such as "732994". This value varies when I am clicking on the different entries, but theoretically shouldn't the value not change since it should be finding the max of all 3 of these entries?

            • 3. Re: getting latest data when there are multiple entries
              ninja
                

              Samxli,

               

              If a field in each record is a Max of dates within that record, it would/should/could vary in each record...why not? 

               

              One approach might be to NOT have three records in this table for one student, but three related records from another table.  Portaled in, and sorted by grad date, you would simply have to look at Portal row [First] and Portal row [last].

               

              Otherwise, perhaps you could do your find for the student, sort the records in the found set by grad date, go to record/request/page[Last] and copy the grad date to a global field that you could then access from every record.

               

              Again, I'm guessing at what you are trying to do...so I'm throwing out ideas.