Marking 1st Invoice as New Customer and all future invoices as Return Customer
I'm using the "Invoices" template from Starter Solution for FM11, would like to write something to mark the first invoice of each customer so that when I find invoices for specific month or year, I can find out who are new customers and who are return customers.
In the "Invoice table", I tried using First Invoice Date = Summary Minimum of "Sale Date" and then wrote a Calculation, =
Case ( First Invoice Date=Sale Date;"New";"Return"), but i realised that it doesn't work.
Customer A - Invoice ID10 invoice date 2/1/2013 - $100
Customer A - Invoice ID25 invoice date 4/5/2014 - $50
If All invoices are showing, it will mark Invoice ID10 as "new" & ID25 as "return" which is fine , but when i find invoices for year 2014 only, it will mark Invoice ID25 as "new", so that means it doesn't work
Any ideas how to perform this task would be great, thanks.