0

My goal for this class is to run for every missing day up until today, with just one call from an anon block.

I would like some assistance with creating a dynamic soql query that will work within the batch apex Database.getQueryLocator. As many of you probably know, there are three required functions within batch apex (Start, execute and finish). I am specifically talking about the start method that query all of the records that will be batched through. I've narrowed down the issue to be the bind variable missingDate. The original variable is being pulled from the argument I give in the constructor from the anon block. It is then used again after being modified to the next day in the finish function to call the next day.

The query works when I run it in an anon block after assigning the variable but returns nothing when testing.

Updated after Rubens Initial Comment(Thank you again):

public class MasterInventoryMissingBatchCleanUp implements Database.Batchable<sObject>{


    public Date missingDate;

    public MasterInventoryMissingBatchCleanUp(Date missingDate){
        this.missingDate = missingDate;
    }

    public Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator(
            'SELECT Aisle__c,Item__c,Last_Record__c,Section__c,Shelf__c,Units_in_Stock__c,units_entry__c, Warehouse_name__c,Account_item_owner__c,Effective_Start_Date__c,Effective_End_Date__c, Status__c,Stock_Location__c,Transaction_Entry__c,Transaction_Number__c,Virtual_Bin_Designation__c, Master_Allocation__c,P_O__c,Shipping_ID__c FROM Stock_Entry__c WHERE Effective_Start_Date__c <= :missingDate AND (Effective_End_Date__c = NULL OR Effective_End_Date__c >= :missingDate) AND units_in_stock__c > 0'  
            );
    }
    public void execute(Database.BatchableContext bc, List<Stock_Entry__c> scope){
    // process each batch of records
        List<Master_Inventory__c > snewt = new List <Master_Inventory__c>(); 
        
        for(Stock_Entry__c se :Scope){
            system.debug('Effective start date: ' + se.Effective_Start_Date__c);
            system.debug('Effective end date: ' + se.Effective_End_Date__c);
            
            Master_Inventory__c snew = new Master_Inventory__c (
                units_entry__c = se.units_entry__c ,
                Units_in_Stock__c = se.Units_in_Stock__c,
                warehouse_name__c = se.warehouse_name__c ,
                Aisle__c = se.Aisle__c,
                Section__c = se.section__c,
                Shelf__c = se.Shelf__c,
                Status__c = se.Status__c,
                Item__c = se.Item__c,
                Account_item_owner__c =se.Account_item_owner__c,
                Effective_Start_Date__c = se.Effective_Start_Date__c,
                Effective_End_Date__c = se.Effective_End_Date__c,
                Master_Allocation__c = se.Master_Allocation__c,
                P_O__c = se.P_O__c,
                Shipping_ID__c = se.Shipping_ID__c,
                Stock_Location__c = se.Stock_Location__c,
                Transaction_Entry__c = se.Transaction_Entry__c,
                Transaction_Number__c = se.Transaction_Number__c,
                Virtual_Bin_Designation__c = se.Virtual_Bin_Designation__c,
                Power_of_One__c = 1,
                Inventory_Date__c = missingDate
                
            );
            
            snewt.add(snew);
            
        }
        
        insert snewt;
    }


public void finish(Database.BatchableContext bc){
    Date newMissingDate = missingDate.addDays(1);
    if( newMissingDate < system.Date.today()){
        MasterInventoryMissingBatchCleanUp myBatchObject = new MasterInventoryMissingBatchCleanUp(newMissingDate);
        Id batchId = Database.executeBatch(myBatchObject,500);

    }

    String[] toAddresses = new String[] {'someone@somewhere.com'};
    Messaging.SingleEmailMessage mail=new Messaging.SingleEmailMessage();
        mail.setToAddresses(toAddresses);
            mail.setReplyTo('someone@somewhere.com');
            mail.setSenderDisplayName('Batch Completion Notification');
            mail.setSubject('Missing Master Inventory Batch Clean Up Completed for Date: '+ string.valueOf(missingdate));
            mail.setPlainTextBody('The Missing Master Inventory Batch Clean Up has Completed');
        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });


}

}

Testclass also after Ruben's initial comment:

@istest
public class MasterInventoryMissingBatchCleanUp_Test{

