4 Replies Latest reply: May 15, 2014 9:58 AM by Bill Watson RSS

    missing captures

    Paul Bernick

      I am trying to capture data that may or may not contain details.   am currently using the Page Header and Detail templates. to capture the following:

       

        1005-00-000000                        $25,319.90    Canadian Security Bonds                                                                               

      February                         $556.50               $0.00             $556.50          $25,876.40

                                                         -


      -


      -


      -


                                                 Totals:             $556.50               $0.00             $556.50          $25,876.40

                                                                                      1006-00-000000                           $750.00    Petty-Cash                                                                               

      -


      -


      -


      -


                                                 Totals:               $0.00               $0.00               $0.00             $750.00

                                                                                      1007-00-000000                         $1,643.96    Postage Meter                                                                               

      January                        $2,360.02               $0.00           $2,360.02           $4,003.98

                                    February                           $0.00           $2,908.88         ($2,908.88)           $1,095.10

                                    March                              $0.00             $110.08           ($110.08)             $985.02

                                    April                              $0.00             $608.67           ($608.67)             $376.35

                                    May                            $2,919.71               $0.00           $2,919.71           $3,296.06

                                    June                               $0.00             $555.16           ($555.16)           $2,740.90

                                    July                               $0.00           $1,156.18         ($1,156.18)           $1,584.72

                                    August                         $2,911.92               $0.00           $2,911.92           $4,496.64

                                    September                         $37.10               $0.00              $37.10           $4,533.74

                                                         -


      -


      -


      -


                                                 Totals:           $8,228.75           $5,338.97           $2,889.78           $4,533.74

       

      Because 1006-00-0000000 does not have any activity by month it will not post anything to the table.  I have tried to use combinations of append and footer to no avail.  Any recommendations would be appreciated.

       

      Paul

        • missing captures
          Grant Perkins

          Originally posted by Paul Bernick:

          [font="courier"]  1005-00-000000                        $25,319.90    Canadian Security Bonds                                                                               

          February                         $556.50               $0.00             $556.50          $25,876.40

                                                             -


          -


          -


          -


                                                     Totals:             $556.50               $0.00             $556.50          $25,876.40

                                                                                          1006-00-000000                           $750.00    Petty-Cash                                                                               

          -


          -


          -


          -


                                                     Totals:               $0.00               $0.00               $0.00             $750.00

                                                                                          1007-00-000000                         $1,643.96    Postage Meter                                                                               

          January                        $2,360.02               $0.00           $2,360.02           $4,003.98

                                        February                           $0.00           $2,908.88         ($2,908.88)           $1,095.10

                                        March                              $0.00             $110.08           ($110.08)             $985.02

                                        April                              $0.00             $608.67           ($608.67)             $376.35

                                        May                            $2,919.71               $0.00           $2,919.71           $3,296.06

                                        June                               $0.00             $555.16           ($555.16)           $2,740.90

                                        July                               $0.00           $1,156.18         ($1,156.18)           $1,584.72

                                        August                         $2,911.92               $0.00           $2,911.92           $4,496.64

                                        September                         $37.10               $0.00              $37.10           $4,533.74

                                                             -


          -


          -


          -


                                                     Totals:           $8,228.75           $5,338.97           $2,889.78           $4,533.74[/font][/quote][/b][/quote]

          • missing captures
            Grant Perkins

            Paul, welcome to the forum.

             

            On a basic level the detail record you select MUST exist in every record you want to report. In your example I can well see the objective of reporting by month and appending the account details but you will end up with the situation you have already described for any accounts which do not have a related transaction.

             

            Detail and footer might work for you here but it really very much depends on what you are seeking for you ultimate output.

             

            The 'Generic' responses to this sort of problem either involve multi-line fields for the month reporting/footer area OR a two step process that would first extract the account summary figures as detail and then run a process treating each monthly analysis amount as a detail record and appending, via a lookup, the detail info from the first extraction.

             

            I suspect the latter approach may be the best option in this case but I would be very pleased to see a different solution offered.

             

            HTH.

             

             

            Grant

            • missing captures
              Paul Bernick

              Thanks for the feedback.  The two pronged approach you have suggested works.  I had considered this myself but wanted to explore a one step approach.

               

              Appreciate your feedback.

               

              Paul

              • missing captures
                Bill Watson

                Well I thought this would be an interesting exercise so here is my solution (Providing I have got the layout right        ):

                 

                Templates:

                 

                C = Character Field

                N = Numeric Field

                 

                Detail - Month Line - Month (C), MonthCr (N), MonthDr (N), MonthNet (N), MonthCloseBal(N)

                Traps: Blank Trap above T of Totals (on the Total Line), Non Blank Trap over rightmost digit of each amount column

                 

                (This will pull out the values and the -


                lines - will fix this in the filters!)

                 

                Append1 - Account Line - AccNo (C), OpenBal(N), AccName (C)

                Traps: NNNN-NN-NNNNNN (Numeric Traps) over the Acc No field

                 

                Footer1 - Total Line - TotalCr (N), TotalDr (N), TotalNet(N), CloseBal(N)

                Traps: "Totals:" over Totals: (minus ")

                 

                In the table view create a new filter using the following formula:

                 

                (=[OpenBal]) .Or. .not.isnull()

                 

                This will result in removal of the --- lines (null amounts) but only where there are already transactions - ie the 1006-00-000000 details will be retained as the open and close balances match

                 

                Hope that all makes sense.

                 

                Can't beat a good single pass solution!