0

I am creating a formula (text) field 'Sub Cohort' on our Subscription object. Formula:

IF(
    ISPICKVAL(Opportunity__r.Type,"New"),
    CASE(
        MONTH( Opportunity__r.CloseDate ), 
        1,  "January", 
        2, "February", 
        3, "March", 
        4, "April",
        5, "May", 
        6, "June", 
        7, "July",
        8, "August", 
        9, "September", 
        10, "October", 
        11, "November", 
        12, "December", 
        "None"
    ),
    "Null"
)

The goal is to look at the related opportunities attached to that record, and pull the month of the opp where oppType = 'new'. However, the formula seems to be only looking at one particular opportunity, because it is returning a 'null', and there are definitely multiple opportunities where oppType = 'new'. I'm wondering if that's the reason it is returning 'null'.

How can I ensure that my formula is looking at the right related opportunity - is this something that can only be done in a flow?

  • what if that Subscription record had 3 Opps with different Types like new,open& close ? – sdandamud1 Dec 19 '19 at 17:15
  • 1
    formulas can only reference related objects via lookup relationships; not child objects – cropredy Dec 19 '19 at 18:18
  • @cropredy is correct here, but what's the relationship between opps and subscriptions? Many Subscriptions to a ingle Opp, or many Opps to a single Subscription? – Brian Miller Dec 19 '19 at 21:02
  • @BrianMiller Many Opps to a single subscription – Nivi Dec 19 '19 at 21:26
  • for a given Subscription w/ a populated lookup to an Opportunity w/ Type = 'New', the formula should return a month name as CloseDate is a required field on Opportunity. But if the Subscription has no parent lookup to an Opportunity, the formula will return "Null" – cropredy Dec 19 '19 at 21:40

Your Answer

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

Browse other questions tagged or ask your own question.