+ Start a Discussion
BrandiTBrandiT 

Roll-Up Summary (Min/Max) with Junction Object Formula Field

I have a many-to-many relationship between 2 objects - contracts & headings.  The junction object is called Contract Headings and is the detail side of the 2 master-detail relationships.

 

On the headings object, I have a lookup formula to pull back a category number from a fourth object Heading Category.  I've also put a cross-object formula on the junction object Contract Headings to pull this contract number over from the Heading object.  I realize you cannot use cross-object formula fields in roll-up summaries so to get around this, I created another formula field on the junction object to pull over the value from the cross-object formula field (on junction object) for category. 

 

So to summarize, on the child object in this attempted roll-up, I have a cross-object formula which I cannot use in a roll-up and another formula to give the value in the un-usable cross-object formula...confusing I know.  :)

 

However, when I try to create the roll-up summary field on the contract object to give the MIN category number from the junction object, the field does not show up. 

 

I really need a roll up summary field on the contract object to give the MIN of the category number on the heading object.  Any idea how I can make this work?

BrandiTBrandiT

Ok I actually found a way around my problem.  I created a seperate number field on the Contract Heading option and created a workfrom rule to update it with the value of the cross-object formula for category number.  Then I was able to use the newly created number field in my roll-up summaries.

 

Works great  :)

Sara-CASASara-CASA

Thanks for posting your solution, BrandiT!  I was able to use your advice here to work around a similar problem (wanting to use a roll-up to get the sum of cross-object formulas in a related list).

 

Note, my workflow rule would not update the number field I created unless the number field had a value.  Took me a while to figre out why this wasn't working, then I just set it so the default value was 0 instead of null, and now it works great!

brettnnycbrettnnyc

BrandiT and/or Sara-CASA,

 

It seems you've both done something that I can't figure out: listing all pertinent fields from one object when one particular field (Job Position) matches the same value of a field in a different object (Open Positions). I have two objects: one named Candidates and one named Open Positions. I have many Candidates and only a few Open Positions. I'd like a Related List or a list via clicking on a Buttom or something that creates a list of all Candidates that have the same Job Title as a particular Open Position. All the possible values of Job Titles (Candidate=Object; multi-select pulldown list) and Open Position (Open Position=Object; pulldown list) fields are the same.

Can you share with me how I need to create the relationships, formula fields, or whatever to make this happen? I have limited experience, but I can create formulas and basic relationships.

Thank you!

 

Shaz-CRMShaz-CRM

Hi,

 

Great idea and everything works well until I change a record on one master object and want the roll-up summary edited in the second master object. Consider the scenario:

 

Travellers <--> Traveller/Services <--> Services.

 

Where Travellers and Services are master objects and Traveller/Services is the junction object. If I update a field called Service Value on the service object, the workflow on the traveller/services object does not fire unless I hit the edit button and save button, even though the cross-object formula field is updated on the Traveller/Services object. I find this very confusing since that field update qualifies as a record update, or does it not? Any ideas?

asigmon1.3887132025520032E12asigmon1.3887132025520032E12
Shaz-CRM,
Did you ever get an answer to your question? I'm having the same problem- My objects Project and Plan are master objects to "All projects" which is the junction object. I also have an object called "Activity" that is a child to Project and a lookup to the All Projects Junction object. I ultimately want a time field on Activity to roll up to Plan. I have created a roll up summary field called Total Time on the Project object that rolls up the time fields from the Activity objects, I then have a cross object formula field on the Junction object that pulls from the Total Time field on the project object, lets call this A. Since a roll up summary can't run from a formula field and I couldn't do a field update from a cross object formula, I then created another formula field on the Junction object, call it B, that pulls from the cross object formula field (A) on the junction object. Then I have another field on the junction object, call it C, that I want to be updated with the value in B. Basically I want C to mirror B and it's only there so that I can then do a roll up summary field on Plan of C. C will update if I click edit save on the junction object but not when I change time on the activity object. I've tried the following criteria in the workflow rule: when the time value on the Activity object >0, field B is different than prior, when modified dates is changed, when time field on the project object is changed, etc. What criteria can I use to make it update automatically?

Thanks in advance and sorry if this is confusing!! 
Tamas Simon 12Tamas Simon 12
Hi

I see this is an old thread but it comes up rnaked first when searching for roll up summary many-to-many ... so here we go:

The above method works. There is also a simpler way to do it.

Consider the classic example:
An order has products...

order -- order item (quantity) -- product (price)

you want to calculate the total on the order
...but first you need to calculate the subtotal of the order item: quantity * product.price
This can be easily done with a formula... but then you hit the brick wall.
The constraint is that we cannot use a formula field in the details to build a roll-up summary field of.

So we need it to be a  "real" field.
We need to do what the formula would calculate with a trigger - and then store the value.
  • instead of adding a formula field add a "real" field and make it read only
  • add a trigger - before insert, before update on the junction object - in this example on order item
  • use this field in the master record to define the roll-up summary - in this example order.total