+ Start a Discussion
DatajunkieDatajunkie 

Need formula that will multiply a field in a related list with a field in the parent

Long story shortened...
 
Our Opportunities are often shared sales with other reps. The Sales Team related list is nice to capture who they are, but that's all it can do. I need to capture not only who they are, but what percent of the sale they get credit for, what that equates to in terms of "volume credit", the commission amount paid to each, and the date paid.
 
The end product will simply be a report that shows the total dollars sold for each rep - which will sometimes include a partial credit sale. Example:
 
Jane Smith sold one Opp by herself, for $100 (is listed as the owner of the Opp)
John Doe sold one Opp by himself, for $200 (is listed as the owner of the Opp)
Jane and John cooperated on another Opp, for $100. Jane is the owner of the Opp, and for this sale, she should receive 75% credit, with John to get 25%
 
The report needs to show:
 
Jane Smith  $175
John Doe   $225
 
Sounds really simple, but is driving me up the wall.
 
I tried creating multiple fields on the Opportunity object, which worked just fine. The data entry would be easy, and the display of information very easy to understand. But then I can't report on it in a useful way, because I can't combine the Amount field with the two Shared Volume fields I created, and match up the rep at the same time. I think the right thing to do is to have a related list, similar to that Sales Team list, but with more fields.
 
I created a custom object - Shared_Sales, with a master-detail relationship to Opportunity. Then I created the fields for Rep, Percent Credit, Commission Paid, and Commission Date. But the "Volume Credit" field should be a calculation of the Opportunity Amount times the Percent Credit (my custom field in the custom object). I can't seem to make the formula "reach back" to the Opportunity record to capture the Amount.
 
I made an S-Control which can do it, but I can't make that S Control show up in the related list on the Opportunity page. Having just the name and percent is good, but they're going to want to see the exact $ volume that they'll receive credit for.
 
That's not even to mention the problem of trying to figure out how to populate the related list with a default record if there are no additional reps. (i.e. - the Owner needs to get 100% credit if there is no one else involved, so I can report on the "Volume Credit" field as a whole).
 
If I'm totally barking up the wrong tree, please tell me! This seems like such a simple function, and management is absolutely adamant that we *must* be able to do this. I've already spent 2 days on this, and I'm no closer to a solution than when I started.
 
HELP!
JakesterJakester
That does sound like something that Salesforce can almost, but not quite, do without custom code. If you're looking for a consultant to help you, I'd highly recommend www.ezsaas.com - I'm a very satisfied customer of theirs.
KristinFKristinF
With Summer '08, you will be able to create a formula field on one object that includes merge fields from the parent records.  Since your custom object has a master-detail relationship to Opportunity, you can create a cross-object formula field on the custom object that includes the Opportunity Amount.  This should do the trick!  Here's a link to cross-object formula idea on the IdeaExchange --  http://ideas.salesforce.com/article/show/10079268.

Also, if you haven't already, check out the idea for providing the ability to split opportunities natively --
http://ideas.salesforce.com/article/show/41050.

Kristin
DatajunkieDatajunkie
WOOHOO! Thanks, KristinF! I did try to search the boards for a similar idea, but I never found a search criteria that pulled up what I was really looking for.  The second link you posted would be fantastic too. But the first one you listed will solve our immediate problem. (It will also come in handy for a pretty wide variety of other things I'd like to do.)  You just saved me a lot of time trying to figure out how to get around the problem. Or worse - paying a consultant to write elaborate code to do the same thing, when waiting 2 months will solve it for free. THANK YOU! :smileyhappy: