Salesforce IdeaExchange

8496

Cross-object Formulas (Roll-Up Summary fields coming in Summer '07!)

I would like to see the following enhancement in a future release:

Foreign field references in formulas – ability to reference fields in parent or other related objects.

From Salesforce Product Management: This idea really encompasses two features - referencing parent fields on a child, and rolling up child information to a parent. The latter of these, with support for custom master/detail relationships only, is on schedule to be available with the Summer '07 release! Stay tuned for more information in the coming months!

steve_andersen 10/07/06	Yeah! A formula on a Contact record that sums up the Amounts on all Closed Won Opportunities from the current year! kind of like calling an S-Control that returns data of a certain type...
OSJMgr 10/08/06	I have been waiting for the same thing! Foreign object references in formula fields.
MariaAtlantic 10/09/06	I have been waiting for this possibility as well for a long time. It is a very useful functionality both for the opportunity object but certainly also for custom objects!
HRearden 10/09/06	Even something simple, like a record count on a detail object (how many emails as associated with this contact or case) would be immensely helpful.
NDeveloper 10/10/06	Summarizing counts or totals of numeric fields on line items (+ and -) is essential for most master detail processes - such as order, invoices, projects, etc. Much cleaner than having to run event code or scontrols - need to have reasoinable governor to prevent performance problems however.
CThompson 10/10/06	This is an absolute "must have."
jgreene 10/11/06	This is critical... very high on my list.
jgreene 10/12/06	So given that this is so popular, why don't we see it scheduled for Winter 2007?
datadriller 10/12/06	Number 2 on my Top-10 list for ages. A definite must-have.
M_Baker 10/12/06	I need this functionality as soon as possible!
ErikM 10/12/06	Extremely useful in conjunction with aggregates (Count, Sum, Average, etc.)
jducoeur 10/12/06	Absolutely. This has been a real issue for the integration of ASAP with Salesforce -- there are a number of places where we could make the UI considerably more responsive and intuitive for end users if we could get to, eg, Opportunity.Owner.E-mail in a formula field. Arbitrary nesting would be lovely, but even a single layer of dereference would make things much more powerful...
bbosche 10/12/06	I've asked for this in different ways. It would be VERY useful to eliminate sControls today and avoid duplication of info across objects and make SFDC more useable as a relational system.
Chris_P 10/12/06	Amen!!
AMartin 10/13/06	Kengelhardt, So, are you going to answer the lady's question and tell us when this functionality will be available? ;=)
Kingsley 10/13/06	Hey guys, wouldn't the embedded s-controls that will be available in Winter '07 take care of part of this (at least for having something displayed in the UI) ?
EricB 10/13/06	Hi all, We have 2 features on the roadmap for formulas that cover these requests: Ability to reference fields in parent objects Ability to aggregate fields in children objects (sum, count, min, max, etc.) Currently, the planned timeframe is 2007. Eric
ErikM 10/14/06	Eric, is this only true for parent and child records? What about other existing links, e.g. the object owner or a lookup field to a custom or user record? Think of the following case: we have a custom user lookup field "Primary Support Contact" on Opportunities, which is neither parent nor child, and it would be cool if I could extract the user's email address with a formula. And what about the owner's title and department? I can't get them either, or is the owner also going to be handled as a parent?
sandrags 10/14/06	Absolutely essential to be able to reference any field on any related object,. whether standard or custom. Without s-controls or any coding. Undil this is availabe, sf is only a semblance of a database.
EricB 10/16/06	Responding to ErikM: The broad goal is to be able to reference fields across any relationship -- master/detail or lookup. So accessing information about the Owner of a record should be possible. There will likely be some limitations (yet to be determined) to ensure there are no performance impacts.
PerGeert 10/16/06	The concept should be expanded along the upcoming enhancements to SOQL with 'relational' expressions also to be available in email templates etc. E.g. I am creating an email template based upon a custom object. This custom object has a lookup reference to the Account object (and others). Creating an email template for this object I would like to be able to reference all fields on the Account object. Currently the only fields I can reference are the Account (name) and AccountId.
ScottKincaid 10/18/06	I can only imagine all the complexities of trying to do something like this. I'm glad it's on the roadmap - looking forward to seeing it sometime in the future.
Odin 10/18/06	I'd like to be able to have a formula field on an Account record that sums up the Amounts on all Closed Won Opportunities from all Child Accounts! This would be awesome!
Bruiser 10/18/06	I've been building my way around this, with limited success, using custom objects, so this enhancement would be most welcome.
saulweiner 10/18/06	Actually, it would be great to execute workflow and validation off related objects also using formula field logic.
JWall 10/27/06	Would this also be able to track the date that a parent was changed? I'd like to have a graphical flag to show on fields that haven't been updated recently...
Eric_Santiago 10/30/06	I wish I could create formula fields that reference a lookup feild on the contact record. As people start creating more complex custom apps this functionality becomes critical.
Chris_P 10/31/06	To Kingsley's comment on pulling in data via an embedded s-control: configure, not code, brother! This is a popular enough request that it should still be within the means of non-programmers to make happen.
Kingsley 11/03/06	@Chris_P: I agree in principle, but formulas are hardly configuration
GeneD 11/05/06	While formulas are more than configuration, they aren't programming either. Something like the report criteria configuration brings the ability for more people to understand the logic but not programming syntax much closer.
EricB 11/08/06	Our vision for the platform continues to be making simple things simple without programming, while providing hooks to do more complex or specialized things with code (sControls, external services written in Java, .NET, PHP, etc., and soon, the Apex programming language). We think of formula expressions as an intermediate step between pure configuration and real procedural programming. We will continue to strive to move the bar on how much is possible without coding. 2 years ago, you would have had to write an sControl to do dependent picklists; now there is a declarative capability.
NSDavid 11/10/06	This is a must have for us also. We have at least 5 applications for this already!
dunerice 11/12/06	I use the service timecards from appexchange. I could put a total hours field in the case record so clients could see from self service portal.
gmichel 11/13/06	This also should come with at least basic functions such as "count", "max", "min", "sum", and possibly more elaborate ones such as statistics. Ideally, it would be possible to filter the parent or children field, for instance on dates. For instance, I would love to get, on a contact, such a field giving the number of phone calls he//she received in the past 3 months.
shotley 11/14/06	I build estimates for construction projects. We have a custom object called properties that manages property stats like width and depth of various floors, lot size, etc. We use these to calculate linear feet, square feet of various materials. Problem is, when we open a new estimate (child object of property), none of that data is accessible. And, since the estimate has lots of inputs, the 500 field limitation is a real problem. Accessing data in the related parent would be a huge help.
Bea 12/12/06	This would be amazing! Would solve a whole host of issues....
michele_p 12/22/06	My list of uses for this seems to be growing every day! Will this be avail in the winter '07 release? Please?
Ben_at_Komet 12/28/06	Merged Idea originally posted 12/21/06 Field calculating across objects I want to create a field that enables me to summarize the amount for all opportunities within one account. Account 1 -Opportunity 1 - amount=$10 000 -Opportunity 2 - amount=$34 000 -Opportunity 3 - amount=$15 000 -Opportunity 4 - amount=$5 000 I could make an opportunity report but it doesn't really help me. Let's say I want to see all accounts with the amount for all it's opportunities ammounting to more than $50 000. I would create the report and set a filter that returns amounts above $50 000. In that case the above account would not appear at all in this report even though we have made business with them amounting to $64 000. Similar problems We've had similar problems with other things where we want to find out things across the object borders. That's a major problem for us and probably for many others. For example we 'd like to have a field in the account that shows if any opportunities are associated with it. If there are any, how many?
larryschor 01/05/07	Merged Idea originally posted 01/05/07 Creating a custom field that can access field across custom objects I've built an expense reporting app that captures Account, Opportunity, Expense Report Date, etc. in a controlling master record called "Expense Report". Expense Line Item Detail is a related object with, as you'd expect, details on each item...line item date, category, amount, expensee, etc. I'd like to create a custom field in the master object, Expense Report, that sums across all related expense line items but cannot, as there's no mechanism to access fields across objects and to create the needed custom function. Help!
Schlinkdog 01/07/07	After reviewing the Winter 07 release, it doesn't appear that this functionality is available quite yet. Does anyone know if this is the case? Thanks!
wenzlb 01/09/07	I see there's already been enough rah rah here, but I can't help it--this is easily top 5 for us in terms of useful enhancements. Salesforce already does things so well generally--here's hoping this one comes soon!
wyliecoyotenz 01/09/07	I think this would be a great benefit. In the meantime, could anyone point me in the direction of an example of an s-control that would sum all the amounts of a parent object? I am not advanced enough to start my own from scratch, just edit someone else's. Many thanks
TimMadigan 01/11/07	I have to agree that this is a MUST for us. We have both monthly recurring fees and one-time fixed fees on a single opportunity and need someway to show the breakdown on the opportunity page, to properly calculate monthly and annualized revenue along with contract revenue. If anyone has a solution that they've already found, it would be much appreciated.
seanster 01/13/07	Bumping Schlinkdog comment - not in Winter '07, so when? Any firm dates? I have creted a simple event management app, using 2 custom objects Training Events and Enrollments (Master/Child). I simply want to calculate the seats taken on events as enrollments increase. Please please when can we have this feature? So many other good ideas here for its application also. Thanks.
GeorgeH 01/19/07	Adding parent/child objects to validation rules would also be a great benefit. How many often does a Salesperson move a close date w/o moving the schedule date? Having a validation rule that warns that the new close date is after (or to close to) the delivery date would be a great improvement.
KSadler 01/23/07	Very helpful to help total the time (hrs) spent on Projects by adding the total time on each individual task ( total time custom field), and having the sum in the Project Object. This is a must to keep a consistant report!!!!
Louise 01/26/07	I have a value on products which I need for line item calculations. You can guess how popular I am, asking the salespeople to enter this data on every line item. You can guess how frustrating it is to have to ask them to do it!
Denis_Miklusiak 02/15/07	Absolutely necessary for the Spring 07 release !!!! The ability to create a formula field in the custom object in using a field in the Master object would be worthwhile : e.g , to calculate a rate between the creation date of the Master VS the close date of the custom object. In the same way, a field dependancy between a standard object ( Master object ) and a custom object ( dependant one ) would be very helpful. For example if we want to link the status of the object "Case" to the status of a custom object.
monicaMfP 02/18/07	Along these lines...how can I get a Project tab to (preferably pre-filled w/ acct info and some info carried from opp tab) to generate automatically once an Opportunity is Closed Won? Possible? S-control code? How? Help! Thanks.
DonovanKruger 02/24/07	This enhancement should go hand-in-hand with Workflow Field Updates. http://ideas.salesforce.com/article/show/59487
LarryDreher 02/26/07	Doesn't look like this made the Spring release or did I miss something in the release notes. We really need this. Start simple. Just allow me to reference an attribute of the parent object from a child.
EW 03/02/07	For those of you interested on the status of these two features, I'm happy to say that we're still on track for release this year, as noted by EricB above. (Although, keep in mind that things can change at any time, and no purchasing decisions should be made on functionality that isn't yet available.) Thanks to all of you who participated in usability testing for these features. If you didn't get a chance to participate and would like to get on board for future usability testing, you can send an email to tester@salesforce.com. Thanks again to everyone and have a great weekend!
lgriffin1@globalmail.com 03/08/07	Merged Idea originally posted 01/02/07 Opportunity Amount Field a Formula (option) We are getting saavy with formulas and it would be great it the standard amount field could be a product of a custom formula. I have found no way to do this today and using a customer amount formula field would result in a loss of campaign opportunity revenue detail as campaign data is fed from the opportunity standard field Amount.
GlennMVista 03/08/07	Merged Comment originally posted 01/04/07 We created 3 custom fields on the Opportunity record to breakdown the "Amount" into 3 buckets, "Product", "Support" and "Prof Svc". I can create a 4th custom field to calculate and display the total of those 3 fields, but would rather have a way to automatically update the standard "Amount" field so that forecasting and other built in features that use Amount will not be impacted and require customization.
TimMadigan 03/08/07	Merged Comment originally posted 01/25/07 This would be helpful for us - we have both Non-Recurring and Monthly Recurring costs for a single product and would like to be able to track them properly for that product and still have the total roll up to the Opportunity Amount instead of creating a separate product for each (Service - Monthly fee & Service - Installation)
SFDC Customer San Francisco, CA 03/08/07	Merged Idea originally posted 10/05/06 Formula Fields Please enable calculations on fields. It would be really valuable to input a formula into a field. For example: One of my fields is called "Total Claims". I need to take that number to fill in the "Total Revenue" field.
paul_underbrink 03/08/07	Merged Comment originally posted 10/22/06 You can already create formula fields. How is this different? Also, in Winter '07 there will be a new feature to allow updating a field based on a formula triggered by a workflow rule.
Liz San Francisco, CA 03/08/07	Merged Idea originally posted 10/05/06 Ability to draw information from product line items in an opportunity formula I would like to draw a custom field that calculates a discounted line item price into a custom formula field in the opportunity record it is related to.
raul.ortega 03/08/07	Merged Comment originally posted 10/12/06 Great idea. We have several fields that we populate them at product level, but need them also (as a sum or percentage) at the opportunity level.
wennerj 03/08/07	Merged Idea originally posted 01/19/07 Ability to create a formula field on an Account object based on field values from it's other related objects (ie: Contacts, Tasks, etc...) We have a custom object MCO that has an MCO Appt Date field on it. You can have multiple MCO records for each Account record. We would like to create a custom FORMULA field on the Account object that would hold the MINIMUM value of all the MCO Appt Dates for that Account. MCO 05 has 01/04/06 MCO 06 has 10/01/06 MCO 09 has 11/15/06 This new Account field should be the MIN(MCO Appt Date), and in this case, it should equate to 01/04/06. According to SFDC Support, using fields from related objects in a formula field on another object is not currently available. We would like the ability to create a formula field on an Account object based on field values from it's other related objects (ie: Contacts, Tasks, etc...).
I2X 03/12/07	I am having the same problem as everyone else and I couldn't help wondering if SF is not putting this feature in because it will allow customers to be able to bypass some of the AppExchange apps as well as get around some limitations between the various versions...just a thought. I really really need to be able to cross reference fields between objects to calculate opportunity amounts correctly as well as quote clients.
briguybb1229 03/12/07	I would like to count the number of cases per Account as well as sum the opportunity dollars for each account and have both values appear as fields at the Account level. By doing this I would eliminate the number of reports that I have to run.
rokclimb 03/14/07	I can't state how many custom hacks I have had to design to get around this limitation. This is #1 on my list.
ollie123 03/16/07	Having been an SFA customer two years ago and now returned to reevaluate it for another business I am amazed that SF has not addressed this limitation. Without this improvement, SF is nothing more than a beautiful database. Data is of no value unless you can gain intelligence from it, and with the heavily restricted reporting in SF at the time of writing, it's a serious obstacle - I cannot do even the most basic financial reporting across custom objects.
JoeH 04/05/07	There are two major problems with Salesforce I face each day, and this is one of them.
bikesandcars 04/12/07	one more vote for this functionality, even the cross-referencing of tables so account data can be displayed on the contacts screen would help our users immensly.
dgildar 04/12/07	Merged Idea originally posted 04/10/07 Opportunity Product data validation Add ability to validate data entry in Opportunity Product conditioned on a specific Product Name. For example, for a Product named "Discount", allow validation rules to access the Product Name field as in the folowing example: If Product Name = 'Discount', Sales Price must be < 0. Currently, the Opportunity Product Name is not accessible by validation rules.
mscotton 04/12/07	I merged in dgildar's idea -- if Validation Rule formulas could reference fields in related objects, then this Opportunity Product validation could be done.
it@mce.be 04/12/07	Merged Idea originally posted 04/03/07 Calculated fields based on fields of related objects Currently it is not possible to reference fields in parent or other related objects when making custom formula fields. It is thus not possible to use fields from the opportunity product and use a formula on opportunity level. Allow to display result of calculation of opportunity-products in opportunity. This would be useful when opportunity products can have different stages (accepted, delayed, to be confirmed, ..). In this case one could display the value of each of the different stages of the opportunity-product.
AlexCRMmanager 04/12/07	Merged Comment originally posted 04/10/07 Duplicate of idea "Formulas that reference fields in parent or related http://ideas.salesforce.com/article/show/22256< merge
bikesandcars 04/19/07	So, My boss is breathing down my neck for why we paid money for a product that doesn't do this functionality... Could we get some word from salesforce.com about WHEN this will be implemented. This is the highest requested modification with 6000+ Promotions..... please please please please. At the very least, since you seem to run on microsoft, allow SQL queries for Tabs (tables): If I could write a custom query in the contacts Tab like " Select Type from Accounts" to just display the Type data under contacts it would be great. To search on it..that would be heaven.
dgildar 04/26/07	This is must have for validation rule formulas, and way overdue -- need to reference foreign fields in parent object record.
MissV 05/02/07	We constantly need to write formulas based on cross-object criteria. Currently, we have to export and write formulas after lots of sorting, grouping, etc. This type of functionality would cut our final report time in half or more...
SarahC 05/10/07	We need Validation Rule formulas to reference names of related objects so that we can better control data entered for opportunities. When is this going to be available?
ResponsysAdmin 05/16/07	Could someone provide a link to an example of how to accomplish this using Apex or an S-Control?
zen_nj 05/31/07	We need validation rule for the current Parent Child feature in salesforce.com so that we can not close out parent case when the child cases are not all closed. This should have been something that was part of the Parent Child Case release!
EW 06/14/07	Changed status to Coming in Summer 07.
jon566 06/18/07	This is our LARGEST problem with Salesforce. Thank you for taking care of it!
Christop 06/25/07	This is our orgnisations top desired functionality. We have at least 7 applications for this. Soooooo looking forward to this.
AlexCRMmanager 06/25/07	Please note everyone - the Summer '07 release does NOT - I repeat - DOES NOT support true cross object formulas. It ONLY supports rollup formulas (MIN, MAX, and SUM) on Master objects in a Master-Detail relationship. Its a nice feature, but a far cry from what most of us want and need... Salesforce.com should not have marked this idea as Delivered, when only a minor part of it is being delivered in the next release. It is causing people to have wrong expectations about what will be possible with Summer '07. Actually, rollup summary fields have just about nothing to do with the original idea if you actually read it. Salesforce.com should have created a separate idea for Rollup Summary Fields and marked that as delivered instead of making everyone think that they were actually delivering true cross-object formulas.
lisaroo 06/28/07	SO many ways to integtrate this that could help tremendously!!
a0v30000000Dm4WAAS 06/29/07	Just ran a test with a preview of the release and confirm (if no more changes are made)... the roll-ups will only be available for Master-Detail relationships as they relate to custom objects related back to masters. It doesn't appear custom fields on Opportunity Products for example, are available for this limited but still useful roll up feature.
a0v30000000DjfFAAS 06/29/07	I have to say that I'm quite disappointed with the roll up summary functionality as currently in the 2007 pre-release. I realize that this is version 1, but why wouldn't this be available between standard objects? The most logical place for roll-ups to work would be for oppportunities, opportunity products and revenue schedules. But you can't do it. Not what I hoped for. Aiden
a0v30000000ERRuAAO 06/29/07	This will be a very useful tool, saving alot of external processing for simple aggregation fields.
Olibailey 07/06/07	It's a nice feature but what I really need is to get formulas working between Opportunities and Opportunity Products.
FICLSalesForceAdmin 07/06/07	I saw in EricB 's note that this is really 2 features: 1 - aggregates of children objects in the parent object such as sum, min, max, count 2 - reference to parent object fields in the child object I have seen in the pre-release summer 07 feature 1 works great and is really easy to use. However, I don't know if feature 2 was implemented I can't see how to reference a parent object field. My example would be for discounts to be set at an Account level, but applied to all Opportunities for that Account.
sparky 07/11/07	Gotta agree with several of the other comments here. While the Summer 07 roll-ups are great, and will come in handy, many of us have been sticking with standard objects partially because we expect that they'll be supported when new features roll out. Eventually, true cross-object formulas, not just roll-ups, should be the goal. (e.g. allow non-aggregate formulas, formulas using data from the parent in the child object, etc.) But as long as we've got roll-ups, please please make them available on standard objects, especially from accounts and contacts (via contact roles) to opps. 2nd priority: from contacts & campaigns to campaign mbrs. Thank you!
CThompson 07/18/07	I've posted a new idea for the original cross-object formula concept that has not been addressed by the Summer 07 release (link below). Start promoting, people. http://ideas.salesforce.com/article/show/71840
TimMadigan 07/26/07	I have to agree with the Summer 07 roll-up release was not what I was expecting. Discovering that the Opportunity Product isn't a child of the Opportunity is very bad, since that is the primary area that both myself and most users seem to want/need this formula. It will have it's uses with custom objects but it's very limited in its use because of this - I'd have expected more, or better, given all the explanations of why this was needed.
ADPEurope 08/28/07	We are really disappointed to discover that roll-up summary fields are not available between standard object and anoteehr standard object...
falkdal 12/12/07	Changed status to Delivered.
rintin Jan 4	This feature isn't really delivered since you can't create a rollup in a case-object that show the maximum date of activity objects contained within the case. Ideally I'd want to be able to create a custom field that shows the latest activity date within the case. The spring 08 release hasn't delivered this functionality.
SFDC_Keeper Feb 8	I would also comment that the Master roll up of opportunity values rolling up to a custom object gets to be a negative. I want to attach multi oppty's to a custom object...which is virtually impossible. It does not allow me to make a master feild on the oppty screen to link to my custom object.
echod May 8	The only thing you can roll up are Parent-child relationships. That's definitely not sufficient.

track comments for this idea

Please log in to post a comment

last 100 promotions:

ultiexec
santiagotrevino
tiffanybenitez
rojamary25sf
pireanadmin
DPHSA
jamiencontonio
scsweeney
nayoub
lvwcgm
joseph_ferraro
frankeilts
mshinn
rsunderland_gtsi
guillermosrs
lahumada
anand_singh
chru
danbig
rosa
tq2109
liveopsness
tmiller02222008
jay83wan
hr
saania
meh
bushranger
knichols
mehdi_farbod
scottebling
leos337
ezunsecured
SFDC_Keeper
radliberty
saad
joshsutterfield
nbanas
ekoorb
spinner65
ETadmin
a.v.
rintin
paulMcGurn
noahc
MParker
megachuckmc
Hayley
crmakronguy
goldenfool
spicemac09
mish
energyindustryuser
SigmaMX
miniguch
hs
bash
Xmike
ei_oh
phonenomena
sonicseanb
BRiddle
rcraven
Mack
VEROSALES
rosendahl
pdsupport
DaveFriedman
rw115
FZ
SeawardT
MJI
JonV
frasuy@hotmail.com
lorik914
dcw
Luvara
karenhenry@acumen.ie
allisonhoward8
deanroper
kvuong
MarkAlton
pkanumury
jain.chirag@gmail.com
SFADMIN98
RentokilAlec
Beowulf
DrewG
jf317820
Ben-Generate
pnuskgh@gmail.com
kprice@sunlandgroup.com.au
Jaymes
ChrisMcL
DBMSRock
lisaroo
cjb
CherylFeldman
smariner
Tek_Righter

Resources

Successforce.com

Cross-object Formulas (Roll-Up Summary fields coming in Summer '07!) - Salesforce IdeaExchange

Salesforce IdeaExchange

Cross-object Formulas (Roll-Up Summary fields coming in Summer '07!)

Search Community

Categories

1-800-NO-SOFTWARE | Privacy Statement | Security Statement