+ Start a Discussion
elossoelosso 

Getting the quantity of product opportunities with a revenue schedule for each months

Hello everyone!

 

I'm new to salesforce and need some help recognizing sales and tracking inventory with Products that have sales/quantities spread across numerous months.

 

Here is my problem: Each Month we can only sell 25 units. I'm trying to track the number of units we've sold, and project which months still have available units. 

 

Note: Each Opportunity Project is 1 unit. However, I'm having difficulty when it comes to Opportunity Projects that have a Schedule assigned to them. For example, one of the products we provide is a 3 month subscription. This means we sell 1 Unit for each month: (if we sell the subscription on 1/1/13, this accounts for 1 unit in Jan, 1 unit in Feb, and 1 unit in March)

 

I would like to know if it is possible to get the quantity of product opportunities for each month specifically for those products that use revenue and quantity schedules. I want to know how many Opportunity Products are active in each month. 

 

Please let me know if this makes any sense. ABSOLUTELY any help would be so sincrerly appreciated!

 

Thank you!

imutsavimutsav

I guess you need a separate Object to track this. This Object will have a product field, Opportunity(lookup), created Date, start Date, end date. Then you need to create a trigger on Opportunity. If the Stage of the Opportunity is 'Closed Won' then this trigger will be fired and would create go through all the lines in the Opportunity and would create separate records in your new Object. Eg. 2 products in the Opportunity will create 2 records in our new Object. If the line is subscription then it will populate start date and end date.(use get months function and create as many records as the no. of months, with each created date as the start of that month.) Trigger will be on before update.

 

Thanks
Utsav

 

[Do mark this answer as solution and give Kudos if this helps you resolve the issue]

elossoelosso

imutsav, could you please explain the trigger... I've never written APEX code before, could you help me wrap my head around it? I really appreciate your help!

elossoelosso

Imutsav, could you please be so kind show list out the fields of the new Object?

 

So far I have:

 

Subscriptions (This is the object)

------------------

Product

Created Date

Start Date

End Date

Opportunity <--- this is what I'm getting confused about. What is the Data Type of this object?

imutsavimutsav

Hi,

 

My appologies for not replying you fast. Anyways you need to create a new object say Oppotunity Tracker(you can call it anything).

Fields : Product (text or lookup to the product table), order start date(date type), order end date(date type), order type(picklist with two values-one time or subscription) and Opportunit(lookup to the Opportunity). 

 

Create a new field(if you don't have it yet) to check the type of the Opportunity(picklist with two values-one time or subscription)

 

I need to know if you guys store the Opportunity product in Opportunity(custom field on Opportunity to store the product) or in the standard Products(child object) on Opportunity.

Anyways you can write a trigger on Opportunity (after update). You don't want to track/report the opportunity/produt unless or until it is 'closed won'. So you don't want to write a trigger for before/after insert. In this after update trigger,

a) you will check if the stage of the opportunity is 'closed won' 

b) if closed won then query all the products related to this Opportunity. (Query on the Opportunity Line Item where OpportunityID = this Opportunity).

check if the Opportunity Type is one time or subscription.

if one time then

 

c) create a new record of Opportunity Tracker(new Object).

OpportunityTracker oTracker = new OpportunityTracker();

oTracker.Name = product.Name; --- correct the syntax and get this value of the product from the Query on Opportunity Line)

oTracker.orderStartDate = date.today();

oTracker.orderEndDate = date.today() + 30;

oTracker.orderType = Opportunity.opportunityType;

oTracker.opportunity = OpportunityId;

}

insert oTracker;

if subscription

a) create a loop to go for 3 times.

List<OpportunityTracker> opptTrackerLists = new List<OpportunityTracker>();

{

for(i=0;i<3;i++) {

OpportunityTracker oTracker = new OpportunityTracker();

oTracker.Name = product.Name; --- correct the syntax and get this value of the product from the Query on Opportunity Line)

if(i==0) {

oTracker.orderStartDate = date.today();

} else if(i==1) {

oTracker.orderStartDate = date.today() + 30;

} else {

oTracker.orderStartDate = date.today() + 60;

 

}

oTracker.orderEndDate = oTracker.orderStartDate + 30;

oTracker.orderType = Opportunity.opportunityType;

oTracker.opportunity = OpportunityId;

opptTrackerLists.add(oTracker);

}

insert opptTrackerLists;

 

 

 

I suppoe this would help you. Let me know if you have any question.

 

Thanks

Utsav

 

[Do mark this answer as solution and give KUDOS if it helps you]