4 Replies Latest reply: May 15, 2014 9:54 AM by Will L. RSS

    Footer Repeat Data

    Will L.

      Ok, I have records that are only one line. There exceptions where there is a second beneath the detial capture line. I used a footer template to capture this data. OK, detail information has no repeats, perfect. The footer data does. I set up a summary with all the footer fields as key fields and selected display, suppress duplicates.

       

      The duplicates in my footer template decreased but are still there. Instead of constant repeats for ex. 50 lines, the data will repeat for ten or so lines break for ten then repeat another 20 so on so forth.

      [font="courier"]  2274-05     35     0     001     Matt Da     0     0

        2274-05               001     Matt Da     11.42     0

        2305-04     0     0     001     DEBORAH     16     0

        2313-05     35     0     001     TIMOTHY     0     0

        2315-02     35     0     001     CARMELL     98     0

        2319-01     35     0     001     WILLIAM     5.08     0

        2320-03     35     0     001     ROCIO S     0     5

        2325-13     35     0     001     MARIA C     0     0

        2325-15     35     0     001     JENIFER     0     0

        2334-04     35     0     001     DIANNE     0     0

        2354-04     35     0     001     THERESA     0     0

        2357-08     35     0     001     MORRIS     187     0

        2362-03     35     0     001     Carlos     0     35

        2363-04     55.08     0     001     JULIE K     16     0

        2378-06     55.08     0     001     KENNETH     8     0

        2381-03     55.08     0     001     STEPHAN     8     0

        2410-02     22.47     0     001     STACI W     0     0

        2410-03     22.47     0     001     KEN TAY     8     0

        2411-05     22.47     0     001     CYNTHIA     0     0

        2416-04     22.47     0     001     David &     0     0

        2425-05     22.47     0     001     MICHELL     0     0

        2425-09     22.47     0     001     JON TRI     5     0

        2428-11     22.47     0     001     Rafael     0     0

        2431-07     22.47     0     001     Lisa Jo     0     0

        2432-07     22.47     0     001     SHANTE     0     0

        2433-11     22.47     0     001     Isaac M     0     0

        2433-12     22.47     0     001     Diana D     0     0

        2436-10     22.47     0     001     NICHOLA     0     0

        2436-11     22.47     0     001     Arlene     0     0

        2441-05     22.47     0     001     NICK CA     8.5     0

        2442-05     22.47     0     001     WISSELL     50     0

        2442-06     22.47     0     001     Wissell     0     0

        2442-08     22.47     0     001     Jesus I     0     0

        2451-03     22.47     0     001     JEFF HU     0     0

        2454-08     22.47     0     001     Melvin     8     0

        2473-05     22.47     0     001     JAMES P     3.41     0

        2475-04     22.47     0     001     STEVE S     68.97     0

        2475-04               001     STEVE S     190.87     0

        2480-04     22.47     0     001     NICOLE     22.47     0

        2480-04               001     NICOLE     22.88     0

        2480-04               001     NICOLE     53.47     0

        2482-12     22.47     0     001     Chris L     0     0

        2497-03     22.47     0     001     Edwin O     5     0

        2520-03     22.47     0     001     ROBERT     5     0

        2543-05     22.47     0     001     SCOTT A     0     0

        2547-03     110     0     001     SUEWELL     0     0

        2555-07     110     0     001     ANNA BE     8     0

        2585-02     110     0     001     Patrici     16     0[/font][/quote]I am a new user of course, but I love the software and it has been surprisingly simple thus far. These little hang ups can be painfully time consuming and I would greatly appreciate any insight. Thank you for any future efforts on my behalf.

       

      Will L.

        • Footer Repeat Data
          Grant Perkins

          Hi Will and welcome to the forum.

           

          What you are seeing with the Footer information is the exact fucntionality that it is intended to provide. I don't think the Footer is the right tool for your purpose here.

           

          From the sample report you posted I see 1 line, 2 line and 3 line records. Without knowing what you need to do with the fields extracted (in terms of the format you need to present or the analysis to be done) it is a little difficult to be sure of the best approach for you but I think you need to treat each line as detail and then aggregate the values, if you need that, in the summary.

           

          Alternatively make your DETAIL template for the last two columns only and then  make an APPEND template that select the fields from the first 5 columns but ONLY for lines where columns 2 and 3 are NOT BLANK.

           

          A third and more complex option would be to create a Detail template to select only lines with columns 2 and 3 populated and then trap the values on the last two columns as either separate or a single multi-line field and split it up afterwards. However I really doubt the

          need to consider that at the moment - we can keep it in reserve as a last resort!

           

          HTH.

           

           

          Grant

          • Footer Repeat Data
            Will L.

            Mr. Grant,

             

            Your as wise as you are insightful. I posted a poor question...

             

            The code that I posted above was the best summary table I could come up with thus far. As for the format of the actual report I am dealing with, it appears as follows...

             

            [font="courier"]ARB040D     USER: MTHCEN00A    CUSTOMER EQUIPMENT RECEIVABLE AGING-BY OLDEST DUE DATE   SITE: 010     10/22/07    6:17:07  PAGE    3 

            A.R.      001  SERVICES                                                                               

            FRANCHISE 1001-400   CITY OF ORLANDO - SELECT        AS OF 10/22/07                                                                               

            CUSTOMER                 EQUIPMENT     CURRENT                                                                POSTED TO   SUSPND A/R 

            NUMBER    STM  NAME      BALANCE       MONTH       01-30       31-60       61-90      91-120        >120     MISC. REV   WRITE OFF                                                                               

            574011-01 001 CITY NI        .00         .00         .00         .00         .00         .00         .00         35.00         .00  

            574036-01 001 ERNIE P        .00         .00         .00         .00         .00         .00         .00         45.00         .00  

            574039-03 001 AMERICA        .00         .00         .00         .00         .00         .00         .00         45.00         .00  

            574043-02 001 KEY REA        .00         .00         .00         .00         .00         .00         .00         98.00         .00  

            574046-01 001 O'BRIEN        .00         .00         .00         .00         .00         .00         .00           .00      135.00  

            574455-01 001 STEVE B        .00         .00         .00         .00         .00         .00         .00           .00      370.00  

            574516-03 001 JESTIN         .00         .00         .00         .00         .00         .00         .00           .00         .00  

            574711-02 001 VARIETY        .00         .00         .00         .00         .00         .00         .00           .00      135.00  

            574988-01 001 800 A-1        .00         .00         .00         .00         .00         .00         .00           .00       80.00  

            574997-02 001 FANTASM        .00         .00         .00         .00         .00         .00         .00        230.00         .00  

            591246-02 001 Hispani        .00         .00         .00         .00         .00         .00         .00        137.94       92.06  

            591461-01 001 A D S I        .00         .00         .00         .00         .00         .00         .00           .00      187.00  

            592720-01 001 Siesta      100.00      100.00         .00         .00         .00         .00         .00           .00         .00  

                   

              • ADJUSTED EQUIP        .00         .00         .00         .00         .00         .00         .00       100.00         .00  

            592724-03 001 GPI Sou      80.00       80.00         .00         .00         .00         .00         .00           .00         .00  

                   

              • ADJUSTED EQUIP        .00         .00         .00         .00         .00         .00         .00        80.00         .00  

            595336-01 001 SUNSHIN        .00         .00         .00         .00         .00         .00         .00         98.00         .00  

            596483-01 001 DR. WEA        .00         .00         .00         .00         .00         .00         .00         55.00         .00  

            596749-01 001 ELLENS       80.00         .00       80.00         .00         .00         .00         .00           .00         .00   /font[/quote]As far as the output is concerned, like all accountants, I just want something I can use in excel. My output, same basic format as the report just with that extra line (and this has been the problem all along) "*Adjusted Equipment..." and all related fields, to show on the same line as the record it pertains to above it. Idealy, since most records do not have that extra "*Adjusted Equipment" line I would like the fields in my report blank for those single line records to be empty.

             

            This explains why I used a footer template, trapping data below. Aagain the first the code I posted was only an example of the problem I am having with repeated data. Here is an exmple of table output i get with no summary.

             

            [font="courier"]FANTASM     0     0     0     0     0     0     0     230     0     0     0     0     0     0     0     0     100     0     ** ADJUSTED EQUIP

            Hispani     0     0     0     0     0     0     0     137.94     92.06     0     0     0     0     0     0     0     100     0     ** ADJUSTED EQUIP

            A D S I     0     0     0     0     0     0     0     0     187     0     0     0     0     0     0     0     100     0     ** ADJUSTED EQUIP

            Siesta     100     100     0     0     0     0     0     0     0     0     0     0     0     0     0     0     100     0     ** ADJUSTED EQUIP

            GPI Sou     80     80     0     0     0     0     0     0     0     0     0     0     0     0     0     0     80     0     ** ADJUSTED EQUIP

            SUNSHIN     0     0     0     0     0     0     0     98     0     0     0     0     0     0     0     0     35     0     ** ADJUSTED EQUIP

            DR. WEA     0     0     0     0     0     0     0     55     0     0     0     0     0     0     0     0     35     0     ** ADJUSTED EQUIP

            ELLENS     80     0     80     0     0     0     0     0     0     0     0     0     0     0     0     0     35     0     ** ADJUSTED EQUIP

            PM Radi     0     0     0     0     0     0     0     0     35     0     0     0     0     0     0     0     35     0     ** ADJUSTED EQUIP

            MONEYLA     0     0     0     0     0     0     0     0     135     0     0     0     0     0     0     0     35     0     ** ADJUSTED EQUIP

            Peerles     0     0     0     0     0     0     0     0     35     0     0     0     0     0     0     0     35     0     ** ADJUSTED EQUIP

            LANDSCA     0     0     0     0     0     0     0     45     0     0     0     0     0     0     0     0     35     0     ** ADJUSTED EQUIP[/font][/quote]This would be exactly what I want except it repeats the last ten columns, which come from my footer template and the "**Adjusted Equpiment " line data. Of course in the nature of a footer, repeating going up till a new value.

             

            So, Grant, I thank you for your first reply but I think I have change the scenario from what I first presented. I'll try your advice but if you know a more direct/simple route I am all ears.

             

            Thanks again- Will

            • Footer Repeat Data
              Grant Perkins

              Will,

               

              Thanks you very much for you extremely kind but I think in this case undeserved comments since I failed to realise that your 'sample' was in fact your output. My apologies. I should have read what you wrote rather than what I thought you were writing.

               

              The approach you need is, I believe, quite similar to parts of the approach I outlined for JoeSalvatore in his recent posting. Certainly worth a try.

               

              If you create an APPEND template using the same trap as for your detail template and then use, for every field that you want to capture from the second line when and only when it exists, a "Start field on" preceding string you will end up the field columns added to the table but only populated when the preceding string exists. The APPEND Template will be 'reset' for every detail record so no repeated information across multiple records.

               

              As for the preceding string to use, it could be "ADJUSTED EQUIP" or some part of it provided that is unlikely to occur on any other line. But if you need that as a field entry as well it cannot be part of the preceding string. Do you need it? If the rest of the fields are only populated because they are preceded by the you can name them accordingly and have the implied identification.

               

              Using V9, as you are, I checked setting the preceding string to **  - which worked in this case but you need to be careful that it would remain unique enough to only pick the fields you want frim the lines you want when applied to a whole report. You could also potentially use five (5) spaces. It would work but the warning is that 5 spaces on a line is not so unusual and so certainly has potential for introducing errors. But it may not be error prone on this report so it is worth checking.

               

              So some combination of

               

              "     ** ADJUSTED EQUIP" 

               

              as the preceding string for all the fields from the second occasional line   should give you what you want. I think. Subject to the contents of the rest of the report. Easy and quick to try with a single field. Maybe just convert your footer template to an append and then modify the properties of each field?

               

               

              For output you will need to reset the order in which the fields are shown in the table and save the whole thing in the model.

               

              HTH.

               

              Grant

               

              [size="1"][ December 17, 2007, 07:44 PM: Message edited by: Grant Perkins ][/size]

              • Footer Repeat Data
                Will L.

                Thank you so very much Mr. Perkins. Thank You.