+ Start a Discussion
john8407john8407 

Help with trigger.

Here is my code:

 

trigger UpdateAccount on Contact (before insert, before update)
{
List<Account> accs= [select id,Finance_Contact_1__c from Account];
for(Contact c : Trigger.new)
{
if(c.Contact_Type__c=='Finance')
{
for(Account acc : accs)
{
acc.Finance_Contact_1__c = c.id;
}
}
}
update accs;
}

 

 

How can I modify it to resolve this error?

Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger UpdateAccount caused an unexpected exception, contact your administrator: UpdateAccount: execution of BeforeUpdate caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times): Trigger.UpdateAccount: line 3, column 1
Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

Cool, that makes a lot more sense.

 

In that case you'll want to restrict your query that is retrieving the accounts to only retrieve those matching the contact's account.  If you put these into a map, you can then traverse your supplied contacts and update the associated account as required.  I think you'll need to make your trigger after insert/update if you want the id of the contact (as before insert there is no id assigned I believe).

 

The following should allow you to retrieve the accounts and apply the updates:

 

 

List<id> accIds=new List<id>();
for (Contact cont : trigger.new)
{
   accIds.add(cont.accountid);
}
Map<Id, Account> accountsById=new Map<Id, Account>();
List<Account> accs= [select id,Finance_Contact_1__c from Account where id in :accids];
accountsById.putAll(accs);

List<Account> accsToUpdate=new List<Account>();
for (Contact cont : trigger.new)
{
   if (cont.Contact_Type__c='Finance')
   {   
       Account acc=accountsById.get(cont.AccountId);
       acc.Finance_Contact_1__c = cont.id;
       accsToUpdate.add(acc);
   }
}

update accsToUpdate;

 

 

 

 

 

All Answers

bob_buzzardbob_buzzard

Can you tell us a bit more about the use case?  I.e. is it really the case that when a contact is added to the system which is of type Finance, every account in the system is updated to have this contact as their Finance_Contact_1__c?  It seems like there'd be a lot of overwriting unless there was only ever one contact of this type.

 

If that is really what needs to happen you could use batch apex to update all accounts.  

john8407john8407

Bob...not every account would need updated.  The user will create the account, then create their contacts.  We require two finance contacts for an order, so we have two lookup fields on the account that link to the contact.  So after a user creates their account, creates their contacts, they have to edit the account and do a lookup to populate the contact.  So I added a field called Contact Type on the contact object, my fields on the accout are Finance Contact 1 and Finance Contact 2.  I am trying to make it where when a user creates/edits a contact and selects the contact type of Finance 1, that contact name is automatically populated on that related account in the account field Finance Contact 1.  So it would only need to update that one account.

bob_buzzardbob_buzzard

Cool, that makes a lot more sense.

 

In that case you'll want to restrict your query that is retrieving the accounts to only retrieve those matching the contact's account.  If you put these into a map, you can then traverse your supplied contacts and update the associated account as required.  I think you'll need to make your trigger after insert/update if you want the id of the contact (as before insert there is no id assigned I believe).

 

The following should allow you to retrieve the accounts and apply the updates:

 

 

List<id> accIds=new List<id>();
for (Contact cont : trigger.new)
{
   accIds.add(cont.accountid);
}
Map<Id, Account> accountsById=new Map<Id, Account>();
List<Account> accs= [select id,Finance_Contact_1__c from Account where id in :accids];
accountsById.putAll(accs);

List<Account> accsToUpdate=new List<Account>();
for (Contact cont : trigger.new)
{
   if (cont.Contact_Type__c='Finance')
   {   
       Account acc=accountsById.get(cont.AccountId);
       acc.Finance_Contact_1__c = cont.id;
       accsToUpdate.add(acc);
   }
}

update accsToUpdate;

 

 

 

 

 

This was selected as the best answer
john8407john8407

Bob, that worked! Thank you very much.  If I have two fields for example finance 1 and finance 2, do I need 2 different triggers, one for each update?

bob_buzzardbob_buzzard

Is it the case that the first finance contact encountered becomes finance_contact_1 and the second finance_contact_2?

 

It would be better to check if finance_contact_1 is empty and if its not assign the contact id to finance_contact_2.  That way the same trigger can handle the first and second.

john8407john8407

The contact with the contact type "Finance 1" goes in the account field called Finance Contact 1.  The contact with the contact type of "Finance 2" goes in the account field called Finance Contact 2.  I just created 2 triggers, but was wondering if it could be made 1.

john8407john8407

If the picklist was changed to a multi-picklist, is there something that I could put in the trigger instead of equals like contains or something?

bob_buzzardbob_buzzard

You should be able to do that in a single trigger.  Something like:

 

 

List<id> accIds=new List<id>();
for (Contact cont : trigger.new)
{
   accIds.add(cont.accountid);
}
Map<Id, Account> accountsById=new Map<Id, Account>();
List<Account> accs= [select id,Finance_Contact_1__c from Account where id in :accids];
accountsById.putAll(accs);

Map<Id, Account> accsToUpdate=new Map<Id, Account>();
for (Contact cont : trigger.new)
{
   if (cont.Contact_Type__c='Finance 1')
   {   
       Account acc=accountsById.get(cont.AccountId);
       acc.Finance_Contact_1__c = cont.id;
       accsToUpdate.put(acc.id, acc);
   }
   else if (cont.Contact_Type__c='Finance 2')
   {   
       Account acc=accountsById.get(cont.AccountId);
       acc.Finance_Contact_2__c = cont.id;
       accsToUpdate.put(acc.id, acc);
   }
}

update accsToUpdate.values();

Note that I've changed the accsToUpdate to be a map, as otherwise the account could appear twice in the list to update. 

 

john8407john8407

Thank you bob! Is this possible if the contact type picklist on the contact is changed to a multi-picklist.  This way users could select contact type of "Finance 1" and "Billing"?

bob_buzzardbob_buzzard

It should be possible.  The selected items from a multi-select picklist are stored in the field as semi-colon separated string.

john8407john8407

So I'm assuming this wouldnt work.  

 

if (cont.Contact_Type__c='Finance 1')

 

What would I use instead of =, I was thinking I need something like contains?

bob_buzzardbob_buzzard

Yes, contains should do the trick.

john8407john8407

how do you do contains?

bob_buzzardbob_buzzard

 

if (cont.Contact_Type__c.contains('Finance 1'))

 

 

john8407john8407

I updated the trigger to contains and for some reason when I try to create a new contact or update a contact without a contact type selected in the multi-picklist, I get this error.  Line 15 is:       if (cont.Contact_Type2__c.contains('Finance 1'))

 

Error: Invalid Data.
Review all error messages below to correct your data.
Apex trigger UpdateAccountFinanceContact caused an unexpected exception, contact your administrator: UpdateAccountFinanceContact: execution of AfterInsert caused by: System.NullPointerException: Attempt to de-reference a null object: Trigger.UpdateAccountFinanceContact: line 15, column 8