4 Replies Latest reply: May 15, 2014 9:52 AM by hagell _ RSS

    multi line with varying second multi line CRAZY

    hagell _

      Hello.

      I am using Monarch V7.2. I am attempting to gather data from a template with account information and the report also contains random comments on the the account which shows up indented and with its own formating.

       

      For example the original report is set up more or less like this(hyphens used to show blank spaces):

       

      ACCOUNT--JOB--NAME        

      BALANCE--CURRENT30--60

       

      ACCOUNT--JOB--NAME:MASTER TOTALS        

      BALANCE--CURRENT30--60

       

       

      Grabbing this data is not a problem, then there are the comments, which arent on all the accounts.

      Something like this:

       

      ACCOUNT1--005--NAME        

      BALANCE--CURRENT30--60

       

      ACCOUNT1--000--NAME:MASTER TOTALS        

      BALANCE--CURRENT30--60

       

      ACCOUNT2--002--NAME        

      BALANCE--CURRENT30--60

       

      ACCOUNT2--027--NAME        

      BALANCE--CURRENT30--60

       

      ACCOUNT2--000--NAME:MASTER TOTALS        

      BALANCE--CURRENT30--60

       

      ACCOUNT3--000--NAME        

      BALANCE--CURRENT30--60

       

      -


      0001--5/24/06-JOE CALLED STORE--TR-5/24/06

      -


      0002-12/19/06-PAID IN FULL, JOE--TR-12/19/06

      -


      TOLD MGR IT WAS IN  

       

      ACCOUNT3--192--NAME        

      BALANCE--CURRENT30--60

       

      ACCOUNT3--000--NAME:MASTER TOTALS        

      BALANCE--CURRENT30--60

       

      If I make the account info the detail I get all account, but garbage as comments

       

      If I make the comment template the detail I get all the comments, but ONLY accounts with comments

       

      I then attempted making both templates appends and floating traps, this returned all the data, but a ton of garbage or duplicate info as well.

       

      What we are needing to generate is all accounts, all jobs, and when there are comments, they will be picked up and tied to the correct account.

       

      Any help would be great! Thank you.

        • multi line with varying second multi line CRAZY
          Grant Perkins

          Hi Hagell and welcome to the forum.

           

          Looks like the account info has to be the detail.

           

          Do you need the comments as individual records or simply a block of 'memo' text?

           

          If memo text, and assuming you always have a third line, probably empty, for each account record, it may be possible to 'paint' a field into that spare line that covers the width of the report allocated to the comments and then set the field's Advanced Properties to START after the last field in the template and END using one of the options offered that makes it work with your report format. 'Blank fields' possibly but as it only takes a second or two to set and option and see the results it may be worth experimenting with several of them to see what you get.

           

          The Comments will be extracted as a text string and will most likely lose their structure as a result. This may not be a problem for you, but if it is we may need to look for a solution to that matter separately.

           

          If the layout of the full report contains something that precludes the use of these ideas there are other approaches that can be taken - but lets consider that later if we need to.

           

          HTH.

           

           

          Grant

          • multi line with varying second multi line CRAZY
            hagell _

            Hi Grant. Thank you for having this forum and for your help, however the fields within the indented comment sections would need to be seperate fields.

             

            I did play with your idea though and may be able to use it for a different project.

             

            The end result should have columns for all the account info and the comments.

            For example the end result's columns should be something like this:

            ACCT JOB NAME BALANCE CURRENT 30  60  90 COMM# COMMDATE COMMENT INITIAL FOLLOWUP

             

            And the data (hyphens are column breaks):

            ACCOUNT1—5—NAME—BALANCE—CURRENT—30—60—90(BLANK)(BLANK)(BLANK)(BLANK)(BLANK)

            ACCOUNT2—2—NAME—BALANCE—CURRENT—30—60—90(BLANK)(BLANK)(BLANK)(BLANK)(BLANK)

            ACCOUNT2—-27—NAME—BALANCE—CURRENT—30—60—90(BLANK)(BLANK)(BLANK)(BLANK)--(BLANK)

            ACCOUNT3—0—NAME—BALANCE—CURRENT—30—60—90—-15/24/06JOE CALLED BRANCHTR5/24/06

            -


            2-12/19/06PAID IN FULL, JOE---TR—12/19/06

            -


            TOLD MGR IT WAS IN-----

            ACCOUNT3192NAMEBALANCECURRENT306090(BLANK)-- (BLANK)-- (BLANK)-- (BLANK)-- (BLANK)

             

            Thank you for your help in this. I would just take what I have and do some excel manipulation, however the program should be able to do that work for me, Im just not that savvy yet. As mentioned, depending on which template is the detail, I can either get all accounts w/ garbage comments or only the accounts with comments.

            • multi line with varying second multi line CRAZY
              Grant Perkins

              Originally posted by hagell:

              ....  the fields within the indented comment sections would need to be seperate fields.

               

              <snip>

               

              For example the end result's columns should be something like this:

              [font="courier"]ACCT JOB NAME BALANCE CURRENT 30  60  90 COMM# COMMDATE COMMENT INITIAL FOLLOWUP /font[/quote][/b][/quote]Which means it will need to look like this:

               

              [font="courier"]ACCOUNT1—5—NAME—BALANCE—CURRENT—30—60—90(BLANK)(BLANK)(BLANK)(BLANK)(BLANK)

              ACCOUNT2—2—NAME—BALANCE—CURRENT—30—60—90(BLANK)(BLANK)(BLANK)(BLANK)(BLANK)

              ACCOUNT2—-27—NAME—BALANCE—CURRENT—30—60—90(BLANK)(BLANK)(BLANK)(BLANK)--(BLANK)

              ACCOUNT3—0—NAME—BALANCE—CURRENT—30—60—90—-15/24/06JOE CALLED BRANCHTR5/24/06

              ACCOUNT3—0—NAME—BALANCE—CURRENT—30—60—902-12/19/06PAID IN FULL, JOE-----TR—

              ACCOUNT3—0—NAME—BALANCE—CURRENT—30—60—90--12/19/06TOLD MGR IT WAS IN-----

              ACCOUNT3192NAMEBALANCECURRENT306090(BLANK)-- (BLANK)-- (BLANK)-- (BLANK)-- (BLANK)[/font][/quote]OK the text is wrong here I suspect (or the format is inconsistent) but the main point is that the account infomation would need to appear once for every line to make any sense of having the comments as the detail - which is what you seem to need.

               

              You mention Excel manipulation - is your primary objective here basically an export to Excel? Do you need the parts of the comments split up into separate fields for analysis or can you just take the comments as long text fields?

               

              One of the problems I can see is that there could be a very variable number of comments and each could be of variable length in terms of its text content. But ultimately your lowest level of reporting need seems to be the comments. Therefore  they would be the detail template and you will need a way to define a detail template even for account transactions with no comments.  Then append the account transaction details.

               

              If that proves difficult - which it well might - there is another approach which involves multi-stage processing.

               

              Firstly create a model to trap and extract all the lines with comments as detail. Append the Account and Job number etc.. Export the resulting table to a file.

               

              Next trap and extract all the Account and job numbers with NO COMMENTS as detail. Add some 'dummy ' fields for the Comments which will not exist. The idea I suggested previously could be used for that - simply folter the results for records without any data in the comments field (or maybe fields or as much of that part of the data that you need to be able to filter effectively.)

               

              Make sure the tables for both extracts are the same. Export this to append to the first extract.

               

              Sort on Account number and job number.

               

              That's it, in outline at least. There is the possibility that something in the report will create some other problems to deal with.

               

              On the other hand if all you want to do is reformat the existing report a little you might be able to do that with the MSRP Utility program (V7 and usable for all versions) or the Monarch Utility (V8 - which your profile suggests you may also have available.)

               

              Maybe even a basic edit to search for and replace a specific string combination in the report would give what you want ready to import into Excel?

               

              You may also need to filter out the Master Totals at some point.

               

              HTH.

               

               

              Grant

              • multi line with varying second multi line CRAZY
                hagell _

                The appending worked great! I knew it had to be something simple. Im an over-analyzer.

                Now I will play with surpressing the duplicate info that pops up.

                Thank you so much.