How can I use a formula field to retrieve a record in a lookup

Question

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.

Answer 1

You can't run a SOQL query in a formula field, but you can traverse relationships. What's happening is that you have assigned a specific Discount object to your quote and that is the only Discount object your formula field can access. If you want something more dynamic then either:

1. Your users will need to update the Lookup to the correct Discount record
2. You will need to use a trigger to perform the query and populate the discount field

Answer 2

Since you are setting the particular Discount at the Account level, your Discount__c lookup field should be on the Account instead of the Opportunity. Then your formula field will use that particular Discount directly, rather than needing to calculate it and look it up every time.

At first I missed that the Product Category is involved in the calculation. Depending on how many Product Categories there are, I would suggest making a separate field on the Discount__c object for each category. The formula field would look like this:

CASE(Product_Category__c, 
    'CAT', Account.Discount__r.CAT_Discount_Percent__c,
    'EPI', Account.Discount__r.EPI_Discount_Percent__c,
    ....,
    0)

Or you could have multiple Discount__c lookups on the Account. The field would look like this:

CASE(Product_Category__c, 
    'CAT', Account.Discount_CAT__r.Discount_Percent__c,
    'EPI', Account.Discount_EPI__r.Discount_Percent__c,
    ....,
    0)

If there are more than just a few product categories, you'll need some Apex.