Query help needed!

Karen Patterson
Karen Patterson
Kudos 1 Name Dropper Facilitator 1 First Anniversary
in Blackbaud Financial Edge NXT® General Discussions

I am trying to create a query that will give me a list of all unapplied accounts receivable payments received, and also report on where those payments were ultimately applied, when, and how much? I've tried looking at a general ledger report of my unapplied payments account, but it's not giving me enough information. Has anyone tried creating a query like this? What search terms did you use? Thanks!

Comments

  • Nikki Baldwin
    Nikki Baldwin Blackbaud Employee
    Tenth Anniversary Kudos 4 Name Dropper Participant

    @Karen Patterson, There are a few ways you can get to this information, including Query, but also on some reports. For example, the Aged Accounts Receivable report, on the Content section of the formatting, you can mark the box to 'Show transaction detail' to see unapplied payments.

    You'd have to go to database view for this, as the report hasn't been brought to webview yet, but in database view > Accounts Receivable > Reports > Transaction Reports > Unapplied Credit Report. You can indicate on the General tab to only include Payments instead of Credits and Payments, and of course set your appropriate date filters.

    With a Query, you'd want to do a dynamic Payment query. On the Criteria tab, I'd recommend only filtering by your dates that you'd like to see. Then on the Output is where you'd put most of your information -- You'll find most of the Payment details under the 'Basic Payment' area and then you can expand on 'Payment Summary Information' to see amount available to apply and applied amounts, and then you can expand on 'Paid Invoices' to add any information for applied payments to said invoices. And further, under 'Paid Invoices', there is another area to expand called 'Application', in which you can see the application post date and status. Use the Sort tab to dictate your order there. One thing to mention with Query vs Reports would be that query is definitely a gathering tool -- so you may see the same payment number on more than one line if any of the other fields you brought in had differing values, like distribution or being applied to more than one invoice, so just keep that in mind.

Categories