Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
jconnerjconner 

Unable to get all values from SOQL statement

Hello,

 

I have a trigger on the Asset object which inserts two Account IDs into two Lists.  One ID is for a Business Account, the other is for a Consumer Account.  Both are correct when looking at the debug log.  The issue I am facing Is I when I go to select an External ID from using the SOQL statement below, it only returns unique values.  An example would be if I have a total of 20 new Assets coming in, with 3 distinct Business Account IDs and 15 distinct Consumer Account IDs, the List of Accounts will only have the 3 and 15 values.  I need it to be populated with all Accounts being updated/inserted.

 

 

trigger Update_Registering_Dealer on Asset (after insert, after update)
{
    List<String> accIds = new List<String>();
    List<String> consIds= new List<String>();
    integer count = 0;
         
    for(Asset a : Trigger.new) 
    {      
            if (a.Type__c == 'Registration')
            accIds.add(a.AccountID);  
            system.debug('AccountID: ' + a.AccountID);
            consIds.add(a.Consumer__c);
            system.debug('Consumer: ' + a.Consumer__c);
            count ++;
    } 

List<Account> accsAssets = [Select a.Account_Number__c From Account a where a.Account_Status__c='Active' and Id IN :accIds];
List<Account> consumerAccount = [Select Registering_Dealer__c From Account where Account_Status__c ='Active' and Id IN :consIds];

 

The business goal here is to pull the External ID from the Business Account, onto the Consumer Account so we can identify where the Consumer purchased their last product.

 

Thank you in advance.

 

Sandeep001Sandeep001

Since you are putting the filter criteria of ID in list of Ids, SOQL will return you unique values only. Also the accounts and consumer account records that are associated to 20 assets are 3 and 15 respectively.

 

Can you give us more details on number of records you are expecting from SOQL to return?
jconnerjconner

I do see your point regarding the 20 assets are associated to only 3 accounts and 15 consumer accounts.

 

When I debug the code the size of the first two lists (accIds & consIds) it will show a size of 20 (expected).

 

The rows returned from the SOQL statement will equal 3 and 15 respectively and I would expect for each. 

Sandeep001 wrote:

Since you are putting the filter criteria of ID in list of Ids, SOQL will return you unique values only.

Is there a different way to perform the SOQL to avoid this issue?  The unique values is what's causing me problems later on in the code.

 
Sandeep001Sandeep001

accIds & consIds are collection of type List and can hold duplicate records. But when you execute a SOQL to fetch records in the list of Ids, it will return you unique 3 and 15 records only.

 

I would have made accIds & consIds collections of Set type instead of List so that it contains unique Id values.

 

You are anyhow getting the correct result. AccIds & consIds lists show 20 ID records in a debug statement due to the fact that all 20 assets have associated AccountID and Consumer__C values but those values are not unique.

 
jconnerjconner
Sandeep001 wrote:

You are anyhow getting the correct result. AccIds & consIds lists show 20 ID records in a debug statement due to the fact that all 20 assets have associated AccountID and Consumer__C values but those values are not unique.

 

 

Correct and that is my issue.

 

Sandeep001 wrote:

accIds & consIds are collection of type List and can hold duplicate records. But when you execute a SOQL to fetch records in the list of Ids, it will return you unique 3 and 15 records only.

 

Do you have any suggestions for a different means to accomplish what I am trying?  I need to iterate through the list of accIds and select an External ID from the Account object.  I need to assign a custom field on the Consumer Account the External ID selected previously.
Sandeep001Sandeep001

If there are 20 records in accIDs list but only 3 of them are unique which means that only 3 records exist in accounts object. In this case, the external id (Account_Number__C) retrieved will also be 3. How can you expect 20 different external id values from 3 unique accounts?

 

Am I missing anything here?
jconnerjconner
Sandeep001 wrote:

How can you expect 20 different external id values from 3 unique accounts?

 

Am I missing anything here?

I do not expect 20 different external id values.  I would expect 20 total external id values, regardless of uniqueness.
Sandeep001Sandeep001

SOQL will not return  you 20 diff external id values.

 

For this, you can create a formula field (say AccountExternalId__c) on Assets that will hold the value of Account.Account_number__c (external id) and then you don't need to execute SOQL to get the external id value. You will get the value in the for loop on assets.

 

List<String> accNumbers = new List<String>();
for(Asset a : Trigger.new) 
    {      
            if (a.Type__c == 'Registration')
            accIds.add(a.AccountID);  
            accNumbers.add(a.AccountExternalId__c);
            system.debug('AccountID: ' + a.AccountID);
            consIds.add(a.Consumer__c);
            system.debug('Consumer: ' + a.Consumer__c);
            count ++;
    }
jconnerjconner

Thank you Sandeep that resolves my issue of getting the external ID for each incoming asset.

 

However, I still have the similar issue where I need to update a field on the Consumer Account with that external ID.  As you mentioned, my SOQL query will only return unique Consumer Accounts so I will now have more External IDs than Consumer Accounts.

 

List<Account> consumerAccount = [Select Registering_Dealer__c From Account where Account_Status__c ='Active' and Id IN :consIds];

 

Any suggestions here?

 
Sandeep001Sandeep001

You can create another formula field say consumerAccNo on Asset to store Account.Registrant_Dealer__c value and don't need SOQL query to fetch.
jconnerjconner
Sandeep001 wrote:

You can create another formula field say consumerAccNo on Asset to store Account.Registrant_Dealer__c value and don't need SOQL query to fetch.


This will enable me to get all the Registrant_Dealer__c values I need, but it will need to be updated with it's related Account_Number__c value from earlier.  How can I update this field without really referring to any Account object?