function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Ted OfficerTed Officer 

Extended formula in Apex(lookup list)

Hi,

 

We ran into an issue where SF can't store more than xxx chars and bytes in the formula. We have a huge lookup list which crucial part of our lead management system.. 

 

It's just a simple thing we need:

If County+State=ChicagoIL, then CountyID=6666

Else if County+State=NapervilleIL, then CountyID=7777

.....

 

So We have this formula:

 

CASE(TRIM(UPPER(County__c))+TRIM(UPPER(MailingState)),
"ALEUTIAN ISLANDSAK","12732",
"ALEUTIANS EASTAK","11844",
"ALEUTIANS WESTAK","9854",
"ANCHORAGEAK","12044",
"BETHELAK","12186",
"BRISTOL BAYAK","12435",
"DENALIAK","10720",
"DILLINGHAMAK","10099",
"FAIRBANKS NORTH STARAK","10403",
"HAINESAK","10576",
"JUNEAUAK","10489",
"KENAI PENINSULAAK","12265",

.................

"WESTONWY","10987","")

 

which 3,000 lines long and somehow we need to implement this as an apex trigger(since SF can't handle big formulas).

 

What would be the best way to accomplosh this? Maybe someone has an example of how it should work as a apex trigger?

 

Any help is appreciated.

 

Thanks

 

MattLacey.ax1065MattLacey.ax1065

With 3000 you're going to struggle a little with some technical limitations.

 

I'd create a custom object which has one field containing the County + State concatenation, and another field storing the corresponding county code — you could then load this via data loader initially.

 

Say it's called CountyLookup__c, you could then do something like this (assuming you're talking about fields on Lead here):

 

trigger AccountBeforeInsertUpdate on Account (before insert, before update)
{
    map<string, string> mapKeyToCode = new map<string, string>();
    
    for(Account sAcct: trigger.new)
    {
        mapKeyToCode.put(sAcct.County__c + sAcct.MailingState, null);
    }

    for(CountyLookup__c sLookup : [select Id, Key__c, Code__c from CountyLookup__c where Key__c in : mapKeyToCode.keySet()])
    {
        mapKeyToCode.put(sLookup.Key__c, sLookup.Code__c);
    }

    for(Account sAcct : trigger.new)
    {
        sAcct.CountyId__c = mapKeyToCode.get(sAcct.County__c + sAcct.MailingState);
    }
}

 

You could also use custom settings for storing the data (list not hierarchy).
Ted OfficerTed Officer

I have very little experience with apex, basically none. So i created custom object 'CountyID', created 2 custom fields 'CountyAndZip' and 'CountyID'.

 

In your code I see it's a trigger Account, should it be trigger Lead if I want this trigger to work for leads?

 

Is CountyLookup__c an object nane(CountyID in my case)?

 

How can I apply your code to fit my situation?

 

Thanks
MattLacey.ax1065MattLacey.ax1065

Here is some modified code, you'll need to check field names etc and it'll also require a class with a test method that inserts a lead

 

trigger LeadBeforeInsertUpdate on Lead (before insert, before update)
{
    map<string, string> mapKeyToCode = new map<string, string>();
    
    for(Lead sLead: trigger.new)
    {
        // check these field names for your org...
        mapKeyToCode.put(sLead.County__c + sLead.MailingState, null);
    }

    for(CountyID__c sCountyId : [select Id, CountyID__c, CountyAndZip__c from CountyID__c where CountyAndZip__c in : mapKeyToCode.keySet()])
    {
        mapKeyToCode.put(sCountyId.CountyAndZip__c, sCountyId.CountyID__c);
    }

    for(Lead sLead : trigger.new)
    {
        sLead.CountyId__c = mapKeyToCode.get(sLead.County__c + sLead.MailingState);
    }
}

 

This should get you off on the right track at least! 
Ted OfficerTed Officer

Thanks Matt!! Within few changes I was able to make it work.

 

trigger LeadBeforeInsertUpdate on Lead (before insert, before update)
{
map<string, string> mapKeyToCode = new map<string, string>();

for(Lead sLead: trigger.new)
{
// check these field names for your org...
mapKeyToCode.put(sLead.County__c + sLead.State, null);
}

for(CountyID__c sCountyId : [select Id, CountyID__c, CountyAndZip__c from CountyID__c where CountyAndZip__c in : mapKeyToCode.keySet()])
{
mapKeyToCode.put(sCountyId.CountyAndZip__c, sCountyId.CountyID__c);
}

for(Lead sLead : trigger.new)
{
sLead.CountyId__c = mapKeyToCode.get(sLead.County__c + sLead.State);
}
}

 

 

 

However, I have no exeprience with testing, what should I do in order to test it?(so i can move this to production)?

 

Thanks
MattLacey.ax1065MattLacey.ax1065

Basically a test method needs to insert data to work on (you should never rely on data in the system as it may not be there!) and then fire actions which will cause your trigger to run... so something like this (same disclaimers as usual) in a new Apex class:

 

