5 Replies Latest reply: May 15, 2014 9:54 AM by Grant Perkins RSS

    v8.01 Data Repeat from occasional non-detail Template

    aziz _

      Hello.  I am having a bit of a problem setting up the templates for a report I'm trying to process through Monarch.  I am self taught on the software, so I'm sure that I'm doing many things inefficiently, if not outright incorrectly.  Also, I noticed several other topics in this forum that look to be similar to my issue, but I'm having a tough time connecting them to a solution for my situation.

       

      Basically, I have a report that has a set of information formatted fairly statically throughout, with an occasional extra line-item on a few records.  When trapped via an append or a footer template, this extra line item is being repeated for each detail until there is a new value for the line item.  Example:

       

      [font="courier"]Model Serial/      Customer Information/     Prog  - - - - - - - - M e t e r / D a t e - - - - - - - - Billing  Coverage Contrcted 

              Equip Id     Meter Description         Type  Service     Actual   Previous   Prev Inv   Inv Thru  Cycle   From/To    Usage                                                                               

      • LD122 45501996     XXXXXX XXXXXX HEALTHCARE  7FQ                                                       12/20/07 09/20/07         0 

              AN895        COMMUNITY INCORPORATED                                                                       12/20/07           

                           X XXXX DRIVE                                                                               

      RICHMOND HTS.,         OH                                                                               

      (XXX) XXX-XXXX     CL4887  Key Op:    ROSEMARY                                                                  

           ____________    BLACK & WHITE TOTALS              75707      80758      75672      80758      80758                             

                           DAYS                           06/21/07   09/19/07   06/19/07   09/19/07   10/20/07                                                                               

      S LC335 50800371                                                                               

      • LC335 50800371     XXXXXXXXX XXXXXXXX CENTER 1FQ                                                       12/21/07 12/21/07     15000 

              BA157        XXXX XXXXXXX AVE                                                                             03/21/08           

                           LORAIN                 OH  Key Op:    LINDA X-XXX-XXXX X 26                                                     

                           (XXX) XXX-XXXX     CLB896  Spec Inst: 8-5                                                                       

           ____________    COLOR TOTALS                      32301      30426      26927      30426      30426                             

                           DAYS                           11/06/07   09/18/07   06/22/07   09/18/07   10/21/07                             

           ____________    BLACK & WHITE TOTALS             273676     252878     212679     252878     267878                             

                           DAYS                           11/06/07   09/18/07   06/22/07   09/18/07   10/21/07                                                                               

      S SYST2 WELCOMEHOME                                                                               

      • LD255 75500472     XXXXXXX XXXXXXX HOME      7FQ                                                       12/21/07 12/21/07         0 

              AS944        XXX S  XXXX ST                                                                               03/21/08           

                           OBERLIN                OH                                                                               

      (XXX) XXX-XXXX     CLC378                                                                               

      ____________    BLACK & WHITE TOTALS                  0          0          0        160        160                             

                           DAYS                                                            09/21/07   09/21/07                             

        /font[/QUOTE]Everything is relatively straightforward.  I have the customer name and serial information as a detail, the address info as an append (had to be two different templates because there is occasionally a second customer name line, as in the first record in the example), BLACK AND WHITE TOTALS as a footer...which seems fine, because every record has a B&W entry...but as you can see in the second record in the example, there is sometimes a COLOR TOTALS line item.  I need to capture that information, but obviously only want that information to be associated with the customer record it should be attached to.  As it is, setting COLOR TOTALS to an append or as a footer template repeats the data to the following records.

       

      I have essentially no experience working with the summary functions of Monarch, but have seen a few seemingly related issues solved via that route on this board.

       

      I hope all of that made sense.

       

      Any suggestions on how I should proceed?

       

      Thank you in advance,

       

      Ehren

        • v8.01 Data Repeat from occasional non-detail Template
          joey

          Welcome Ehren.

           

          This is quite a challenge to tackle. I did not have a chance to try out this solution, but I think it will work.

           

          I suggest setting your append template to trap on  the line ____________.  Paint  5 text fields that can hold either a date or a money value.  For each field, set the advanced properties to End Field on Blank Value.  This way, you will either have two lines or four lines extracted for each detail record.

           

          What you have is 5 raw fields that have either two lines worth of data or four lines of data.

           

          You will need to set up calculated fields (20 of them) to split this data up using the TextLine function.  TextLine for the fourth line of any field will return an empty string if there are two fields.  You'll need to check this for the B&W records to see if they are the first two, or the last two.

           

          For example:

           

          B&W Service Meter = if(isempty(TextLine(Field1,4)),TextLine(Field1,1),TextLine(Field1,3))

          B&W Service Date = if(isempty(TextLine(Field1,4)),TextLine(Field1,2),TextLine(Field1,4))

          Color Service Meter = TextLine(Field1,3)

          Color Service Date = TextLine(Field1,4)

           

          You may want to convert these text values into numbers val() or dates ctod() to work with them later.  Once you have all of your fields, you can hide the 5 raw fields.

           

          Quite a bit of work, but there may be an easier way.

          • v8.01 Data Repeat from occasional non-detail Template
            aziz _

            Interesting approach.  I've not worked with any calculated fields in Monarch to this point, but I can probably figure it out.  I'll give that a run through and see what I get.

             

            Chances are I'll be back with more questions.   smile.gif[/img]

             

            Thanks for the assist, Joey.

            • v8.01 Data Repeat from occasional non-detail Template
              Data Kruncher

              Joey's been typing while I attempted to model this beast.

               

              And while his solution may work (haven't tried it out yet), I believe that a solution similiar to that proposed by Grant in this recent post on  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=001840;p=1#000003"]Repeat Footer Data[/url] will work well.

               

              I built most of the model starting with a 5 line detail template, using the asterisk in column 1 as the trap.

               

              To that I added a 6 line B&W append template, begining with the asterisk row, also using the asterisk to trap.

               

              Then I added a 6 line Color append template, also trapping an asterisk in column 1 of line 1.

               

              For the total row numbers, use "color" or "black & white" on the field definition advanced tab as the preceding string on the current line.

               

              For the Days row date values, use "color" or "black & white" as the string that appears anywhere in the previous line.

               

              Just as Joey says for his proposed solution, this is a bit of work too.

               

              I'm certain that this solution will work for you.

               

              HTH,

              Kruncher

               

              [size="1"][ December 19, 2007, 04:52 PM: Message edited by: Data Kruncher ][/size]

              • v8.01 Data Repeat from occasional non-detail Template
                aziz _

                Does setting the preceding string value stop the data from repeating if the next detail does not have an accompanying footer entry?  I.e. will telling the Color field to look for the "color totals" string make it reset after the single instance?

                 

                Unfortunately, I've been pulled into another project for the balance of the day, and won't be able to work through your suggestions until tomorrow morning.  I really appreciate both of your help, and I'm honestly a little suprised by the quick and informed responces here.  Many not-exactly-mainstream apps like Monarch don't have the community to give this kind of support.

                 

                Thanks again, and I'll update shortly.

                • v8.01 Data Repeat from occasional non-detail Template
                  Grant Perkins

                  Hi Ehren,

                   

                  I'm going to suggest something slightly different for my interpretation of the report. No ides if it is correct though. Also can you let us know what the output requirement is?

                   

                  OK, the 2 line meter records are the DETAIL. Each meter is the lowest level of analysis you can undertake. You need to capture the type (B&W or color) as a field and then the meter readings and dates in fields named for the column headings.

                   

                  The information for the customer is an append and covers as a minimum the first 2 lines of the customer details - so a 2 line sample.

                  [font="courier"] Model Serial/      Customer Information/     Prog  - - - - - - - - M e t e r / D a t e - - - - - - - - Billing  Coverage Contrcted 

                          Equip Id     Meter Description         Type  Service     Actual   Previous   Prev Inv   Inv Thru  Cycle   From/To    Usage                                                                               

                  XXXXXX XXXXXX HEALTHCARE  7FQ                                                       12/20/07 09/20/07         0 

                                       COMMUNITY INCORPORATED                                                                       12/20/07            /font[/quote]So you get the Name and address info, either as Name and Address TEXT Block or separate Name field and then an address field extracted from the second line and set up as a multi line field ending with "None of the Above" to make it stop when it hits another template.

                   

                  You can also get the billing cycle info on the extreme right, the Prog Type code and, if you need them, the "Key Op" field and "Spec Inst" fields (where they exist) using the Preceding String concept of the Advanced Field Properties.

                   

                  The last line of the 'address block' seems to be a phone number and something else. Trap this into a separate template so as not to confuse the Address Block processing later.

                   

                  You address blocks may be complicated since the information looks incomplete to me but I don't claim to be an expert on US postal formats. It may require a little tweaking along the lines previously suggested to get a good degree of accuracy. I would be surprised if you can get 100%. Name and address data is notoriously poor in many (most?) databases. If you get 100% you would be a candidate for the Guinness Book of Records.

                   

                  Finally it looks like (based on the sample), you need a 3 line append template for the Model/Serial  lines (can there be more than 3 lines?). Trap on the "*" in line 2 which seems always to exist. The grab the fields you need form all three lines.

                   

                  So... other than a complete solution for the Address information, I think that will do the job -  based on the 3 sample records.

                   

                  You may, of course, find other records which are different and give rise to further challenges! But we have to start somewhere.

                   

                  If you get stuck PM me with an email address to which I can send my C&P file version of the sample you posted and the model that works with it.

                   

                  HTH.

                   

                   

                  Grant