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

        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", 

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.