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
Best Answer
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