Apex Code Development You need to sign in to do that
Don't have an account?
BrandiT 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?
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 :)
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!
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!
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?
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!!
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.