Open Forum

AP Invoice and related Check Number and Date

  • 1.  AP Invoice and related Check Number and Date

    Posted 12-05-2017 02:54 PM

    Hi,

    I am probably just missing this but how can I generate a query that returns a list of invoices and the check (number and date) that paid them?  I see the info in the APDoc table but dont see the relationship between the invoice (VO) and the check (CK).

    Thanks!!




    ------------------------------
    Darryl Worth
    American Society of Hematology
    Washington DC
    ------------------------------


  • 2.  RE: AP Invoice and related Check Number and Date

    Posted 12-05-2017 02:55 PM
    Forgot to mention we are on SL2011 FP1.

    ------------------------------
    Darryl Worth
    American Society of Hematology
    Washington DC
    ------------------------------



  • 3.  RE: AP Invoice and related Check Number and Date

    SILVER CONTRIBUTOR
    Posted 12-05-2017 02:58 PM
    There's a many-to-many relationship between vouchers and checks, so that requires a "linking table" in a relational database. In this case, it's APAdjust.

    ------------------------------
    Jonathan Van Houtte
    Senior Consultant
    XLstatements, LLC
    Clifton Park NY
    ------------------------------



  • 4.  RE: AP Invoice and related Check Number and Date

    Posted 12-05-2017 03:18 PM
    Awesome!!  Checking Schema now to make sure I grab the correct fields.

    Thank you!!


    ------------------------------
    Darryl Worth
    American Society of Hematology
    Washington DC
    ------------------------------



  • 5.  RE: AP Invoice and related Check Number and Date

    Posted 12-06-2017 07:45 AM
    The information you're looking for is in the table call APadjust. The column called adjustedrefnbr holds the reference to the apdoc for related vouchers and memos and the column called adjingrefnbr holds the check number. You may still want to join to the APDoc or vendor table to get additional information.

    ------------------------------
    John Connell
    Senior Consultant
    Samadhi Group, Inc.
    Kensington MD
    ------------------------------



  • 6.  RE: AP Invoice and related Check Number and Date

    Posted 12-06-2017 09:06 AM
    FYI as a starting point you can use the ap check register report. That report provides the apdoc details for the check then uses apadjust to link to the paid documents and show those details. You can see how it goes to apdoc twice once for check and again for paid documents.

    M

    Sent from my iPhone





  • 7.  RE: AP Invoice and related Check Number and Date

    Posted 12-06-2017 10:37 AM
    ​Darryl - do you have "AP Apply" quick query set up?  I believe this is still available in 2011 and was shown to us at the DSLUG a year or so ago.  The QQ was a free add-on that our VAR was able to install.

    Here are the columns that automatically come in AP Apply query:

    Vendor Name
    Vendor ID
    RefNbr
    InvcNmbr
    CheckNbr
    InvcDate
    OrigDocAmt
    CheckAmt
    DocBal
    Type
    CheckDate
    PONbr
    PerPost

    We already transitioned to DSL 2015, but I'm pretty sure we had this on 2011 version, too.  Check with your VAR; let me know if you have any questions.


    ------------------------------
    Misha Goloborodko
    Assistant Controller
    NuScale Power, LLC
    Portland OR
    ------------------------------



  • 8.  RE: AP Invoice and related Check Number and Date

    Posted 12-07-2017 12:04 PM
    ​The AP Apply quick query that Misha referenced is a custom query we built for our clients.  It is not part of the standard SL query set.  Daryl, your VAR can probably build you a query that contains exactly what you want to see.  Likely they will be happy to help.

    ------------------------------
    Cindy Smith
    Director of Client Service
    Synergy Business Solutions, Inc.
    Portland OR
    ------------------------------



  • 9.  RE: AP Invoice and related Check Number and Date

    BRONZE CONTRIBUTOR
    Posted 12-07-2017 04:02 PM
    Do we have a similar query (or report) for AR Apply.... to show Customer, AR Invoice Number, Invoice Date, as well as payment check number and date?

    ------------------------------
    Cathy McCarter
    Financial Analyst
    Innovative Wireless Technologies
    Lynchburg VA
    ------------------------------