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

    Trapping when detail lines differ by section

    ltw _

      I am somewhat new to Monarch and am trying to configure templates to capture data within a multiple section report.  I have been through the lessons and have worked on extracting several forms of data.


      The problem is with the multiple sections, as the data for each section is formatted differently, making it very difficult to define a single detail trap.  By example, the sections of the report look like this:

      [FONT="Courier New"][CODE]INCOME:

         Net Rent                                           538.90            

            Renter: Jones          05/05-05/12

         Cleaning Costs                                                  134.72

         Owner Stay                                                     

            Owner: Doe             05/21-05/27

         Cleaning Costs                                                   44.87


         05/04/08 Complementary Maintenance WO 123456                      0.00

                     Delivered ladder                  

         05/01/08 Maintenance               WO 123457                     10.00

      SUMMARY:                         YEAR-TO-DATE         CURRENT PERIOD 

         BEGINNING BALANCE                                               804.01

          Rental Income                     5146.32        1286.58            

          Cleaning Costs                    1258.36                      314.59

      OCCUPANCY:        Regular   Owner   Guest    Comp   Other   Total   Occ %

         THIS MONTH          10       0       0       0       0      10    30.0[/CODE]


      This is a partial view, but I think it illustrates the idea.  You will note that the sections are delineated by a word starting the the first column and ending with a colon.  You will also note that the layout varies quite a bit between sections.  There are also comment lines associated with the entries that may or may not appear.  Finally, in the Income section, you will notice that the "cleaning costs" line is related to the entry above it.


      So the question is one of strategy in configuring traps to capture this data.  Can someone give me some guidance in this regard?


        • Trapping when detail lines differ by section
          Grant Perkins

          ltw, welcome to the forum.


          Looking at your sample I'm trying to find a single key that links the sections together and could be considered to be the detail record to which everything else is attached - but I don't really see one. I guess it is a property reference not shown.


          Now of the sections you have shown look good to be Derail templates and that is what you need to be clear about first - what it the lowest level at which these pieces of information relate to each other.


          But even if we assumed a property reference the report sections don't really combine well.  Income records and expenses records don't relate on a one to one basis to each other not can they be grouped in an obvious  structured common format. There may be a way to group them that makes sense but it's not obvious.


          Likewise Summary and Occupancy might work together but how is not obvious.


          So it comes down to knowing what you want the result or results to look like once you have finished and then we can reverse engineer things from there. We may end up using multiple models and a several step process - but right now it's difficult to be sure. I didn't check which version of Monarch you have - I hope it's recent and the Pro version. Life will be sweeter then!


          What can you tell us about the results you need to achieve?



            • Trapping when detail lines differ by section
              ltw _


              Thanks for the response.  Sorry I was slow in getting back - I've been swamped.


              Anyway, for brevity, I only included a portion of the entire report.  Above what I included in my post is a header that includes name and rental unit information, for example:

              June 4, 2008                                           



              Rental Owner LLC                    UNIT 202 (ST202)

              John Doe                            SEASIDE TOWNHOMES               

              123 Main Street          

              Anytown CA 12345



                                                     MONTH ENDING May 31, 2008[/CODE]

              I haven't had too much difficulty trapping and extracting this as an append, so I didn't include it.  Everything else is obviously tied to this.  I should note, however, that dates in this format are kind of interesting, but that is the least of my challenges.


              I essentially agree with you conclusions about combining the different types of records, the relationships between income, expense, etc., and grouping in general.  This is, of course, why I went to the forum to see if anyone more knowledgeable about Monarch has any ideas. 


              The real goal here is to detect and extract the information contained in the several types of entries in the report and then filter, reconfigure, and provide reports in different formats.  My plan has always been to tie this to a MS Access database for these purposes using the OLE interface, so that I can provide a fairly seamless interface for the user.


              You mentioned multiple models and several steps.  Is this to imply that you might attack this problem by creating a model for each section (eg. Income, Expense, Summary, etc.) and then process the document several times, one time per model, and then combine everything together?  If, in your experience, this a common way to approach this and the best way to treat a problem like this, then I can certainly implement as such.  It would appear that this will be possible through the OLE interface.


              Incidently, I have Monarch V9 Pro, which I just got, so I should have all of the latest and best for this purpose.


              What are your thoughts on all of this?


                • Trapping when detail lines differ by section
                  Grant Perkins



                  Multiple models, one for each of Income, Expenses and the Summary/Occupancy combined would be one approach, though in theory you don't need Summary since the numbers, re-presented as required, can be re-reported with their own summary by selection.


                  I assume that using an Access database allows you to build an entire data set from periodic reports that are not available as a set - thought if they were Monarch could probably process them in one go.


                  If the objective is Access (or more broadly 'some external data collection') then I would guess a 3 table Access database would be OK so 3 models would make life easy. If you want a 1 table Access database  than a one pass solution would be worth seeking out to keep things simpler though a multi step solution is still perfectly viable, all actioned via a batch script or VB code for example.


                  An alternative might be to trap all lines with financial values with all data presented on a line before the value in a single field (including the 'extra' lines that appear sometimes) and then 'repurpose' those fields for greater consistency using calculated fields in the Monarch table. Conditional processing based on the section of the report could be employed.


                  It's unfortunate though that the 'Owner Stay' line does not have a value, although that may not be an insurmountable problem.


                  If working with the value lines as detail the Summary and Occupancy figures don't seem to fit in at all at the detail line level - not unexpected.


                  If you look at 3 models each should be quite simple it seems.


                  A 1 model solution would involve a few things that could be considered 'advanced' so possibly best understood by working with a sample 'ideas' model. I could put something together based on your posted samples if that would be of serious interest.








              • Trapping when detail lines differ by section
                ltw _


                Actually, the Summary section is needed because it has the year-to-date numbers which are not contained elsewhere. 


                As it turns out, the app that I am developing will incorporate information from three other reports, so the multiple pass concept isn't a big deal because I am doing anyway with the other reports.  I am really just using the Monarch engine to extract the data into a structure of tables in MS Access.  I will be using VB and the OLE interface to make all of this happen.  I think I will proceed in that direction.


                Thanks for your assistance...

                  • Trapping when detail lines differ by section
                    Grant Perkins


                    Actually, the Summary section is needed because it has the year-to-date numbers which are not contained elsewhere. 




                    It surely sounds like the multi-pass route it the way to go for populating the Database tables.


                    The problem I have with the Summary sections (in terms of a single pass solution certainly) is that they don't relate only and specifically to the detail lines individually so they information does not really belong to anything else in a useful sense once the detail has been broken out of the report.


                    In the other hand a separate extraction model for the summary information is entirely feasible if you can ensure suitable periodicity - in the case of YTD I guess that simply means checking the end date of the period and replacing any previous entries if the latest export supersedes them.


                    Of course once you have the extraction running and your database building from an annual start point you would have a source for calculating the YTD value anyway I would think, based on any sub-selection criteria you may wish to use. In fact Monarch, accessing you database tables, could be very good for that sort of analysis!


                    Good luck with the project.