3 Replies Latest reply on Apr 10, 2014 4:02 PM by EplotEplot

# Marking 1st Invoice as New Customer and all future invoices as Return Customer

### Title

Marking 1st Invoice as New Customer and all future invoices as Return Customer

### Post

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.

Eg

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.

• ###### 1. Re: Marking 1st Invoice as New Customer and all future invoices as Return Customer

I'd use a calculation field such as you have already, but the calculation would use the ExecuteSQL function.

Unfortunately, it's beyond my capability to instruct on the details as I'm still learning about this very versatile function myself.

It'll be something along the lines of:

ExecuteSQL (
"SELECT MIN ( \"Sale Date\" )
FROM \"Invoice\" a
WHERE a.\"_kf_CustomerID\" = ?
" ; "" ; "¶" ;
// This bit below replaces the question mark above
_kf_CustomerID
)

So it will return the earliest Sale Date from the table Invoice for that Customer regardless of how the found set is sorted or filtered.
• ###### 2. Re: Marking 1st Invoice as New Customer and all future invoices as Return Customer

You can also set up such a calculation, if you define it in the customer table rather than the invoices table. From the context of the customer table, the minimum date (via either a summary field in invoices or a Min function in this calculation field), will be the minimum date of all the invoice records linked to a specific customer. A calculation field in Invoices can then compare this value to the invoice date to flag the invoice of the first invoice of a new customer.

• ###### 3. Re: Marking 1st Invoice as New Customer and all future invoices as Return Customer

No worries, will try and see how it goes.