How can I get the name of the Lead Owner in a Lead custom formula field?

Question

I've got an application that reads Lead records from Salesforce via the API and I want to link the Lead Owner field to an attribute in the application. The Lead Owner field doesn't up in the list of available fields but all the custom fields do.

So, my first attempt at a solution was to create a custom field that displayed the Lead Owner name. In the SF formula editor, as far as I can tell, it doesn't display the actual data field but instead displays the ID string. Which is pretty meaningless in the context that I need it for.

alt text http://skinny.fire-storm.net/forposting/insertfield.JPG

Is there a way that we can get at the data in the object that the ID string references?

alt text http://skinny.fire-storm.net/forposting/havewant.JPG

I have the RED BOX but need the GREEN BOX.

EDIT: I can't change the application code that calls the API. I can only change salesforce. So, this is more of a salesforce superuser / formula-writer question, not a question about writing code that calls the SF API.

Christhood

Answer 1

Salesforce allows access to related data through what they call relationship queries. Instead of joining, you specify the query like this:

System.debug([SELECT Owner.Name FROM Lead WHERE Id = '00QS00000037lvv'].Owner.Name);

Try running that in the system log, just replace the lead ID with one that you're looking at.

When accessing the data through the API, the principle is the same, your proxy objects should allow you to access Lead.Owner.Name.

EDIT:

I agree with eyescream, since you can't change the application code, creating an Apex trigger would be the best way to go here. Here's some code to get you started:

trigger Lead_UpdateOwner on Lead(before insert, before update)
{
    Map<Id, String> ownerMap = new Map<Id, String>();
    for (Lead lead : Trigger.new)
    {
        ownerMap.put(lead.OwnerId, null);
    }

    if (ownerMap.size() > 0)
    {
        for (User[] users : [SELECT Id, Name FROM User WHERE Id IN :ownerMap.keySet()])
        {
            for (Integer i=0; i<users.size(); i++)
            {
                ownerMap.put(users[i].Id, users[i].Name);
            }
        }
        for (Lead lead : Trigger.new)
        {
            lead.OwnerName__c = ownerMap.get(lead.OwnerId);
        }
    }
}

lead.OwnerName__c would need to be the name of your custom field on the lead object that will hold the owner name. Type Text, length 121.

Subsidize

That would work, except I can't change the code that calls the Salesforce API. I can only change Salesforce. I have should have specified that in my original post. – Christhood

I don't have apex triggers in my corporate SF account, but I'm working on getting them! Thx. – Christhood

Answer 2

I had a similar problem, but wanted all the current and future User fields available. Since a custom lookup field to the User is not restricted by formula fields, I created one named OwnerLookup on the Opportunity and Account objects, then used a triggers to populate it on creation or edit. For example the Opportunity trigger is this:

trigger OpportunityTrigger on Opportunity (before insert, after insert, before update, after update) {
    if(trigger.isBefore && trigger.isInsert) {
        OpportunityTriggerHandler.newOpportunity(Trigger.old, Trigger.new);
    }    
    else if(trigger.isAfter && trigger.isInsert){
        //OpportunityTriggerHandler.futureUse(Trigger.new);
    }
    else if(trigger.isBefore  && trigger.isUpdate){
        OpportunityTriggerHandler.updateOpportunity(Trigger.new, Trigger.oldMap);
    }
    else if(trigger.isAfter && trigger.isUpdate){
        //OpportunityTriggerHandler.futureUse(Trigger.new, Trigger.oldMap);
    }
}

and the OpportunityTriggerHandler class (Apex code) is:

public with sharing class OpportunityTriggerHandler {
    public static void newOpportunity( List<Opportunity> oldOpportunitys, List<Opportunity> newOpportunitys ) {
        for (Opportunity opp: newOpportunitys) {
            updateOwnerData( opp );
        }
    }

    public static void updateOpportunity( List<Opportunity> oldOpportunitys, Map<Id, Opportunity> newOpportunitys ) {
        for (Opportunity opp: oldOpportunitys) {
            updateOwnerData( opp );
        }
    }

    public static void updateOwnerData( Opportunity opp ) {
        opp.OwnerLookup__c = opp.OwnerId;
    }
}

I then create Formula fields on the Opportunity/Account objects to get to any of the owner (User) object fields, such as Oppty Owner Name formula field:

OwnerLookup__r.FirstName & " " & OwnerLookup__r.LastName

Arctogaea

Answer 3

VLOOKUP function would be a good try, but

• it's available only in validation rules, not in field definitions
• it can be used only on custom objects and you need data from User

I'd say you need to query from your application for

SELECT Owner.FirstName, Owner.LastName FROM Lead

Other than that... some "after insert, after update" trigger that would populate your custom field when owner changes?

Kurr

Answer 4

Just posting for completeness sake (and for the Google searches): The issue arises with any object that can be queued, not just Lead, since the source of it is that the owner can refer to either a user (as usual) or a queue object.

This can be resolved using a formula field instead of triggers, like below:

BLANKVALUE(Owner:Queue.QueueName, Owner:User.FirstName & " " & Owner:User.LastName)

Basically, the BLANKVALUE function in the formula checks whether the owner.queuename is blank, and if so gives the name of the user.

Soapberry

OMFG you life saver!!! This solution is even better than using APEX – Bohun