+ Start a Discussion
Nerv123Nerv123 

Updating a Lead field based on conditions and join with a custom table.

Another newbie head-scratch!
 
Please help!
 
I have four fields in Lead object, TotalScore__c, Sector__c, State and ISR__c
I have three field in a table called ISR, namely, ISRSector__c, ISRState__c and ISRName__c
 
I need to write a Scontrol which has the following logic,
 
If the TotalScore__c > 5, then join the two tables based on the equality of Sector and State. Using the resultant record, set the Lead.ISR__c = ISR.ISRName__c
 
How can I achieve this?
 
Thanks In Advance!
Mike
jrotensteinjrotenstein
Hello Mike,

Yes, this is possible to do in an S-Control, using a Query and some Javascript logic. The basic flow would be:

Code:
(This is in pseudo-code)

if {!Lead.TotalScore__c} > 5
{
  Query: select ISRName__c
         where  ISRSector__c = {!Lead.Sector__c}
                and ISRState__c = {!Lead.State}

  Set Lead.ISR__c = Result of query
}

 
However, the most important question is WHEN would you like this calculation to occur?

An S-control only runs when a record is displayed for a user. So, the ISR would only get updated when somebody is viewing the Lead record. If you need it to be updated at other times (eg it appears in a report), you should consider creating an APEX trigger that fires when TotalScore, Sector or State change.

Also, if you do choose to use an S-Control, be aware that the code would run every time the Lead is viewed, so it is effectively recalculating the ISR each time. This might slow down your views and cause unnecessary updates. There are several ways to avoid this. One that springs to mind is:
  • Create a Workflow that nulls the ISR field whenever TotalScore, Sector or State change
  • Have the S-Control recalculate the ISR field only when it is null
Please tell us more about the situation in which your ISR needs to be recalculated and we might be able to offer more advice. If you do want the S-Control to run as you described, I'd be happy to provide some sample code that should work.
Nerv123Nerv123

Hello John,

Thank You So Much for your response, and especially, the offer for sample code! For the current situation, I do think it should be a trigger.

I think if the Scontrol worked only if the ISR is not null is a good idea. Can you describe how to write either case, whether Scontrol or trigger, please, so that I can try to follow the difference in coding involved in both situations.

Thanks ,

Mike

jrotensteinjrotenstein
Alas, Apex is more complex and requires a bit more than just copying and pasting code (eg understanding your sandbox environment and knowing how to migrate code).

Take a look at Chapter 4 in the Force.com Cookbook and you'll see a seciton "Bulk Processing Records in a Trigger" which will give you the framework for your trigger and for learning how to setup an Apex development environment. If you need help, pay a visit to the Apex Code Development forum.