    @TestSetup
    static void makeData(){
        // Create your test data
        Account acc = new Account();
        acc.name ='Test';
        acc.RecordTypeID = '012j0000000pirBAAQ';
        insert acc;

        Stock_Location__c sl = new Stock_Location__c();
        sl.Aisle__c = 'a';
        sl.Section__c = '2';
        sl.Shelf__c = 'a';
        insert sl;

        Sourcing_Item__c sitem = new Sourcing_Item__c();
        sitem.name = 'test mitem';
        sitem.Unit_of_measure__c = 'unit';
        sitem.Account_Owner__c = acc.id; 
        insert sitem;



        Date oneMonthAgo = System.today().addMonths(-1);
        List<Stock_Entry__c> stockEntryList = new List<Stock_Entry__c>();
        Stock_Entry__c sec1 = new Stock_Entry__c();
        sec1.Virtual_Bin_Designation__c = 'test';
        sec1.units_Entry__c = 1;
        sec1.item__c = sitem.Id;
        sec1.status__c = 'In hand';
        sec1.aisle__c = 'a';
        sec1.section__c = 2;
        sec1.shelf__c = 'a';
        sec1.Last_Record__c = True;
        sec1.Effective_Start_Date__c = oneMonthAgo; 
        sec1.Effective_End_Date__c = NULL;
        sec1.units_in_stock__c = 1;
        stockEntryList.add(sec1);
        
        Stock_Entry__c sec2 = new Stock_Entry__c();
        sec2.Virtual_Bin_Designation__c = 'test';
        sec2.units_Entry__c = 1;
        sec2.item__c = sitem.Id;
        sec2.status__c = 'In hand';
        sec2.aisle__c = 'a';
        sec2.section__c = 2;
        sec2.shelf__c = 'a';
        sec2.Last_Record__c = True;
        sec2.Effective_Start_Date__c = oneMonthAgo;
        sec2.Effective_End_Date__c = NULL;
        sec2.units_in_stock__c = 2;
        stockEntryList.add(sec2);
        
        Stock_Entry__c sec3 = new Stock_Entry__c();
        sec3.Virtual_Bin_Designation__c = 'test';
        sec3.units_Entry__c = 1;
        sec3.item__c = sitem.Id;
        sec3.status__c = 'In hand';
        sec3.aisle__c = 'a';
        sec3.section__c = 2;
        sec3.shelf__c = 'a';
        sec3.Last_Record__c = True;
        sec3.Effective_Start_Date__c = oneMonthAgo;
        sec3.Effective_End_Date__c = NULL;
        sec3.units_in_stock__c =3;
        stockEntryList.add(sec3);
        
        insert stockEntryList;

            
    }


    @istest static void test() {
        Test.startTest();
        date twoDaysAgo = system.today().addDays(-2);
        MasterInventoryMissingBatchCleanUp miTest = new MasterInventoryMissingBatchCleanUp(twoDaysAgo);
        Id batchId = Database.executeBatch(miTest);
        Test.stopTest();
        
        Integer expectedRecords = [SELECT COUNT() FROM Stock_Entry__c];
        System.assertEquals(expectedRecords, [SELECT COUNT() FROM Master_Inventory__c], 'There are not ' + expectedRecords + ' records');

        }
}

What this test returns: System.AssertException: Assertion Failed: There are not 3 records: Expected: 3, Actual: 0

2 Answers 2

1

The query in start method has three conditions that must be fulfilled:

  1. Effective_Start_Date__c <= :missingDate
  2. Effective_End_Date__c = NULL OR Effective_End_Date__c >= :missingDate
  3. units_in_stock__c > 0

In the makeData method you create three Stock Entry with these values:

  1. Effective_Start_Date__c = system.today();
  2. Effective_End_Date__c = NULL;
  3. units_in_stock__c = null (because you never set it)

Then you set twoDaysAgo as missingDate, but today (Effective_Start_Date__c) is not less or equal than twoDaysAgo.
So the first and the third conditions of the start query are not verified.

In the makeData method you could set Effective_Start_Date__c = System.today().addMonths(-1); and units_in_stock__c = 1.

Date oneMonthAgo = System.today().addMonths(-1);
List<Stock_Entry__c> stockEntryList = new List<Stock_Entry__c>();
Stock_Entry__c sec1 = new Stock_Entry__c();
sec1.Virtual_Bin_Designation__c = 'test';
sec1.units_Entry__c = 1;
sec1.item__c = sitem.Id;
sec1.status__c = 'In hand';
sec1.aisle__c = 'a';
sec1.section__c = 2;
sec1.shelf__c = 'a';
sec1.Last_Record__c = True;
sec1.Effective_Start_Date__c = oneMonthAgo; 
sec1.Effective_End_Date__c = NULL;
sec1.units_in_stock__c = 1;
stockEntryList.add(sec1);

Stock_Entry__c sec2 = new Stock_Entry__c();
sec2.Virtual_Bin_Designation__c = 'test';
sec2.units_Entry__c = 1;
sec2.item__c = sitem.Id;
sec2.status__c = 'In hand';
sec2.aisle__c = 'a';
sec2.section__c = 2;
sec2.shelf__c = 'a';
sec2.Last_Record__c = True;
sec2.Effective_Start_Date__c = oneMonthAgo;
sec2.Effective_End_Date__c = NULL;
sec2.units_in_stock__c = 2;
stockEntryList.add(sec2);

