4 Replies Latest reply: May 15, 2014 10:03 AM by Nick Osdale-Popa RSS

    Filtering for last paid check

    Nick Osdale-Popa

      (aka I hate working with dates - been working with this since Thursday)

       

      Here's the report sample:

      RUN DATE 02/26/09 09:47:52                           ACCOUNTS PAYABLE MODULE                                           PAGE    12

      ********COMPANY NAME********                       DETAILED VENDOR HISTORY                                                                               

      BY PAYABLE CO/DIV, BY VENDOR #, BY DATE                                                                               

      FOR PERIOD 04/01/04 - 02/28/09                                                                               

      INV/PAY               INVOICE #/ INV DATE DESCRIPTION/               INVOICE    DISC AVAIL/   DISCOUNT         CHECK      CURRENT

      TYPE  JOB             CHECK #    CHK DATE JOINT PAYEE                 AMOUNT     RETAIN BAL      TAKEN        AMOUNT      PAYABLE

                                                                                      VENDOR:   1103     GEORGE BAILEY'S WONDERFUL TERM LIFE, INC.                                                                               

      V     0501            29408      07/02/08                             940.33          0.00        0.00          0.00       940.33

                                                                                      0.00                                      

      V     0501            29517      07/08/08                             187.72          0.00        0.00          0.00      1128.05

                                                                                      0.00                                      

      C *   0501             23671     07/22/08 Computer Check                                          0.00        940.33       187.72

      C *   0501             23671     07/22/08 Computer Check                                          0.00        187.72         0.00

      V     0501            C30117     08/13/08                            -777.90          0.00        0.00          0.00      -777.90

                                                                                      0.00                                                                               

      -


      VENDOR TOTALS:                                                         350.15          0.00        0.00       1128.05      -777.90

                                                                                      0.00                                                                               

      CANCELLED CHECK AMOUNT:            1128.05                                                                               

      VENDOR:   1110     MR. MAGOO'S OPTOMETRY                                                                               

      S     0501            109186     09/11/06                             420.00          0.00        0.00          0.00       420.00

                                                                                      0.00                                      

      C *   0501             21706     12/08/06 Computer Check                                          0.00        420.00         0.00

      S     0501            116373     12/31/06                             420.00          0.00        0.00          0.00       420.00

                                                                                      0.00                                      

      V     0501            0118482    02/21/07                             560.00          0.00        0.00          0.00       980.00

                                                                                      0.00                                      

      V     0501            0119930    03/16/07                             750.00          0.00        0.00          0.00      1730.00

                                                                                      0.00                                      

      C *   0501             20089     04/09/07 Computer Check                                          0.00        750.00       980.00

      C     0501             20178     05/08/07 Computer Check                                          0.00        560.00       420.00

      C *   0501             20200     05/14/07 Computer Check                                          0.00        420.00         0.00

                                                                    -


      VENDOR TOTALS:                                                        2150.00          0.00        0.00       2150.00         0.00

                                                                                      0.00                                                                               

      CANCELLED CHECK AMOUNT:            1590.00            

      /code

      Trapping is not a problem, it's filtering to show just the last paid check (Type C) for each vendor:

      Vendor#  VendorName                            CheckDate   Check#         CheckAmt

      1103     GEORGE BAILEY'S WONDERFUL LIFE, INC.   07/22/08    23671           187.72

      1110     MR. MAGOO'S OPTOMETRY                  05/14/07    20200           420.00[/code]

      I'm hoping to accomplish this in one-pass.

        • Filtering for last paid check
          Data Kruncher

          Nick,

           

          The solution lies in one of my favorite new features in V10.

           

          First, sort descending on check date.

           

          Now create a filter for C Type records, and go to the Advanced tab. There's a new option to isolate unique records based on the current sort.

           

          That should do it.

            • Filtering for last paid check
              Nick Osdale-Popa

              You da man, Kruncher!

               

              Actually, I have anomalies with records that look like this:

              C     0806             25747     02/27/09 Computer Check                                          0.00         86.31     23297.39     

              C     0803             25747     02/27/09 Computer Check                                          0.00        358.27     22939.12     

              C     0803             25747     02/27/09 Computer Check                                          0.00        546.43     22392.69     

              C     0803             25747     02/27/09 Computer Check                                          0.00       1228.47     21164.22     

              C     0803             25747     02/27/09 Computer Check                                          0.00       5177.64     15986.58     

              C     0803             25747     02/27/09 Computer Check                                          0.00        230.71     15755.87     

              C     0803             25747     02/27/09 Computer Check                                          0.00         99.15     15656.72     

              C     0803             25747     02/27/09 Computer Check                                          0.00         29.92     15626.80     

              C     0803             25747     02/27/09 Computer Check                                          0.00        714.57     14912.23     

              C     0803             25747     02/27/09 Computer Check                                          0.00        631.86     14280.37     

              C     0803             25747     02/27/09 Computer Check                                          0.00         36.69     14243.68     

              C     0803             25747     02/27/09 Computer Check                                          0.00        341.41     13902.27     

              C     0803             25747     02/27/09 Computer Check                                          0.00        400.71     13501.56     

              C     0803             25747     02/27/09 Computer Check                                          0.00        659.97     12841.59     

              C     0803             25747     02/27/09 Computer Check                                          0.00          2.93     12838.66     

              C     0803             25747     02/27/09 Computer Check                                          0.00       2390.54     10448.12     

              C     0803             25747     02/27/09 Computer Check                                          0.00       2473.33      7974.79     

              C     0803             25747     02/27/09 Computer Check                                          0.00       2116.55      5858.24     

              /code

               

              And records that span pages. So I threw in the Page Header trap, trapped on the Page Number and created a calc field for Line(). Sort order then became:

               

              VendorName (asc), PageNum (desc), LineNum (desc). Then filter as you suggested. The dates are already in ascending order, so the Line/Page sort automatically puts them in descending order.

               

              Spot checking so far are returning the results desired.

                • Filtering for last paid check
                  Nick Osdale-Popa

                  WHOA!

                  Found a Bug! ( I hope Gareth and Mike are reading this)

                   

                  I saved that model. Closed everything down. Reopened the report and model. Went to table. Returned 166 rows (this is arbitrary as it's some times 134, 144, etc). Turn off the filter, reapply the filter and I get the correct returned record count of 268. There are 270 vendors in my file. 2 of which did not get a check paid to them (type c).

                    • Filtering for last paid check
                      Nick Osdale-Popa

                      I sent the report/model on to Gareth to take a look at. He did everything he could to replicate the problem without success. His suggestion was to clear out Monarch's working folder found at:

                      HKEY_CURRENT_USER\Software\Datawatch\Monarch Pro\Settings\Workpath

                       

                      I did that and I now can not replicate the error. So if you get inconsistent results with your filters, that may be the first thing to try.