Exclude Customer Ledger Entries by "Closed by Date"

Hi Everyone,

I’m working on a commission report that will first pull a list of sales people (D20) and then next pull a list of documents; 1) invoices that are paid in full and 2) posted credits for each sales person (H20).

Let’s say on May 1st an invoice was paid. No problem, the invoice pulls into the report as expected and the sales rep will receive a commission payment. The problem I’m having is if the payment was unapplied and then applied again in June, that invoice will show up again for commission payment in June even though we paid it in May.

The Table and filter field I’m using in H20 for the paid invoice is the “Customer Ledger Entry” and "Closed at Date". The “closed at date” seems to change if payment is unapplied and then applied again with no record of the previous close date.

I’m hoping someone might have run into this in the past or can possibly suggest a way to exclude these duplicate payments.

I’ve attached my report for reference. Any help is appreciated!

Karl

• Commission Report.xlsx