Customer Referral Program

[Kevin Moody]

I am not sure if I am posting in the correct location. 

I have been working on a database for a few years now, I will not call myself an expert, but I have figured out how to do a lot of things.  I am at a spot where I need help. 

I have created custom invoices and status screens for my screen printing and embroidery company, now I need to create a customer referral program.

 

I have already set up the relationships where I linked the referrer to the referred.  I can see totals of each referred customer in the referrer's customer layout. 

 

I need a way to add the points, which is 1 point per $1 spent, to the points section of the customer layout.  Then I need to have a way to apply the points to an invoice of the customers choice, and deduct the points from the total once the discount has been applied.  I think it is an easy solution to this, I have just been racking my brain over this issue, could someone please point me in the right direction.

 

I am thinking that I need to create a table with referred line items, then as an invoice status is changed from estimate to invoice, then it will set variables for the data needed, then set fields in the proper place in the line item tables.  Then write a calculation that just adds the points from the new record into the points field.  Once a discount has been applied, then delete points from the points field.

 

What is the most efficient way to do this?