+ Start a Discussion
Dhananjay Patil 12Dhananjay Patil 12 

Need A help in SOQL to Understand the relationship between Opportunity and Attachments Records

Hey Guys,

I have created a following query: 

Select Id,Name,SponsorLetter__c,(Select Name From Attachments where Name Like'Sponsor Letter%') from opportunity where SponsorLetter__c<>true

In this query SponsorLetter_c is a custom field of type checkbox and we have craeted a visual force page inwhich provided a button for this perticular field.functionality behind this button is if I Attach a document,In the Notes and Attachement the document is geeting displayed along with Field Label+Name of the Docuemnt.
E.g.If I attach a doucment name 'test.txt',in the notes and attachment it display the fiel anme like 'SponserLetter-test.txt' like that which is our current functionality.
now when the document is attached.Checkbox is checked(true) for SponsorLetter__c(Again this is visualforce page modification by proving an images of checkbox based on the value).

I am testing negative scenario like When the document is attached,Checboxbox is getting checked but in the Query Editor I chenged the value to False even if document is attached.
Now the query that I mentioned Earlier,it gives me result of All the opportunity records where SponsorLetter__c is false and in the attachements column it displays blank/null value  for the records as well as it display the Opportunity record where I've done my negative scenario including the name of attachment.

Now this query is working as expected but my concern is I want to display only those Opportunity Records where SponsorLetter__c=false and in Attachments Column
it display does not display the blank value.

I have tried with the below new query to solve the problem 

Select Id,Name,SponsorLetter__c,(Select Name From Attachments where Name Like'Sponsor Letter%') from opportunity where SponsorLetter__c<>true AND Attachment.Name<>''

if you observe the last condition i.e. "Attachment.Name<>''  ",it throws an error.but the reason I used this condion is to avoid the blank values which i am getting in Attachments Column.

Can Sommeone help me to sort out this problem?

In this case I have created negative scenario on only one opportunity record whose SponsorLetter__c=false though it contains an attachments.Ideally I should get Only one records(i.e.Negative scenario)

Please provide ur suggession along with query where do I put the exact contion in order to avoid blank value in the attachments.
Dhananjay Patil 12Dhananjay Patil 12
User-added image
Dhananjay Patil 12Dhananjay Patil 12
I have attached the screenhot for the better understanding.When I fire the 1st query it gives me the result that i mentioend in the scareenshot.
But I am looking for those query where where I should get only 1 records in the result taht I marked as Red.
Dhananjay Patil 12Dhananjay Patil 12
The Custom field of type checkbox is 'SponserLetterAggreedUpon__c'.In the query I've mentioned 'SponsorLetter__c'.Ignore it.Consider instead of 'SponsorLetterAgreedUpon__c' it is 'SponsorLetter__c' in screenshot.Sorry for the typo.  :0
Tavva Sai KrishnaTavva Sai Krishna
Hi Dhananjay,

In order to access the highlighted record which in your screenshot try below query. 
Select Name,parentId,parent.SponsorLetterAgreedUpon__c,parent.name From Attachments where Name Like'Sponsor Letter%' AND parent.SponsorLetterAgreedUpon__c = false

Also , i havent tested. let me know with the outcomes of this

Sai Krishna Tavva.