4 Replies Latest reply: May 15, 2014 10:16 AM by Grant Perkins RSS

    Model with Multiple Record Types

    bminer _

      Hello,

      I have some data that I'm trying to parse in Monarch, and looking for a little guidance in the model.

       

      In the data, the first two characters signify a record number, followed by the Account Number, then other data pertaining to the record type (i.e. Record 01-General, 02-Balances, 03-Company Info, etc.).  The issue is that the only guaranteed record is 01.  All the rest can either appear, or not appear, depending on the existence of the data.

       

      Any thoughts on the best way to model this?

       

      01123456DOE,JOHN                             

      02123456000000123.45+000000099.99-           

      03123456ACME INCORPORATED               123456

      03123456OTHER COMPANY              98798794564

      01234567DOE,JANE                             

      03234567COMPANY 3                     11111111

      03234567COMPANY 4                   9999999999

      01345678DOE,HANK                             

      /CODE

        • Model with Multiple Record Types
          Olly Bond

          Hello bminer,

           

          This is best handled by taking the 01 record as the Detail, the others as Footer templates, and using the Cleared By function - this needs v10 or above.

           

          But you seem to have more than one 03 record for some 01 lines. Is there safe upper limit - will you ever have more than 40 lines starting "03" for a single record starting "01"? If so, then there is a trick using the multiple column region we could use to get the data out in one model.

           

          If not, you might need to use two models to get at the data, one using 01 as the detail and the other using 03 as the detail.

           

          Best wishes,

           

          Olly

            • Model with Multiple Record Types
              bminer _

              Unfortunately, I'm on v9, so I'm not sure if I'm out of luck wth the "Cleared by" function.

               

              There shouldn't be more than 40 "03" records, usually only a handful (less than 5).

              However, I did forget to put in my sample data there is an order to the 03 records, so the data would look more like the below data (with the fields in "03" being, Record Type, Account Number, Order #, Other Data...).

               

              Am I best to use multiple models?

              01123456DOE,JOHN                             

              02123456000000123.45+000000099.99-           

              031234561ACME INCORPORATED               123456

              031234562OTHER COMPANY              98798794564

              01234567DOE,JANE                             

              032345671COMPANY 3                     11111111

              032345672COMPANY 4                   9999999999

              01345678DOE,HANK

              /CODE

                • Model with Multiple Record Types
                  Olly Bond

                  Hello,

                   

                  I'd suggest you're best off upgrading to a newer version of Monarch

                   

                  But yes, you can attack this using multiple models, which I think is probaby going to be easier than trying to use the "guru" trap (trapping on 01 as Detail, and as Append, using advanced field properties to pick out the 02 and 03 records which are sometimes there...).

                   

                  If you use the 01 line as page header template as well as a detail template, then you can use Page() as a helpful field to join records together between models.

                   

                  Best wishes,

                   

                  Olly

                    • Model with Multiple Record Types
                      Grant Perkins

                      Hello bminer,

                       

                      Have you considered extracting each record (trapped  on the 01 line for which I am assuming there is only one per record) as  a block of text and then splitting it up using the TextLine function?

                       

                      In  your example it looks like the 03 lines (other line types may have  similar?) also have a sequence number. You could combine the 03 and 1,  2, etc, to give an ordered reference for each record.

                       

                      If you then think in terms of a fixed table array for the output where the cells would have the data from, say,

                       

                      01

                      02

                      031

                      032

                      033

                       

                      etc.

                       

                      you  can create calculated fields for each line type ar required. Not all  fields would be populated for every record but that would not matter -  the field 'column' for all possible fields would exist in the array.

                       

                      It looks like each line might generate 2 fields for the array.

                       

                      01 gives Family Name; Given Name

                       

                      02  - 2 amount values, one a percentage?

                       

                      031 - A company Name and a reference number.

                      032 - A company Name and a reference number.

                       

                      So  long as you don't end up with hundreds of columns that should be a  usable table. If so the main work would come in slicing and dicing the  data lines. Based on the samples that looke quite straightforward using  the SPLIT functions.

                       

                      Does that idea help at all?

                       

                       

                      Grant Perkins