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
JoyDJoyD 

How to query custom field on Event Owner?

I am running a query on the Event object, and it's working until I tried grabbing a custom field on the User who is assigned (the Owner).  I already had standard fields pulling from the Owner, so not sure why it's not working for the custom field.  I pulled my naming from the schema browser a few different times, just to be sure...

 

Here's my query:

 

tempAppts = [SELECT WhoId, WhatId, OwnerId, Owner.FirstName, Owner.Profile__c, Owner.Name, Subject, StartDateTime, Result__c
	FROM Event
	WHERE WhoId = :contactID
	AND StartDateTime >= :yaynow
	AND Result__c != 'Rescheduled'];

OwnerId, Owner.FirstName, and Owner.Name work fine, but once I put in Owner.Profile__c I get an error on save.  I have tried OwnerId.Profile__c, Owner__r.Profile__c, etc.  The error message is strange as well, references "Name" instead of "Owner":

 


Save error: No such column 'Profile__c' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

 

 

mikefmikef

Looks like a bug, I would file a Salesforce Support ticket. I tested in the explorer and can repo this issue.
JoyDJoyD

Mike, you can or can't reproduce this issue, just so I am sure what you mean?
mtbclimbermtbclimber

While it certainly seems like a bug it's actually a feature request of sorts. (though I can absolutely see how you feel otherwise). I will explain.

 

Normally the referenceTo property on a relationship field is a good signal for whether a relationship is polymorphic or not. However, this particular relationship: Event.Owner defined by the Event.OwnerId field *is* polymorphic, i.e. it can be related to multiple types of objects, even though we only report a single value in the referenceTo array property. For example if you call this in Apex, you will receive only one value for "User":

 

 

System.debug('ReferenceTo: ' + Event.OwnerId.getDescribe().getReferenceTo());

 That, however is not the complete story for whether the relationship points to a single object, like User, or the "Name" object which we use to provide a mechanism to always access the name value for a particular relationship regardless of which type it is in a polymorphic relationship. To know if the relationship points to the "Name" object instead of the single type declared in the referenceTo property you need to look at the field's "namePointing" property. In the case of Event.OwnerId this value is "true". Running this code will show that:

 

 

System.debug('NamePointing: ' + Event.OwnerId.getDescribe().isNamePointing());

 

You are probably wondering how this is polymorphic if we only report the "User" object in the referenceTo. Well that's where the "feature request" comes in. In the system an Event can be owned by a User OR it can be owned by a Calendar (Setup | Customize | Activities | Public Calendars and Resources). This is how resource calendars are defined in salesforce.  Unfortunately the Calendar entity is not API exposed, though events owned by them can be.   If you create one such event and query for it like this:

 

 

SELECT Subject, OwnerId, Owner.Name FROM Event

 

You'll see the name of your calendar and the ownerId will start with the '023' prefix vs. '005' as would be the case for a User.

 

As for the actual question here, you'll have to issue a second query to get the user detail you are after unfortunately.

 

Hope that helps.

 

 

 

 

 
JoyDJoyD

Thanks for the reply.  Don't think I would have EVER figured that out on my own.

 

But now I'm wondering, what's the best practice for doing a double query like that and being able to simply access that information?  (I'm still learning)  

 

Obviously, I can run through the List results from the Event query, and then for each item, query for each Owner (User)...but isn't that a little heavy?  Is there a way to 'bulkify' a query in that manner so it's just one query and not one for each Event?

 

And then how do I tie that back to the corresponding Event (I'm already using a wrapper class on the Event)?
mtbclimbermtbclimber

Sure. Glad I could help clear this up. Sorry it's so opaque.

 

The best practice is the same as managing for bulk in a trigger. I would process the results in this fashion:

 

 

  • Iterate through the events creating a map of userID => collection of events (or your wrapper instances) owned by that the user (key)
  • issue a single query using the keys from the map with an "IN" clause to get the custom field value(s) for each of the unique ids found
  • Iterate through the results of the query and for each user, apply the custom field value(s) against the respective list of events accordingly

 
JoyDJoyD

I hate to keep asking what are probably basic questions, but I searched and searched the SOQL documentation and couldn't find anything about how to use a Map with the IN clause.  I played around and used a List of ID's instead, but it's quite convoluted.  Could you perhaps point me to documentation regarding how to use the Map in a SOQL statement?

 

(I also had problems putting the ID onto the Map without a User, but since I wouldn't get the User till in the later query, I wasn't sure how to handle...tried just throwing a dummy User in there but got stuck once I got to the SOQL)

 

Here is my code with my weird workarounds using a List instead of Map...any critiques would be welcome as I'm still figuring this all out :-)

 

 

Integer i;
i = 0;
List<ID> ownerIds = new List<ID>();
        	
//go through all events and grab all Owners
for(Event t:tempAppts){
	if (ownerIds.isEmpty()) {
        	ownerIds.add(t.OwnerId);
        }
        else {
        	Boolean found = false;
        	for(ID i2:ownerIds){
        		if (!found && (i2 != t.OwnerId)) {
        			ownerIds.add(t.OwnerId);
        			found = true;
        		}
        	}
        }
}
        	
//query for all User info
List<User> tempOwners = new List<User>();
tempOwners = [SELECT FirstName, Name, Profile__c
		FROM User
		WHERE ID IN :ownerIds];
        	
//for each Event, grab the User info from the query, then create an ApptWrapper
//and add to the appts list
for(Event t:tempAppts){
	User u = new User();
        Boolean foundIt = false;
        for(User u2:tempOwners){
        	if (!foundIt && (u2.ID == t.OwnerId)) {
        		u = u2;
        		ApptWrapper aw = new ApptWrapper(t,i,u);
        		appts.add(aw);
        		i++;
        		foundIt = true;
        	}
        }
}
numAppts = i;

 

 
achukachuk

It is great that Sales Force designed the product with concept of polymorphism in mind... Unfortunately it looks like the person who put the design together did not finish reading _that_ book about object-oriented programming :-)

 

This particular design together with all kind of limitations in the system make Owner field quite useless for an enterprise end-user.

 

Lets say I got stuck with a simple request from the business - get report on tasks completed by region (a custom field on User object)

Two most obvious approaches will not work:

1) Reporting - there are no real joins in Sales Force, so "normal report" will not work (I wonder if "advanced" report engine will ever resolve this problem)

