New Customer/Product for Commissions

[Bekah]

Our sales get paid differently depending upon whether it is the first-time order; or the first time the customer has ordered a specific product. Can I get ideas on how to approach this. I've been trying to think how to do it for hours now. ok. for month of October, I can find invoices for cusomter. But how do I know if it's their first order? I can count them and if 1 it's fine. But if they ordered 3 times in October, only their first October invoice would couunt for 'new commission'. And how do I separate them from a 3 count where 2 might have come from a prior month and only count their first in this month as 'new commish'???

If I can understand how to approach this, I can apply the theory to first-time products also. I have standard Contacts to Invoices on ContactID; Invoices to LineItems on InvoiceID. Any push-start would help please very much.

[Fenton]

First, you need a self-relationship on the CustomerID in the Invoice table, and a CustomerID AND ProductID self-relationship in Invoice Line Items; depending on whether the discount is "per 1st Order," or whether it's "per 1st product."

We'll do the 1st Order first; but it's the same principle. The discount for that would be on the entire order, hence in the Invoice table, applied to the total.

A field which is auto-entered by calculation (do not replace value), which uses a Lookup on a self-relationship or relationship will fail for its first unique entry; because the relationship is evaluated afterwards, so it's too late.

I first saw this in the file "Serialize by Category," by John Marks Osborne, at http://www.DatebasePros.com. Actually he did it a little differently, using the "Use" option, 1 for the 1st entry, then looking up a "null" field for subsequent entries. But it comes to more or less the same thing.

So, that "Customer1stFlag" field will be empty ONLY for the 1st entry for that self-relationship (or 1 if you use the "null" method). Include this in the calculation for the total, to determine whether a discount applies.

It would be about the same for the 1st product for a customer, except: 1. It would be in the line items table, applied only to that line.* 2. The self-relationship would be on CustomerID AND ProductID.

*It would apply to every quantity on the line. So if someone bought 10 of a product at once, for the 1st time, all would be discounted; unless you wrote the calculation to only do 1 of them.

[Bekah]

Hi there Fenton!

This makes total sense. I just couldn't picture it but you've given me a clear vision of it. I can do this! When a new invoice is created, it won't lookup the first one (based upon that new selfjoin), but subsequent customer invoices will.

I'm unclear how to 'bring this up to date' however, loop through records flagging them since we already have invoices in the system. Auto enter only works when the record is first created, right? Well, I probably need to just study it because you probly gave me the answer and I don't see it as clearly as I think I might. Thank you SOOOOOOOO much!!

[Fenton]

Ah, yes; that's the trouble with methods like this, they only work for new data entered, not for existing records. But does it really matter about old records? They've already been calculated. Do you really want to go back and change the totals for exising invoices?

[The relationship will check earlier records automatically; it does not care whether they were "processed" or not. So you don't need to mark earlier entries for it to tell whether a new entry is the "1st" or not.]

Also, the pecularity of the method is that the 1st entry is empty. So one would need to populate all records EXCEPT the 1st with a marker (generally 1). [i believe that's one reason John Osborne used the "lookup a 'null' field" method instead, as it makes more sense to "mark" the 1st, rather than mark the others.]

If you need to either "unmark" or mark the records, I would think a Loop would be the simplest method. Almost exactly like loops to mark duplicate records (_gCustomerID has global storage)

Freeze Window

View as [ Form ]

Show All Records

Sort Records [

[Bekah]

I have no doubt the loop will work perfectly.

"Do you really want to go back and change the totals for exising invoices?"

Well, commissions haven't been figured for October yet. It normally takes them hours/days to calculate it. They wanted to know if 'my nifty program' could just pop through 15,000 invoices (with 70,000 lineitems) for this first month of our new fiscal - October and hand them totals. I told them of course it could and I think it will now, thanks to you. script makes sense!

I'd give you a big kiss if you were in Philly! I mean hug!

[Fenton]

Well, I'm not likely to be heading to Philly, not with winter coming on. We San Diegans freeze solid, just like water -)

I didn't do the "1st product" method/script explicitly. But, in 7 you'd just do an AND relationship (which I mentioned), and you'd do it in the Line Items table.

In other words, the 1st Order discount, and the 1st Product discount, are two separate and independently occurring things, each within its own table.

The Loop for 1st Product would be similar, but you'd have to set/compare both the CustomerID AND the ProductID, only setting the mark when both matched, but resetting the global(s) whenever either did not. The Sort would be on both also.

[-Queue-]

I wonder if it would be easier to use calculations like serial = self::serial and invoice = self::invoice to determine the first product and invoice. You would just have to decide how the self-relationship(s) should be created.

[Bekah]

Well I'm stuck I'm afraid. I found out the rules have changed (or I misunderstood them). I tried changing the script to new needs but it never finds a single customer. Im sure its because I messed with it. I looped through five years for all 'first time' orders so I could compare to our bookkeepr. Nada, and there are many hundred. I'm sure it's because I changed what we were looking for.

From invoice POV:

1) Not all new Contacts count. A brand new (firs-time-ever-order) Contact only counts if there is a date in the AssignedDate field in Contacts. AssignedDate can be any date (just not empty). If there is AssignedDate that first order counts but no subsequent orders.

2) An old Contact orders again. Their prior order was at least two years ago. They count as new - but must have AssignedDate also. No subsequent orders count - unless it's again another two years + until they order. I pinned them down to 'two years from the order date (InvoiceDate).

This is certainly above my capacity. They want reports tomorrow. Either script or relationship. I know selfjoin but not how to compare these rules in a calculation. More help would be mostly appreciated.

oh and Queue I have straight serial in invoices and lineitems both. I also have unique IDs made from these serials.

Fenton, I confess I was confused. vs. 7 with all it's '::' confuses me because I lose track of the perspective. I didn't know if I should be on a layout based on TO invoices or TO selfjoininvoices and I couldn't tell if the self join was setting invoices or the other. not your fault.

And I couldn't tell - usually that means it's a related file but in 7 they all look related. So I wasnt' sure if I was setting the selfjoin or the main fine and didnt know my point ov view. I got lost, I think. But mainly I tried to grab their prior order date and bring it ahead with me. But I'd have to clear the global date if the customer changed. And if the customer changed, i couldn't grab THAT customer's prior date ... and on it went.

[Fenton]

Here's an attempt. The 1st mark is auto-entered by a calculation. And there is also a script to do existing records. You don't have to do all records (in fact why would you want to?). However, it will NOT do the 1st record it encounters correctly; because it can't do the date comparison. Just look that one up manually.

It also doesn't recalculate if you change the creation date. But why should you?

CustomerMark.fp7.zip

[Bekah]

Fenton, It worked perfectly.

I was late with the report but our bookkeeper almost fainted. Thank you so much!