9 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Multiple Footers

    Christina Chandra

      Dear All,

       

      I need some problems in trapping correct fields in a report. This report is an audit trail report so it is basically captured all fields which were changed. On other words, the report only show fields which had been changed. So it does not show all fields because there are so many of them.

       

      Report's example:

       

      -


      Type   Rec  User Vendor Number 

      U      VD   AS   J.45         

       

      -


      Type   Rec  User Vendor Number 

      U      VD   IR   S.585         

       

       

                    Old Value         Current Value

      Vendor Name   A                 B

       

      -


      Type   Rec  User Vendor Number 

      U      VS   AS   A.600         

       

                    Old Value         Current Value

      Term          30DY              14DY

       

      -


       

      I trapped line Type   Rec ... as detail and the others as footers. However, the result was incorrect. The footer was stored on incorrect details field.

      Please help....

       

      Thx & Rgds,

      Christina

        • Multiple Footers
          Grant Perkins

          Christina, welcome to the forum.

           

          Looking at your sample records the first one has no change information and therefore no footer for the record.

           

          That means that it (the record) will pick up the next footer that Monarch finds. In your example that would be the change information for the second record.

           

          The second record should get the second record footer and the third record should get the third record footer.

           

          Is that what you are seeing in the results?

           

          Do you also sometimes have more than one change line for each record?

           

          I think you need to find a way to select both the data lines from the detail template and not use a footer line. There should be one or two ways to do this but it would be useful to know the answer to my question about how many change lines there can be before making a suggestion for how to approach the problem.

           

          HTH.

           

           

          Grant

          • Multiple Footers
            Christina Chandra

            Dear Grant,

             

            Like what you said, the second record should get the second record footer and the third record should get the third record footer. If a record does not have a footer, like the first record, then it should not take other records' footers.

             

            In one lines, there could be up to 20 footers. Each line will have different field, example:

             

            -


            Type   Rec Ty User                       Vendor Number 

             

            I          VS        CCHANDRA       11624         

             

                                                     Old Value            Current Value

             

                  Allow AWT Flag                                   Y

                  Vendor Address                                      JL.Margonda Raya 177 C

                  City                                                         Depok

                  Country                                                   ID

                  ZIP                                                          16423

                  Phone                                                      021 77212019-1

                  Fax                                                          021 77212091-1

                  Account Liability                                    IHM.204099.000.0000.0700

                  Account Prepayment                               IHM.106399.000.0000.0700

                  Term                                                        14DY

                  Tax Number                                             02.197.829.3.412.000

                  Tax Name                                                Test  Vendor

                  Tax Address                                             Margonda Raya 177C Depok

                  Bank Type                                               TRANSFER

             

            -


             

             

            If not using footers, then what should I use? In a model can we have more than one detail template?

             

            Many thanks

            Christina

            • Multiple Footers
              Grant Perkins

              Originally posted by Christina Chandra:

              [font="courier"]----


              Type   Rec Ty User                       Vendor Number 

               

              I          VS        CCHANDRA       11624         

               

                                                       Old Value            Current Value

               

                    Allow AWT Flag                                   Y

                    Vendor Address                                      JL.Margonda Raya 177 C

                    City                                                         Depok

                    Country                                                   ID

                    ZIP                                                          16423

                    Phone                                                      021 77212019-1

                    Fax                                                          021 77212091-1

                    Account Liability                                    IHM.204099.000.0000.0700

                    Account Prepayment                               IHM.106399.000.0000.0700

                    Term                                                        14DY

                    Tax Number                                             02.197.829.3.412.000

                    Tax Name                                                Test  Vendor

                    Tax Address                                             Margonda Raya 177C Depok

                    Bank Type                                               TRANSFER

               

              -


              [/font][/quote]If not using footers, then what should I use? In a model can we have more than one detail template?

               

              Many thanks

              Christina /b[/quote]Hi Christina,

               

              A model can only have one detail template and you cannot define 20 footer templates (or 20  templates of any sort as this exceeds the maximum number of templates possible.

               

              Do you need to report any records, like the first record in your original sample, where there are no changes reported?

               

              If you do need those records then that line must be part of the detail template.

               

              If you do not need to report those records (because the purpose of the analysis is to look at the changes actually reported) then you can use the change lines as the detail and the "Type Rec ... etc" line as an APPEND template to capture the data.

               

              If you do need the "Type" lines anyway I can see two ways to approach the task. Which one to use will depend on what you need to see at the end of the process. What will happen to the data extracted and how does it need to be presented to the user?

               

              Method one.

               

              Use a single template trapping on the "Type" line. Make the data SAMPLE at least 3 lines long, based on your first set of sample records. (You may need to adjust that if the real report is very different to the sample. The sample should not have more lines that the shortest record in the report. Create a  field in the third line and make it wide enough to include all of the characters that might appear in a change line. Make this a multi-line field so that is captures the changes as a block of text. You can split the block of text into individual fields using calculated fields in the Monarch Table. (I will cover this later if you think this is the best approach.)

               

              Method 2.

               

              Requires Monarch V8 Pro version - I  am not sure from your profile if you have the Standard or Pro version.

               

              If you have the Pro version there is some potential for exporting part of the record to a lookup table and then linking the lookup back to the primary data. However for this report I would guess that doing so would offer no benefit over the previous method UNLESS there is something in the format of the report that prevents successful use of the multi-line field option.

               

              At this point the thing I most need to know is how the data need to look when Monarch has finished its task.

               

               

              HTH.

               

               

              Grant

              • Multiple Footers
                Christina Chandra

                Dear Grant,

                 

                Would it be a lot of diffence if records with changes are included or not in the model?

                 

                From your option given, I would choose first option. Would you mind explaining further how to do it. Will it involve a lot of end-user interaction?

                 

                Thx & Rgds,

                Christina

                • Multiple Footers
                  Grant Perkins

                  Hi Christina,

                   

                    Originally posted by Christina Chandra:

                  Dear Grant,

                   

                  Would it be a lot of diffence if records with changes are included or not in the model?[/b][/quote]I am not sure what you are thinking of here. If you just want a report listing all the

                   

                  [font="courier"]Type   Rec Ty User                       Vendor Number 

                  I          VS        CCHANDRA       11624          /font[/quote]lines with no change details then that would be easy of course. But not very useful perhaps?

                   

                    [b]

                  From your option given, I would choose first option. Would you mind explaining further how to do it. Will it involve a lot of end-user interaction?

                  /b[/quote]The first method suggested will give you access to the complete record with the changes. The model will be a little complex to develop, maybe, but should not need any interecation from the user once it is complete. So if you are developing the model for others to use there is no need for them to know about the techniques used.

                   

                  You will be trapping the entire record as a detail record. If you have some records which look just like the 2 lines I inserted above with a third blank line under the first 2, select one of those record as the SAMPLE for the template.

                   

                  Trap for "Type" on the first line.

                   

                  On the second line in the sample create the fields you need for the data on the line.

                   

                  On the third line in the sample (or you could use the first line since you are not defining any field there- but think about that later ....) 'paint' a field that starts where the first data line for the changes starts and is wide enough to capture the widest line you may see reported for any changes made in the system.

                   

                  Using your second sample data the field would start with "Allow AWT Flag" and finish about 100 characters later I think. It needs to be wide enough to be sure that the longest "current value" field is covered. This will need to be defined as a MEMO field.

                   

                  (The sample does not seem to be very well aligned after the cut and paste  - which is not unusual but it does make it difficult to be sure about giving the correct advice. I assume that the report does in fact have 3 columns for the changes section - the name of the field being changed, the old data (all blank in your sample?) and the new data. If the fields are well defined later stages will be easier. If they are not later stages will be a little more complex!)

                   

                  Go to the advanced properties for this field and set the "Start Field On" value to "Old Value" (Or "Current Value" or some unique wording from that sub-header line) in the "String :       anywhere in previous line" option.

                   

                  Set the "End Field On" value to one of the options offered EXCEPT the line count option - you will be making this a multi-line field which will end before the next detailed template starts when the report is assessed by Monarch. Try the other options to see which you think works best. I cannot be sure  at the moment because I am not confident I have samples that cover all the possibilities that could occur in the report. However the "Non Blank preceding string of 1 characters" will probably be OK.

                   

                  Save the template and the model.

                   

                  In your table in your table you should now be able to see the fields from your second row and a large field for the Memo field. To see the full contents of the field use the "Show Field Contents" option in the Monarch "Window" Menu and then click on the field cell you want to see into.

                   

                  So far you will have extracted all the data. The next question is what do you want to to with the changes information? (If anything - maybe the Memo field and its contents will be enough?)

                   

                  If you want to take the analysis further there are a number of possibilities for using the memo field data to create fields of changes by change type or to filter the data set based on information in the memo field.

                   

                  For example if you want to list any "Vendor Address" changes you could create a calculated field using the TextLine function in its 'search' mode.

                   

                  So if you had called the multi-line field "Memo Field"

                   

                  TextLine(,"Vendor Address") would give you the first line in the Memo block with "Vendor Address" in it. The other fields could be separated from the block using the same method. In fact you can do this quickly for many fields simply by DUPLICATING another calculated field and and changing the field name and the search string in the formula.

                   

                  If you want to separate the changes into their own fields for output you can easily do that too.

                   

                  Assuming that the Field Name, Old value and New value are in consistent columns in the report it should be possible to work out where each column starts and ends in the wide field extracted from the line. Use the Monarch SUBSTRING function as part of a calculated field which uses your first calculated field for the change in question and specify which parts of the long field you need for each of the 'Old' and 'New' values.

                   

                  (If the values are not consistently and neatly arranged in columns in the report then you may need to use the RSPLIT() or LSPLIT() functions and some others to get the extracts you need. I will not describe that technique at the moment to avoid any confusion but we can come back to it if you need to.)

                   

                  Remember that you can hide any fields that you need for getting to the data but do not want to report about.

                   

                  Also if you have a calculated field that is then used in another calculated field and is not required except as part of the calculation you can replace the field name of the first field wherever it appears in the formula for the second field with the FORMULA that would create the first field. This can make some complex formulas a little difficult to read and interpret BUT if large fields are involved, as they are here, there can be value in doing so to enhance the capacity of Monarch processes. However the database in terms of size and number of fields needs to be quite large before  the benefits of keeping the number of fields down would come to be important.

                   

                  I hope this helps. If you get stuck and can send me a sample of a real report I could apply a model to it and send it back to you as a sample to experiment with. If you think that would help send me a Private Message and I will send you an email which will give you an address to send a more complete sample file. I will then develop a model and send it back for you to consider.

                   

                  I hope this helps a little.

                   

                   

                  Best regards,

                   

                   

                  Grant Perkins

                  • Multiple Footers
                    Christina Chandra

                    Can anyone please help me on this issue?

                    • Multiple Footers
                      Grant Perkins

                      Christina,

                       

                      I sent you an email with a sample model attached on 15 November. Did you receive it?

                       

                      The requirement for the report as it is is quite difficult - the email was very long in an attempt to cover all of the issues.

                       

                      You indicated at some point that you could perhaps change the format of the source report to make it easier to analyse. Is that still something you could do? I was not sure if the problems you were hoping to solve by doing that were real problems or something that could be seen in the sample report only, maybe caused by editing work to disguise the data.

                       

                      If you did not get the email I will resend it. Let me know.

                       

                      Best regards,

                       

                       

                      Grant

                      • Multiple Footers
                        Christina Chandra

                        Dear Grant,

                         

                        I did not receive your last email. Please resend it again. About the problems in the sample layout, it also happened in the real reports. But I haven't changed it yet, in case you faced another problems that needs report's layout modification.

                         

                        Thanks a lot

                        Regards,

                        Christina

                        • Multiple Footers
                          Grant Perkins

                          Just to confirm that I have forwarded a copy of of the original email and also prepared a new mail (text copied from the previous mail) and attached the ideas model to it.

                           

                          So, 2 mails sent a few minutes ago.

                           

                          If you do not receive them let me know.

                           

                          Is it possible that your corporate mail system rejects emails with attachments? Or they are automatically delivered to your junk email folder? (I did not get a reject or 'bounce' message last time.)

                           

                          If that is the case is there another way I can send the model file to you?

                           

                           

                          Grant