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
Andrea SloanAndrea Sloan 

Revenue by Contact for 3 years

Revenue by Contact for three years
I need to track revenue information based on the name of contacts who placed an opportunity. Revenue by Contact name. The catch is that our Opportunity screen and Contact objects are not related in any way (and will not be for various reasons) BUT they are both children of the Accounts object.
Requirement:
If Formula Field name "Booking Agent Name" in Opportunities contains the same name as the standard  "Name" field inside Contacts (we are not include Title, just first and last name) then DO not create a record in Contacts screen but add the expected revenue amount of the opportunities screen field to the Contact's "Contact Revenue" field (needs to be created) provided the Contact and the Opportunity share the same CRM ID Account AND provided the Opportunity's "File Status" picklist type field reads "Confirmed/Traveled"
We would like to tally the revenue by Contact for all travel Dates (found in opportunities) of This year ,last year and two years ago. This is based on the Opportunity's Travel Date  field.

New Contact Fields will need to be created labeled:
Revenue this year
Revenue last year
Revenue two years ago

If the Booking Agent Name in the Opportunity's object does not exist inside the Contact's object for that same CRM ID Account, then the first and last name of the "Booking agent name" of opportunities should be used to create a new Contact name for that account with matching First and Last name (from the opportunities screen). The rest of the above should then apply once the Contact record is created. Please see screenshots of Contacts and Opportunities screen.

In Parallel to this, I wold like to create a child object for contacts that houses the list of all opportunities fore ach contact where I can later created a rollup count of how many opportunties the contact has brought in based on the opportunity's Travel date field as well as the total revenue figure.

I don't know anything about creating APEX or codig with classes. If anyone can help me with the sample code, that would be great help.


User-added imageUser-added image
GauravGargGauravGarg
Hi Andrea,

1. First Scenario: Formula Field
To create a formula field on Opportunity Object, we need to first link Contact and Opportunity with Lookup Field of Contact on  Opportunity object with the filter criteria of CRM ID - Account. Now we have Formula field having Agent Name same as Contact Name. 

2. Second Scenario: Revenue Calculation
Write Apex code to identity Opportunity with current year revenue, last year revenue and last to last year revenue. 

3. Third Scenario: Roll Up Summary
The Roll Up Summary worked on master-detail relationship. 
Since we already created lookup Releationship between Contact and Opportunity with Lookup filter. We need to migrate all the opportunities so that every opportunity must be linked to Contact. 
Now, convert the relationship to Master-Details and create Roll Up Summary with required filter criteria. 

Hope this helps!!!

Contact me on below details, if we need to implement this. 

Thanks,
Gaurav
Skype: gaurav62990