the last of a record

  • jj4188

    I have a table that stores history for different types of records for many customers.
    Let's say the table is called {History}, and the field that tells me what kind of history is {History.Type}
    In this field, there are four different types:  A, B, C, and D.  I have a field {History.Date} that tells me when each record is entered.

    I want to create a report that tells me how long it has been since there has been a B for all customers.
    I have another field in date format {Main.TodaysDate} that I will use to finish my calculation.  I just need to know how to use only the last B and not all B's

    Can anybody tell me how I would do something like this?

    I assume I need to use a formula. I know how to get the # of days since another date.  I just need to know how to get the last B in {History.Type} for each customer.

    Thanks JJ

    • Jim Menard
      Jim Menard

      I assume you are doing this in a footer. You can try using the max aggregation function (see\) or, if that doesn't work for date values, you can do it manually. Here's how: use the report startup script to set a "max_b" value to nil, then in the detail section add a script that updates max_b if appropriate.

        # Uses text comparison for date fields, but that should work properly
        max_b = '{History.Date}' if '{History.Type}' == 'B' && '{History.Date}' > max_b

      Then you can use max_b in your footer formula.