0

I'm trying to identify Opportunity products from the same Opportunity and the same product that spans 12 consecutive months based on start date/end date fields. I can easily tell if one line is 12 months but when you look at multiple lines that are less than 12, how can you identify the ones that are consecutive and then ensure that the grand total is 12?

An individual opportunitylineitem doesn't know about the related oli records it only knows itself, so I'd have to loop through them all one at a time but how can you keep track that one product has 6 months and another product has 6 months and they are next to each other to make it consecutive?

I made this chart in excel to help me identify the use cases. In this example Product A, B, D all span 12 months which I want to flag but product C has a gap so that shouldn't meet the criteria. I'd want to flag and identify the A, B, D lines so that I can do some things later.

enter image description here

Is there a way to figure out if different things are consecutive?

1 Answer 1

1

The good news: There is
The bad news: There isn't a built-in class to do this. You'll need to work out your own algorithm

The simplest thing I can think up right now would be:

  1. ORDER BY StartDate (in a query, obviously) so you don't need to worry about ordering things yourself in Apex (based on the StartDate)
  2. Build a Map<OpportunityLineItem, List<OpportunityLineItem>> so you can separate and hold individual OLIs for each OppId-PBEId pair
    • Using an SObject as a map key is a fantastic way to easily (and quickly) perform multi-field comparisons
    • You just need to be careful to never alter the object instance you use as the key after you put it into the map
  3. Iterate over the keyset of that map, with a nested loop over the list it contains as a value.
    • Declare and initialize a counter to 0
    • Declare a Date variable to later track the end date
  4. In the inner loop
    • if the end date is null or not in the same month as the current start date, zero the counter
    • figure out the number of months between the current start and end dates, and add it to the counter
    • If your counter is >= 12, congrats! you found a consecutive 12-month period. Record that OLI somewhere, or set a field, or something. Then break; to get out of the inner loop
    • Set the end date variable (declared in the outer loop) to the current end date. This is the end of the inner loop

By ordering by the StartDate we only need to worry about whether or not the current StartDate matches with the previous EndDate (as opposed to needing to search the rest of the list for a matching EndDate). If there are any gaps, we can simply start over in trying to find a 12-month run.

Declaring the count and date variables in the outer loop helps to prevent issues where you forget to clear the variable. It ensures that each new List of OLIs that you iterate over has a clean slate to work with.

The downsides there are that this is technically O(n^2) due to the nested loop (but realistically, it should behave closer to O(n)), and it doesn't handle overlapping dates.

The main point is that it is possible. I would expect that there is a more efficient approach out there, and you may need to make adjustments to fit the requirements you have.

Breaking down a problem into smaller sub-problems like this is an important skill for programmers. "This would be easier to figure out if X" is usually a good place to start ("X" here being "the OLIs were already in ascending date order").

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.