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;

}
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;

}
  • 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 '15 at 17:43
  • Thank you for the Quick response, let me try this workaround – Kishore Ravi Aug 12 '15 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.