2 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Calculated Sequence Number

    Carol R

      I need to develop a calculated sequence number field.  I have a listing of account numbers.  Each time the account number changes the sequence number needs to start over at 1.  Is this possible in a Monarch model?  I’m using Monarch Pro 8.0.


      Account number     Sequence number

      12345                      1

      12345                      2

      12345                      3

      67890                      1

      67890                      2

      45678                      1

        • Calculated Sequence Number

          I've done it before, but my approach required multiple models.


          The first model takes your input and summarizes by account number.  There is a calculated field for the line number, and the summary lists the minimum line number.


          The second model takes your input and does an external lookup to the output of the first model based on the account number and returns the minimum line number.  The calculated field for sequence number is the current line number minus the minimum line number + 1. 


          This of course assumes that your report is sorted by account number.  If it is not, then you'll need three models, the first one runs before the two models and creates a sorted excel spreadsheet that is used as input for the first and second models described.


          This is a quick overview off the top of my head.  Let me know if you have specific questions and I can help you work it out.

            • Calculated Sequence Number
              Olly Bond

              Hello Carol,


              As Joey suggests, it's likely to need multiple passes. There is one glimmer of hope for a slightly easier fix, if you can get the report to behave nicely with Account number on a Page Header template, then you might be able to use the Page() and Line() functions to generate codes like you need.


              But in 99% of cases, this is going to need a multi-pass approach.


              Best wishes,