I have created a lookup field between the Quote Line Items object and a custom Discounts object. I want to retrieve a certain discount dependent on discount code and product category. My Discounts object looks like this:
Discount Key, Discount Percent 30G_CAT, 30 30G_DIL, 40 30G_EPI, 50 50P_CAT, 10 50P_DIL, 20 50P_EPI, 15 etc.....
Discount Key is uploaded as "Name" in my Discounts object which seems to link correctly from the Quote Line Item record for the lookup. Of course, the lookup includes all records in my Discounts object. What I want to do is return the record matching the Discount Key which is Discount Code & '_' & Product Category:
IF( Discount_Lookup__r.Name = Quote.Opportunity.Account.Discount_Code__c & '_' & Product_Category__c , Discount_Lookup__r.Discount_Percent__c , 0.0 )
This will actually return the value I want IF it's the first record in Discounts. Beyond the first record, it doesn't work.
Any suggestions? Thanks in advance.