3

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,
        };
    }
  • what is your expected output, can you edit your question and update that – Santanu Boral Jan 27 '17 at 21:43
4

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);
    }
}
  • Should that have a 3rd for loop after // then fill in the one? – Dan Wooding Jan 28 '17 at 0:06
  • @DanWooding Not sure what you mean: within the Revenue_Pipeline_Schedule__c SObject loop, the first loop over many fields clears all the many fields then the second piece of code fills in the one field. – Keith C Jan 28 '17 at 9:08
  • This is the closest of the answers that solve my issue. However, it's working too well. Because there can be multiple schedule records it's in this loop where it clears all the values updates a values clears all the value updates the value till only one field is populated at the end. I thought about this last night, if I move the clear out of the loop to fill in the amount. Is there a way to loop once and then loop multiple times? – Dan Wooding Jan 30 '17 at 15:09
  • @DanWooding You can run the for (Revenue_Pipeline_Schedule__c s : scope) loop twice, once to do the clear and then a second time to fill in the values. But you would need to take care in the query in the start method that all the Revenue_Pipeline_Schedule__c for a Revenue_Pipeline__c are included in one batch which might be awkward. – Keith C Jan 30 '17 at 15:14
  • I think I can do that easily, I generate all Revenue_Pipeline_Schedule__c records at the same time everytime with another class so if I grab them by modified date that's all for one record. – Dan Wooding Jan 30 '17 at 15:20
3

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();
}
  • 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 Jan 27 '17 at 23:40
  • I figured as much - if you're constrained to using a batch, then the only solution I can think of is to clear the values then re-process all of children for each parent that is updated. – SFDC Neuf Jan 30 '17 at 16:35
1

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();
1

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;
}
  • 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 Jan 27 '17 at 23:43
  • 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 Jan 28 '17 at 0:05
  • @DanWooding Please see my update. It's not that straightforward when you don't want to use a trigger. – smukov Jan 28 '17 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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