Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
sdetweil

# calculated field on Case, dependant on multiple soql results

so, we have this algorithm we developed on our legacy system that helps us calculate the relative priority of customer cases.

we have a large backlog being worked all the time, and its hard to tell which one to select from the list. The customer entered priority is not enough.

So, we have this 7 part algorithm that takes cues from other parts of the  customer relationship

1. how much pending new business is at risk

2. the age of open cases of each severity (1-4) (the older it gets the higher its priority becomes) for THIS SAME product

3. the size of the sum of their maint contract value (higher means more)

4. what support offerings they have contracted for.. there are potentially15, each has a value..

5. whether they have opened a mgmt review (we call it escalation) of their support service

6. how much contract renewal might be at risk.

7. the priority of existing open cases.

today we calculate this once a day for every case. mostly because we have to extract a bunch of data from different systems,

get it into one place, then do the calcs, then take the output and expose it to the case mgmt UI for the user.

with SF, all this data will be locally available now.

this would be 'easy' in an apex class, but seems pretty hard in a calculated field.

after all that, this really sounds like NOT a calculated field, but really the output of a timed job that runs thru all the issues and set this field.

any other intersting suggestions?  new to sf..

thanks

sfdcfox

Formulas are rather limited in terms of compiled size/character limits. You would find it difficult to implement a process this complex while working within those bounds. I would probably recommend a blended approach; you can use a combination of formula fields, workflow rules, rollup summaries, and triggers to implement your system logic.

For example, 1, 3, 4, 5, 6, and 7 could probably be calculated through rollup summaries and/or formula fields, and 2 could be implemented with delayed workflow rules. In theory, I don't see any reason why you'd need to use Batch Apex at all, and I can envision a setup that uses only native features (no Apex Code or Visualforce at all). Of course, I don't have "all" the details, so I might be missing something here. Just take a look at the features I mentioned before you assume that you need to write code.

roseann

I have a very similar challenge as you have described to apply our case scoring model.     Curious to know more specifics about the solution you implemented to solve this?   Thanks in advance !

Steve :-/

Hi Roseann,

I just posted a possible solution for you on your other post