Hello Everyone!  

I have a lookup field on Opportunity called "Affiliate."  This points to a custom object called "Affiliate."   The business needs a notification when this field has a unique value for the first time.  

So, let's say we have three Affiliate names, "Cat", "Dog", and "Bird."    We have an Account called "Big Company Inc" and we have many opportunities being pushed via ETL tool every day.  Some of these opptys have a value for "Affiliate."   Let's say that in the past year we have had Opptys with Affilates "Cat" and "Dog" many times, but never "Bird."

The first time an oppty is pushed with a lookup value "Bird" for Affiliate, they need to know.  What is the best way to acomplish?

I want to stay away from triggers becuase we have thousands of records being inserted and updated every hour from the ETL tool.  Here's what I was thinking:

Maybe create a one time job to concatinate all values from this lookup into a text field seperated by commas.  Then write a batch job that take every opportunity with a value for Affilate that came in that day, check the values against the text field and if one is new, send the notification.   Am i over thinking this?

Thanks in advance!!!!!   Can't wait to mark "best answer!"
G