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

    Variable append / detail question

    simonmallinson _

      Hello there - newbie here.

       

      I've read a number of posts on variable appends and have recreated the detail / append model with preceding text ok, but I believe my data is one step more complex than that - I may be wrong (I hope I am).

       

      Anyway, I have a report for panes of glass per window with possible details for internal grilles (0-x rows). The structure is as follows :

       

      A0 010    1234567890123456789012345678901234567890

      BSPMSY6  123344SIMON                         27.05.200830.05.2008                              00025

      E1       010101010100010081000812028 4/20/ 4      0003                        0000001.0000000             SPMSY6  xxxxxxT

      H4T-20-4T Soft C                                                                           G

      E2       010101010100010081000393028 4/20/ 4      0003                        0000001.0000000             SPMSY6  xxxxxxT

      H4T-20-4T Soft C               notes 2                       notes 3                       G

                                                • xxxxx xxxxxxxx xxxxxxx - xxxxx xxxxx  xx xxx x  xx- xxxx xxx xxxxxxx-xxxx-xxxxx *************************

                                                         MAWI-Schnittstelle Glas

      E2       010101020200010081000393028 4/20/ 4      0003                        0000001.0000000             SPMSY6  xxxxxxT

      H4T-20-4T Soft C               notes 2                       notes 3                       G

      E3       010101010100010033700812028 4/20/ 4      0003                        0000001.0000000             SPMSY6  xxxxxxT

      H4T-20-4T Soft C                                                                           G

      F00006Silver 18mm Grilles 0001800000159.5000000000000000159.5000000081200000177.5000000000000000177.5000000081200000

      F00006Silver 18mm Grilles 000180000000000000000041500000003370000000415000000000000000003970000000337000000039700000

      E3       010202010200010033700812028 4/20/ 4      0003                        0000001.0000000             SPMSY6  xxxxxxT

      H4T-20-4T Soft C                                                                           G

      F00006Silver 18mm Grilles 0001800000159.5000000000000000159.5000000081200000177.5000000000000000177.5000000081200000

      F00006Silver 18mm Grilles 000180000000000000000041500000003370000000415000000000000000003970000000337000000039700000

      @END@[/CODE]

       

      Rows A and B are once per order

      Rows E and H detail the glass type and structure

      Rows F detail any grilles, but may not appear at all or may appear multiple times.

       

      In an ideal world F would be my detail but because it may not appear I'm stuck.

       

      I would like to see all relevant A,B,E,H and F data in one line if thats possible.

       

      This is a new report one of our software vendors is doing for me so if it takes re-ordering H and F so there is a fixed end record, I think I can get that done. Also if a blank line is required I think I could get that as well.

       

      Thanks for any help - please let me know if there is anything else you need.

       

      Simon Mallinson

      V9 Pro

        • Variable append / detail question
          Grant Perkins

          Simon, welcome to the forum.

           

          A space line after the H lines if there are no F lines would make life a little easier. You could then define you detail template as 3 lines long, map the first 2 lines and then set up the third line as a wide field (or maybe fields?) using the multi-row field options so that whether there is nothing to report or many lines they table will have a place for the content.

           

          On the other hand you can do the same thing making the H line a single multi-line field so that includes the F lines if they exist and then using some slice and dice techniques to extract the data fields from the resulting table field. The TEXTLINE() function in V9 is likely to be very useful there.

           

          So, E, H and F would be in the detail template. A dand B in an Append template. Calculated fields would be used to split the large line fields into component parts using conditional processing and Monarch text manipulation functions.

           

          Now what I don't know is how much all of this will mean to you based on your Monarch exposure to date. (Newbie just to the forum or newbie to Monarch?)  If the answer is 'Not very much.' then I will try to take you through it stage by stage. It's not difficult but a structured approach should make for a clearer understanding of the explanation.

           

          HTH.

           

           

           

          Grant

            • Variable append / detail question
              simonmallinson _

              Thanks Grant!

               

              For now at least I'll investigate the HLine being a multi-line field. I can do that, but have not used the textline() - I'll have a go first. Plan B will be to try and get a change to the report.

               

              Thanks for your quick reply.

               

              For info I've only used Monarch for about 6 hours but am getting there..

                • Variable append / detail question
                  Grant Perkins

                   

                  For info I've only used Monarch for about 6 hours but am getting there..[/quote]

                   

                  Half way to being an expert then!

                   

                  For the Multi-Line field capture have a look at Chapter 14 of the pdf learning guide that, hopefully, will be in your Monarch menu section.

                   

                  Do you need to split the data on the F rows into fields or just leave it as memorandum information?

                   

                  If memorandum and you get the report modified with the extra line than you can just leave the field as it is. (More or less.)

                   

                  If not memo or you work from the H row of an unmodified report you will want some slicing and dicing activity. Check the "Functions Reference" in the Monarch Help file and in particular the following functions (for starters);

                   

                  Textline

                  Lsplit, Rsplit

                  Trim, Ltrim, Rtrim, Intrim.

                  Left, Right, Substr

                   

                  There may be others that will come into play but those are likely to be the initial deployments so brief familiarisation is recommended before diving into the manipulation work.

                   

                  I'm guessing from the Job Title in your profile that you are not unfamiliar with data manipulation work at some level so I don't think the concepts will new to you. If you end up in a "fog" or otherwise struggling let us know immediately. If you find you are working through it OK that will be great but do let us know how you are getting on.

                   

                  Most importantly, have fun!

                   

                  Grant

                    • Variable append / detail question
                      simonmallinson _

                      What I've done so far is take a 2 line detail of E and H rows with a trap of E and defined the fields, then taken A and B as normal appends trapped appropriately. For the F line I set up an append with a trap of E and set the preceding character to F as well as 'none of the above' for 'end on'. This I've set as a memo. To remove the @end@ I've set that up as a hidden append. This seems to give me all the details I need on 1 row.

                       

                      I can now filter the fields using logic and string functions for the bar information in any F lines. Luckily it's fixed length so it's easy to divide the string up.

                       

                      I'll let you know how that goes but thanks again.

                       

                      The strange thing is when I produce the report from the software, there are nonprinting control characters in the first column but not if I copy the file to create a new one using ctrl C / ctrl V in explorer in windows. I've set Monarch to ignore irrelevant text - they still appear - they must be bizarre C++ characters.

                       

                      Simon

                        • Variable append / detail question
                          Grant Perkins

                          Simon,

                           

                          I sounds like you are well in control on this one and as you are already into preceding strings and 'End on' options I am sure the rest will not prove too challenging. With a fixed format to work with splitting a long field into separate fields based on known positions should be relatively simple. The  SUBSTR should be all you need. The only downside is that if the grille data needs to be some sort of second level detail record where it occurs you you are going to be a little troubled trying to get it into columns for each field when you have more than one line. Your table will more naturally result in multiple columns giving each field in each line individually.

                           

                          An always present F line (even if empty) would allow the grille info to become the detail  ... but in many ways that does not seem right either since the F lines are subsidiary to the E/H lines. If that becomes an issues there are ways to approach it, possible using multiple steps and lookup tables for example.

                           

                          I assume you made the @end@ a Footer append? Logical although probably not critical since you are hiding it anyway.

                           

                          The off characters are a slight puzzle. Not something I have had to deal with regularly so I don't claim expertise, but if the available Input Options for ignoring a number of leading characters are not having an effect it may be worth asking the developer of the report where the characters are coming from and what their purpose is. Just in case there is something there that is important to know.

                           

                          I predict expert level achieved by the end of the day!

                           

                          Grant