Hello,

I got this query:

Select Family__r.Name, Family__c, Name From Product2  (Ok Works)

Select Family__r.Name, Family__c, Name From Product2 Group By Family__r.Name, Family__c, Name (Not Working)

Error Message: MALFORMED_QUERY - duplicate alias: Name.

Ok I got it, Name and Name but if I remove it:

Select Family__r.Name, Family__c From Product2 Group By Family__r.Name, Family__c (ok works)

But the problem is I need Name of the product as well.

Did someone have an idea?

Thanks.

I'm working on a solution to convert leads automatically to Account/Contacts/Opportunities. The process is as follows: 

1. Lead with Lead Source = "Deal Registration" inserted (Trigger after insert puts them into set<id> and passes to class/static method. 

2. Query Opportunity object, looking for existing open opportunity for company name/city/state combination

3. If none exists, query Account object looking for matching account/city/state combination 

4. If Account exists, look for matching contact, if none exists insert new contact

5. If Account exists, insert opportunity for account and if contact exists set as contact role on opportunity,

         ELSE insert Account and Contact and insert opportunity 

I can get the lead convert working and can add the opportunity to the account if the account name matches exactly. I know I need to use a SOSL query to look for a matching account to my lead, however, I can only figure out how to do this one record at a time - I can't figure out how to bulkify this. Any help you can offer me would be greatly appreciated!

My setup code is below: 

/**************************
Use to determine if the deal reg is valid, then call convertLead to do the conversion work
**************************/

public class dealRegHandling{
    
    public static void dealRegProcess(Set<id> dealRegIds){
     	List<Lead> dealRegs = [SELECT id, Name, FirstName, LastName, Email, Phone, Registered_Partner_Preferred_Partner__c,
                               Registering_Rep__c, Registering_Rep_Email__c, Registering_Rep_Name__c,Deal_Registration_Status__c,
                               Deal_Registration_Product__c, Deal_Registration_Expiration_Date__c, City, State, PostalCode,
                               Company
                               FROM Lead WHERE id IN :dealRegIds];
        
        List<Opportunity> regdOpptys = [SELECT id, AccountId, Account.Name, Account.BillingState, Account.BillingCity, 
                                        Registered_Partner_Preferred_Partner__c, Registering_Rep__c, Registering_Rep_Email__c, 
                                        Deal_Registration_Status__c, Deal_Registration_Product__c, 
                                        Deal_Registration_Expiration_Date__c 
                                        FROM Opportunity WHERE isClosed = False
                                        AND Registered_Partner_Preferred_Partner__c != NULL];
        
        Map<String, Opportunity> openOpptys = new Map<String, Opportunity>();
        for(Opportunity o : regdOpptys){
            String opptyKey = o.Account.Name.remove(' ').toLowerCase() + o.Account.BillingCity + o.Account.BillingState;
            system.debug('opportunity search key>>>'+opptyKey);
            openOpptys.put(opptyKey, o);
		}
        List<Lead> leadsToConvert = new List<Lead>();
        List<Lead> leadsToUpdate = new List<Lead>();
        map<id, boolean> leadToWhat = new map<Id, boolean>();
        for(Lead l : dealRegs){
            String leadKey = l.Company.remove(' ').toLowerCase() +  l.City + l.State;
            system.debug('lead search key is >>>'+leadKey);
            system.debug('lead search result is>>>'+openOpptys.get(leadKey));
            Boolean matchedDR = false;
            if(openOpptys.get(leadKey) == NULL){
                matchedDR = false;
                l.Deal_Registration_Status__c = 'Approved';
                leadToWhat.put(l.id, matchedDR);
                leadsToUpdate.add(l);
                leadsToConvert.add(l);
            } else {
                matchedDR = true;
                l.Deal_Registration_Status__c = 'Denied';
                leadToWhat.put(l.id, matchedDR);
                leadsToUpdate.add(l);
            }
        }
        update leadsToUpdate;
        convertLead con = new convertLead();
        con.convertLeads(leadToWhat, leadsToConvert);
    }
}

 My conversion code is here: 

public class convertLead{

    public void convertLeads(map<id, boolean> matchedAcct, List<Lead> leadsToConvert){
        
        for(Lead l : leadsToConvert){
            
            Database.leadConvert lc = new Database.LeadConvert();
                lc.setLeadId(l.id);
                
                LeadStatus convertStatus = [SELECT id, MasterLabel FROM LeadStatus WHERE IsConverted = True LIMIT 1];
                lc.setConvertedStatus(convertStatus.MasterLabel);
                Date close = date.today().addDays(30);
                lc.setOpportunityName(l.Registered_Partner_Preferred_Partner__c + ' - ' + l.Name + ' - ' + close);
                
            if(matchedAcct.get(l.id) == true){
                Id acctId = [SELECT id, Name FROM Account WHERE Name LIKE :l.Company AND BillingState = :l.State].id;
                lc.setAccountId(acctId);
                Id contactId = [SELECT id FROM Contact WHERE LastName like :l.LastName AND Email = :l.email].id;
                lc.setContactId(contactId);
            }
            Database.LeadConvertResult lcr = Database.convertLead(lc);
            system.assert(lcr.isSuccess());
            system.debug('lead converted>>>'+lcr.isSuccess());
            }
    }
}

Hello All,

I have the below aggregate Results query which is working fine.

public List <AccountPDF> getAccountsSnapshotYearly(){

    AggregateResult[] groupedResults = [SELECT Customer_Name__c,MAX(Segment__c),SUM(Load_Count__c) FROM Snapshot__c GROUP BY Customer_Name__c ORDER BY Customer_Name__c ASC];
       
        for (AggregateResult ar : groupedResults)  {
                                   
            snapshotResult.add(new AccountPDF(String.valueOf(ar.get('Customer_Name__c')), String.valueOf(ar.get('expr0')), String.valueOf(ar.get('expr1'))));
        } 
     return snapshotResult;
        
    }

The result of the above code is something like this.  Grouped and ordered by Customer Name.

Customer Name      Max(segment)             sum(load count)

ABC                                          7                                    40

XYZ                                           3                                    10

CAB                                          2                                    30

QWE                                        9                                    20

Now I want to order/sort this table by Sum(load count) - Should display in this order 10, 20, 30, 40 of load count. not in customer name order.

But when I include that 'ORDER BY Load_Count__c' in the aggregate result query, it throws error - Ordered field must be grouped or aggregated: Load_Count__c.  The Load_Count__c field is already aggregated in the query but still it shows the same error. 

Cant we order by an aggregated field?

Any other alternatives available?

Hello,

I got this query:

Select Family__r.Name, Family__c, Name From Product2  (Ok Works)

Select Family__r.Name, Family__c, Name From Product2 Group By Family__r.Name, Family__c, Name (Not Working)

Error Message: MALFORMED_QUERY - duplicate alias: Name.

Ok I got it, Name and Name but if I remove it:

Select Family__r.Name, Family__c From Product2 Group By Family__r.Name, Family__c (ok works)

But the problem is I need Name of the product as well.

Did someone have an idea?

Thanks.

I'm working on a solution to convert leads automatically to Account/Contacts/Opportunities. The process is as follows: 

1. Lead with Lead Source = "Deal Registration" inserted (Trigger after insert puts them into set<id> and passes to class/static method. 

2. Query Opportunity object, looking for existing open opportunity for company name/city/state combination

3. If none exists, query Account object looking for matching account/city/state combination 

4. If Account exists, look for matching contact, if none exists insert new contact

5. If Account exists, insert opportunity for account and if contact exists set as contact role on opportunity,

         ELSE insert Account and Contact and insert opportunity 

I can get the lead convert working and can add the opportunity to the account if the account name matches exactly. I know I need to use a SOSL query to look for a matching account to my lead, however, I can only figure out how to do this one record at a time - I can't figure out how to bulkify this. Any help you can offer me would be greatly appreciated!

My setup code is below: 

/**************************
Use to determine if the deal reg is valid, then call convertLead to do the conversion work
**************************/

public class dealRegHandling{
    
    public static void dealRegProcess(Set<id> dealRegIds){
     	List<Lead> dealRegs = [SELECT id, Name, FirstName, LastName, Email, Phone, Registered_Partner_Preferred_Partner__c,
                               Registering_Rep__c, Registering_Rep_Email__c, Registering_Rep_Name__c,Deal_Registration_Status__c,
                               Deal_Registration_Product__c, Deal_Registration_Expiration_Date__c, City, State, PostalCode,
                               Company
                               FROM Lead WHERE id IN :dealRegIds];
        
        List<Opportunity> regdOpptys = [SELECT id, AccountId, Account.Name, Account.BillingState, Account.BillingCity, 
                                        Registered_Partner_Preferred_Partner__c, Registering_Rep__c, Registering_Rep_Email__c, 
                                        Deal_Registration_Status__c, Deal_Registration_Product__c, 
                                        Deal_Registration_Expiration_Date__c 
                                        FROM Opportunity WHERE isClosed = False
                                        AND Registered_Partner_Preferred_Partner__c != NULL];
        
        Map<String, Opportunity> openOpptys = new Map<String, Opportunity>();
        for(Opportunity o : regdOpptys){
            String opptyKey = o.Account.Name.remove(' ').toLowerCase() + o.Account.BillingCity + o.Account.BillingState;
            system.debug('opportunity search key>>>'+opptyKey);
            openOpptys.put(opptyKey, o);
		}
        List<Lead> leadsToConvert = new List<Lead>();
        List<Lead> leadsToUpdate = new List<Lead>();
        map<id, boolean> leadToWhat = new map<Id, boolean>();
        for(Lead l : dealRegs){
            String leadKey = l.Company.remove(' ').toLowerCase() +  l.City + l.State;
            system.debug('lead search key is >>>'+leadKey);
            system.debug('lead search result is>>>'+openOpptys.get(leadKey));
            Boolean matchedDR = false;
            if(openOpptys.get(leadKey) == NULL){
                matchedDR = false;
                l.Deal_Registration_Status__c = 'Approved';
                leadToWhat.put(l.id, matchedDR);
                leadsToUpdate.add(l);
                leadsToConvert.add(l);
            } else {
                matchedDR = true;
                l.Deal_Registration_Status__c = 'Denied';
                leadToWhat.put(l.id, matchedDR);
                leadsToUpdate.add(l);
            }
        }
        update leadsToUpdate;
        convertLead con = new convertLead();
        con.convertLeads(leadToWhat, leadsToConvert);
    }
}

 My conversion code is here: 

public class convertLead{

    public void convertLeads(map<id, boolean> matchedAcct, List<Lead> leadsToConvert){
        
        for(Lead l : leadsToConvert){
            
            Database.leadConvert lc = new Database.LeadConvert();
                lc.setLeadId(l.id);
                
                LeadStatus convertStatus = [SELECT id, MasterLabel FROM LeadStatus WHERE IsConverted = True LIMIT 1];
                lc.setConvertedStatus(convertStatus.MasterLabel);
                Date close = date.today().addDays(30);
                lc.setOpportunityName(l.Registered_Partner_Preferred_Partner__c + ' - ' + l.Name + ' - ' + close);
                
            if(matchedAcct.get(l.id) == true){
                Id acctId = [SELECT id, Name FROM Account WHERE Name LIKE :l.Company AND BillingState = :l.State].id;
                lc.setAccountId(acctId);
                Id contactId = [SELECT id FROM Contact WHERE LastName like :l.LastName AND Email = :l.email].id;
                lc.setContactId(contactId);
            }
            Database.LeadConvertResult lcr = Database.convertLead(lc);
            system.assert(lcr.isSuccess());
            system.debug('lead converted>>>'+lcr.isSuccess());
            }
    }
}

Hello All,

I have the below aggregate Results query which is working fine.

public List <AccountPDF> getAccountsSnapshotYearly(){

    AggregateResult[] groupedResults = [SELECT Customer_Name__c,MAX(Segment__c),SUM(Load_Count__c) FROM Snapshot__c GROUP BY Customer_Name__c ORDER BY Customer_Name__c ASC];
       
        for (AggregateResult ar : groupedResults)  {
                                   
            snapshotResult.add(new AccountPDF(String.valueOf(ar.get('Customer_Name__c')), String.valueOf(ar.get('expr0')), String.valueOf(ar.get('expr1'))));
        } 
     return snapshotResult;
        
    }

The result of the above code is something like this.  Grouped and ordered by Customer Name.

Customer Name      Max(segment)             sum(load count)

ABC                                          7                                    40

XYZ                                           3                                    10

CAB                                          2                                    30

QWE                                        9                                    20

Now I want to order/sort this table by Sum(load count) - Should display in this order 10, 20, 30, 40 of load count. not in customer name order.

But when I include that 'ORDER BY Load_Count__c' in the aggregate result query, it throws error - Ordered field must be grouped or aggregated: Load_Count__c.  The Load_Count__c field is already aggregated in the query but still it shows the same error. 

Cant we order by an aggregated field?

Any other alternatives available?

I am looking at a possible solution to help enforce that our sales reps add the correct contacts to an opportunity and assign roles to those contacts (as well as identifying who the primary contact is for the opportunity). The scenario would work like the following:

For our opportunities we know that we need at least two specific roles (defined in our opportunity contact roles) on every opportunity. I'd like to create two custom true/false fields on the opportunity (one for each role that we need). These fields would be updated by an apex trigger that would set the appropriate field to true when a contact was assigned the appropriate contact role for the opportunity.

I would also like to add a custom true/false field that would be updated to true when a contact associated with the opportunity was assigned as the "primary" contact (ensuring that all opportunities by a certain stage have a contact that has been assigned as the primary).

I could then have validation rules on those custom opportunity fields that wouldn't allow the opportunity stage to be advanced until the sales rep had identified and associated the required contacts/roles and primary.

So the opportunity contact roles that would be "required" would be:

1. Sales leader
2. Marketing leader

And the custom checkbox fields I would define for the Opportunity would be:

1. Sales_Leader_Identified__c
2. Marketing_Leader_Identified__c
3. Primary_Assigned__c

I'm very new to the apex trigger world and would really love some help in creating this trigger if anyone is up for the challenge. Thanks for any help or guidance you can give here!