3 Replies Latest reply: Jun 28, 2016 1:04 PM by Olly Bond RSS

    Monarch Pro 9.0: Selecting last detail in append

    Rick O

      I have !Monarch Pro 9.0 and have a template with a detail type, but just want to return the last detail line. I don’t really have any special fields to indicate that it is the last detail for the group. Basically just want one detail per append (the last one). Is this at all possible with this version of Monarch?

        • Re: Monarch Pro 9.0: Selecting last detail in append
          Olly Bond

          Hello Rick,


          Yes, but you might need two models. There's a link to the Invisible Data webinar in my status, I think, which might help you with this stuff. Three options, easiest first.


          In the existing model, define a calculated field called Record with expression Recno(). This just gives you a 1,2,3 counter in the table. Define a Sort on the Append field Ascending then Record descending. Define a Filter with expression 1 and in the Advanced tab select the Append field(s) as the key and tick the boxes to show unique rows and also only the first record as sorted record for duplicates.


          If you have really ugly data, you might have to trap every line with a blank trap as a detail template in a new model,. then add a Record field, then sort on record descending, then export the table as fixed width text to give yourself an upside-down version of your initial report, then work from that in a second model. I've only had to do that once in several thousand models.


          A third option is to define a Record field, export the table as an Access table called Data.mdb. Also make a summary on the Append field(s) with Max(Record) as the Measure. Export the summary as an Access table called Keys.mdb. Open Keys.mdb as a table in a new project and make a lookup to fetch in the Detail fields from Data.mdb matching on the Record field.


          Best wishes,