AddThis Feed Button

Recent Items

I was configuring DataLoader to export a list of Opportunities, and I went to select the “Owner Name”. However, only OwnerID is available on an Opportunity.

“No problem!” I think to myself, as I go and create a Custom Field with a formula equal to Owner.Alias.

“What?” I say in surprise. “It won’t let me access a field on the Owner object!”

NoLink

Mmm. This is strange. Then a Google Search reveals an 18-month old Ideas request to Make “Owner Id” Look up fields available for formulas.

Oh dear.

Well, that’s a shame, but it’s easily solved! I created:

  • A field called Owner_Link__c of type Lookup(User)
  • A Trigger to copy OwnerId to Owner_Link__c when the Owner is changed
  • A test for the Trigger

Trigger:

trigger Update_OwnerLink_on_Owner_Update on Opportunity (before update, before insert) {

  // When 'Owner' field is changed, update 'OwnerLink' too

	// Loop through the incoming records
	for (Opportunity o : Trigger.new) {

		// Has Owner chagned?
		if (o.OwnerID != o.Owner_Link__c) {
			o.Owner_Link__c = o.OwnerId;
		}
	}
}

Test:

public with sharing class TriggerTest_OwnerLink {

	static TestMethod void testOwnerLink() {

		// Grab two Users
		User[] users = [select Id from User limit 2];
		User u1 = users[0];
		User u2 = users[1];

		// Create an Opportunity
		System.debug('Creating Opportunity');
		Opportunity o1 = new Opportunity(CloseDate = Date.newInstance(2008, 01, 01), Name = 'Test Opportunity', StageName = 'New', OwnerId = u1.Id);
		insert o1;

		// Test: Owner_Link should be set to user 1
		Opportunity o2 = [select id, OwnerId, Owner_Link__c from Opportunity where Id = :o1.Id];
		System.assertEquals(u1.Id, o2.OwnerId);
		System.assertEquals(u1.Id, o2.Owner_Link__c);

		// Modify Owner
		o2.OwnerId = u2.Id;
		update o2;

		// Test: Owner_Link should be set to user 2
		Opportunity o3 = [select id, OwnerId, Owner_Link__c from Opportunity where Id = :o2.Id];
		System.assertEquals(u2.Id, o3.OwnerId);
		System.assertEquals(u2.Id, o3.Owner_Link__c);
	}
}

This then gave me a new Owner object on my Opportunity on which I could create Formulas:

Result

I could also use it in the DataLoader by referring to Owner_Link__r.Alias.

Hooray!

Easy to solve, but it’s a shame it was necessary.

The Bottom Line

  • Formulas can’t access Opportunity.Owner fields
  • Create a ‘shadow’ field to hold Owner and populate it via a Trigger

5 Responses to “Making Owner available in Formulas”

  1. Niels Says:

    Hi again John,

    since I like your blog and keep finding useful stuff here, I’m happy to share with you a small improvement I did to your code. The test method failed on my org, since it was using an inactive user. Therefore I modified line 6 like this:

    User[] users = [select Id from User where Isactive= True limit 2];

    Now it works fine. May be you want to publish this.

    Thanks again,
    Niels

  2. Gauravp Says:

    I tried your technique and could access and insert Owner name in the formula field. However, no values are being returned when I test it out. Anything I am doing wrong? attached link has the screenshot. http://tinypic.com/r/2qbgcvk/5

  3. The Enforcer Says:

    I’d have to see your code or data to know what’s going wrong.

    I’d suggest writing some code via the System Log to test what’s happening, much along the line of the tests. Try and retrieve a record and look at the value of the link.

    Boy, it’s been so long since I wrote that blog that I don’t even remember writing it! :)

  4. Evgeny Says:

    Also I’d recommend to update the code with check out if Owner is User (not Queue)
    Other wise trigger will return error: “data changed by trigger for field Owner_Link: id value of incorrect type”

    Here is a code which could be used for this:

    //Owner is User
    if(o.owner.type==’User’) {
    // Has Owner chagned?
    if (o.OwnerID != o.Owner_Link__c) {o.Owner_Link__c = o.OwnerId;}
    }

  5. SFDC consultant - Evgeny Says:

    Here is a final version which works for custom object

    trigger Project_Update_OwnerLink on Project__c (before Insert, before Update) {

    // Loop through the incoming records
    for(Project__c p:trigger.new) {
    //Owner chagned
    if (p.OwnerID != p.Owner_Link__c) {
    // New Owner is User (not a queue) – update the field
    if(((String)p.OwnerId).substring(0,3) == ’005′ ){
    p.Owner_Link__c = p.OwnerId;
    }
    else{
    // New Owner is Queue – clear out the field
    p.Owner_Link__c = null;
    }
    }
    }
    }