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
bpl3792bpl3792 

Trying to aggregate columns in a SOQL query. Is this possible?

The query I wrote is this...

Select id, License_Certificate_Number__c, Type__c, Start_Date__c, Expiration_Date__c, State__c,
(Select id, License_Certificate_Number__c, Type__c, Start_Date__c, Expiration_Date__c, State__c
from Qualification__c where Provider__c='00540000000x5ha' and RecordTypeId='01230000000DHlx'),
(Select id, License_Certificate_Number__c, Type__c, Start_Date__c, Expiration_Date__c, State__c
from Qualification__c where Provider__c='00540000000x5ha' and RecordTypeId='01230000000DHmR')

 I'm trying to get it to act similarly to this query in SQL....

SELECT a.name,a.Region__c ,rt.Name, 
 qd.Type__c DEAType ,qd.State__c  ,qd.License_Certificate_Number__c DEANumber ,
c.Name,c.GlobalProviderID__c
, qs.Type__c StateLincType,qs.License_Certificate_Number__c StateLinc,qs.State__c ,
qb.Type__c StateLincType,qb.License_Certificate_Number__c StateLinc,qb.State__c 
FROM dbo.Account a 
      INNER JOIN RecordType rt ON a.RecordTypeId =rt.Id 
      INNER JOIN dbo.Provider_Facility_Associations__c pfa on pfa.Hospital__c  =a.id
      INNER JOIN dbo.Contact c on c.id =pfa.Provider__c
      LEFT OUTER JOIN dbo.Qualification__c qD ON qd.Provider__c =c.id AND qd.RecordTypeId ='01230000000DHlxAAG' AND qd.Type__c ='DEA'
      LEFT OUTER JOIN dbo.Qualification__c qS ON qs.Provider__c =c.id AND qs.RecordTypeId ='01230000000DHlxAAG' AND qs.Type__c ='State Medical' AND qs.State__c =a.Physical_State__c
      LEFT OUTER JOIN dbo.Qualification__c qB ON qb.Provider__c =c.id AND qb.RecordTypeId ='01230000000DHmRAAW'

 
I'm trying to display multiple qualification records in the same row of their associated contact. Any help would be greatly appreciated.

Eugene NeimanEugene Neiman

The two statements not equivalent, but this may get you started:

 

SELECT name, Region__c, (Select id, License_Certificate_Number__c, Type__c, Start_Date__c, Expiration_Date__c, State__c
from Qualification__r WHERE (Provider__c='00540000000x5ha' AND RecordTypeId='01230000000DHlx') OR (Provider__c='00540000000x5ha' AND RecordTypeId='01230000000DHmR') ) FROM Account
bpl3792bpl3792

I took the account portion out just to see if I could get the or working but it's saying no records exist for that query. It looks like it should work but I think there is something missing.

 

 

Select id, License_Certificate_Number__c, Type__c, Start_Date__c, Expiration_Date__c, State__c
from Qualification__c WHERE (Provider__c='00540000000x5ha' AND RecordTypeId='01230000000DHlx') OR (Provider__c='00540000000x5ha' AND RecordTypeId='01230000000DHmR')
sfdcfoxsfdcfox

In salesforce.com, you have to "follow a relationship" to form a valid query (with the exception of subqueries, which may be used as filters). As I understand it, you're basically trying to return one row of data that contains the three types of qualifications where all qualifications are present on different records within the Qualification__c table. You won't exactly retrieve this format, as it will instead contain one row for the contact and related account information, and an array of elements stored in a relationship field for the qualifications. Alternatively, you can retrieve the data as simply qualifications, where the contact and account information is duplicated in each row. In reality, since you're looking for a contact that meets multiple qualifications, you'll probably end up doing something like this:

 

select ...,(select ... from qualifications__r where ...) from contact where
id in (select provider__c from qualification__c where ...) and
id in (select provider__c from qualification__c where ...) and
id in (select provider__c from qualification__c where ...)

You need the first subquery to link the contact to their qualifications, and the last three subqueries to filter in a way that only contacts that meet all three qualifications will appear in the result set.

 

This is not as difficult as it looks like, but you'll need to spend some time thinking about how to design this query, since it is a bit different than SQL.
bpl3792bpl3792

I have the data displaying properlly but not becasue of SOQL. I'm currently using javascript to hold on to that data and build a map. I'm currently executing about 5 queries and 3 of them nested in a for loop. It's the only way I can think of that would get this to work since the initial query is using LIKE. I wouldn't be able to supply the provider ID until you know what provider you're looking for. I realized this after I started building off of what you said...or at least what I understood. 

select id,Provider_Facility_Association__r.Provider__r.Id,
Provider_Facility_Association__r.Provider__c,
Provider_Facility_Association__r.Provider__r.FirstName,
Provider_Facility_Association__r.Provider__r.Middle_Name__c,
Provider_Facility_Association__r.Provider__r.LastName,
Provider_Facility_Association__r.Provider__r.Gender__c,
Provider_Facility_Association__r.Provider__r.HomePhone,
Provider_Facility_Association__r.Provider__r.Birthdate,
Provider_Facility_Association__r.Provider__r.Birth_City__c,
Provider_Facility_Association__r.Provider__r.Birth_Country__c,
Provider_Facility_Association__r.Provider__r.SSN_Encrypted__c,
Provider_Facility_Association__r.Provider__r.Provider_NPI_ID__c,
Provider_Facility_Association__r.Provider__r.Primary_Specialty__c,
Provider_Facility_Association__r.Provider__r.Practicing_Specialty__c,
Provider_Facility_Association__r.Provider__r.Title,
Provider_Facility_Association__r.Id,
Provider_Facility_Association__r.First_Scheduled_Date__c,
Provider_Facility_Association__r.Region__c,
Provider_Facility_Association__r.Hospital__r.id,
Provider_Facility_Association__r.Hospital__r.Name,
Provider_Facility_Association__r.Hospital__r.Phone,
Provider_Facility_Association__r.Hospital__r.Fax,
Provider_Facility_Association__r.Hospital__r.Physical_State__c,
Provider_Facility_Association__r.Hospital__r.Corp_Entities__r.id,
Provider_Facility_Association__r.Hospital__r.Corp_Entities__r.Name,
Provider_Facility_Association__r.Hospital__r.Corp_Entities__r.Entity_EIN__c,
Provider_Facility_Association__r.Hospital__r.CEP_Group_NPI_ID__c,
tsg_carrier__c, tsg_carrier__r.Name,Provider__c, Individual_PTAN__c, Effective_Date__c,
Provider_Enrollment_Status__c, Amount_on_Hold__c, Billing_Company__c,(Select id from qualification__r where Provider__c='00540000000x5ha')
from Provider_Carrier__c
where Provider_Facility_Association__r.Provider__r.Name like 'Mark Mitchell' and Provider_Facility_Association__r.Provider__r.Id in(select id, License_Certificate_Number__c, Type__c, Start_Date__c, Expiration_Date__c, State__c, Provider__c from qualification__c where RecordTypeId='01230000000DHlx') and Provider_Facility_Association__r.Provider__r.Id in (select id, License_Certificate_Number__c, Type__c, Start_Date__c, Expiration_Date__c, State__c, Provider__c from qualification__c where RecordTypeId='01230000000DHmR')

 

 

Here is a screenshot of my code...maybe it will make my end goal a little clearer. 

Screen Shot

 

Trying to get data with no limits so that's why were using the AJAX toolkit, nothing like Querymore in Apex. 
Please don't judge me XD