2

I would like to create a formula-field on account that is a comparison based on any one of group of child objects satisfying a conditional. I am a SQL developer tasked with this challenge so it seems like a very simple thing to do, but I can not find any documentation on grouping cross object in formulas.

Here is my example:

Standard object [Account] has a custom field [Is Customer].

Custom object [Subscription] has a field [End Date] and a field [AccountID].

[Is Customer] should be true if any [Subscription] records with [Subscription].[AccountID] = [Account].[ID] and [Subscription].[End Date] < TODAY().

How do I group all the [Subscription] records by [Subscription].[AccountID] inside the formula-field so that I can compare the max [End Date] to TODAY()?

Improve this question
2
  • what is the type of relationship on subscription to the Account I mean Master or Lookup
    – Suresh
    Jun 29, 2015 at 14:42
  • It's a Lookup relationship.
    – Ben Kean
    Jun 29, 2015 at 14:49

1 Answer 1

Reset to default
2

You can't do that directly in a formula field. You will need to create a "Roll-up Summary" field on Account to find the Subscription with the highest end date.

Then your formula field will be able to compare the value from the roll-up summary to TODAY().

If you don't have the option to create a roll-up summary on Account using Subscription as the child object, then the relationship between Subscription and Account is probably of the wrong type. It could be Master-Detail or Lookup. To make a roll-up, it will need to be Master-Detail.

That's not a step-by-step guide, but will hopefully set you on the right track so that you can google the rest.

Improve this answer
2
  • The field to get the highest end date can also be populated with a roll-up trigger or using the free "Roll-up helper" from the App Exchange if a M-D relationship doesn't exist.
    – crmprogdev
    Jun 29, 2015 at 14:47
  • I have a Lookup relationship and the custom object is managed so I can't change that relationship type. I'll start looking for roll-up trigger info, thanks for the advice.
    – Ben Kean
    Jun 29, 2015 at 14:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .