0

I have a saved search to display invoices with original amount due, then if any credit memos applied, along with if any discount taken by the customer for terms, and then the actual payments against the invoices. The issues I'm having is when I'm adding new formula column to display the date invoice was paid in full. When I do that, numbers for "Credit Memo Amount" line and "Disc. Taken" are going wonky when I try to add any this new formula column to the saved search. Does anyone have any idea why that is? System seems to be multiplying numbers many times over. Any input is appreciated.

saved search results tab

Imran
  • 93
  • 2
  • 15

1 Answers1

1

The issue is that as soon as you take one value from the System Information you will now get a row for each of the lines in the System Notes on the Invoice, which is going to affect your SUM columns. To prevent against this you need to add something to your criteria to limit the number of System Notes fields. For example, you could add a System Notes : New Value filter and set it equal to Paid In Full. This should limit it down to one line.

Jon Lamb
  • 1,413
  • 2
  • 16
  • 28
  • Thanks for solving the mystery. Numbers look better, however still not entirely accurate, and I believe it could be that some invoices were marked paid in full twice for various reasons by accounting folks when applying an unapplying payments . Any other suggestion? – Imran Jul 30 '18 at 22:55
  • If 'Paid In Full' shows up multiple times in the System Notes then you are in a pretty tricky situation. I would double check that this is the case by clicking into one of your grouped results and confirming there are duplicate lines. If it is the case the only thing I can think of right now is to create a field on the Invoice where you capture the latest 'Paid In Full' date and then run your search off of that date. If you did that you could either create a script to populate it or use a summary search to populate it. – Jon Lamb Jul 31 '18 at 01:06