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

    Variable Multi line append problem

    jsachey _

      Ok, I've been working on this particular report for days now, and i am not getting very far with it.  I've been using Monarch off-and-on for a year now, and i can usually figure something out, but this is throwing me for a loop.

       

      Here is a sample of the report:

       

      00001615  1615-001     06/24/2008  00/00 HSE                               1615 Net 30                    M  07/01/2007 07/01/2009  

      Recur Inv My Company01.                                                                                12 12/31/2030                                                                               

      Credit Limit =              Customer Balance + Order Total =           1,296                                               

      Service-No       Service-Description             Unit       Quantity          Unit Price        Ext. Price Tx Com  Sales Account    

                       My Company - MIDLOTHIAN                                                                               

      -


                                                                                       

      510503R          Soft Annual Support             EA             1.0000        491.000000           491.00  Y   N 41050-01010-0020   

      230404R          POS Phone Support - Annual      EA             1.0000        718.000000           718.00  Y   N 41100-01010-0000   

      510700R          CC Software Maint - Annual      EA             1.0000         70.000000            70.00  Y   N 41050-01010-0020   

      510503D1         Discount: Annual Soft Support   EA             1.0000         30.000000-           30.00- Y     41050-01010-0020   

      230404D1         Discount: Annual POS Phn Supt   EA             1.0000         50.000000-           50.00- Y   N 41100-01010-0000                                                                               

      Sub Total                1,199.00                                                                               

      Tax Total                   97.43                                                                               

      Invoice Total            1,296.43                                                                               

      00010083          1008 12/26/2006  01/25 HSE                              10083 Net 30                    M  03/01/2007 03/01/2009  

      Recur Inv Awesome Company2                                                                                12 12/31/2030                                                                               

      Credit Limit =              Customer Balance + Order Total =           4,727                                               

      Service-No       Service-Description             Unit       Quantity          Unit Price        Ext. Price Tx Com  Sales Account    

                       LJS #2, TB #13260,                                                                               

      -


                                                                                       

      510503R          SOFT  Annual Support            EA             2.0000        491.000000           982.00  Y   N 41050-01010-0020   

                       -


                                                                                       

      TB #50013911, TB #2491,                                                                               

      TB #4391, TB #21258, TB #4454                                                                               

      (ABOVE UNITS PAY $42                                                                               

      ANNUAL MAINTENANCE SEPARATELY)                                                                               

      -


                                                                                       

      510503R          Soft  Annual Support            EA             5.0000        449.000000         2,245.00  Y   N 41050-01010-0020   

                       -


                                                                                       

      ALL SITES                                                                               

      -


                                                                                       

      510603R          Soft    License Annual Support  EA             1.0000      1,500.000000         1,500.00  Y   N 41050-01010-0020                                                                               

      Sub Total                4,727.00                                                                               

      Tax Total                    0.00                                                                               

      Invoice Total            4,727.00                        

      /code

       

      I'm setting the detail template(single line) to the records having a service-no.  I would like to append the text above those lines (ie, My Company - Midlothian) to EACH detail record below it. 

       

      The 2nd example, makes things more difficult because for each detail line, the append lines above it can be variable in height.

       

      Ideally, i would like to have a single record show the entire text of the 'comment' field on one line (ie, TB #50013911, TB #2491, TB #4391, TB #21258, TB #4454 (ABOVE UNITS PAY $42 ANNUAL MAINTENANCE SEPARATELY), next to the 2nd Soft Annual Support detail record .

       

       

      Can anyone help me out with this?

       

      If you need more information, please reply.

       

      thanks in advance.

       

      Joe

        • Variable Multi line append problem
          Olly Bond

          Hello Joe,

           

          (Just to check - what version of Monarch are you using? v9 allows you to use NOT traps, and v10 allows you to turn off appends templates based on instances of others. Both of these techniques might be useful here. And in case we need a two pass process - do you have Monarch Pro?)

           

          I've just checked your profile and see you have Monarch v10.5. You should be fine using two different append templates and then perhaps a calculated field to deliver the data into one field.

           

          One template traps on "Service-No" with a two line selection and selects the "My Company : MIDLOTHIAN". Call this template and field "Version 1". Define the field to have advanced properties to end on "none of the above". Don't panic if at this stage the report window looks a mess... Select a new template, with a two line selection, trapping on "----


          " and selecting the first line of "TB #50013911, TB #2491,       " - again, call this "Version 2" and set the advanced field properties accordingly.

           

          Now you've got your data, in v9 you'd need a calculated field but in v10 (which I've only got at home so can't replicate here) I think you can experiment with the options to turn off appends on the instance of another append template, which should give you what you need.

           

          Best wishes,

           

          Olly

            • Variable Multi line append problem
              Data Kruncher

              Welcome to the forum Joe.

               

              Olly's got the right idea of course, but unless I'm missing something, I think that you can get this done with a single append template.

               

              Include as many lines as you need for the append template. I used 4 lines.

               

              With a series of B traps in the first columns and a single NOT - in the first description position, paint the field wide enough for your description and on the Advanced tab, set the End Field On to None of the above. I simply named the field A.

               

              Now in the Table create a calculated field to clean up the extraneous dashes:

               

              Replace(A,"--","")[/code]

               

              Edit: Oddly, the report window doesn't show the LJS #2 data shaded as being detected as an append item, but it's in the Table just fine. ???

              HTH,

                • Variable Multi line append problem
                  jsachey _

                  Ok, I've tried both of your techniques (thank you for the quick replies).  I think Data Kruncher has the right technique though.  I didn't think that i should make a 4-5 line single append, even though some records were only 1 line high (max height being 5 lines).  It seems that making a single 4 line append, trapping B's to the left and right of the text seems to work (and trapping a NOT - on the first column of the text data, followed by a N trap on the 2nd column of text).

                   

                  Unless i missed something in the monarch documentation, can someone help me understand how this append works?  Meaning, if for each instance of my record, i have between 1 and 5 lines of append-worthy data, how does Monarch know that can be variable (between 1 and 5).  Is it because of the 'End Line = none of the above" option in the advanced tab?

                   

                  EDIT:  I did this before, but i tried it again... I created my page header and detail template.  I then created a single line append template on the line beginning with "TB #50013911..." by using a (NOT "-") followed by a nonblank char trap.  I set the adv properties to "end line=none of the above".  When i look in the table, all i see are hyphens "----


                  " with no text data.

                   

                  I deleted that append template and then recreated it by selecting 4 lines for the append (line beginning with "TB #50013911..." and ending with "ANNUAL MAINT..."  I again used the blank trap prior to and after the actual text lines.  For the text line, the 1st column was a NOT "-" trap followed by a non-blank trap.  when i look at the table data now, for that record i see 5 lines worth of info (all on one line).  It starts with "TB #5001..." but ends on the hyphens "----


                  ".  If you look at the report, you will see that the hyphens are essentially on line 5, but i only selected 4 lines for the append.

                   

                  I am very confused... and i hope i was able to explain well enough..if no, please let me know.  What's more important to me is WHY this is working, not THAT it is working

                   

                  thanks,

                   

                  Joe

                    • Variable Multi line append problem
                      Data Kruncher

                      It's all wonderfully confusing at times, isn't it?

                       

                      I sense that you're about to follow that dangerous path that many of us also encountered wherein we decide to no longer just accept what Monarch gives us, but rather to (attempt to) understand what it's doing behind the scenes, so as to absolutely control and command Monarch.

                       

                      Be warned that it's a never ending game, but damn it's fun!

                       

                      The kicker that controls your 5 line field result from a 4 line template is the End Field On None of the above. That'll keep that field going until Monarch detects the next valid template.

                        • Variable Multi line append problem
                          jsachey _

                          Kruncher...

                           

                          what i don't understand, is that if i create a single line append and set the field adv properties to end on none of the above, my table will only return the hyphens as data.

                           

                          I think i may have an understanding of it, but i need to see if I'm right.  After reviewing my report, i found instances of the data i wanted to capture being 10 lines high (instead of the 4 as in my sample report).  I found that using 4 lines high didnt return some of the data that had 5-10 lines of data i needed.  I created an append template that was 14 lines high (just to be safe), used the same trapping structure, and it looks like it worked perfectly (data that was 1 line high was trapped with no problem as well as data that was 10 lines high). 

                           

                          I guess what I'm trying to say is that in my case, to trap variable line height data, i need to know what the MAX height is, and use that number as the number of lines i use to create the append template.   Am I correct in that assumption?

                           

                          EDIT:  And thank you guys so much for the responses.  I am quite taken aback by the help, great customer service is hard to find these days.  Keep up the great work.

                            • Variable Multi line append problem
                              Data Kruncher

                              Yes, you're correct; it does help to know the maximum number of rows that might be encountered.

                                • Variable Multi line append problem
                                  jsachey _

                                  aha!

                                   

                                  The kicker that controls your 5 line field result from a 4 line template is the End Field On None of the above. That'll keep that field going until Monarch detects the next valid template[/QUOTE]

                                   

                                  the next valid template being any template(detail, page header, footer) or the next same template (this particular append template)?

                                    • Variable Multi line append problem
                                      Data Kruncher

                                      Any template: detail, another append, what have you.

                                      • Variable Multi line append problem
                                        Grant Perkins

                                        aha!

                                         

                                         

                                         

                                        the next valid template being any template(detail, page header, footer) or the next same template (this particular append template)?[/quote]

                                         

                                        Probably NOT a Page Header template  - Page Headers have special 'Invisible' properties.

                                         

                                        As a rule it is safest to assume that the next valid template is another example of the current template type.

                                         

                                        Take some care, with variable multi line records, about selecting your number of sample lines. I'm not here taliking about the actual data content  - in most case, floating traps being an exception, the information in the sample is merely an aid to getting traps and fields in the right column across the screen.

                                         

                                        But the number of lines can be important, more especially for detail records For example of you see that your report has 4 lines for each detail record and set a 4 line sample all will be fine UNLESS some records for some reason only have 3 lines and no space line before the next record (which would still give Monarch 4 lines to play with ...)

                                         

                                        In that instance the 4 line definition will cover line  1 of the next record. Monarch will not 'see' the next record, no matter how many lines it has, because the template says each record should have 4 lines ... or , to slightly more loosely interpret the 'rules', AT LEAST 4 lines, though it may be allowed to have more.

                                         

                                        So some care is required. Define for the minimum number of lines and then deal with the records that exceed the minimum, even if they are in the majority, and all will be well.

                                         

                                        It is not that uncommon for sample reports to avoid providing a full picture of the 'real world' - the real world then providing a nasty reminder of how real it can be the first time you run your perfected process in anger!

                                         

                                        Just thought I would mention that as you are clearly in idea and information absorbing mode right now.

                                         

                                        HTH.

                                         

                                         

                                        Grant