1

I'm trying to create a trigger that will remove a custom object called customer when another customer with the same first name, last name and title (MR, MRS, Miss) is added, so only the second entry is stored while the first entry is deleted. This all works well but Customer__c also holds a Lookup to Account__c. I want to check a custom value in the old entry and check it against the same field of the latest entry and see if it is higher. If so, the lookup to account should be changed to the lookup in the first entry. Maybe my code makes more clear:

trigger handleDuplicates on Customer__c( before insert, before update ) 
{
    //run through all the customers that are addressed at any one time
    for ( Customer__c c : Trigger.new )
    {
        try
        {
            //find out whether there is a customer in the DB already, that uses the same title, first name and last name.
            Customer__c dbc = [select First_Name__c, Last_Name__c, Title__c, Account__c from Customer__c];

            //first name, last name and title must be unique!            
            if ( c.First_Name__c == dbc.First_Name__c && c.Last_Name__c == dbc.Last_Name__c && c.Title__c == dbc.Title__c )
            {
                //replace the account of the last added customer if the priority is less than that of the previous entry
                if ( c.Account__r.Priority__c > dbc.Account__r.Priority__c )
                {
                    c.Account__r = dbc.Account__r;
                }

                //delete dbc if the first name, last name and title are the same as a previous entry.
                delete dbc;
            }
        } catch ( Exception e )
        {
            //oops, can't access the database, or something else went wrong.
            c.addError( e.getMessage( ) );
        }       
    }    
}

the problem is, when I try to create two customers with the same name and title, I get the error: SObject row was retrieved via SOQL without querying the requested field: Customer__c.Account__r which I guess is produced because I don't specifically query the Account__r. The problem is, if I do this, I get an error that there is no Account__r in the custom object customer and I'm absolutely stuck. Anyone knows how to fix this problem?

6
  • 1
    As well as the problem with the account reference, your trigger will also hit governor limits when Customer__c is updated in bulk because you are doing a query per Customer__c. See e.g. the answer to lead convert to avoid duplicate values to contact for techniques to avoid that.
    – Keith C
    Commented Sep 9, 2014 at 16:34
  • If you must reference the Account priority, you will have to also query that for the Trigger.new values using the Contact.AccountId value. You can include that in the dbc query using a relationship query.
    – Keith C
    Commented Sep 9, 2014 at 16:36
  • How do I do that? If I query Account__c, I get the same error I would if I didn't query account at all and I can't query account__r, I get the error: No such column 'Account__r' on entity 'Customer__c'. Commented Sep 9, 2014 at 16:41
  • For the dbc case you should be able to have ..., Title__c , Account__r.Priority__c from Customer__c in the query. But do reconsider the overall algorithm too as mentioned in my first comment.
    – Keith C
    Commented Sep 9, 2014 at 16:55
  • Thank you for your help :) it is working now. I understand what you mean: I can do only a single query every so many seconds/ minutes, so better to use a list and then perform a list query I guess? Commented Sep 9, 2014 at 17:53

0

You must log in to answer this question.

Browse other questions tagged .