function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
warrnewarrne 

Fields not appearing in Roll up summary field creation. Suspect cross object Formula

Hi there. I may need some developer help here.

 

I’m trying to create a roll up summary of a field on 1 object, that is created by a Formula, that looks up to another object, but the fields dont appear in the aggregate drop down, when I try to create it,

 

basic background is that I have 3 objects.

 

  1. Advertising Goal. An advertising goal that we need to achieve,
  2. Ad spots. We may have to buy multiple ad spots to fulfill the goal
  3. Websites. Ad spots exist on other websites. We may have to buy 1 or more spots, on 1 or more of the websites.

 

Advertising goal and websites are both currently  master objects to the ad spot, since ad spots and their stats need to be rolled up to view at either level. ie how many spots have we bought on a website, how many spots are we using to support an advertising objective.

 

Ad spots also have an ‘ad type’ which is a picklist of 3 values. Button, banner or text link. Each website may charge differently  for each type of spot, and the record for what they charge is held in 3 fields on the website’s record, called button rate, banner rate and text link rate.

 

To set how much the spot is going to cost on the ad spot record, we have a ‘monthly rate’ field, where  we have developed a case function that references the ‘type’ picklist on the ad spot record, and depending on which value is specified, looks to the website record and returns the appropriate field value. Formula is below.

 

CASE( Placement_type__c ,"button",Partner_Website__r.button_Rate__c,"Banner", Partner_Website__r.Banner_rate__c,"Text link Ad" , Partner_Website__r.Text_Link_Ad_Rate__c, 0)

 

We’re happy with this at least and it seems to work.

 

However, now we need to know how much the advertising goal is going to cost from having secured all these ad spots, so we need to create a rollup summary field, that sums the monthly rate field, across all the associated placements.

 

When we try to create a roll up summary though, the Formula fields do not appear.

 

I'm hitting a wall with this as I'm not a programmer and just don't have the skills to work around, and SF support can’t help me either. They ahve suggested that you can't roll up formulas that involve other objects. So I would love to hear from anyone that can explain what is going on, and offer a solution.

 

Best 

 

Warren Cowan

e warren.cowan@greenlightsearch.com

.

 

Best Answer chosen by Admin (Salesforce Developers) 
EnthEnth

As support have said, you cannot have a roll up summary field on a cross-object formula field, but there is a workaround.

 

If the fomula only changes when the base record changes (i.e. you don't need it updated both when record A and record B are changed), then instead of having a formula field create a normal field and write a workflow that implements your formula as a field update. This will then give you a field that you can roll up.

 

 

All Answers

EnthEnth

As support have said, you cannot have a roll up summary field on a cross-object formula field, but there is a workaround.

 

If the fomula only changes when the base record changes (i.e. you don't need it updated both when record A and record B are changed), then instead of having a formula field create a normal field and write a workflow that implements your formula as a field update. This will then give you a field that you can roll up.

 

 

This was selected as the best answer
warrnewarrne

sounds like a good one enth, although my experience with workflows is limited. Could you elaborate a bit more on the logic in setting that up.

 

thanks in advance

 

<n00b> warren </n00b>

Rob Kallai 1Rob Kallai 1
If the field you are trying to rollup is a Lookup field, make sure that the Lookup Options>Don't allow deletion of the lookup record that's part of a lookup relationship, is selected. Otherwise, the field will not appear on the rollup fields list.
User-added image