public with sharing class TestTriggers
{
    public static testmethod void TestLeadInsertUpdate()
    {
        // insert a county ID object that we'll use for our lead
        CountyID__c sCountyID = new CountyID__c();
        sCountyID.CountyID__c = 'TESTID';
        sCountyID.COuntyAndZip__c = 'TESTTS';
        insert sCountyID;

        // again - check these fields, I'm just going off what I had before!
        Lead sLead = new Lead();
        sLead.County__c = 'TEST';
        sLead.State = 'TS';
        insert sLead;

        // now check that it worked ok, have to reload the fields we want...
        sLead = [select Id, CountyID__c from Lead where Id = : sLead.Id];

        // this says, throw an exception if the field on Lead is not what we expect, vital to ensure our tests are valid
        System.Assert(sLead.CountyID__c = 'TESTTS';
    }
}

 
Ted OfficerTed Officer

Thanks, I was not able to figure out the error:

 


Error: Compile Error: Method does not exist or incorrect signature: System.Assert(String) at line 21 column 9 

 

Line 21 is:         System.Assert(sLead.CountyID__c = 'TESTTS');

 

Thanks again.

 
MattLacey.ax1065MattLacey.ax1065
Oh man,

Stupid typo on my part! Should be == to test for equality, not one = which does assignment. There is also system.assertEquals which takes two parameters to compare, same test but I suppose it prevents silly mistakes like that!
Ted OfficerTed Officer

I got error:

Failure Message: "System.DmlException: Insert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName, Company]: [LastName, Company]", Failure Stack Trace: "Class.TestTriggers.TestLeadInsertUpdate: line 15, column 9 External entry point"

 

Then I adjusted test case to be:

 

public with sharing class TestTriggers
{
    public static testmethod void TestLeadInsertUpdate()
    {
        // insert a county ID object that we'll use for our lead
        CountyID__c sCountyID = new CountyID__c();
        sCountyID.CountyID__c = 'TESTID';
        sCountyID.COuntyAndZip__c = 'TESTTS';
        insert sCountyID;

        // again - check these fields, I'm just going off what I had before!
        Lead sLead = new Lead();
        sLead.County__c = 'TEST';
        sLead.State = 'TS';
        sLead.LastName = 'Lastnamexx';
        sLead.Company = 'Leads';
        insert sLead;

        // now check that it worked ok, have to reload the fields we want...
        sLead = [select Id, CountyID__c, Lastname, Company from Lead where Id = : sLead.Id];

        // this says, throw an exception if the field on Lead is not what we expect, vital to ensure our tests are valid
        System.Assert(sLead.CountyID__c == 'TESTTS');
    }
}

 

 

 

 

and now I'm getting when installing:


Failure Message: "System.AssertException: Assertion Failed", Failure Stack Trace: "Class.TestTriggers.TestLeadInsertUpdate: line 23, column 9 External entry point"

 

Any ideas? Thanks
MattLacey.ax1065MattLacey.ax1065

Hmmm, would appear the code is not doing quite what we want - hence the value of test methods!

 

Before the line with the assert put this in:

 

System.Debug('\n\n\nsLead\n\n\n');

 

This will print the lead out in the log and you'll be able to see the value of CountyID__c.  The '\n' combinations print out a new line (like hitting the enter key) so it'll make this debug line much easier to spot in the log.
Ted OfficerTed Officer

Thanks, I added it but not sure what to do next?

 

Where to see that error/log?


Thanks
MattLacey.ax1065MattLacey.ax1065

Apologies! If you're working in Force.com IDE / Eclipse you can run tests and see the log there. Otherwise, go to Setup -> Develop -> Apex Test Execution.

 

There you can select the tests to run and link through to the logs when they've finished, *or* you can go to Apex Classes and hit the Run All Tests button at the top.

 

Good luck!
Ted OfficerTed Officer

Thanks, here is the log

 

http://s175432170.onlinehome.us/apex.log

 

I see the debug gave "sLead' string, not sure what that means? shouldn't it give me the value instead of what we entered?

 

Thanks
MattLacey.ax1065MattLacey.ax1065

Humble apologies, my mistake again! I keep doing silly things, and because you're not a dev you don't know it! Clearly the perils of coding in a forum :) 

 

System.Debug() logs strings out to the log - because we put sLead inside the string it just logged that literal value, not the value of the variable (i.e. the lead).

 

Do this:

 

System.Debug('\n\n\n' + sLead + '\n\n\n');

 
Ted OfficerTed Officer

Thanks again

 

this is the log

 

http://s175432170.onlinehome.us/apex2.log

 

I was looking at but I seems slead.countyid is =testid, while it should be TESTTS? I cant see where the issue is
MattLacey.ax1065MattLacey.ax1065

Hopefully this is the last silly mistake! Coding my proxy clearly has it's challenges, especially as I can't run and test the code myself.

 

When we insert the code, the county ID is in fact TESTID:

 

 

// insert a county ID object that we'll use for our lead
CountyID__c sCountyID = new CountyID__c();
sCountyID.CountyID__c = 'TESTID';
sCountyID.COuntyAndZip__c = 'TESTTS';
insert sCountyID;

 

We use a map of County + State to get the ID:

 

sLead.CountyId__c = mapKeyToCode.get(sLead.County__c + sLead.MailingState);

 

I got a bit mixed up writing the text method, and wrote in the County & State combo not the ID we're expecting in the assert statement. So after all that, the assert is what's wrong! It should be:

 

System.Assert(sLead.CountyID__c == 'TESTID');

 Apologies for that, but at least we know the code works now! 
Ted OfficerTed Officer

Thanks a ton! really help us out here.

 

One more question, can i trigger it to run for all existing leads? so it updates countyid based on this trigger?
MattLacey.ax1065MattLacey.ax1065

Depends on how many you have - if you've got around a thousand or so then the easiest solution is to just run something like this in the System Log window (unless you've got a few thousand you shouldn't have issues with governor limits).

 

for(Lead [] leads : [select Id from Lead])
{
  update leads;
}

 

 

Otherwise, I'd say the easiest way is to export all Leads using data loader, then do an update against them using the retreived CSV (don't change any fields of course). The idea is that by firing an update the trigger will run and do it's magic. You could also write a batch apex class, but the less code the better!