5 Replies Latest reply: May 15, 2014 10:03 AM by Hootie _ RSS

    Is there an easy way to trap this data?

    Hootie _

      Hi everyone, I'm very new to Monarch and these forums so please forgive me if this is a stupid question.  I've taken a look through the forum, but can't find exactly what I need so I thought I would ask in the hope someone could help.

       

      I'm having a problem trying to trap some information from the report below, it shows 3 accounts each with a list of transactions and a total due.

       

      I need to extract all the detail lines for an account where there is a balance in any of the transactions even if the total is zero.  So based on the report below, the extracted data would show 3 lines for accounts 22222 and 33333, but exclude details for account 11111

       

      I managed to trap 11111 and 22222 using a footer for the total field and then filter, but account 33333 is causing me the problem.

       

      Is there an easy way to trap this data?

       

            Account :   11111                                              

            

               Date   Type   Invoice No. / Cash Ref       Amount        Balance

              -


        -


        -


             -


              -


       

            28/12/00  INV    281200-03                    578.00           0.00

            23/01/01  PAY    XXXXX-230101-03              578.00-          0.00

                                                                     -


       

                                                 Total Now Due             0.00

                                                                     ==========

       

            Account :   22222

                                                                                      Date   Type   Invoice No. / Cash Ref       Amount        Balance 

              -


        -


        -


             -


              -


         

            31/07/06  INV    310708-01                  2,229.00           0.00 

            11/09/06  PAY    XXXXX-110906-01            2,229.00-          0.00 

            24/04/07  CRN    240407-12                     95.00-         95.00-

                                                                     -


         

                                                 Total Now Due            95.00-

                                                                     ==========                                                                               

      Account :   33333

                                                                                      Date   Type   Invoice No. / Cash Ref       Amount        Balance 

              -


        -


        -


             -


              -


         

            17/10/08  INV    171008-01                    752.00          70.00 

            26/10/08  CRN    261008-05                     70.00-         70.00-

            28/11/08  PAY    XXXX-281108-01               682.00-          0.00 

                                                                     -


         

                                                 Total Now Due             0.00 

                                                                     ========== 

                                                                     /CODE

       

      Thanks

        • Is there an easy way to trap this data?
          Nick Osdale-Popa

          Is this the output you want:

          Account   Date       Type   Invoice       Amount   Balance

          22222     24/04/07   CRN    240407-12     -95.00    -95.00

          33333     17/10/08   INV    171008-01     752.00     70.00

          33333     26/10/08   CRN    261008-05     -70.00    -70.00

          /code

           

          If so, then your detail trap is on the line with the date, type, invoice, amount and balance.

          Append trap on the account #.

           

          Disregard the Total due line entirely.

           

          Once the traps are made, just make a filter where:

          Balance <> 0

           

          Does that work for you?

            • Is there an easy way to trap this data?
              Hootie _

              I need a bit more data than that unfortunately. 

               

              I need to extract all the detail lines for an account where there is a balance in any of the transactions even if the total is zero. So I need to get all 3 lines for accounts 22222 and 33333 like this -

               

              22222      31/07/06  INV    310708-01                  2,229.00           0.00 

              22222      11/09/06  PAY    XXXXX-110906-01            2,229.00-          0.00 

              22222      24/04/07  CRN    240407-12                     95.00-         95.00-

              33333      17/10/08  INV    171008-01                    752.00          70.00 

              33333      26/10/08  CRN    261008-05                     70.00-         70.00-

              33333      28/11/08  PAY    XXXX-281108-01               682.00-          0.00 

              /CODE

                • Is there an easy way to trap this data?
                  Chad _

                  Did you get this figured out?  I have a solution if you want to PM me.

                    • Is there an easy way to trap this data?
                      Grant Perkins

                      Hootie,

                       

                      Not withstanding Chad's offer of assistance (and thanks to Chad for posting as this is one I had been meaning to look at but but other events meant it fell from view) this should work for you. It's a filter solution, not a trap issue.

                       

                      Trap the transactions as detail lines.

                       

                      Trap the Account Number as an Append.

                       

                      Go to the table view.

                       

                      Add a calculated field for each record:

                      Balance<>0[/SIZE]

                      /code

                       

                      where BALANCE is the Balance column. Call it what you like. I'll call it "Acct bal gt zero". The populated value, assuming you created a NUMERIC field will be 0 (if the test is NOT TRUE) or 1 (if the test IS TRUE).

                       

                      Now set up a summary with Account, Date, Type, Invoice as key fields and Amount, Balance and as measures. SUM should be fine in this case.

                       

                      In the summary definition go into the PROPERTIES of the ACCOUNT field (Right Click in the sumary definiton window). Select the MATCHING tab and click on the radio button for MEASURE LIMIT. Make the field to be measured , the test to be "<>" (not equal to) and the value to be 0.

                       

                      [NOTE: You may also want to tick the box for 'Hide Unmatched value' rows BUT be aware that if you use totals in the resulting output the Unmatched values will still be included in those numbers so you may want to consider how you wish to handle that situation. Formerly these values were always /Idisplayed under a grouping of 'Other'.]

                       

                      The effect of this will be to filter the summary to only display Account numbers where at least on of the Balance values is not 0.

                       

                      Once you are happy that the test works as required for the report you can go into the properties for the MEASURE and tick the box that will make it a hidden field if hiding is required.

                       

                      Does this give you what you need?

                       

                      Grant

                       

                      PS - Chad, is this similar to your solution? I feel it's always good to see alternatives as part of a continuous learning process.