+ Start a Discussion
sroberts_MEsroberts_ME 

Data filtering

Hi all,

 

I am new to the force.com platform and was wondering if you could help.

 

My goal is to output all of the contacts from all accounts that have asset "a" and do not have asset "b". Based on my understanding this can not be done with the normal reports section, but my guess is it is pretty simple in apex. The thing I can't quite figure out is how to check "a" and "b" against all the values of the assets related list for each account in our database.

 

I hope that's enough information, thanks for your help!

 

-Sam 

Shashikant SharmaShashikant Sharma

Try this one

 

List<Asset> a;
           List<Asset> b;
           List<Contact> listCon = [Select id from Contact where AccountId != null And id in (Select ContactId from Asset where id in: a.id) And id not in (Select ContactId from Asset where id in: b.id) ];
       

 Here a and be should be Asset records. Please let me know if any issues

 

I have created a test class also please let me know i understood your issue correctly or not.

 

@istest
class TestClass 
    {
    
       static testmethod void testQuery()
       {
           Account acc = new Account(Name = 'Test Account');
           insert acc;
           
           Contact c1 = new Contact(lastName ='lastName1' , AccountId = acc.id);
           insert c1;
           
           Contact c2 = new Contact(lastName ='lastName2' , AccountId = acc.id);
           insert c2;
           
           
           Asset a = new Asset(Name = 'TestAssetA' , Contactid = c1.id , AccountId = acc.id );
           insert a;
           
           Asset b = new Asset(Name = 'TestAssetB' , Contactid = c1.id, AccountId = acc.id );
           insert b;
           
           Asset c = new Asset(Name = 'TestAssetA' , Contactid = c2.id , AccountId = acc.id);
           insert c;

           //Conatact with Asset a and c are allowed 
           List<Asset> listAssetAllowed = new List<Asset>();
           listAssetAllowed .add(a);
           listAssetAllowed .add(c);

           //Conatact with Asset b is not allowed 
           List<Asset> listAssetNotAllowed = new List<Asset>();
           listAssetNotAllowed.add(b);
           
           List<Contact> listCon = [Select id , Name from Contact where AccountId != null And id in (Select ContactId from Asset where id in: listAssetAllowed) And id not in (Select ContactId from Asset where id in: listAssetNotAllowed) ];
           //Only contact2 will come in result
           system.debug('*********** listCon : '+ listCon);
           
       }

}

 You can run this test and verify result by the query that i suggested.