4

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.

1
  • I agree with Mike below, you are going to have to write a trigger to accomplish this Sep 24 '13 at 15:18
4

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
1
  • Thanks for the help Mike. I feel that you're right. I was trying to avoid triggers, but it looks like the solution. Thanks again. @Jeremy Nottingham
    – Adam H.
    Sep 24 '13 at 19:34
1

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.

2
  • Yes, Jeremy, unfortunately there are 35 product categories, which would make for a pretty ugly case statement ;) Thank you so much for you answer though, it did help to make my decision. I'll mark it as useful.
    – Adam H.
    Sep 25 '13 at 15:22
  • Looks like I'll need 3 more reputations before I can mark it useful. I'll come back to it when I can mark it.
    – Adam H.
    Sep 25 '13 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.