Compare to prior invoices

[LindaG]

Invoices are flagged with DocType = Sale if commissions should be considered (based upon items sold). They get a special commission if there have been no other invoices with DocType = Sale for that Customer within one year. The current invoice would qualify for special commission also if it's the very first order (and it's also DocType Sale) -- no prior invoices.

I created a date calc which takes the Ship Date and subtracts one year. It's called 1YearAgo.

I've created another copy of invoices called Commissions sorted on ShipDate descending and joined on:

CustomerCode = CustomerCode

DocType = DocType (Doc type only contains words SALE or is blank)

1YearAgo >= commissions:ShipDate

It doesn't work. I need to be able to view each invoice and have it look backwards to see if it should receive special commissions. How please? Do I need use script instead? Tried everything - desperate.

[LindaG]

I realized I was trying to split and get two results (NO prior invoices and invoices greater than a year). I reversed my thinking and related on any customer invoices that happened during that year then use a calc to find no relationship. it's giving the right results but causes the portal to draw so slowly I don't want to use it.

I guess I'll run a script to set a regular invoice field with the calc so I won't have to display it. Both the calc and the relationship are quite ugly and slow but boss need answers in the morning. If anyone knows a better way I'd appreciate suggestions anyway ... maybe I'll try auto enter after this script finishes to keep it up to date.