1 Reply Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    model help for a newbie

    knightson _

      I am currently using monarch pro v 7.0 and am having difficuly creating a model to work with a report, I would like to extract the data to put it in access.  One problem that I'm having is that some data is above the detail and other data is below the detail.  But the biggest problem is that sometimes one or two "append" lines are present and other times they are not.  When I create a model I end up getting some lines duplicated.  I will try to lay this out so it makes more sense.

       

      This is an example of the report I need to pull data from (i'm not sure it will look correct?)  You will notice that if there is no narrative loaded then there is no line; its not left blank there just is no line.  This is also true for the MAINTENANCE EVENT # line.  If there is no event # then the line is not there.

      [font="courier"] 

      1: AIR ABORT                       AA 

            EQUIP      S O R T I E             CAUSE  CAUSE                           S T A R T    S T O P

             ID      DATE NUMBER MOD           CODE   NARRATIVE                      DATE  TIME    DATE  TIME

            A1234   05194  101   01            MT9    UNSCHEDULED MAINTENANCE        05194 0115   05194 0205 

      THIS FLIGHTS NARRATIVE:  AIR ABORT DUE TO SOMETHING 

                DEVIATION CAUSED BY MAINTENANCE EVENT # C123456789

       

      2: CANCELLATION                    CX 

            EQUIP      S O R T I E             CAUSE  CAUSE                           S T A R T    S T O P

             ID      DATE NUMBER MOD           CODE   NARRATIVE                      DATE  TIME    DATE  TIME

            A1234   05196  101   01            WX1    WEATHER                        00000 0000   05195 2200 

      THIS FLIGHTS NARRATIVE:  LINE CX'D

       

      3: EARLY TAKEOFF                   ET 

            EQUIP      S O R T I E             CAUSE  CAUSE                           S T A R T    S T O P

             ID      DATE NUMBER MOD           CODE   NARRATIVE                      DATE  TIME    DATE  TIME

            A1234   05200  121   01            OP1    AIRCREW                        05200 0104   00000 0000 

      THIS FLIGHTS NARRATIVE:  CREW REQUESTED EARLY TAKEOFF  

            A1234   05206  101   01            OP1    AIRCREW                        05206 0124   00000 0000 

      THIS FLIGHTS NARRATIVE:  AIRCREW DECIDED TO LAUNCH MISSION EARLY

       

      4: LATE TAKEOFF                    LT 

            EQUIP      S O R T I E             CAUSE  CAUSE                           S T A R T    S T O P

             ID      DATE NUMBER MOD           CODE   NARRATIVE                      DATE  TIME    DATE  TIME

            A1234   05192  151   01            MT9    UNSCHEDULED MAINTENANCE        05192 0415   00000 0000 

      THIS FLIGHTS NARRATIVE:  LINE LATE DUE TO

                DEVIATION CAUSED BY MAINTENANCE EVENT # C123456789

            A1234   05192  161   01            MT9    UNSCHEDULED MAINTENANCE        05192 0944   00000 0000 

      THIS FLIGHTS NARRATIVE:  LINE LATE DUE TO MXS  

                DEVIATION CAUSED BY MAINTENANCE EVENT # C123456789

            A1234   05208  101   01            OP2    OPS AIRCREW MANAGEMNT          05208 0527   00000 0000 

      THIS FLIGHTS NARRATIVE:  LINE LATE   

       

      5: EARLY LANDING                   EL 

            EQUIP      S O R T I E             CAUSE  CAUSE                           S T A R T    S T O P

             ID      DATE NUMBER MOD           CODE   NARRATIVE                      DATE  TIME    DATE  TIME

            A1234   05200  121   01            OTE    EARLY LAND COMP MISSION EARLY  05200 0429   00000 0000 

      THIS FLIGHTS NARRATIVE:  LANDED EARLY  

       

      6: LATE LANDING                    LL 

            EQUIP      S O R T I E             CAUSE  CAUSE                           S T A R T    S T O P

             ID      DATE NUMBER MOD           CODE   NARRATIVE                      DATE  TIME    DATE  TIME

            A1234   05192  151   02            WX1    WEATHER                        05192 0702   00000 0000 

      THIS FLIGHTS NARRATIVE:  LATE LAND TO COMPLETE MISSION 

            A1234   05192  161   01            OP2    OPS AIRCREW MANAGEMNT          05192 1243   00000 0000 

            A1234   05206  101   02            OP2    OPS AIRCREW MANAGEMNT          05206 1105   00000 0000  /font[/quote]With all the models that I have attempted to make using, headers, footers, append, detial, etc.. I can'not get monarch to "skip" the field and leave it blank if there is no line for the narrative or event #.  i.e. my out put looks like this...

      [font="courier"] 

      eg_AA     eg_AIR ABORT DUE TO RADIOS AND     eg_C051946701     eg_A1234     eg_05194     eg_101     eg_01     eg_MT9

      AA               A1234     05194     101     01     MT9

      CX     AIR ABORT DUE TO      C123456789     A1234     05196     101     01     WX1

      ET     LINE CX'D      C123456790     A1234     05200     121     01     OP1

      ET     CREW REQUESTED EARLY TAKEOFF     C123456791     A1234     05206     101     01     OP1

      LT     AIRCREW DECIDED TO LAUNCH MISSION EARLY     C123456792     A1234     05192     151     01     MT9

      LT     LINE LATE DUE TO      C123456793     A1234     05192     161     01     MT9

      LT     LINE LATE DUE TO MXS ON FUEL CONTROL INVERTER     C123456794     A1234     05208     101     01     OP2

      EL     LINE LATE DUE TO OPS CHANGING T/O TIME FROM 1000 TO 1230     C123456795     A1234     05200     121     01     OTE

      LL     LANDED EARLY AT DEPLOYED LOCATION     C123456796     A1234     05192     151     02     WX1

      LL     LATE LAND TO COMPLETE MISSION     C123456797     A1234     05192     161     01     OP2

      LL     LATE LAND TO COMPLETE MISSION     C123456798     A1234     05206     101     02     OP2

      AD     LATE LAND TO COMPLETE MISSION     C123456799     A1234     05187     111     01     HHQ[/font][/quote]notice the narrative and event # repeat from a previous line if there should'nt be one?

       

      Does anyone have any suggesstions on how to fix this?  Hopefully my ramblings make sense.

       

      Thank You,

       

      Mike J

        • model help for a newbie
          Grant Perkins

          Mike,

           

          The description makes perfact sense - quite a tricky one.

           

          Detail lines based on those starting A1234 should be easy enough I guess.

           

          Appends for the first (Numbered) lines of the various records also no problem.

           

          The Narrative and causes lines are the problem.

           

          They would almost work as footer templates - but not quite.

           

          So I am going to suggest that you make them Appends (yes I know they come after the detail but bear with me) and multi-line to cope with the variability of one or two lines and also using a preceding string to identify whether the field exists or not. This will take us a little way off the regular Monarch usage beaten path!

           

          I don't know what you set as a trap for your detail line but I used on ethat indicated all the spaces at the start of the line and a non-blank character for the first position of the Equip ID field. I also set a NUMERIC CHARACTER trap for the LAST position of the first date field in the row.

           

          Now, to make the selection process think that some information in a 'footer' location related to the detail is in fact in an APPEND position we need to set a trap that seems to be positioned BEFORE or EQUAL TO the detail trap.

           

          So  create another Template as an APPEND using the same trap settings and a single line for the sample. Which line does not matter too much. Paint a field that is wide enough to include the text of either the Narrative or Deviation rows across the line. I started mine at position 10 (I think) to capture the start of the deviation line. I made the width 80 in case there were long sections of text. You may need to adjust this. You will get some garbage at the start of the field but that can easily be cleaned out with slicing and dicing, as can any unwanted text in other parts of the field. (Note that from Version 8 on there is a function to make splitting multi line fields into their component lines a little less work. However in this case they would probnably still need to be tidied up for your purposes.)

           

          Once you have painted the field, right mouse click on it to access the field properties and select the 'Advanced' tab.

           

          In the "Start Field On" section enter something like 'this fl' in the box provided for a preceding string. As much or as little as is needed of the text that preceded the start of your narrative field can be used. So long as it is valid and will be unique at preceding only the field you want it should be fine.

           

          In the "End field on" section pick a suitable option. I have used End of Left Justification but in this case any of the last 3 should work. The second option would also work but would give a third blank line sometimes.

           

          And that should be it. As far as I can tell it works with the sample data format so hopefully it will work with the rest of the full report.

           

          Your Narrative field will contain something like:

          [font="courier"]TS NARRATIVE:  AIR ABORT DUE TO SOMETHING DEVIATION CAUSED BY MAINTENANCE EVENT # C123456789[/font][/quote]Slicing and dicing using various functions it should be easy enough to define where to split the lines (The Word DEVIATION will be consistently available I assume?)That would give 2 fields. Strip the "TS NARRATIVE: " text from the start of the first field and you leave the narrative you need. DO the same for the first 17 or whatever characters of the Deviation field and you have that reference as well. Take more characters away if the text is always the same.

           

          There are several functions available for slicing and dicing the strings so you can approach that according to personal preferences. I will be happy to make some detailed suggestions if you need them but as a generic guide for V7 have a look at LSPLIT (and maybe RSPLIT), SUBSTR, EXTRACT and so on. RIGHT and LEFT functions may also be of some use.

           

          For more variable position calculations you may need INSTR, LEN and the TRIM, RTRIM and LTRIM functions may be useful to know about as well.

           

          As mentioned previously, Version 8 offers TextLine and InTrim functions which could also be useful for V8 users by making some common manipulations easier using a single function rather than severl to achieve the result required.

           

          I hope this helps (and that it works!)

           

          I can send a model as an email attachment if you can receive it - send me a Private Message with an email address if you want me to do that.

           

          Good luck.

           

           

          Grant