2) "second best" - add a "formula" field - but one would need a lookup function (something that would accept ID, object name, and field name and return value) - there is no such thing

 

So there is a need to create a custom field and maintain copy of the data from user object in task object... with all apex trigger limitations it will be real fun to maintain

 

If anybody can think about a better approach - please share

 

 

 
mtbclimbermtbclimber

Sorry for the delayed response. 

 

I was eluding to something more like this:

 

 

public class EventUtils {

    public static List<EventWrapper> getEventsWithOwners(List<Event> eventList) {
        /* The map structure to use to aggregate the events by user */
        Map<Id, List<Event>> userEventMap = new Map<Id, List<Event>>();
        
        String userKeyPrefix = User.SObjectType.getDescribe().getKeyPrefix();
        
        /* go through the events passed in, skip tasks not owned by 
           an actual user, adding them to the map for the given ownerId. */
        for(Event e:eventList) {
            String oid = e.ownerId;
            if(!oid.startsWith(userKeyPrefix)) { continue; }
            
            if(userEventMap.get(oid) == null) {
                userEventMap.put(oid, new List<Event>());    
            }
            
            userEventMap.get(oid).add(e);
        }
        
        /* The return object */
        List<EventWrapper> eventUserList = new List<EventWrapper>();
        
        /* Get the users for each key in the map, iterate through them
           and construct the relevant EventWrappers */
        for(User u:[SELECT ProfileId FROM User WHERE Id IN :userEventMap.keySet()]) {
            for(Event e:userEventMap.get(u.id)) {
                eventUserList.add(new EventWrapper(e,u));
            }    
        }
        
        return eventUserList;
    }

    public class EventWrapper {
        public Event event { get; set; }
        public User owner  { get; set; }
        
        public EventWrapper(Event e, User u) {
            event = e;
            owner = u;
        }
    }
}

 

 
mtbclimbermtbclimber

 

achuk wrote:

1) Reporting - there are no real joins in Sales Force, so "normal report" will not work (I wonder if "advanced" report engine will ever resolve this problem)

 

No, there is no standard report that will do this and I do not believe there are plans to change it in the near future.

 

 

achuk wrote:

2) "second best" - add a "formula" field - but one would need a lookup function (something that would accept ID, object name, and field name and return value) - there is no such thing

There are open asks (ideas) around exposure of owner as a traversable relationship in spanning formulas which is effectively the solution you are suggesting.  No ETA on that currently either, I'm afraid.

 

 

achuk wrote:

 

So there is a need to create a custom field and maintain copy of the data from user object in task object... with all apex trigger limitations it will be real fun to maintain

 

If anybody can think about a better approach - please share

 

I can't think of one currently but as I consider the use case you've described I'm not sure the limitations you are referring to come into play necessarily. Arguably you only want to set the user data on the task when it's "Closed" anyway. If a user moves to a new region should the "credit" for the task closing follow him/her?

 

Your point is valid generally, but I wonder if in this case you'd actually use something else anyway.

 
Max ShirazMax Shiraz

If you have a look at the Enterprise WSDL you can see that the Event.Owner is not a User object but actually a Name object.  (type=ens:Name). 

 

This Name object is an object that you can't directly query in SOQL but if you look at the WSDL definition of Name you can see that its got various *standard* fields from the User (Alias, Email, Name, ProfileId etc). So you might get some standard fields that look like User fields, but it's not a User, so trying to get custom fields clearly won't work.

 

You can get to a User record - and all the fields it holds - via the Event's Account Owner using something like this:

 

select Account.Owner.Name, Account.Owner.MyCustomeField__c from Event

 

So if you know that Event and Account have the same owner then you're good.
JmthibeaJmthibea
As achuk suggested...

It may be simpler to add a custom formula field on Activity Object and user this as the formula:

owner:user.custom_Field_Name__c

Now you can use it directly in your SOQL query without having to do extra processing with code (and way easier to maintain or change).
Padmavathi RudraPadmavathi Rudra
Please try Owner.UserRole.Name for getting owner role from Events object.It worked for me.
Here is my soql query- select id,Owner.Name ,Owner.UserRole.Name ,subject from Event