+ Start a Discussion
Dhananjay Patil 12Dhananjay Patil 12 

Didn't understand relationship 'Opportunity__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

Hello All,

I have a following query: 

SELECT name,ParentId FROM Attachment WHERE name like 'Spon%' AND ParentId IN (SELECT ID FROM Opportunity where SponsorLetterAgreedUpon__c=false) 

this query is working is expected but I have to add the "SponsorLetterAgreedUpon__c"(type checkbox) field at the start of select statement i.e.

SELECT name,ParentId,SponsorLetterAgreedUpon__c FROM Attachment WHERE name like 'Spon%' AND ParentId IN (SELECT ID FROM Opportunity where SponsorLetterAgreedUpon__c=false) 

like this.In order to check the value but if I use "Opportunity.SponsorLetterAgreedUpon__c"  then it throws an error that I mentioned in the heading.
The reason that I am adding this field is because I have to run a cleanup job using data loader and I must add this field in the select statement in order to know the value in column.
 Can I someone tell me what I've missed?
 
 I am not actually getting the Relationship between Opportunity and Attachment.
 Ideally below query should've worked:
 
 SELECT name,ParentId,Attachment.Opportunity__r.SponsorLetterAgreedUpon__c FROM Attachment WHERE name like 'Spon%' AND ParentId IN (SELECT ID FROM Opportunity where SponsorLetterAgreedUpon__c=false) 
 
 
 Let me know with you reply
Best Answer chosen by Dhananjay Patil 12
Nagendra Prasad ChNagendra Prasad Ch
 There is no field called Opportunity on Attachments object(https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_attachment.htm)


You can only query id,name,type of parent from Attchments,
SELECT name,ParentId,Parent.Name,Parent.Type FROM Attachment WHERE name like 'Spon%' AND ParentId IN (SELECT ID FROM Opportunity where SponsorLetterAgreedUpon__c=false) 

You can try below query(it will not work in dataloader)
select id,name,SponsorLetterAgreedUpon__c,(select id,name from Attachments) from opportunity where SponsorLetterAgreedUpon__c=false

Work around is, run above query in Workbench, copy paste the rows into excel sheet 

All Answers

BRTRAJBRTRAJ
Your nested query first gets only the the ID data from opportunity object based on condition, and then this data is used to select records from attachment object. Since the system did not gather the data of sponsorletteragreedupon field in the first step, it is throwing error while running the second step of your query. Hence the error assumed that you are trying to get the value from relationship.
To my knowledge, nested queries does not allow usage of field from subquery in the outer query when the field is not part of the subquery selection.

Since the value in the expected field is always going to be false, see if your cleanup class really needs this field, or just change it to ignore it for the current run. 
Dhananjay Patil 12Dhananjay Patil 12
@Ch Nagendra Prasad if I use Parent.SponsorLetterAgreedUpon__c thene it throws an error "No such column 'SponsorLetterAgreedUpon__c' on entity 'Name'"

If I use Opportunity.SponsorLetterAgreedUpon__c then it throws the same error that i mnetionewd in the heading..
ShivaKrishna(Freelancer)ShivaKrishna(Freelancer)
Hi Dhananjay,

FYIP, there is no direct relation between the attachment and opportunity but it can be related with opportunity with parentid. ParentId can be id of any of the record of object that support attachements (accounts, opportunity etc...)
    
    so you may face problem with ( Parent.SponsorLetterAgreedUpon__c == false ) as parent is not perticular to opportunity.
    
    better approach is 
    
    SELECT name,ParentId FROM Attachment WHERE name like 'Spon%' AND ParentId IN (SELECT ID FROM Opportunity where SponsorLetterAgreedUpon__c=false) 
    
    if you want to limit the no of opportunities in above query you can add that to query criteria.

let m know, if it helps you or need any help :)
naga.sfdc.backup@gmail.com
Nagendra Prasad ChNagendra Prasad Ch
 There is no field called Opportunity on Attachments object(https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_attachment.htm)


You can only query id,name,type of parent from Attchments,
SELECT name,ParentId,Parent.Name,Parent.Type FROM Attachment WHERE name like 'Spon%' AND ParentId IN (SELECT ID FROM Opportunity where SponsorLetterAgreedUpon__c=false) 

You can try below query(it will not work in dataloader)
select id,name,SponsorLetterAgreedUpon__c,(select id,name from Attachments) from opportunity where SponsorLetterAgreedUpon__c=false

Work around is, run above query in Workbench, copy paste the rows into excel sheet 
This was selected as the best answer
Dhananjay Patil 12Dhananjay Patil 12

@ShivaKrishna(Freelancer):So the thing is clear.I can not directly use the SponsorLetterAgreedUpon__c in select statement like SELECT name,ParentId,Opportunity.SponsorLetterAgreedUpon__c
 

if I have to run the query in data loader and when the result is getting displayed whose SponsorLetterAgreedUpon__c=false.
but what if I have to change the value of SponsorLetterAgreedUpon__c to "true" .I mean to say After running the correct query if the records are found which conatins an attachment and SponsorLetterAgreedUpon__c=false then I have to run a job where I have to change the value of SponsorLetterAgreedUpon__c to true..
Do u have abny suggession inwhich O can change the value of SponsorLetterAgreedUpon__c to true..I fI get the records.

Dhananjay Patil 12Dhananjay Patil 12
@Ch Nagendra Prasad  Naga,I have tried the below query earlier:

select id,name,SponsorLetterAgreedUpon__c,(select id,name from Attachments) from opportunity where SponsorLetterAgreedUpon__c=false

when i run this query it filters the records based on the condition in SponsorLetterAgreedUpon__c.So it dispalys only those records whose SponsorLetterAgreedUpon__c is false and in the attachments column it displays Attachment name as well as the blank attachments..but i have to avoid the blank attachments.But as I can not use nested query in the dataloader,this iscenario is no of my use.

The query that you've provided is fine:
SELECT name,ParentId,Parent.Name,Parent.Type FROM Attachment WHERE name like 'Spon%' AND ParentId IN (SELECT ID FROM Opportunity where SponsorLetterAgreedUpon__c=false) 

But I need a favor if I get  a list of records whose SponsorLetterAgreedUpon__c is false and what if I change the value of SponsorLetterAgreedUpon__c to true.
Can I directly add the column manually in csv file and upload the file using dataloader?
let me know the recommended way because if i get a record which contains an attachment and whose SponsorLetterAgreedUpon__c=false,I have to change the value of SponsorLetterAgreedUpon__c to true for those perticular records..
 
Nagendra Prasad ChNagendra Prasad Ch
Yes, you can do that.
Change the SponsorLetterAgreedUpon__c feld in CSV and update through data loader. While updationg, map only id,SponsorLetterAgreedUpon__c  in data loader mappings. 
 
Nagendra Prasad ChNagendra Prasad Ch
You have to do update on Opportunity object, not on Attachments. Copy opportunity Ids,SponsorLetterAgreedUpon__c  values you want to update to new CSV file. From Dataloader select Update and then opportunity object. Select new copied CSV, map Id,SponsorLetterAgreedUpon__c and select new copied CSV and update.
Dhananjay Patil 12Dhananjay Patil 12
@Ch Nagendra Prasad Thanks Naga,This will be helpful...