Investors Bonds_Pitched__c –> The total number of Bond_Buy__c that have been pitched (offered) to the investor. Bonds_Purchased__c –> The total number of Bond_Buy__c the investor has purchased.
Bond Offerings Each bond offering is for a total of $1,000,000 and is composed of 1,000 units of $1,000. Units_Pending__c –> The number of $1,000 Bond_Buy__c units that are being pitched to investors but have not yet been purchased or rejected. Units_Sold__c –> The total number of $1,000 Bond_Buy__c units that have been purchased.
Bond Buy Bond_Offering__c –> The bond offering being purchased. Investor__c –> The investor being pitched the bond offering. Units__c –> The number of $1,000 bond units that are being offered. Status__c –> The status of the bond buy. Valid values are Pending, Passed, and Purchased.
* Requirement When changing any of these four custom fields (Bond_Offering__c(it's a lookup), Investor__c(it's a lookup), Status__c, Units__c) on custom object Bond_Buy__c, 2 custom fields (Bonds_Pitched__c and Bonds_Purchased__c) on custom object Investor__c and 2 custom fields (Units_Pending__c and Units_Sold__c) on custom object Bond_Offering__c need to be updated. The updates should occur in real-time and should accommodate when Bond_Buy__c are created, edited, deleted, and undeleted.
Notice:roll-up summary NOT allowed (cause it is NOT master-detail relationship)
Below is my code. I am using trigger on Bond_Buy__c. It might be wrong. *The scenario is I may have 0 in Bonds_Pitched__c or Bonds_Purchased__c or Units_Pending__c or Units_Sold__c fields when the Investors or Bond Offerings HAS one or more Bond Buy. I guess the problem might be ----> else { inv.Bonds_Pitched__c =0; } in the for loop. But i have no idea how to fix it. Please help.
trigger BondBuyUpdate on Bond_Buy__c (after insert, after update) {
//Limit the size of list by using Sets which do not contain duplicate elements
set<Id> InvestorIds = new set<Id>();
set<Id> InvestorIdsP = new set<Id>();
set<Id> BondOfferingIdsP = new set<Id>();
set<Id> BondOfferingIdsS = new set<Id>();
//When adding new Bond_Buy__c or updating existing Bond_Buy__c
for(Bond_Buy__c bonbuy : trigger.new){
if(Trigger.isInsert ||(Trigger.isUpdate && (Trigger.oldmap.get(bonbuy.id).Status__c!=bonbuy.Status__c ||
Trigger.oldmap.get(bonbuy.id).Units__c!=bonbuy.Units__c ||
Trigger.oldmap.get(bonbuy.id).Bond_Offering__c!= bonbuy.Bond_Offering__c||
Trigger.oldmap.get(bonbuy.id).Investor__c!=bonbuy.Investor__c)))
{
InvestorIds.add(bonbuy.Investor__c);
InvestorIdsP.add(bonbuy.Investor__c);
BondOfferingIdsP.add(bonbuy.Bond_Offering__c);
BondOfferingIdsS.add(bonbuy.Bond_Offering__c);
}
}
//Map will contain one Investor/BondOffering Id to one sum value
map<Id,Double> InvestorIdsPitched = new map <Id,Double>();
map<Id,Double> InvestorIdsPurchased = new map <Id,Double>();
map<Id,Double> BondOfferingIdsPending = new map <Id,Double>();
map<Id,Double> BondOfferingIdsSold = new map <Id,Double>();
//Produce a sum of units on child object and add them to the map
//use group by to have a single parent Id with a single sum value
//Investor Bonds_Pitched__c new update
for(AggregateResult q : [select Investor__c,sum(Units__c)s1 from Bond_Buy__c where Investor__c IN :InvestorIds group by Investor__c]){
InvestorIdsPitched.put((Id)q.get('Investor__c'),(Double)q.get('s1'));
}
List<Investor__c> InvestorToUpdatePitched = [Select Id, Bonds_Pitched__c from Investor__c where Id IN :InvestorIds];
for(Investor__c inv : InvestorToUpdatePitched ){
if(InvestorIdsPitched.containsKey(inv.Id))
{
inv.Bonds_Pitched__c = InvestorIdsPitched.get(inv.Id);
}
else
{
inv.Bonds_Pitched__c =0;
}
system.debug('resultPitched '+inv.Bonds_Pitched__c);
}
if(InvestorToUpdatePitched.size()>0)
{
update InvestorToUpdatePitched;
}
//Investor Bonds_Purchased__c new update
for(AggregateResult q : [select Investor__c,sum(Units__c)s2 from Bond_Buy__c where Status__c='Purchased' and Investor__c IN :InvestorIdsP group by Investor__c]){
InvestorIdsPurchased.put((Id)q.get('Investor__c'),(Double)q.get('s2'));
}
List<Investor__c> InvestorToUpdatePurchased = [Select Id, Bonds_Purchased__c from Investor__c where Id IN :InvestorIdsP];
for(Investor__c inv2 : InvestorToUpdatePurchased ){
if(InvestorIdsPurchased.containsKey(inv2.Id))
{
inv2.Bonds_Purchased__c = InvestorIdsPurchased.get(inv2.Id);
}
else
{
inv2.Bonds_Purchased__c =0;
}
system.debug('resultPurchased '+inv2.Bonds_Purchased__c);
}
if(InvestorToUpdatePurchased.size()>0)
{
update InvestorToUpdatePurchased;
}
//Bond_Offering__c Units_Pending__c new update
for(AggregateResult q : [select Bond_Offering__c,sum(Units__c)s3 from Bond_Buy__c where Status__c='Pending' and Bond_Offering__c IN :BondOfferingIdsP group by Bond_Offering__c]){
BondOfferingIdsPending.put((Id)q.get('Bond_Offering__c'),(Double)q.get('s3'));
}
List<Bond_Offering__c> BondOfferingToUpdatePending = [Select Id, Units_Pending__c from Bond_Offering__c where Id IN :BondOfferingIdsP];
for(Bond_Offering__c bon : BondOfferingToUpdatePending){
if(BondOfferingIdsPending.containsKey(bon.Id))
{
bon.Units_Pending__c = BondOfferingIdsPending.get(bon.Id);
}
else
{
bon.Units_Pending__c =0;
}
}
if(BondOfferingToUpdatePending.size()>0)
{
update BondOfferingToUpdatePending;
}
//Bond_Offering__c Units_Sold__c new update
for(AggregateResult q : [select Bond_Offering__c,sum(Units__c)s4 from Bond_Buy__c where Status__c='Purchased' and Bond_Offering__c IN :BondOfferingIdsS group by Bond_Offering__c]){
BondOfferingIdsSold.put((Id)q.get('Bond_Offering__c'),(Double)q.get('s4'));
}
List<Bond_Offering__c> BondOfferingToUpdateSold = [Select Id, Units_Sold__c from Bond_Offering__c where Id IN :BondOfferingIdsS];
for(Bond_Offering__c bon2 : BondOfferingToUpdateSold){
if(BondOfferingIdsSold.containsKey(bon2.Id))
{
bon2.Units_Sold__c = BondOfferingIdsSold.get(bon2.Id);
}
else
{
bon2.Units_Sold__c =0;
}
}
if(BondOfferingToUpdateSold.size()>0)
{
update BondOfferingToUpdateSold;
}
}
trigger BondBuyUpdate2 on Bond_Buy__c (after update, after delete, after undelete) {
set<Id> InvestorIdsOlder = new set<Id>();
set<Id> InvestorIdsPOlder = new set<Id>();
set<Id> BondOfferingIdsPOlder = new set<Id>();
set<Id> BondOfferingIdsSOlder = new set<Id>();
for(Bond_Buy__c bonbuy : trigger.old)
{
{
InvestorIdsOlder.add(bonbuy.Investor__c);
InvestorIdsPOlder.add(bonbuy.Investor__c);
BondOfferingIdsPOlder.add(bonbuy.Bond_Offering__c);
BondOfferingIdsSOlder.add(bonbuy.Bond_Offering__c);
}
}
map<Id,Double> InvestorIdsPitchedOld = new map <Id,Double>();
map<Id,Double> InvestorIdsPurchasedOld = new map <Id,Double>();
map<Id,Double> BondOfferingIdsPendingOld = new map <Id,Double>();
map<Id,Double> BondOfferingIdsSoldOld = new map <Id,Double>();
//Investor Bonds_Pitched__c old update
List<Investor__c> InvestorToUpdatePitchedOld = [Select Id, Bonds_Pitched__c from Investor__c where Id IN :InvestorIdsOlder];
for(Investor__c inv : InvestorToUpdatePitchedOld ){
if(InvestorIdsPitchedOld.containsKey(inv.Id))
{
inv.Bonds_Pitched__c = InvestorIdsPitchedOld.get(inv.Id);
}
else
{
inv.Bonds_Pitched__c =0;
}
system.debug('resultPitched '+inv.Bonds_Pitched__c);
}
if(InvestorToUpdatePitchedOld.size()>0)
{
update InvestorToUpdatePitchedOld;
}
//Investor Bonds_Purchased__c old update
List<Investor__c> InvestorToUpdatePurchasedOld = [Select Id, Bonds_Purchased__c from Investor__c where Id IN :InvestorIdsPOlder];
for(Investor__c inv2 : InvestorToUpdatePurchasedOld ){
if(InvestorIdsPurchasedOld.containsKey(inv2.Id))
{
inv2.Bonds_Purchased__c = InvestorIdsPurchasedOld.get(inv2.Id);
}
else
{
inv2.Bonds_Purchased__c =0;
}
system.debug('resultPurchased '+inv2.Bonds_Purchased__c);
}
if(InvestorToUpdatePurchasedOld.size()>0)
{
update InvestorToUpdatePurchasedOld;
}
//Bond_Offering__c Units_Pending__c old update
List<Bond_Offering__c> BondOfferingToUpdatePendingOld = [Select Id, Units_Pending__c from Bond_Offering__c where Id IN :BondOfferingIdsPOlder];
for(Bond_Offering__c bon : BondOfferingToUpdatePendingOld){
if(BondOfferingIdsPendingOld.containsKey(bon.Id))
{
bon.Units_Pending__c = BondOfferingIdsPendingOld.get(bon.Id);
}
else
{
bon.Units_Pending__c =0;
}
}
if(BondOfferingToUpdatePendingOld.size()>0)
{
update BondOfferingToUpdatePendingOld;
}
//Bond_Offering__c Units_Sold__c old update
List<Bond_Offering__c> BondOfferingToUpdateSoldOld = [Select Id, Units_Sold__c from Bond_Offering__c where Id IN :BondOfferingIdsSOlder];
for(Bond_Offering__c bon2 : BondOfferingToUpdateSoldOld){
if(BondOfferingIdsSoldOld.containsKey(bon2.Id))
{
bon2.Units_Sold__c = BondOfferingIdsSoldOld.get(bon2.Id);
}
else
{
bon2.Units_Sold__c =0;
}
}
if(BondOfferingToUpdateSoldOld.size()>0)
{
update BondOfferingToUpdateSoldOld;
}
}
Investors Bonds_Pitched__c –> The total number of Bond_Buy__c that have been pitched (offered) to the investor. Bonds_Purchased__c –> The total number of Bond_Buy__c the investor has purchased.
Bond Offerings Each bond offering is for a total of $1,000,000 and is composed of 1,000 units of $1,000. Units_Pending__c –> The number of $1,000 Bond_Buy__c units that are being pitched to investors but have not yet been purchased or rejected. Units_Sold__c –> The total number of $1,000 Bond_Buy__c units that have been purchased.
Bond Buy Bond_Offering__c –> The bond offering being purchased. Investor__c –> The investor being pitched the bond offering. Units__c –> The number of $1,000 bond units that are being offered. Status__c –> The status of the bond buy. Valid values are Pending, Passed, and Purchased.
* Requirement When changing any of these four custom fields (Bond_Offering__c(it's a lookup), Investor__c(it's a lookup), Status__c, Units__c) on custom object Bond_Buy__c, 2 custom fields (Bonds_Pitched__c and Bonds_Purchased__c) on custom object Investor__c and 2 custom fields (Units_Pending__c and Units_Sold__c) on custom object Bond_Offering__c need to be updated. The updates should occur in real-time and should accommodate when Bond_Buy__c are created, edited, deleted, and undeleted.
Notice:roll-up summary NOT allowed (cause it is NOT master-detail relationship)
Below is my code. I am using trigger on Bond_Buy__c. It might be wrong. *The scenario is I may have 0 in Bonds_Pitched__c or Bonds_Purchased__c or Units_Pending__c or Units_Sold__c fields when the Investors or Bond Offerings HAS one or more Bond Buy. I guess the problem might be ----> else { inv.Bonds_Pitched__c =0; } in the for loop. But i have no idea how to fix it. Please help.
trigger BondBuyUpdate on Bond_Buy__c (after insert, after update) {
//Limit the size of list by using Sets which do not contain duplicate elements
set<Id> InvestorIds = new set<Id>();
set<Id> InvestorIdsP = new set<Id>();
set<Id> BondOfferingIdsP = new set<Id>();
set<Id> BondOfferingIdsS = new set<Id>();
//When adding new Bond_Buy__c or updating existing Bond_Buy__c
for(Bond_Buy__c bonbuy : trigger.new){
if(Trigger.isInsert ||(Trigger.isUpdate && (Trigger.oldmap.get(bonbuy.id).Status__c!=bonbuy.Status__c ||
Trigger.oldmap.get(bonbuy.id).Units__c!=bonbuy.Units__c ||
Trigger.oldmap.get(bonbuy.id).Bond_Offering__c!= bonbuy.Bond_Offering__c||
Trigger.oldmap.get(bonbuy.id).Investor__c!=bonbuy.Investor__c)))
{
InvestorIds.add(bonbuy.Investor__c);
InvestorIdsP.add(bonbuy.Investor__c);
BondOfferingIdsP.add(bonbuy.Bond_Offering__c);
BondOfferingIdsS.add(bonbuy.Bond_Offering__c);
}
}
//Map will contain one Investor/BondOffering Id to one sum value
map<Id,Double> InvestorIdsPitched = new map <Id,Double>();
map<Id,Double> InvestorIdsPurchased = new map <Id,Double>();
map<Id,Double> BondOfferingIdsPending = new map <Id,Double>();
map<Id,Double> BondOfferingIdsSold = new map <Id,Double>();
//Produce a sum of units on child object and add them to the map
//use group by to have a single parent Id with a single sum value
//Investor Bonds_Pitched__c new update
for(AggregateResult q : [select Investor__c,sum(Units__c)s1 from Bond_Buy__c where Investor__c IN :InvestorIds group by Investor__c]){
InvestorIdsPitched.put((Id)q.get('Investor__c'),(Double)q.get('s1'));
}
List<Investor__c> InvestorToUpdatePitched = [Select Id, Bonds_Pitched__c from Investor__c where Id IN :InvestorIds];
for(Investor__c inv : InvestorToUpdatePitched ){
if(InvestorIdsPitched.containsKey(inv.Id))
{
inv.Bonds_Pitched__c = InvestorIdsPitched.get(inv.Id);
}
else
{
inv.Bonds_Pitched__c =0;
}
system.debug('resultPitched '+inv.Bonds_Pitched__c);
}
if(InvestorToUpdatePitched.size()>0)
{
update InvestorToUpdatePitched;
}
//Investor Bonds_Purchased__c new update
for(AggregateResult q : [select Investor__c,sum(Units__c)s2 from Bond_Buy__c where Status__c='Purchased' and Investor__c IN :InvestorIdsP group by Investor__c]){
InvestorIdsPurchased.put((Id)q.get('Investor__c'),(Double)q.get('s2'));
}
List<Investor__c> InvestorToUpdatePurchased = [Select Id, Bonds_Purchased__c from Investor__c where Id IN :InvestorIdsP];
for(Investor__c inv2 : InvestorToUpdatePurchased ){
if(InvestorIdsPurchased.containsKey(inv2.Id))
{
inv2.Bonds_Purchased__c = InvestorIdsPurchased.get(inv2.Id);
}
else
{
inv2.Bonds_Purchased__c =0;
}
system.debug('resultPurchased '+inv2.Bonds_Purchased__c);
}
if(InvestorToUpdatePurchased.size()>0)
{
update InvestorToUpdatePurchased;
}
//Bond_Offering__c Units_Pending__c new update
for(AggregateResult q : [select Bond_Offering__c,sum(Units__c)s3 from Bond_Buy__c where Status__c='Pending' and Bond_Offering__c IN :BondOfferingIdsP group by Bond_Offering__c]){
BondOfferingIdsPending.put((Id)q.get('Bond_Offering__c'),(Double)q.get('s3'));
}
List<Bond_Offering__c> BondOfferingToUpdatePending = [Select Id, Units_Pending__c from Bond_Offering__c where Id IN :BondOfferingIdsP];
for(Bond_Offering__c bon : BondOfferingToUpdatePending){
if(BondOfferingIdsPending.containsKey(bon.Id))
{
bon.Units_Pending__c = BondOfferingIdsPending.get(bon.Id);
}
else
{
bon.Units_Pending__c =0;
}
}
if(BondOfferingToUpdatePending.size()>0)
{
update BondOfferingToUpdatePending;
}
//Bond_Offering__c Units_Sold__c new update
for(AggregateResult q : [select Bond_Offering__c,sum(Units__c)s4 from Bond_Buy__c where Status__c='Purchased' and Bond_Offering__c IN :BondOfferingIdsS group by Bond_Offering__c]){
BondOfferingIdsSold.put((Id)q.get('Bond_Offering__c'),(Double)q.get('s4'));
}
List<Bond_Offering__c> BondOfferingToUpdateSold = [Select Id, Units_Sold__c from Bond_Offering__c where Id IN :BondOfferingIdsS];
for(Bond_Offering__c bon2 : BondOfferingToUpdateSold){
if(BondOfferingIdsSold.containsKey(bon2.Id))
{
bon2.Units_Sold__c = BondOfferingIdsSold.get(bon2.Id);
}
else
{
bon2.Units_Sold__c =0;
}
}
if(BondOfferingToUpdateSold.size()>0)
{
update BondOfferingToUpdateSold;
}
}
trigger BondBuyUpdate2 on Bond_Buy__c (after update, after delete, after undelete) {
set<Id> InvestorIdsOlder = new set<Id>();
set<Id> InvestorIdsPOlder = new set<Id>();
set<Id> BondOfferingIdsPOlder = new set<Id>();
set<Id> BondOfferingIdsSOlder = new set<Id>();
for(Bond_Buy__c bonbuy : trigger.old)
{
{
InvestorIdsOlder.add(bonbuy.Investor__c);
InvestorIdsPOlder.add(bonbuy.Investor__c);
BondOfferingIdsPOlder.add(bonbuy.Bond_Offering__c);
BondOfferingIdsSOlder.add(bonbuy.Bond_Offering__c);
}
}
map<Id,Double> InvestorIdsPitchedOld = new map <Id,Double>();
map<Id,Double> InvestorIdsPurchasedOld = new map <Id,Double>();
map<Id,Double> BondOfferingIdsPendingOld = new map <Id,Double>();
map<Id,Double> BondOfferingIdsSoldOld = new map <Id,Double>();
//Investor Bonds_Pitched__c old update
List<Investor__c> InvestorToUpdatePitchedOld = [Select Id, Bonds_Pitched__c from Investor__c where Id IN :InvestorIdsOlder];
for(Investor__c inv : InvestorToUpdatePitchedOld ){
if(InvestorIdsPitchedOld.containsKey(inv.Id))
{
inv.Bonds_Pitched__c = InvestorIdsPitchedOld.get(inv.Id);
}
else
{
inv.Bonds_Pitched__c =0;
}
system.debug('resultPitched '+inv.Bonds_Pitched__c);
}
if(InvestorToUpdatePitchedOld.size()>0)
{
update InvestorToUpdatePitchedOld;
}
//Investor Bonds_Purchased__c old update
List<Investor__c> InvestorToUpdatePurchasedOld = [Select Id, Bonds_Purchased__c from Investor__c where Id IN :InvestorIdsPOlder];
for(Investor__c inv2 : InvestorToUpdatePurchasedOld ){
if(InvestorIdsPurchasedOld.containsKey(inv2.Id))
{
inv2.Bonds_Purchased__c = InvestorIdsPurchasedOld.get(inv2.Id);
}
else
{
inv2.Bonds_Purchased__c =0;
}
system.debug('resultPurchased '+inv2.Bonds_Purchased__c);
}
if(InvestorToUpdatePurchasedOld.size()>0)
{
update InvestorToUpdatePurchasedOld;
}
//Bond_Offering__c Units_Pending__c old update
List<Bond_Offering__c> BondOfferingToUpdatePendingOld = [Select Id, Units_Pending__c from Bond_Offering__c where Id IN :BondOfferingIdsPOlder];
for(Bond_Offering__c bon : BondOfferingToUpdatePendingOld){
if(BondOfferingIdsPendingOld.containsKey(bon.Id))
{
bon.Units_Pending__c = BondOfferingIdsPendingOld.get(bon.Id);
}
else
{
bon.Units_Pending__c =0;
}
}
if(BondOfferingToUpdatePendingOld.size()>0)
{
update BondOfferingToUpdatePendingOld;
}
//Bond_Offering__c Units_Sold__c old update
List<Bond_Offering__c> BondOfferingToUpdateSoldOld = [Select Id, Units_Sold__c from Bond_Offering__c where Id IN :BondOfferingIdsSOlder];
for(Bond_Offering__c bon2 : BondOfferingToUpdateSoldOld){
if(BondOfferingIdsSoldOld.containsKey(bon2.Id))
{
bon2.Units_Sold__c = BondOfferingIdsSoldOld.get(bon2.Id);
}
else
{
bon2.Units_Sold__c =0;
}
}
if(BondOfferingToUpdateSoldOld.size()>0)
{
update BondOfferingToUpdateSoldOld;
}
}
