We have several divisions of our company, and I am trying to create a VF page using an extension that will return a list of our custom object records (Roles__c), but then somehow group them on a page by division using maybe pageBlockTables. I'm created this VF page to use as a Site so that another area of our company can view some of our data.
No editiing of this data will need to be done -- it's display/read only.
public class roleControllerExtension {
private final Role__c r;
public roleControllerExtension (ApexPages.StandardSetController stdController){}
public String queryString(){
String queryString = 'SELECT r.Id, r.Name, Customer_Name__c, Bill_Account__c, Contact_Name__c,Title__c,Phone__c,Mobile__c, Division_Name__c FROM Role__c r';
return queryString;
}
public ApexPages.StandardSetController roleController {
get {
if(roleController == null) {
String query = queryString();
return new ApexPages.StandardSetController(Database.getQueryLocator(query));
}
return roleController;
}
}
public List<Role__c> getRoles(){
return (List<Role__c>) roleController.getRecords();
}
}
What I'd like is for each pageBlockTable to contain one division's roles. Does anyone have a recommendation of how to get there? The recordSetVar is going to bring back ALL division roles, which is fine, but how do I get them in separate pageBlockTables?
I know I'm missing something really simple here, but I'm trying to optimize a trigger by implementing AggregateResult and am stumped about how to get from an AR object to an SObject. I'm trying to sum off children and update to parent.
Assume:
---------------------
Child object:
Child__c
Fields:
- childFieldA__c
- childFieldB__c
- childfieldC__c
Parent Object:
Parent__c
Fields:
- summedFieldA__c
- summedFieldB__c
- summedfieldC__c
So here's what I've got thus far:
trigger updateParent on Child__c (after insert, after update, after delete, after undelete) {
Set<Id> parentIdSet = new Set<Id>();
for(Child__c c : Trigger.new){
parentIdSet.add(c.Parent__c);
}
for (aggregateresult ar:[select Parent__c, sum(childFieldA__c) sumA, sum(childFieldB__c) sumB, sum(childFieldC__c) sumC From Child__c where Parent__c in :parentIdSet Group By Parent__c ]){
// (...big area of confused mystery exists here...)
}
}
For each Parent__c, I want to then go through and set the values like so: Parent__c.summedFieldA = ar.get('sumA')
... and then do a big update() at the end.
Keep in mind that I'm trying to optimize, here, and have been trying to keep loops out of loops and other practices. My main question is: now that I have the AR objects, how do I go through and update the Parent__c objects with the values I've summed in the AR object? I know this is something obviously simple, but it's just stumping me on how to make the two object set types interact -- AR and Parent__c.
I've seen several questions arise about this and ran into it too when trying to import data via the DataLoder and wanted to see if I could get some help. First...
Business Context:
--------------------------
I have a cascading roll-up that needs to happen within the following hierarchy of objects:
Organization (account object)
Bill Account (custom) Premise (custom)
We have premises (think "your house") and bill accounts (you living AT a particular premise). There is a many-to-one relationship between premises and bill accounts -- you can have multiple electrical connection points as a customer. However, we may also have a need to roll several bill accounts up to what we call an "organization". So an organization of "McDonalds" may have many bill accounts.
We want to be able to see revenue aggregated all the way up the chain. A trigger on Premise__c updates revenue, kilowatt hour usage, etc. on its parent Bill_Account__c. From there, a trigger on Bill_Account__c fires and updates the Organization (Account) that it belongs to.
The Code
---------------
On Premise__c, I have this trigger:
trigger updateBillAccountSummedUsage on Premise__c (after insert, after update, after delete, after undelete) {
ch 2012
// This trigger will update a billAccount record with values found on the related Bill Accounts
// First, we create a Set to hold billAccount IDs of Bill Accounts in trigger and a List to hold billAccounts which will be updated
Set<Id> premiseIdSet = new Set<Id>();
List<Bill_Account__c> billAccountUpdateList = new List<Bill_Account__c>();
// Next, we go through either trigger.old (in the case of deletes or updates) or trigger.new
// if this is an insert, update or undelete. When going through the trigger, we create ths set of billAccount IDs
// that are found within the trigger.
// If this is a delete trigger, pull them the trigger.old, otherwise from trigger.new
if(Trigger.isDelete || trigger.isUpdate)
for(Premise__c p : trigger.old){
premiseIdSet.add(p.Id);
}
if(trigger.isInsert || trigger.isUpdate || trigger.isUndelete)
for(Premise__c p : trigger.new){
premiseIdSet.add(p.Id);
}
// Now we query for all of the billAccounts associated with the Bill Accounts in the trigger using the above created set of Ids
List<Bill_Account__c> billAccountList = [Select b.Premise_Number__r.Rolling_12_Month_kWh_Total__c, b.Premise_Number__r.Rolling_12_Month_Revenue_Total__c, b.Premise_Number__r.Rolling_12_Month_Peak_Demand__c, b.Premise_Number__r.Id, b.Premise_Number__c, b.Name, b.Id From Bill_Account__c b where b.Premise_Number__c IN :premiseIdSet];
// We iterate through the billAccounts and then through the Bill Accounts related to those billAccounts.
for(Bill_Account__c b : billAccountList){
// Set the billAccount record's fields with the newly summed totals.
b.Rolling_12_Month_kWh_Total__c = b.Premise_Number__r.Rolling_12_Month_kWh_Total__c;
b.Rolling_12_Month_Revenue_Total__c = b.Premise_Number__r.Rolling_12_Month_Revenue_Total__c;
b.Rolling_12_Month_Peak_Demand__c = b.Premise_Number__r.Rolling_12_Month_Peak_Demand__c;
billAccountUpdateList.add(b);
}
if(!billAccountUpdateList.IsEmpty()) update billAccountUpdateList;
}
On Bill_Account__c, I have this trigger to go off and update the Organization (Account) record:
trigger updateOrganization on Bill_Account__c (after insert, after update, after delete, after undelete) {
// This trigger will update a organization record with values found on the related Bill Accounts
// First, we create a Set to hold organization IDs of Bill Accounts in trigger and a List to hold organizations which will be updated
Set<Id> organizationIdSet = new Set<Id>();
List<Account> organizationUpdateList = new List<Account>();
// Next, we go through either trigger.old (in the case of deletes or updates) or trigger.new
// if this is an insert, update or undelete. When going through the trigger, we create ths set of organization IDs
// that are found within the trigger.
// If this is a delete trigger, pull them the trigger.old, otherwise from trigger.new
if(Trigger.isDelete || trigger.isUpdate)
for(Bill_Account__c ba : trigger.old)
{if(ba.Organization__c != null)
organizationIdSet.add(ba.Organization__c);
}
if(trigger.isInsert || trigger.isUpdate || trigger.isUndelete)
for(Bill_Account__c ba : trigger.new){
if(ba.Organization__c != null)
organizationIdSet.add(ba.Organization__c);
}
// Now we query for all of the organizations associated with the Bill Accounts in the trigger using the above created set of Ids
List<Account> organizationList = [select o.Name, o.Id, (select Bill_Account_Status__c, Rolling_12_Month_kWh_Total__c, Rolling_12_Month_Revenue_Total__c, Rolling_12_Month_Peak_Demand__c, Name, Id, Division__r.External_Division_ID__c, Division__r.Name, Premise_City__c From Bill_Accounts__r where Bill_Account_Status__r.External_Status_ID__c = '02') From Account o where o.Id IN :organizationIdSet];
for(Account o : organizationList){
Decimal totAnnualkWh = 0;
Decimal totAnnualRev = 0;
Decimal peakDemand = 0;
String divList, locList;
Integer i=0,j=0;
Set<String> uniqueDivisionSet = new Set<String>();
Set<String> uniqueLocationSet = new Set<String>();
Map<String, String> divMap = new Map<String,String>();
dataClean dc = new dataClean();
// Sum the revenue and kWh and check to see if the current record has the highest peak demand of all of the Bill Accounts
for(Bill_Account__c ba : o.Bill_Accounts__r){
totAnnualRev += ba.Rolling_12_Month_Revenue_Total__c;
totAnnualkWh += ba.Rolling_12_Month_kWh_Total__c;
if (ba.Rolling_12_Month_Peak_Demand__c >= peakDemand)
peakDemand = ba.Rolling_12_Month_Peak_Demand__c;
// We have a List of Division Names and ID's, but need to put them in a Map structure to sort by Division number.
if (ba.Division__r.Name!=null){
divMap.put(ba.Division__r.External_Division_ID__c,ba.Division__r.Name);
uniqueDivisionSet.add(ba.Division__r.Name);
}
dc.toTitleCase(ba.Premise_City__c));
uniqueLocationSet.add(dc.toTitleCase(ba.Premise_City__c));
}
List<String> sortedLocList = new List<String>();
sortedLocList.addAll(uniqueLocationSet);
sortedLocList.sort();
for (string locName:sortedLocList){
if (sortedLocList.size()>7){
locList = sortedLocList.size() + ' locations around the state.';
}else{
if (j==0){
locList = locName;
}else if (j==sortedLocList.size()-1){
locList = locList + ' and ' + locName;
}else {
locList = locList + ', ' + locName;
}
}
j++;
}
//We'll add all of the Map keys to a list so we can sort them.
List<String> sortedDivIDList = new List<String>();
sortedDivIDList.addAll(divMap.keySet());
sortedDivIDList.sort();
//Run through the sorted list and grab the Value associated with the newly-sorted Keys.
i=0;
for(string divName : sortedDivIDList){
if (i==0){
divList = divMap.get(divName);
}else if (i==(uniqueDivisionSet.size()-1)){
divList = divList + ' and ' + divMap.get(divName);
}else {
divList = divList + ', ' + divMap.get(divName);
}
i++;
}
// Set the organization record's fields with the new values.
o.Divisions__c = divList;
o.Bill_Account_Locations__c = locList;
o.Rolling_12_Month_kWh_Total__c = totAnnualkWh;
o.Rolling_12_Month_Revenue_Total__c = totAnnualRev;
o.Rolling_12_Month_Peak_Demand__c = peakDemand;
organizationUpdateList.add(o);
}
if(!organizationUpdateList.IsEmpty()) update organizationUpdateList;
}
(Note in the above code, that there are a few extra bits of code which, for every Organization getting updated, go through and make a string out of values shown in its child records, notably a list of our divisions and a list of cities that the bill accounts are in.)
When I do a 12k record premise upsert through the Data Loader, I get the "System.LimitException: Too many script statements: 200001" error. I have a feeling that it has to do with the 3-level deep trigger firing that is going on, but can't really pinpoint where to optimize the "for" loops or SOQL.
I'd greatly appreciate any tips or thoughts on lines that I could tune-up to get past what is obviously "scripting gone wild".
We have several divisions of our company, and I am trying to create a VF page using an extension that will return a list of our custom object records (Roles__c), but then somehow group them on a page by division using maybe pageBlockTables. I'm created this VF page to use as a Site so that another area of our company can view some of our data.
No editiing of this data will need to be done -- it's display/read only.
public class roleControllerExtension {
private final Role__c r;
public roleControllerExtension (ApexPages.StandardSetController stdController){}
public String queryString(){
String queryString = 'SELECT r.Id, r.Name, Customer_Name__c, Bill_Account__c, Contact_Name__c,Title__c,Phone__c,Mobile__c, Division_Name__c FROM Role__c r';
return queryString;
}
public ApexPages.StandardSetController roleController {
get {
if(roleController == null) {
String query = queryString();
return new ApexPages.StandardSetController(Database.getQueryLocator(query));
}
return roleController;
}
}
public List<Role__c> getRoles(){
return (List<Role__c>) roleController.getRecords();
}
}
What I'd like is for each pageBlockTable to contain one division's roles. Does anyone have a recommendation of how to get there? The recordSetVar is going to bring back ALL division roles, which is fine, but how do I get them in separate pageBlockTables?
A--< B >-- C . On A there is a RFS on B, and then there is an after update trigger that when run populates fields on B. One of B's Fields are then rolled-up into a field on C.
Question:
The trigger works, but i need to run it on the existing records in the DB to bring everything up to date. How do I do that? I already tried running a 'force mass recalculation with the RFS on A and C.
I know I'm missing something really simple here, but I'm trying to optimize a trigger by implementing AggregateResult and am stumped about how to get from an AR object to an SObject. I'm trying to sum off children and update to parent.
Assume:
---------------------
Child object:
Child__c
Fields:
- childFieldA__c
- childFieldB__c
- childfieldC__c
Parent Object:
Parent__c
Fields:
- summedFieldA__c
- summedFieldB__c
- summedfieldC__c
So here's what I've got thus far:
trigger updateParent on Child__c (after insert, after update, after delete, after undelete) {
Set<Id> parentIdSet = new Set<Id>();
for(Child__c c : Trigger.new){
parentIdSet.add(c.Parent__c);
}
for (aggregateresult ar:[select Parent__c, sum(childFieldA__c) sumA, sum(childFieldB__c) sumB, sum(childFieldC__c) sumC From Child__c where Parent__c in :parentIdSet Group By Parent__c ]){
// (...big area of confused mystery exists here...)
}
}
For each Parent__c, I want to then go through and set the values like so: Parent__c.summedFieldA = ar.get('sumA')
... and then do a big update() at the end.
Keep in mind that I'm trying to optimize, here, and have been trying to keep loops out of loops and other practices. My main question is: now that I have the AR objects, how do I go through and update the Parent__c objects with the values I've summed in the AR object? I know this is something obviously simple, but it's just stumping me on how to make the two object set types interact -- AR and Parent__c.
I am attempting to populate my lookup field from Accounts using a query to get the correct id. I need to update several thousand records, but my existing trigger will not do more than 100. Here is the code. What changes do I need to make for this to work?
trigger CustNameStatus on Sales_History__c (before insert, before update){
I've seen several questions arise about this and ran into it too when trying to import data via the DataLoder and wanted to see if I could get some help. First...
Business Context:
--------------------------
I have a cascading roll-up that needs to happen within the following hierarchy of objects:
Organization (account object)
Bill Account (custom) Premise (custom)
We have premises (think "your house") and bill accounts (you living AT a particular premise). There is a many-to-one relationship between premises and bill accounts -- you can have multiple electrical connection points as a customer. However, we may also have a need to roll several bill accounts up to what we call an "organization". So an organization of "McDonalds" may have many bill accounts.
We want to be able to see revenue aggregated all the way up the chain. A trigger on Premise__c updates revenue, kilowatt hour usage, etc. on its parent Bill_Account__c. From there, a trigger on Bill_Account__c fires and updates the Organization (Account) that it belongs to.
The Code
---------------
On Premise__c, I have this trigger:
trigger updateBillAccountSummedUsage on Premise__c (after insert, after update, after delete, after undelete) {
ch 2012
// This trigger will update a billAccount record with values found on the related Bill Accounts
// First, we create a Set to hold billAccount IDs of Bill Accounts in trigger and a List to hold billAccounts which will be updated
Set<Id> premiseIdSet = new Set<Id>();
List<Bill_Account__c> billAccountUpdateList = new List<Bill_Account__c>();
// Next, we go through either trigger.old (in the case of deletes or updates) or trigger.new
// if this is an insert, update or undelete. When going through the trigger, we create ths set of billAccount IDs
// that are found within the trigger.
// If this is a delete trigger, pull them the trigger.old, otherwise from trigger.new
if(Trigger.isDelete || trigger.isUpdate)
for(Premise__c p : trigger.old){
premiseIdSet.add(p.Id);
}
if(trigger.isInsert || trigger.isUpdate || trigger.isUndelete)
for(Premise__c p : trigger.new){
premiseIdSet.add(p.Id);
}
// Now we query for all of the billAccounts associated with the Bill Accounts in the trigger using the above created set of Ids
List<Bill_Account__c> billAccountList = [Select b.Premise_Number__r.Rolling_12_Month_kWh_Total__c, b.Premise_Number__r.Rolling_12_Month_Revenue_Total__c, b.Premise_Number__r.Rolling_12_Month_Peak_Demand__c, b.Premise_Number__r.Id, b.Premise_Number__c, b.Name, b.Id From Bill_Account__c b where b.Premise_Number__c IN :premiseIdSet];
// We iterate through the billAccounts and then through the Bill Accounts related to those billAccounts.
for(Bill_Account__c b : billAccountList){
// Set the billAccount record's fields with the newly summed totals.
b.Rolling_12_Month_kWh_Total__c = b.Premise_Number__r.Rolling_12_Month_kWh_Total__c;
b.Rolling_12_Month_Revenue_Total__c = b.Premise_Number__r.Rolling_12_Month_Revenue_Total__c;
b.Rolling_12_Month_Peak_Demand__c = b.Premise_Number__r.Rolling_12_Month_Peak_Demand__c;
billAccountUpdateList.add(b);
}
if(!billAccountUpdateList.IsEmpty()) update billAccountUpdateList;
}
On Bill_Account__c, I have this trigger to go off and update the Organization (Account) record:
trigger updateOrganization on Bill_Account__c (after insert, after update, after delete, after undelete) {
// This trigger will update a organization record with values found on the related Bill Accounts
// First, we create a Set to hold organization IDs of Bill Accounts in trigger and a List to hold organizations which will be updated
Set<Id> organizationIdSet = new Set<Id>();
List<Account> organizationUpdateList = new List<Account>();
// Next, we go through either trigger.old (in the case of deletes or updates) or trigger.new
// if this is an insert, update or undelete. When going through the trigger, we create ths set of organization IDs
// that are found within the trigger.
// If this is a delete trigger, pull them the trigger.old, otherwise from trigger.new
if(Trigger.isDelete || trigger.isUpdate)
for(Bill_Account__c ba : trigger.old)
{if(ba.Organization__c != null)
organizationIdSet.add(ba.Organization__c);
}
if(trigger.isInsert || trigger.isUpdate || trigger.isUndelete)
for(Bill_Account__c ba : trigger.new){
if(ba.Organization__c != null)
organizationIdSet.add(ba.Organization__c);
}
// Now we query for all of the organizations associated with the Bill Accounts in the trigger using the above created set of Ids
List<Account> organizationList = [select o.Name, o.Id, (select Bill_Account_Status__c, Rolling_12_Month_kWh_Total__c, Rolling_12_Month_Revenue_Total__c, Rolling_12_Month_Peak_Demand__c, Name, Id, Division__r.External_Division_ID__c, Division__r.Name, Premise_City__c From Bill_Accounts__r where Bill_Account_Status__r.External_Status_ID__c = '02') From Account o where o.Id IN :organizationIdSet];
for(Account o : organizationList){
Decimal totAnnualkWh = 0;
Decimal totAnnualRev = 0;
Decimal peakDemand = 0;
String divList, locList;
Integer i=0,j=0;
Set<String> uniqueDivisionSet = new Set<String>();
Set<String> uniqueLocationSet = new Set<String>();
Map<String, String> divMap = new Map<String,String>();
dataClean dc = new dataClean();
// Sum the revenue and kWh and check to see if the current record has the highest peak demand of all of the Bill Accounts
for(Bill_Account__c ba : o.Bill_Accounts__r){
totAnnualRev += ba.Rolling_12_Month_Revenue_Total__c;
totAnnualkWh += ba.Rolling_12_Month_kWh_Total__c;
if (ba.Rolling_12_Month_Peak_Demand__c >= peakDemand)
peakDemand = ba.Rolling_12_Month_Peak_Demand__c;
// We have a List of Division Names and ID's, but need to put them in a Map structure to sort by Division number.
if (ba.Division__r.Name!=null){
divMap.put(ba.Division__r.External_Division_ID__c,ba.Division__r.Name);
uniqueDivisionSet.add(ba.Division__r.Name);
}
dc.toTitleCase(ba.Premise_City__c));
uniqueLocationSet.add(dc.toTitleCase(ba.Premise_City__c));
}
List<String> sortedLocList = new List<String>();
sortedLocList.addAll(uniqueLocationSet);
sortedLocList.sort();
for (string locName:sortedLocList){
if (sortedLocList.size()>7){
locList = sortedLocList.size() + ' locations around the state.';
}else{
if (j==0){
locList = locName;
}else if (j==sortedLocList.size()-1){
locList = locList + ' and ' + locName;
}else {
locList = locList + ', ' + locName;
}
}
j++;
}
//We'll add all of the Map keys to a list so we can sort them.
List<String> sortedDivIDList = new List<String>();
sortedDivIDList.addAll(divMap.keySet());
sortedDivIDList.sort();
//Run through the sorted list and grab the Value associated with the newly-sorted Keys.
i=0;
for(string divName : sortedDivIDList){
if (i==0){
divList = divMap.get(divName);
}else if (i==(uniqueDivisionSet.size()-1)){
divList = divList + ' and ' + divMap.get(divName);
}else {
divList = divList + ', ' + divMap.get(divName);
}
i++;
}
// Set the organization record's fields with the new values.
o.Divisions__c = divList;
o.Bill_Account_Locations__c = locList;
o.Rolling_12_Month_kWh_Total__c = totAnnualkWh;
o.Rolling_12_Month_Revenue_Total__c = totAnnualRev;
o.Rolling_12_Month_Peak_Demand__c = peakDemand;
organizationUpdateList.add(o);
}
if(!organizationUpdateList.IsEmpty()) update organizationUpdateList;
}
(Note in the above code, that there are a few extra bits of code which, for every Organization getting updated, go through and make a string out of values shown in its child records, notably a list of our divisions and a list of cities that the bill accounts are in.)
When I do a 12k record premise upsert through the Data Loader, I get the "System.LimitException: Too many script statements: 200001" error. I have a feeling that it has to do with the 3-level deep trigger firing that is going on, but can't really pinpoint where to optimize the "for" loops or SOQL.
I'd greatly appreciate any tips or thoughts on lines that I could tune-up to get past what is obviously "scripting gone wild".
I've looked up as many examples of URLFOR as I can find, and I think I'm following them nicely, but I'm getting an error when I try to save the S-Control that says Syntax error. Found '['
I'm putting the [ just where it seems it should go in the examples. Can anyone tell me what the problem is then with this thing??
I installed picture uploader, but once I upload a photo, it does appear on my Contacts page. I think I'm missing something. I installed the app, added the Upload Picture button, and added the Picture field to the page layout. Has anyone else had trouble with this? All I get when I upload a picture is a box with a red X. Does the Picture ID field or Picture Uploader Processor (custom S-control) need to be place in the page layout for this to work. Any help will be greatly appreciated!!