Stock_Entry__c sec3 = new Stock_Entry__c();
sec3.Virtual_Bin_Designation__c = 'test';
sec3.units_Entry__c = 1;
sec3.item__c = sitem.Id;
sec3.status__c = 'In hand';
sec3.aisle__c = 'a';
sec3.section__c = 2;
sec3.shelf__c = 'a';
sec3.Last_Record__c = True;
sec3.Effective_Start_Date__c = oneMonthAgo;
sec3.Effective_End_Date__c = NULL;
sec3.units_in_stock__c =3;
stockEntryList.add(sec3);

insert stockEntryList;

Moreover the batch will run only once as explained by Phil, so the amount of created Master_Inventory__c records will be exactly the same as the Stock_Entry__c records collected by the start method, therefore the actual assert is wrong (you create 3 Stock Entry records, but you expect 6 Master Inventory records).
It should be:

Integer expectedRecords = [SELECT COUNT() FROM Stock_Entry__c];
System.assertEquals(expectedRecords, [SELECT COUNT() FROM Master_Inventory__c], 'There are not ' expectedRecords + ' records');

The way you added a variable to the query used in the Database.getQueryLocator is already fine.
start, execute and finish are instance methods, so they can see instance variables.

Anyway since you aren't using a dynamic query, you should prefer inline queries in the start method. This way if you mistype a field, you won't be able to deploy the class, while using a query string will lead to a runtime error. Moreover with inline queries you bind variable field.

Docs

unlike inline SOQL, dynamic SOQL can’t use bind variable fields in the query string.

I.E. Not Allowed (results in a "Variable does not exist" error)

MyCustomObject__c myVariable = new MyCustomObject__c(field1__c ='TestField');
Database.getQueryLocator('SELECT Id FROM MyCustomObject__c WHERE field1__c = :myVariable.field1__c');

Allowed

MyCustomObject__c myVariable = new MyCustomObject__c(field1__c ='TestField');
Database.getQueryLocator([SELECT Id FROM MyCustomObject__c WHERE field1__c = :myVariable.field1__c]);
4
  • Ruben, Thanks for adjusting the test data. My partner and I were messing around with it last week and I forgot to adjust back the effective_start_date__c so it would meet the criteria. I did however totally miss the units_in_stock__c and assigned it as units_entry__c. So thanks again for spotting that. However even with the adjustments and using your test data exactly, the actual still returns 0 records. If you could take a look at the query in the start method at the bind variables, Should this be how its set up? I am now going to try and use literals and hardcoded datetimes to test.
    – Jebert
    Feb 14 at 19:15
  • Ok, so I've confirmed it is the variable. I used the literal TODAY in the soql query and it returned the 3 records. However the first time I did this I accidently left the missing date as two days ago causing the batch class to run twice from the recursive call to the same class in the finish method. So the final actual was 6 for the first time I ran it and then 3 as expected when I changed the missing date to today as well. So that still begs the question how am I supposed to add a variable to a Database.getQueryLocator query?
    – Jebert
    Feb 14 at 19:39
  • @Jebert I've added a bit to the answer. Anyway you correctly added missingDate to Database.getQueryLocator query. If literal TODAY works, you could add System.debug('Start - missingDate: ' + String.valueOf(missingDate)); in the start method and a debug in the execute to print the effective_start_date__c value, then compare such values.
    – RubenDG
    Feb 14 at 20:40
  • this is exactly what I needed. Thanks for your help. Also to anyone reading this after, I needed to also use the method string.valueOfGmt() to get the correct time as using valueOf() on a date/datetime will give it relative to your time zone(I believe, I just knew it threw it off and this gave me the correct time)
    – Jebert
    Feb 15 at 16:06
1

You start your test with 2 days ago and expect the iterative execution of the batch through to "today".

However, this won't happen. Why? Because asyncs to be run are collected between Test.startTest and Test.stopTest and then executed once, synchronously and immediately during the call to Test.stopTest. Any new asyncs created during the "async" execution, in Test.stopTest, do not get collected or executed, but instead just sit there in the async queue and are discarded when the test method finishes.

The documentation states (my emphasis):

Any code that executes after the stopTest method is assigned the original limits that were in effect before startTest was called. All asynchronous calls made after the startTest method are collected by the system. When stopTest is executed, all asynchronous processes are run synchronously.

Note how there's no mention of collecting and executing more that are generated during stopTest.

So in your case, you correctly call Database.executeBatch between startTest and stopTest which will execute the processing for today - 2 when stopTest is called. That batch execution will itself call Database.executeBatch in finish, but since this is no longer before the stopTest call it doesn't get collected or executed and so your test only has the batch run once.

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.