3

I have a list of Custom Objects, created by customers each day - they can be considered like trouble tickets. Against each one, is a Task created when our agents make an outbound call back to the person. This is through the Salesforce Phone CTI.

I want to list each NEW Custom Object record crated TODAY, and list beside each record: Blank if no tasks done (ie no one called the customer) Username and call time if a call has been made

I have the following:

SELECT Id, Owner.Name, Subject
from Task
where Whatid in (SELECT Id 
FROM Registration_Object__c 
WHERE (CreatedDate = TODAY)
AND RecordType.Name = 'Media Registration')

which lists the details I want, but its limited to only those Media Registrations that have a task associated (ie the ones with no task yet aren't listed with blanks)

In SQL its a join that would do it but I'm new to SOQL, so can't figure out the relationship for the query, or if maybe it needs to be 2 queries.

I've tried a few permutations of

Select id, Tenant_Name__c, Tenant_Phone1__c, (SELECT Id, Owner.Name, Subject from Task)
FROM Registration_Object__c 
WHERE (CreatedDate = TODAY
AND RecordType.Name = 'Media Registration')

which I know is wrong.

I should have the subquery related back to the Id from the main Select in some format such as:

...where WhatId in (the Id from the main query)

Or should I do it as 2 queries? (pull all Ids created Today, then pull all Tasks with a WhatId in that list?)

Thanks

2 Answers 2

5

If you use the Child Relationship Name Tasks you should be able to do it in one query.

Example using Account:

SELECT Id, Name, (SELECT Id, Subject FROM Tasks) FROM Account WHERE CreatedDate = TODAY

0

I don't see any problem with the second query you make

Select id, Tenant_Name__c, Tenant_Phone1__c, (SELECT Id, Owner.Name, Subject from Tasks)
FROM Registration_Object__c 
WHERE (CreatedDate = TODAY
AND RecordType.Name = 'Media Registration')

If we assign the query to list of Registration_Object__c we can show the list of Registration records and either a blank or list of task records as below

<apex:repeat value="{!listRegistration}" var="reg">
    <apex:outputText value="{!reg.Tenant_Name__c}"/>
    <apex:repeat value="{!reg.Task}" var="t" rendered="{!IF(reg.Task != NULL && reg.Task.size > 0,true,false)}">
        <apex:outputText value="{!t.Owner.Name}"/>
        <apex:outputText value="{!t.Subject}"/>
    </apex:repeat>
</apex:repeat>

Hope it helps.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .