Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a batch process that updates a bunch of currency fields on a parent record based on the month of a child record. It works great and runs every hour to find new records and update the parent record accordingly.

If the date changes on the child record it updates the new corresponding currency field on the parent. The problem is that it does not clear out the now incorrect currency field.

To start if the following is true:

child.Date__c = 2017-01-01
child.Amount__c = 100

Then the logic runs and the output is:

parent.Jan2017__c = 100;
parent.Feb2017__c = 0;
parent.Mar2017__c = 0;

Current Results

If the child record is updated and changes to this:

child.Date__c = 2017-02-01
child.Amount__c = 100

The logic runs and the result is:

Jan2017__c = 100;
Feb2017__c = 100;
Mar2017__c = 0;

Expected Result:

If the child record is updated and changes to this:

child.Date__c = 2017-02-01
child.Amount__c = 100

The logic runs and the result is:

Jan2017__c = 0;
Feb2017__c = 100;
Mar2017__c = 0;

How can I clear out the populated currency fields to 0 and then let the logic run to populate the correct value?

Example code:

global void execute(Database.BatchableContext BC, List<Revenue_Pipeline_Schedule__c> scope) {

    Map<Id, Revenue_Pipeline__c> pipelines = new Map<Id, Revenue_Pipeline__c>();

    for (Revenue_Pipeline_Schedule__c schedule : scope)
    {
        Id pipelineId = schedule.Revenue_Pipeline__c;
        pipelines.put(pipelineId, new Revenue_Pipeline__c(Id=pipelineId));
    }
    // now all keys are initialized

    for (Revenue_Pipeline_Schedule__c schedule : scope)
    {
        SObjectField field = RevenuePipelineScheduleUpdaterHelper.dateToField.get(schedule.Date__c);
        if (field != null)
        {
            pipelines.get(schedule.Revenue_Pipeline__c).put(field, schedule.Amount__c);
        }
    }   
    update pipelines.values();
}

Helper:

   public with sharing class RevenuePipelineScheduleUpdaterHelper {

    public static Map<Date, SObjectField> dateToField = new Map<Date, SObjectField>
    {
        Date.newInstance(2014, 10, 1) => Revenue_Pipeline__c.Oct2014__c,
        Date.newInstance(2014, 11, 1) => Revenue_Pipeline__c.Nov2014__c,
        Date.newInstance(2014, 12, 1) => Revenue_Pipeline__c.Dec2014__c,
        Date.newInstance(2015, 01, 1) => Revenue_Pipeline__c.Jan2015__c,
        };
    }
share|improve this question
    
what is your expected output, can you edit your question and update that – Santanu Boral 10 hours ago

Not sure I fully understand, but I suggest:

for (Revenue_Pipeline_Schedule__c s : scope) {

    Revenue_Pipeline__c p = pipelines.get(s.Revenue_Pipeline__c);

    // First clear all
    for (SObjectField f : RevenuePipelineScheduleUpdaterHelper.dateToField.values()) {
        p.put(f, 0);
    }

    // Then fill in the one
    SObjectField f = RevenuePipelineScheduleUpdaterHelper.dateToField.get(s);
    if (f != null) {
        p.put(f, s.Amount__c);
    }
}
share|improve this answer
    
Should that have a 3rd for loop after // then fill in the one? – Dan Wooding 8 hours ago

Is there a reason you're using a batch here instead of a trigger? With a trigger, you could use the oldMap to reference what the record looked like before the edit. If you move this logic to a trigger, then each time a Pipeline Schedule is updated, you can "undo" the effect of its original amount/date on the Pipeline before adding the new amount/date.

Many of the other Answers seem to assume that there is a single Pipeline Schedule per Pipeline - but this will solve the problem without affecting the results of other Pipeline Schedules on the parent Pipeline.

Assume a parent with 3 children:

child1.Amount__c = 50;
child1.Date__c = 2017-01-01;
child2.Amount__c = 100;
child2.Date__c = 2017-01-01;
child3.Amount__c = 75;
child3.Date__c = 2017-02-01;

So,

Jan2017__c = 150;
Feb2017__c = 75;
Mar2017__c = 0;

If child2 becomes:

child2.Amount__c = 50;
child2.Date__c = 2017-02-01;

I assume the parent should become:

Jan2017__c = 50;
Feb2017__c = 125;
Mar2017__c = 0;

If called from a trigger on both insert and update, the following code should ensure this:

//Called from both insert and update, oldMap is null on insert.
//Assumes that Pipeline Schedules cannot be reparented
public void processRecords (Map<Id, Revenue_Pipeline_Schedule__c> oldMap, Map<Id, Revenue_Pipeline_Schedule__c> newMap)
    Set<Id> pipelineIds = new Set<Id>();
    Set<Id> pipelineSchedulesToProcess = new Set<Id>();
    for (Id key : newMap.keySet()){
        Revenue_Pipeline_Schedule__c newRecord = newMap.get(key);
        Revenue_Pipeline_Schedule__c oldRecord = oldMap == null ? new Revenue_Pipeline_Schedule__c(Amount__c = 0) : oldMap.get(key);
        if (newRecord.Amount__c != oldRecord.Amount__c || newRecord.Date__c != oldRecord.Date__c){
            pipelineSchedulesToProcess.add(newRecord.Id);
            pipelineIds.add(newRecord.Revenue_Pipeline__c);
        }
    }

    if (pipelineSchedulesToProcess.isEmpty()){ return; }

    Map<Id, Revenue_Pipeline__c> pipelines = new Map<Id, Revenue_Pipeline__c>([
        SELECT Oct2014__c, Nov2014__c, Dec2014__c, Jan2015__c
        FROM Revenue_Pipeline__c
        WHERE Id IN :pipelineIds
    ]);
    Map<Date, SObjectField> fieldMap = RevenuePipelineScheduleUpdaterHelper.dateToField;
    for (Id key : pipelineSchedulesToProcess){
        Revenue_Pipeline_Schedule__c newRecord = newMap.get(key);
        Revenue_Pipeline_Schedule__c oldRecord = oldMap == null ? new Revenue_Pipeline_Schedule__c(Amount__c = 0) : oldMap.get(key);
        Revenue_Pipeline__c pipeline = pipelines.get(newRecord.Revenue_Pipeline__c);

        //Subtract the amount that was added originally
        Decimal oldValue = pipeline.get(fieldMap.get(oldRecord.Date__c));
        Decimal valueToSubtract = oldRecord.Amount__c;
        pipeline.get(fieldMap.get(oldRecord.Date__c)).put(oldValue - valueToSubtract);
        //Add the new amount to the correct field
        Decimal newValue = pipeline.get(fieldMap.get(newRecord.Date__c));
        Decimal valueToAdd = newRecord.Amount__c;
        pipeline.get(fieldMap.get(newRecord.Date__c)).put(newValue - valueToAdd);
    }
    update pipelines.values();
}
share|improve this answer
    
Good thoughts, I moved to outside a trigger because I kept hitting limits as the full transaction creates a parent then creates children, to update parent again I would run into all sorts of issues trying to make it bulk. – Dan Wooding 8 hours ago

I have tried to put simple logic to retrieve month value from the field.

Since every first day of the month, the process will be running, so from the schedule.Date__c get the current month.

Since, your field naming convention is Jan, Feb Mar,... etc, so create a map which holds the month integer and Name of the month.

Finally, compare the current month in the loop and update other month data with 0.

private static final Map<Integer, String> mapMonthValues = new Map<Integer,String>();
    static {
        mapMonthValues.put(1,'Jan');
        mapMonthValues.put(2, 'Feb');
        mapMonthValues.put(3,'Mar');
        mapMonthValues.put(4,'Apr');
        mapMonthValues.put(5,'May');
        mapMonthValues.put(6,'Jun');
        mapMonthValues.put(7,'Jul');
        mapMonthValues.put(8,'Aug');
        mapMonthValues.put(9,'Sep');
        mapMonthValues.put(10,'Oct');
        mapMonthValues.put(11,'Nov');
        mapMonthValues.put(12,'Dec');
    }

