Hi, I am writing a salesperson commission report which is based on paying/reclaiming commission once a customer’s invoice/credit has been settled. I have gone about this by looking at the Customer Ledger Entry table and looking at the “Closed at Date” field to find the invoices/credits settled in the reporting period. This is working great 90% of the time, however on some credit memos the Closed at Date is blank even though the credit is closed and fully settled. I can get around this by looking at the “Applies-to Doc No” to find the contrad invoice and using it’s “Closed at Date”. However I have found a couple of invoices that have no “Closed at Date” even though they have been fully applied to Payments, and don’t understand why. Is there a more reliable way I should be using to find when invoices/credits were settled? Any help appreciated, Jonathan
The important field is Closed by Entry No… If this is filled in, then Closed at Date is probably filled in as well. If it is not filled in, then you need to find the Customer Ledger with the current Entry No. in the Closed by Entry No. field. If you want to fully understand these applications, try to follow the logic of the Aged Accounts Receivable Report. Another option might to be to create a flowfield to the Detailed Customer Ledgers with the latest date of type Application?