Finding the first invoice for each customer

[akumi]

I currently have a list of invoices with customer's names (eg John, Eric,etc), i would like to make a "calculation" that can mark it as the customer's first invoice.

 

Lets say John has invoices

 

Invoice No 12 issued on 5/Jan/2013

Invoice No 28 issued on 10/Jan/2013

Invoice No 32 issued on 2/Feb/2013

 

and

 

Eric has invoices

 

 

Invoice No 18 issued on 8/Jan/2013

Invoice No 25 issued on 17/Jan/2013

Invoice No 40 issued on 5/Feb/2013

 

 

What would be the best way to mark Invoice 12 and 18 as "new"?? using "Summary Field" using Minimum but this only works for current found sets. So any ideas would be appreciated! Thanks.

 

[Wim Decorte]

You definitely do NOT want to make this a calculation.  This is just a static bit of info that is set once and does not need to be recalculated over and over.

 

Many different ways you can do this and much really depends on the what the process is for creating an invoice.

 

You probably already have a relationship between the customer table and the invoices table, on the customer id.  If the relationship is not sorted then the very first related invoice record is the first invoice created.  So to update your existing data, all you need to do is loop through your customers and set a flag in that first related invoice to mark it.

[LaRetta]

Hi akumi,

Is this a one-time thing or an on-going need? If on-going then an easy way is to create a self-join ( in graph duplicate your table ) and join:

ClientName = ClientName

... although I hope you have a Client table with unique IDs and use the IDs instead. Then, if those invoice dates are in natural ascending creation, no other action is required - simply place your self-join date on your layout to see the first client invoice ( or access it directly from script or calculation since parent tables always see the first child according to the sort order of the relationship ). If invoice dates aren't guaranteed to be in order then sort the self-join ascending ( relationship dialog in the graph ) on that date. To flag an invoice as the customer's first, it would be calculation ( result is number ):

invoiceID = self-join::InvoiceID

... producing a Boolean 1 if true. This calculation need not be in your field definitions. It can be conditional format if only for display. The best approach would be determined by where the calc is required ( export, used in other calculations, only displayed on layouts etc ). If 12, ExecuteSQL() might also be used.

LOL, I type slowly on iPad, Wim's response wasn't there when I started. :-)

[LaRetta]

Wim said, "You definitely do NOT want to make this a calculation."

Good point since it involves almost static result but an invoice date can change ( before posted to month-end ) and potentially if an invoice were flagged manually and another invoice was re-dated earlier then you now have two 'firsts'.

So if you choose not to use calculation and instead set a flag, invoice date-changes should be scripted or triggered to remove flag from prior invoice if needed. Also if the original flagged invoice is deleted, you need to trap for that and flag a different invoice. New customer invoices can be flagged on their first order going forward.

If invoice date is always creation date and you never delete then no need for added protections. Thanks, Wim.

[akumi]

Thanks for both of your answers!!! Yes LaRetta, it's ongoing and yes i do have a client table with Unique ID. Will try to digest what you have mentioned and try to get it working, will let you know if there's any more questions. I will definitely have some sort of boolean box to show that it's 1 if it's the first invoice as i need to use it when i do a find. Have a great weekend!!!

[akumi]

Hi LaRetta, i managed to work out most of it,

 

You said

" simply place your self-join date on your layout to see the first client invoice"

 

I would like to know what's this self-join date? is it the date field from the duplicate invoice table?

 

Also "on your layout" which layout are you refering to? Layout for Customer table or Invoice table? 

 

Thanks.

[LaRetta]

Akumi asked, "I would like to know what's this self-join date? is it the date field from the duplicate invoice table?"

Yes, select the invoice date field from the self-join relationship.

"Also "on your layout" which layout are you refering to? Layout for Customer table or Invoice table? "

It should work on either. But do you change Invoice dates? If you can control new Invoice creation, invoice date change and invoice deletion, administering via script would be the best - especially now with the advent of triggers.

I had not noticed this was in Finding forum; my apology ... if you are performing finds then a stored flag of 1 is MUCH better as Wim suggests, than an unstored calculation. Placing the self-join date on the layout is separate from needing a flag for searching.

I feel we are mixing methods and we should regroup and look again at the best method for you. So when you say you've worked out most of it, what have you done so far? And are you planning to script new invoices and control invoice date ( if invoice date can ever be changed from creation date ) as well as control invoice deletion?