//Change for loop like this:
for (Revenue_Pipeline_Schedule__c schedule : scope)
    {
        Integer month = schedule.Date__c.month(); //retrieve the current month
        SObjectField field = RevenuePipelineScheduleUpdaterHelper.dateToField.get(schedule.Date__c);
        if (field != null)
        {
            if(fieldfield.getDescribe().getName().contains(mapMonthValues.get(month)))
            {
                pipelines.get(schedule.Revenue_Pipeline__c).put(field, schedule.Amount__c);
            }
            else
            {
                pipelines.get(schedule.Revenue_Pipeline__c).put(field, 0);
            }

        }
    }   
    update pipelines.values();
share|improve this answer

Try updating your second for loop in the execute(..) method to go through all the keys in your dateToField Map and set all of the fields to 0, apart from the one that needs to get updated. Like this:

for (Revenue_Pipeline_Schedule__c schedule : scope)
{
    for(Date dateKey : RevenuePipelineScheduleUpdaterHelper.dateToField.keySet())
    {
        SObjectField field = RevenuePipelineScheduleUpdaterHelper.dateToField.get(dateKey);
        pipelines.get(schedule.Revenue_Pipeline__c)
            .put(field, dateKey == schedule.Date__c ? schedule.Amount__c : 0);
    }
}  

However, I do have to say that your Parent object looks badly designed. Since you are clearing the previous values, why would there be a need to have a field for each month?

For this use case I would change the Parent object to have one currency field, and one date field to store the month of the currency. But I may not be seeing the bigger picture.


UPDATE

OK, so I think I understand your requirement now.

If it is possible I would add two new fields to your child object:

  • Old_Amount__c
  • Old_Date__c

Now, update your batch

global void execute(Database.BatchableContext BC, List<Revenue_Pipeline_Schedule__c> scope) {

    Set<Id> pipelineIds = new Set<Id>();
    List<String> fieldNames = new List<String>();

    for (Revenue_Pipeline_Schedule__c schedule : scope)
    {
        pipelineIds.add(schedule.Revenue_Pipeline__c);

        SObjectField oldField = RevenuePipelineScheduleUpdaterHelper.dateToField.get(schedule.Old_Date__c); 
        SObjectField newField = RevenuePipelineScheduleUpdaterHelper.dateToField.get(schedule.Date__c);

        if(oldField != null){
            fieldNames.add(oldField+'');
        }       
        if(newField != null){
            fieldNames.add(newField+'');
        }
    }

    //This will retrieve all required fields
    String query = 'SELECT ' + String.join(fieldNames, ',') +
        ' FROM Revenue_Pipeline__c WHERE Id IN :pipelineIds';

    Map<Id, Revenue_Pipeline__c> pipelines = 
        Map<Id, Revenue_Pipeline__c>((List<Revenue_Pipeline__c>)Database.query(query));

    //now that you have all the fields for pipelines, 
    //go through the updated children (scope) and 
    //subtract the old value from parents, and 
    //add the new one   

    for (Revenue_Pipeline_Schedule__c schedule : scope)
    {
        SObjectField oldField = RevenuePipelineScheduleUpdaterHelper.dateToField.get(schedule.Old_Date__c); 
        SObjectField newField = RevenuePipelineScheduleUpdaterHelper.dateToField.get(schedule.Date__c);

        Revenue_Pipeline__c p = pipelines.get(schedule.Revenue_Pipeline__c);      

        if(oldField != null){
            //subtract the old amount from the old month field
            p.put(oldField, p.get(oldField) - schedule.Old_Amount__c);
        }

        if (newField!= null)
        {    
            //add the new amount to the new month field   
            p.put(newField, p.get(newField) + schedule.Amount__c);
        }

        //update the old field values on the child
        schedule.Old_Date__c = schedule.Date__c;
        schedule.Old_Amount__c = schedule.Amount__c;
    }   

    update pipelines.values();
    update scope;
}
share|improve this answer
    
I needed a field per month because of reporting requirements. I tried to for weeks to convince them/design an alternate solution and got shot down everytime. The child object holds one currency one date field so that's better design. It's just then I need to populate the currency/date back to the parent... – Dan Wooding 8 hours ago
    
I tried this had 3 existing month values, and I switched to 3 different months. The batch ran successfully clearing out the existing values but only populated one of the new months.. :( – Dan Wooding 8 hours ago
    
@DanWooding Please see my update. It's not that straightforward when you don't want to use a trigger. – smukov 6 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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