function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Kenji775Kenji775 

Few basic Apex questions

Hey guys,

trying to write a very simple trigger. I need to keep track of all the people in an account under the age of 18. I figured easiest way to do this was have a trigger attached to contacts, that says whenever a contact inserted, updated, or deleted to find all contacts in the same account as the one we are modifying that are under the age of 18 and update the counter on the account.

 

Just in the sake of efficiency I have a few questions.

 

1) How can I tell how many rows were returned in a query? I don't really need the data in the query, just how many rows it returned. Seems wasteful to do a loop to count the records.

 

2) Can I update an object without selecting it first? For example, I need to update the account, I already know the Id of the account to update because it is on the contact. I am simply updateing a field, and it seems inefficient to have to select the account object with it's own query before I am able to update it. However it says I cannot specify the ID of a contact object.

 

Here is my code.

(Yes I know its not bulk safe, I still don't understand how to code in that style)

 

 

trigger UpdateNumberOfChildrenCounter on Contact (after insert, after update, after delete)
{
//The account object we will be updateing. The same account as the one the contact is in.
Account contactAccount = new Account();
//All the contacts in the given account under the age of 18
Contact allAccountContacts = new Contact();

//Find all people in this account under the age fo 18. Don't need any data,
//just the number of them there are.
allAccountContacts = [select Id From Contact Where AccountId =:Trigger.new[0].AccountId and age__c < 18];

//This line tosses an error because "field is not writeable". Well I am just
//trying to do an update.
contactAccount.Id = Trigger.new[0].AccountId;

//I don't know the syntax for getting the number of records a query returns, so I
//am using .recordCount as a place holder.
contactAccount.Number_of_Children__c = allAccountContacts.recordCount;

//Update the accoutn object.
update contactAccount;

}

 

 Thanks a bunch for any help. I know it is all totally novice stuff.

 

Message Edited by Kenji775 on 02-11-2010 09:36 AM
Rajesh ShahRajesh Shah

1. Regarding your second point, yes you can do an update without actually selecting a record. Following is how I do it:

 

Account acc = new Account(Id = con.AccountId); acc.someField = some Value; update acc;

 

 2. Regarding bulk, you can try out the following way:

  1. Get all AccountId from the trigger.new in a set. Query all the accounts with Id in this set.
  2. Create a map of <AccountId, List<Contact>> or <contactId, Account>.

 

 Regarding calculation, I think the best way would be to add/delete the no of contacts based on the records in trigger.new. For e.g. If my account has 2 as rec count, then I am inserting 2 contacts for the account with age<18, I would add 2 to the record count. Similarly for update (probably also check if the age was modified of the contact from under 18 to above or vice versa) and delete. I should not be bothering by querying the existing records for the account. Hope I am clear.

 

Kenji775Kenji775

Ah thank you so much for your input. I am happy to finally know how update records without selecting them.

 

For your suggestion on the counter, you are right, a runing tally would probably be better, and i use that methodology in other situations. However, because we already have a bunch of data, i would have to get the counter set to the right number first, then activate the trigger. That might be kind of tricky to do based on the amount of data we have. I'll probably use this methodology for a while, then when all the counters are correct, change over to the running tally system, VS the query system I am using now.

 

So that just leaves the question of how to get the number of records from a query. Anyone know that one? I can do a loop to count, but that seems kinda lame.

SuperfellSuperfell
do a count query, e.g. Integer c = [select count() from contact where accountId= :foo];
Rajesh ShahRajesh Shah

Well in that case, I suggest to first create the map <AccountId, List<Contact>>. Once done, you can just loop through all Accounts and find the count based on the size of the list of the value corresponding to that.

As an another thought, you could do a one time update of all records using anonymous block.

Kenji775Kenji775

Ah thank you so much, that is just what I was looking for. I didn't know you could make that type of query, very cool.

 

Now I think i might try to make this thing bulk capable. Here is what I have now if anyone feels like helping me out :P Otherwise I can hack away it for a while. Again, thanks everyone, I really appreciate it.

 

 

trigger UpdateNumberOfChildrenCounter on Contact (after insert, after update, after delete)
{
//The account object we will be updateing. The same account as the one the contact is in.
Account contactAccount = new Account(Id = Trigger.new[0].AccountId);
double numberOfChildren;

//Find how many children in the account
numberOfChildren = [select count() From Contact Where AccountId =:Trigger.new[0].AccountId and age__c < 18];

contactAccount.Number_of_Children__c = numberOfChildren;

//Update the accoutn object.
update contactAccount;
}

 

 

 

Message Edited by Kenji775 on 02-11-2010 11:22 AM
Kenji775Kenji775
Would you mind elaborating a bit on the using execute anonymous for the one time upate? I know you can use that type of thing for doing async calls, and web service callouts, but how would I use that for this problem? Thanks!
Kenji775Kenji775

Hey, I tried to make it bulk capable, and failed spectacularily.

 

I have a mapping of accountIds, and once I get the above error fixed, I'll have the accountId and number of children. But I am not sure exactly how to package that info it an updateable statment. How do I turn that query into something Salesforce will take as an update?

 

Here is my broke ass code. Once again, as always, and help is greatly appreciated. Thank you.

 

trigger UpdateNumberOfChildrenCounter on Contact (after insert, after update, after delete)
{
Contact[] allContacts = Trigger.new;
Contact accountContacts = new Contact();


//Create a list of account ID's from the contact records
Set<Id> accountIds = new Set<Id> {};
for (Contact c : allContacts)
{
accountIds.add( c.AccountID );
}

//Build the map of contacts from the list above
//Is basically just a list of account ID's: EX 001T000000DJuFR,001T000000DJuSF,001T000000DJuRE,...
Map<ID, Account> accounts = new Map<ID, Account>([SELECT ID FROM account WHERE Id in: accountIds ]);

//Now I need to find the total number of contacts under 18 for each account.
//I need the make sure to get a seperate total for each account, so I select the accountID, and group by it.
//The result set should hopefully look something like (the counts are just random placesholders in this example)
// AccountID Count
//Row 1 001T000000DJuFR 5
//Row 2 001T000000DJuSF 2
//Row 3 001T000000DJuRE 3
AggregateResult[] allAccounts = [select AccountId, COUNT(Id) From Contact Where AccountId in: accountIds and age__c < 18 group by AccountID ];


for (AggregateResult ar : allAccounts)
{
Account updateAccount = new Account();
updateAccount.Number_of_Children__c = 0.0;
accounts.put(ar.Id,updateAccount);

}


//Update the account object.
update accounts.values();
}

 

 

 

 

Message Edited by Kenji775 on 02-11-2010 11:56 AM
Message Edited by Kenji775 on 02-11-2010 12:27 PM
Rajesh ShahRajesh Shah

Execute Anonymous:

You can execute an apex script from Eclipse or System Log in UI. The changes that the apex script would make will be reflected in the database. So basically I would develop a script which gets me all account and their correponding contacts. I then update the account based on the contact details. Its like a backend update of the records. I can update 10,000 records in single go using Anonymous Apex.

 

Trigger:

I didn't meant to use the count in that way. Aggregate functions are not allowed in SOQL. (They would be allowed after summer10 release). 

 

Set<Id> accIdSet = new Set<Id>(); // Create Account Id set for(Contact con: trigger.new) accIdSet.add(con.AccountId); // Query Contact records List<Contact> contactList = [Select Id from Contact where ....]; // Create Map <AccountId , List<Contact>> Map<Id, List<Contact>> conAccMap = new Map<Id, List<Contact>>(); for(Contact con: contactList) { if(conAccMap.containskey(con.AccountId)) conAccMap.get(con.AccountId).add(con); else conAccMap.put(con.AccountId, new List<Contact>{con}); } for(Id AccId : conAccMap.keySet()) { // I have the Account Id. Get the Size (Count) of corr Contacts. Integer conSize = conAccMap.get(AccId).size(); Account acc = new Account(Id = accId); // Rest of updates // Add the acc to list of Accounts to be updated accList.add(acc); } update accList;

 

 

 

Kenji775Kenji775

Oh thank you so much for the cool tip. I will be doing that.

 

Regarding my original trigger, I am very very close to getting it to work. I think I have this one last error to deal with and I am good.

 

Invalid initial expression type for field Account.Id, expecting: Id    UpdateNumberOfChildrenCounter.trigger   

line 24

 

For some reason it doesn't like ar.get('AccountId') as an ID. I tried assigning that to a variable and then just assigning that variable to the ID, but that didn't work either. Any thoughts?

 

 

 

 

 

trigger UpdateNumberOfChildrenCounter on Contact (after insert, after update, after delete) { Contact[] allContacts = Trigger.new; Account[] updateAccounts = new Account[]{}; //Create a list of account ID's from the contact records Set<Id> accountIds = new Set<Id> {}; for (Contact c : allContacts) { accountIds.add( c.AccountID ); } //Build the map of contacts from the list above //Is basically just a list of account ID's: EX 001T000000DJuFR,001T000000DJuSF,001T000000DJuRE,... Map<ID, Account> accounts = new Map<ID, Account>([SELECT ID FROM account WHERE Id in: accountIds ]); AggregateResult[] allAccounts = [select AccountId, COUNT(Id)numKids From Contact Where AccountId in: accountIds and age__c < 18 group by AccountID ]; for (AggregateResult ar : allAccounts) { //This line is causing errors apperantly "Invalid initial expression type for field Account.Id, expecting id" Account thisAccount = new Account(Id=ar.get('AccountId')); thisAccount.Number_of_Children__c = ar.get('numKids'); updateAccounts.add(thisAccount); } //Update the account object. update updateAccounts; }

 

 

 

Rajesh ShahRajesh Shah
Why are you doing it this way? Can't you directly use ar.AccountId?
Kenji775Kenji775

Perhaps, I'll give it a shot. In the docs that talk about using aggregate function data, they used

 

System.debug('Campaign ID' + ar.get('CampaignId'));

 

EX

 

AggregateResult[] groupedResults
= [SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId];

for (AggregateResult ar : groupedResults)
{
System.debug('Campaign ID' + ar.get('CampaignId'));
System.debug('Average amount' + ar.get('expr0'));
}

 So i figured that was the way to do it.I'll try the other way.

 

 

EDIT: Yeah, without using the ar.get() it fails. I guess ar is actually an object, so you need to use the get method to pull any real data out of it. The error I get when trying

 

Account thisAccount = new Account(Id=ar.AccountId);


was

 

Invalid field AccountId for SObject AggregateResult    

Message Edited by Kenji775 on 02-11-2010 01:05 PM
vasvas

I believe get() method on AggregateResult returns an object which would need to be converted to the actual type. Try using String.valueOf(ar.get('accountId')) and Integer.valueOf() for the expr0. Let me know if it works.

 

I wonder where the AggregateResult methods are documented. I looked up in Apex documentation and couldn't find it.

Kenji775Kenji775

You're a genius! Thank you, yes that did it. How do you guys learn this stuff? It doesn't seem like there is enough info anywhere to draw the conclusions you do, but somehow you guys managed to put it all together. Regardless, my trigger works now. Thanks all involved!

 

Just for completness sake, here is the final trigger. If anyone needs to create counters on child objects that bulk capable, here is the code.

 

 

trigger UpdateNumberOfChildrenCounter on Contact (after insert, after update, after delete)
{
Contact[] allContacts = Trigger.new;
Account[] updateAccounts = new Account[]{};


//Create a list of account ID's from the contact records
Set<Id> accountIds = new Set<Id> {};
for (Contact c : allContacts)
{
accountIds.add( c.AccountID );
}

//Build the map of contacts from the list above
//Is basically just a list of account ID's: EX 001T000000DJuFR,001T000000DJuSF,001T000000DJuRE,...
Map<ID, Account> accounts = new Map<ID, Account>([SELECT ID FROM account WHERE Id in: accountIds ]);

//Get all the accounts, and the number of kids. Group by the account ID to get all the info
//I need together.
AggregateResult[] allAccounts = [select AccountId, COUNT(Id)numKids From Contact Where AccountId in: accountIds and age__c < 18 group by AccountID ];

//Loop over the aggregate result set
for (AggregateResult ar : allAccounts)
{
Account thisAccount = new Account(Id=String.valueOf(ar.get('accountId')));
thisAccount.Number_of_Children__c = Integer.valueOf(ar.get('numKids'));

//Add this new account to the list of account objects
updateAccounts.add(thisAccount);
}

//Update the account object.
update updateAccounts;
}

 

 

 

Message Edited by Kenji775 on 02-11-2010 01:36 PM
Message Edited by Kenji775 on 02-11-2010 01:37 PM
vasvas

That's how it works in Java so I just guessed it. Glad it helped.

 

Rajesh, 

I couldn't find the info I was looking for on that link, specifically the AggregateResult class structure and all its methods. Now I'm really curious.

wchristnywchristny

Greetings,

 

I saw this thread and realized that I had a similar need recently and was able to resolve it without having to write a trigger for it.  I used a Roll-Up Summary field with a Field Update rule.  For the issue in this thread, I would have tried the following:

 

1. Create a custom number field in Contact called "IsChild".

2. Create a Field Update rule in Contact to set "IsChild" based on the Age field, using "1" to indicate a child and "0" for an adult.  This would fire each time a Contact record is created or edited.

3. Create a Roll-Up Summary field in Account to summarize "IsChild" in Contact.  The result would give you the number of children in the account.

4. Do an initial population of "IsChild" for existing records.

 

The trick is that you can't create a Roll-Up Summary field to summarize a formula field.  So "IsChild" would have to be a number field, and the Field Update rule is needed to flag child Contact records accordingly.

 

Depending on how you use it, this might be an alternate solution.

 

Kenji775Kenji775
For some reason, my account object is not able to summarize my contact objects. I have no idea why, it just isn't even in the list of available relationships when creating a rollup field on the account. Thanks for the alternate idea though, I can probably use that type of approach on other problems.
Rajesh ShahRajesh Shah

Details on AggregateResult are available in the Apex Developers Guide. Heres the link:

http://www.salesforce.com/us/developer/docs/apexcode/index.htm

wchristnywchristny
You're right.  Salesforce won't let you create a Roll-Up Summary between two standard objects.  I guess Salesforce didn't anticipate the need to do a Roll-Up Summary on Contacts.
Kenji775Kenji775
I do find that rather odd. Seems like it should have been pretty simple to do, or even just include in the list of options. Oh well, such is the life of a Salesforce admin.
Rajesh ShahRajesh Shah
Salesforce does allows creating roll up summary fields between standard objects. For eg, you could have a roll up summary field on Account with details from Opportunity (Eg: Sum of Amount of all Opportunities for an Account). I think its way Salesforce has defined the relationship between Accounts and Contact that doesn't allows roll up summary fields.
Kenji775Kenji775
Whats so special about contact->account relationships I wonder? I mean, it's a relationship like any other, as far as I can tell.
Idd BaksheeshIdd Baksheesh

Hi A quick question, in the aggregate function, suppose I am aggregating using a relationship.

 

list<aggregateResult> LstAgr= [select Something from somewhere group by  object1__r.object2__c limit 1000];

 

Then while fetching from AggregateResult do I have to mention the relationship.

 

SetOfAccount.add(String.Valueof(ar.get('object1__r.object2__c')));

 

or do I only mention the object2???

 

SetOfAccount.add(String.Valueof(ar.get('object2__c')));