Apex Code Development You need to sign in to do that
Don't have an account?
simon chao can a user child query lead object?
Hi,
I am new to salesforce and I am still trying to learn. I have a question regarding child queries.
Within my Leads object I have a field called Lead Status (API name: Status). A user can have many leads, and I was wondering how i can query it so that I know how many lead status = new for a certain user. Basically I want to look into a user that contains a list of lead status = new. thanks.
I am new to salesforce and I am still trying to learn. I have a question regarding child queries.
Within my Leads object I have a field called Lead Status (API name: Status). A user can have many leads, and I was wondering how i can query it so that I know how many lead status = new for a certain user. Basically I want to look into a user that contains a list of lead status = new. thanks.
you can get the data from lead object itself, below is an example to find out the users that created the leads
select name, address, status, CreatedBy.Name, LastModifiedBy.name
from lead
where
status like '%Working%'
and CreatedBy.Name like 'c%'
"Appreciate your feedback"
Hi Raj, that seems to be working. I also have a custom field within the users is it possible to add that field at the end of the select?
Select Id,Status,Owner.Id from Lead Where Status='Converted' AND OwnerId IN ( Select Id, Is_Available__C from User Where Is_Available__C =true)
i tried this but i get an error (unknown error parsing query). Any idea as in why?
Select Id,Status,Owner.Id from Lead Where Status='Converted' AND OwnerId IN ( Select Id from User Where Is_Available__C =true)
Before I was able to query the users with leads that have the correct Lead Status. Now, nothing shows up.
the Is_Available__c is a customized user field, it appears as a checkbox when you a new user is being created.
Select Id,Status,Owner.Id ,Owner.Is_Available__C from Lead Where Status='Converted' AND OwnerId IN ( Select Id from User Where Is_Available__C =true)
^
ERROR at Row:1:Column:30
No such column 'Is_Available__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.
There is a limitation on what fields owner can be accessed, if you want a custom column to be queired then you have a create a customer formula column in the Lead object to refer to the is_available__c column in user, then use the query to get the data
example query
Select Id, name, Status,Owner.Id ,Owner_Is_Available__c from Lead Where Status like '%Open%'
and Owner_Is_Available__c = true
Owner_Is_Available__c is formula column in lead object refering to $user.is_availble__c columns.
"Appreciate your feedback"
Simon,
There is a limitation on what fields owner can be accessed, if you want a custom column to be queired then you have a create a customer formula column in the Lead object to refer to the is_available__c column in user, then use the query to get the data
example query
Select Id, name, Status,Owner.Id ,Owner_Is_Available__c from Lead Where Status like '%Open%'
and Owner_Is_Available__c = true
Owner_Is_Available__c is formula column in lead object refering to $user.is_availble__c columns.
"Appreciate your feedback"
Also, I think I phrased my question wrong from the beginning or made it not clear. I want to query the User object to make sure it is_available__c, and from there I child query the Lead to find each of the lead Id assigned to the individual users, as well as the lead status
Is that possible?
User is refered as who columns in the Lead object and not be as child object, so child to parent relationship may not work in this context.
I dont' quite understand. What do you mean by user is refered as "who columns"?
I also tried the approach you mentioned earlier by creating a custom formula field that references the is_Availalbe__C user field and that didn't work either. Is there another way for me to query the Lead Id, Status of a user who has their is_Available_c custom field checked have?
Lead to user ( actually not parent to child relationship ), there are couple of standard columns like created by id, modified by id, owner etc., default columns reference to user object. for you to query user, there are limited columns that can be accessed as owner object and other fields are not accessible, since is_available__c is customer field in user object, we have to create a formula in Lead object to acess is_available__c. below is an example for this
Select Id, name, Status,Owner.Id ,Owner_Is_Available__c from Lead Where Status like '%Open%'
and Owner_Is_Available__c = true
Note: Owner_Is_Available__c is formula column in lead object refering to $user.is_availble__c columns.
Now, you have changed your question: you want child to parent relation query. you were thinking user is child object for Lead. this is not entire acccurate. though User and Lead object have relationship, this is not child parent relation. the user standard fields are refered in Lead object. so you will not able to write query as child to parent relationship. so, you have to write the query on Lead object and access user custom fields using formula and allowed standard fields as referred in the above query.
I hope this is helpful.
"Appreciate your feedback"
Select Id, name, Status,Owner.Id , Is_Available__c from Lead Where Status like '%Open%' and Owner_Is_Available__c = true
before it was erroring and wasn't able to query it, now it works. thanks
what would be the best practise to assign a lead to a user who has the least lead in which the lead status ='new'?