0

Whenever an account goes 18 months past the end date for the latest event that they have booked, we have code set up that will automatically change the account from being an active client to an in-active client. The end date is captured by Latest_Event_End_Date__c and is from the Account object.

I have another item in the Duplicate Record Set object called the DRS_Latest_Event_End_Date_Summary__c, which is a roll-up summary that grabs whatever the Latest Event End Date is for any accounts in the Duplicate Record set. For example, if I have two accounts, one with the end date of 12/18/2019 and one with 12/18/2020, DRS_Latest_Event_End_Date_Summary__c will return 12/18/2020.

In the Scheduler (code below) I am trying to compare these two fields from the two objects, so that this only changes accounts to in-active clients when the where Latest_Event_End_Date__c >= DRS_Latest_Event_End_Date_Summary__c. I tried implementing this below, and it is not working.

Does anyone have input as to how I can compare these two fields properly, so that if the Latest Event End Date is greater than or equal to to the Summary End Date, then the account type changes to in-active, but if the Latest Event End Date is less than the Summary End Date, then nothing happens.

Here is the code:

Global class AccountRevertScheduler implements Schedulable {

    global void execute(SchedulableContext SC) {

        try {
            AccountRevertLoader myLoader = new AccountRevertLoader();
            //
            // Setup the query string that will be used by the Loader to select accounts
            //
            // if (Latest Event End Date is greater than 18 then update account to "Client - In-Active)
            //
                myLoader.query = 'select id, Name, Latest_Event_End_Date__c, Previous_Account_Type__c, Account_Type_Updated_At__c from Account ' +
                                 'select DRS_Latest_Event_End_Date_Summary__c from DuplicateRecordSet' +
                                 'where Latest_Event_End_Date__c >= DRS_Latest_Event_End_Date_Summary__c AND where Latest_Event_End_Date__c >= :firstDate AND Latest_Event_End_Date__c < :lastDate and Type != \'Client - In-Active\'' ;
                myLoader.firstDate = Date.Today().addMonths(-19);
                myLoader.lastDate = Date.Today().addMonths(-18);
            //
            // calls the AccountRevertLoader 
            //
            ID batchprocessid = Database.executeBatch(myLoader,175);
        }
        catch (exception e){ 
       
        }
        finally {

        }   
    }
}

EDIT: Updated code with checkbox

Global class Account18MonthRevertScheduler implements Schedulable {

    global void execute(SchedulableContext SC) {

        try {
            Account18MonthRevertLoader myLoader = new Account18MonthRevertLoader();
            //
            // Setup the query string that will be used by the Loader to select accounts
            //
            // if (Latest Event End Date is greater than 18 then update account to "Client - In-Active)
            //
                myLoader.query = 'select id, Name, Type, DRS_client_active__c, Latest_Event_End_Date__c, Previous_Account_Type__c, Account_Type_Updated_At__c from Account ' +
                                 'where DRS_client_active__c = false AND where Latest_Event_End_Date__c >= :firstDate AND Latest_Event_End_Date__c < :lastDate and Type != \'Client - In-Active\'' ;
                myLoader.firstDate = Date.Today().addMonths(-19);
                myLoader.lastDate = Date.Today().addMonths(-18);
            //
            // calls the Account18MonthRevertLoader 
            //
            ID batchprocessid = Database.executeBatch(myLoader,175);
        }
        catch (exception e){ 
       
        }
        finally {

        }   
    }
}
3
  • First thing to do is learn SOQL. Your query structure is completely wrong and you cannot include field comparisons in SOQL. You need a specific relationship between the records or you need two queries.
    – Phil W
    Dec 18, 2020 at 21:22
  • @phil-w If I loaded the value of DRS_Latest_Event_End_Date_Summary__c and stored it as a variable, would I be able to compare it then? Or if I created a new field within the Account object itself that pulled the value of DRS_Latest_Event_End_Date_Summary__c, would I be able to use that as a comparison? Dec 18, 2020 at 22:12
  • One typical approach is to create a checkbox formula field that does the required comparison, but that requires a relationship between the two objects so the formula can include a cross-object reference. Other approaches include two queries for the two sets of data that can be mapped from one to the other.
    – Phil W
    Dec 18, 2020 at 23:02

1 Answer 1

1

You have a couple of options:

  1. Create a checkbox formula field on DuplicateRecordSet, in which you will do the comparison: Latest_Event_End_Date__c >= DRS_Latest_Event_End_Date_Summary__c Then in the query, filter for that checkbox: "where my_comparison_checkbox__c = true".

  2. Second option is to remove that comparison from the query, and query for all records. Then you will need to iterate over the query results and do the comparison yourself.

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.