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()?

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

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 | |
  • 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 '15 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 '15 at 14:53

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.