+ Start a Discussion
Eli Flores, SFDC DevEli Flores, SFDC Dev 

Help with basic SOQL query

I'm having a problem finding the right syntax for a pretty simple SOQL query.

 

So i have a basic query

 

Select Time_Clock__r.Team_Member_Employee__c FROM Time_Record__c where Id='xxxx'

 

which will return the Contact Id. Now I need to get the User associated with that contact. I've tried

 

SELECT Id from User where ContactId in (Select Time_Clock__r.Team_Member_Employee__c FROM Time_Record__c where Id='a0D3000000CiPAk')

 

and

 

SELECT Id from User where ContactId = (Select Time_Clock__r.Team_Member_Employee__c FROM Time_Record__c where Id='a0D3000000CiPAk')

 

I can't seem to find the correct syntax to achieve this. I'd appreciate any help.

Best Answer chosen by Admin (Salesforce Developers) 
MattLacey.ax1065MattLacey.ax1065

No worries, then you'll want something like this:

 

 

set<id> setContactIds = new set<id>();

for(Time_Record__c [] timeRecords : [Select Time_Clock__r.Team_Member_Employee__c FROM Time_Record__c where Id='a0D3000000CiPAk'])
{
  for(Time_Record__c [] timeRecord : timeRecords)
  {
    if(timeRecord.Time_Clock__r != null)
    {
      setContactIds.add(timeRecord.Time_Clock__r.Team_Member_Employee__c);
    }
  }
}

for(User u : [SELECT id FROM user WHERE ContactId in : setContactIds])
{
  // etc...
}

 

 

Written on the fly in the browser but should get you going :)

 

All Answers

MattLacey.ax1065MattLacey.ax1065

The first option looks correct — are you sure it's returning contact IDs and not some other object's id? Are you getting an error or just no rows returned?

Siddharth Birari.ax1164Siddharth Birari.ax1164

Would you please provide the proper details regarding the relationships between the objects which you are using? its very hard to understand the actual requirement.

 

One more thing, I couldn't get that how would you get Contact Id in the first mentioned query by you...

Eli Flores, SFDC DevEli Flores, SFDC Dev

When I try it in the data loader I get an error that says:

 

The inner select field 'Time_Clock__r.Team_Member_Employee__c' cannot have more than one level of relationships. Probably should've included that in the first post. Haven't had my coffee yet. :smileyhappy:

MattLacey.ax1065MattLacey.ax1065

Ha ha, that coffee problem gets me every morning.

 

I didn't realise you couldn't traverse more than one level in an inner query. Seems to me like your best option would be to do the one query, and then run a second using the list of ids obtained from the first. Are you working in apex or some other language via the API?

Eli Flores, SFDC DevEli Flores, SFDC Dev

Well it relates to Customer Portal users

 

Time_Record__c has a lookup field Time_Clock__c that points a custom object Time_Clock__c

Time_Clock__c has a look up field Team_Member_Employee__c that points to Contact

 

When a portal user is created the User object has a ContactId field that populated with the associated contact.

 

What I have is the Time_Record__c Id and I'm trying to get the associated User Id

Eli Flores, SFDC DevEli Flores, SFDC Dev

I'm trying to write a trigger that works on the Time_Record__c object so I'm working in apex.

MattLacey.ax1065MattLacey.ax1065

No worries, then you'll want something like this:

 

 

set<id> setContactIds = new set<id>();

for(Time_Record__c [] timeRecords : [Select Time_Clock__r.Team_Member_Employee__c FROM Time_Record__c where Id='a0D3000000CiPAk'])
{
  for(Time_Record__c [] timeRecord : timeRecords)
  {
    if(timeRecord.Time_Clock__r != null)
    {
      setContactIds.add(timeRecord.Time_Clock__r.Team_Member_Employee__c);
    }
  }
}

for(User u : [SELECT id FROM user WHERE ContactId in : setContactIds])
{
  // etc...
}

 

 

Written on the fly in the browser but should get you going :)

 

This was selected as the best answer
Eli Flores, SFDC DevEli Flores, SFDC Dev

It was the : syntax that solved the problem. Wrote a line like this in the trigger:

 

User myUser = [select Id from User where ContactId=:myRecord.Time_Clock__r.Team_Member_Employee__c];

 Thanks! I really appreciate it.

MattLacey.ax1065MattLacey.ax1065

No worries mate, glad to be of assistance!