1

How to write a select statement in For loop I have a requirement as below we need a trigger to insert record into Location object everythime an Account record is added/update but it has to check if duplicate record exists in Location before inserting, i can limit my select statement to 2 records

here is my trigger code, let me know if any workaround :)

trigger AddLocation on Account (after insert, after update) {    
    Id rt = null;
    List<SVMXC__Site__c> locations = new List<SVMXC__Site__c>();

    ///put acounts into a set
    Account[] accounts;
    accounts = Trigger.new;
    set<Id> accountIds = new set<Id>(); 
    for (Account a : accounts) {
            accountIds.add(a.id);
    }
    //get sites with matching accounts
    SVMXC__Site__c[] siteList = [SELECT Id, SVMXC__Account__c from SVMXC__Site__c where SVMXC__Account__c IN :accountIds];
    //add sits to set
    set<Id> sites = new set<Id>(); 
    for (SVMXC__Site__c sl : siteList) {
            sites.add(sl.SVMXC__Account__c);
    }

     // Set up maps for record ids               
    Map<String, Id> recordTypeMap = new Map <String,Id>();
    for (RecordType aRecordType : [SELECT Id, Name FROM RecordType WHERE IsActive = TRUE AND SobjectType = 'SVMXC__Site__c' LIMIT 1000] ) {
        recordTypeMap.put(aRecordType.Name, aRecordType.Id);            
    }

    rt =  recordTypeMap.get('Standard Location');

    for (Account account : System.Trigger.new) {

        String hasAsite;
       // SVMXC__Site__c SvLocs = new SVMXC__Site__c();
        Set<Id> LocId = new set<Id>();


        SVMXC__Site__c[] SvLocs = [SELECT Id from SVMXC__Site__c 
                            where  SVMXC__Street__c=:account.ShippingStreet   
                              and  SVMXC__City__c    =: account.ShippingCity  
                              and  SVMXC__State__c   =: account.ShippingState      
                              and  SVMXC__Zip__c     =: account.ShippingPostalCode 
                              and  SVMXC__Country__c =: account.ShippingCountry];


        for(SVMXC__Site__c SL : SvLocs ) { 
            LocId.add(SL.Id);
        }


         if (LocId.size() > 0){
          hasAsite = 'Y'; 
         }


        if (account.Type == 'Generator' && hasAsite != 'Y') {
            //create service max location record
            SVMXC__Site__c location = new SVMXC__Site__c(
                                    name = account.Name,
                                    SVMXC__Street__c = account.ShippingStreet,
                                    SVMXC__City__c = account.ShippingCity,
                                    SVMXC__State__c = account.ShippingState,
                                    SVMXC__Zip__c = account.ShippingPostalCode,
                                    SVMXC__Country__c = account.ShippingCountry,
                                    SVMXC__Account__c = account.Id,
                                    SVMXC__Site_Phone__c = account.Phone,
                                    recordTypeId = rt,
                                    SVMXC__Longitude__c =  account.Shipping_Geo_Location__Longitude__s,
                                    SVMXC__Latitude__c = account.Shipping_Geo_Location__Latitude__s,
                                    EPA_ID__c=account.EPA_ID__c
                                    );

            locations.add(location); 

        }   

    }

    insert locations;

}

1 Answer 1

0

Can try below code, I have modified system.trigger.new for loop. You can modify full code according to your requirement.

trigger AddLocation on Account (after insert, after update) {    
    Id rt = null;
    List<SVMXC__Site__c> locations = new List<SVMXC__Site__c>();

    ///put acounts into a set
    Account[] accounts;
    accounts = Trigger.new;
    set<Id> accountIds = new set<Id>(); 
    for (Account a : accounts) {
            accountIds.add(a.id);
    }
    //get sites with matching accounts
    SVMXC__Site__c[] siteList = [SELECT Id, SVMXC__Account__c from SVMXC__Site__c where SVMXC__Account__c IN :accountIds];
    //add sits to set
    set<Id> sites = new set<Id>(); 
    for (SVMXC__Site__c sl : siteList) {
            sites.add(sl.SVMXC__Account__c);
    }

     // Set up maps for record ids               
    Map<String, Id> recordTypeMap = new Map <String,Id>();
    for (RecordType aRecordType : [SELECT Id, Name FROM RecordType WHERE IsActive = TRUE AND SobjectType = 'SVMXC__Site__c' LIMIT 1000] ) {
        recordTypeMap.put(aRecordType.Name, aRecordType.Id);            
    }

    rt =  recordTypeMap.get('Standard Location');

    List<String> street = new List<String>();
    List<String> city = new List<String>();
    List<String> state = new List<String>();
    List<String> zipCode = new List<String>();
    List<String> country = new List<String>();

    for (Account account : System.Trigger.new) {

        String hasAsite;
       // SVMXC__Site__c SvLocs = new SVMXC__Site__c();
        Set<Id> LocId = new set<Id>();
        street.add(account.ShippingStreet);
        city.add(account.ShippingCity);
        state.add(account.ShippingState);
        zipCode.add(account.ShippingPostalCode);
        country.add(account.ShippingCountry);
     }   

    }

    Map<ID,SVMXC__Site__c> sitemap = [SELECT ID,Name,SVMXC__Street__c,SVMXC__City__c,SVMXC__State__c,
    SVMXC__Zip__c,SVMXC__Country__c FROM SVMXC__Site__c WHERE SVMXC__Street__c IN : street AND SVMXC__City__c IN : city AND  SVMXC__State__c IN : state AND SVMXC__Zip__c IN : zipCode AND SVMXC__Country__c IN : country];

    for(Account acc : trigger.new){
        for(SVMXC__Site__c site : sitemap.keySet()){
            if(site.SVMXC__Zip__c.equals(acc.ShippingPostalCode) && site.SVMXC__Street__c.equals(ShippingStreet)&& Other location condition){
                //dont do any thing
            }else if (account.Type == 'Generator' && hasAsite != 'Y'){
                Add in location list;
            }
        }
    }

    insert locations;

}
2
  • basically I am trying to store location information in a list and the get the SVMXC__Site__c. Once you have SVMXC__Site__c list you can loop through account and compare SVMXC__Site__c fields with account fields if they are not matching for a record then add a new SVMXC__Site__c in location list and insert after the loop ends.
    – San
    Aug 12, 2015 at 17:43
  • Thank you for the Quick response, let me try this workaround Aug 12, 2015 at 18:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .