7 Replies Latest reply: May 15, 2014 10:05 AM by Data Kruncher RSS

    Template development help

    boliphant _

      I have been using Monarch for sometime but I have a report that I  just can't figure out a template for.  I don't think it is really that complicated but I just can't figure it out. 

       

      How do I post either a sample of the report or something that shows the general formatting of the report?  I tried creating an example but when I previewed my post the formatting was gone.

        • Template development help
          Data Kruncher

          Hello and welcome to the forum boliphant.

           

          Follow [URL="http://www.monarchforums.com/showthread.php?t=2290"]these instructions[/URL] for posting sample reports here. The most important part is starting your sample with the tag keyword (without the space and ending with (again, without the space).

            • Template development help
              boliphant _

              Following is an excerpt from the report that I am trying to pull data out of:

               

              Route/Acc/Occ         Service Location        Billing Account                 Meter Number    Amount Due  

              -


              -


              -


              -


              -


               

              001-00250-01  TOMS, MICHAEL A                 Cust # 000000001                61529764              457.77

                            303  PROSPECT ST                TOMS, MICHAEL A                                             

                            ANYWHER XX 12345                PROSPECT                                                                               

              ANYWHERE XX 12345                                           

              001-00410-02  SMITH, ANN B.                   Cust # 000000002                61529834              158.26

                            407  HARFORD ST                 SMITH, ANN B                                                

                            ANYWHER XX 12345                HARFORD                                                                               

              ANYWHERE XX 12345                                           

              001-00440-02  LANCASTER, FRANCIS M            Cust # 000000003                61529646              179.79

                            401  HARFORD ST                 LANCASTER, FRANCIS M                                        

                            ANYWHER XX 12345                HARFORD                                                                               

              ANYWHERE XX 12345                                           

              001-00503-00  JONES, KAREN                    Cust # 000000004                00100503              167.64

                            111  SCROGGINS ST               JONES, KAREN                                                                               

              SCROGGINS                                                                               

              ANYWHERE XX 12345                                           

              Route 001 count...........           4                                                              963.46

              002-00250-03  HILL, ELAINE & LANCE            Cust # 000000005                60679761              984.81

                            812  WASHINGTON AVE             HILL, ELAINE & LANCE                                        

                            ANYWHER XX 12345                WASHINGTON                                                                               

              ANYWHERE XX 12345                                           

              002-00345-02  DAVIS, ANN                      Cust # 000000006                61520201              394.16

                            503  WASHINGTON AVE.            DAVIS, ANN B                                                

                            ANYWHER XX 12345                WASHINGTON                                                                               

              PO BOX 3                                                                               

              ANYWHERE XX 12345-6789                                      

              002-00590-01  PENSMITH, CHARLES               Cust # 000000007                00200590              179.76

                            18 W HAWTHORNE DRIVE            MASONIC TEMPLE                                              

                            MASONIC TEMPLE  CANS X 1        HAWTHORNE                                                   

                            ANYWHER XX 12345                PO BOX 952                                                                               

              ANYWHERE XX 12345-                                      

              /CODE

               

              What I am interested in doing is creating a table with records containing the the the Route/Acc/Occ #, service location info and amt due that I can export to Excel but cant' figure out how to do it.

                • Template development help
                  Data Kruncher

                  Begin by highlighting a single row with the Route/Acc/Occ number. I defined the trap with only a single N in position 1.

                   

                  Now paint the RouteAccOcc field. ServiceLocation is 24 wide, and on the Advanced tab, set to End Field On End of Left Justification.

                   

                  Paint the Meter number if desired, and the AmountDue. OK the template definition.

                   

                  In the Table window, you can create three calculated fields if you want to split up the Route, Acc, and Occ numbers. The formulas for Route, Acc, and Occ respectively are:

                  [SIZE=2]LSplit(,3,"-",1)[/SIZE][/code]

                  [SIZE=2]LSplit(,3,"-",2)[/SIZE][/code]

                  [SIZE=2]LSplit(,3,"-",3)[/SIZE][/code]

                   

                  Finally, if you want to split up the name and address data into separate fields, use the Address Block feature with the Service field.

                   

                  You might want to review the Learning Guide for the Advanced settings for field definitions, as well as the section for Address Blocks.

                   

                  HTH,

                  Kruncher

                    • Template development help
                      boliphant _

                      This only gives me the service location data on the first line of each record and what I want is a record with all of the service location info from all the lines with service location data in them.

                       

                      I am looking for a table like this

                       

                       

                      001-00250-01 | Serv Loc Info Ln1 | Serv Loc Info Ln2 | Serv Loc Info Ln 3 | Serv Loc Info Ln 4 | Amt

                       

                      The problems are that the number of lines of Service Location Info varies from record to record and the postal trap doesn't work because not all service location blocks contain city/state/zip data

                        • Template development help
                          Data Kruncher

                          I'm afraid that my description wasn't sufficiently clear for you. The Address Block feature isn't the same as the Postal Trap.

                           

                          The Postal Trap produces  in the trap line, and you're correct, this is not what you want.

                           

                          The Address Block feature is available in the Table window, and it relies on a field that has already been captured (or a calculated field) that appears in the Table.

                           

                          To access the Address Block wizard, click the envelope icon in the Table window toolbar.

                            • Template development help
                              boliphant _

                              You are correct that I confused the postal code trap with the address block feature but now thanks to you i understand the difference.

                               

                              Your solution captures all the data - now all I have to figure out is how to parse the address block data into its assorted components. 

                               

                              Thank you.

                                • Template development help
                                  Data Kruncher

                                  Working with addresses can be a bit of a challenge. Monarch's Address Block feature is very, very good at deciphering address components. But because you often find that incomplete addresses exist (for many reasons), Monarch isn't always perfect - often, but not always. For that reason it's always good practice to review the extracted address data manually, and to use the optional error code.

                                   

                                  If Monarch thinks that it could not extract the address as you wished, it will produce a non-zero error code. These codes are documented in the online help, and help you to narrow down the cause of the problems.

                                   

                                  Thanks for the update, and good luck with your project.