+ Start a Discussion
Daniel B ProbertDaniel B Probert 

Dynamic object use within query.

i've confused myself

i have an object with 2 fields

Form Name (external reference and unique name that is stored within the form_id field within a different custom object)
ObjectName__c (formula field that based on name enter the correct custom object mycustomobject__c)
Salesforce_Record_Count__c (i want to put the number of records that match my search critera in here)

i then want to create a batch process that will do something like this:

global class UpdateMonitoringForms implements Database.Batchable<sObject>{

    string query;
    global Database.querylocator start(Database.BatchableContext BC){
      Query = 'Select ID,Form_Name__c,Salesforce_Record_Count__c From Monitoring_Form__c';
      return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Monitoring_Form__c> scope){
      List<Monitoring_Form__c> lstmforms = new List<Monitoring_Form__c>();  
       for (Monitoring_Form__c mf:scope) {
        string object = mf.ObjectName__c;
        IF(mf.ObjectName__c!= 'DO NOT PROCESS'){
          mf.Salesforce_Record_Count__c = [select count() from object where Form_ID__c =:mf.Form_Name__c];
        }
        lstmforms.add(mf)
        }
        update lstmforms;       
    }

    global void finish(Database.BatchableContext BC){

    }
}

so basically my challenge is how can I build my select count to use potentially a different customobject__c each time it runs.

I plan to limit this batch to only updating 20 records at time.

any thoughts or betters ways of doing this, please do as for clarification.

dan


Best Answer chosen by Daniel B Probert
Daniel B ProbertDaniel B Probert
ok i figured out what the issue was i had to change it to perform a like instead of =:

for (Monitoring_Form__c mf:scope) {
                IF(mf.Object__c!= 'DO NOT PROCESS'){
                    String qryString = 'SELECT count() FROM ' + mf.Object__c +' where Form_ID__c LIKE \''+mf.Form_Name__c+'\'';
                    system.debug(qryString);
                    integer qryCount = Database.countQuery(qryString);
                    mf.Salesforce_Record_Count__c = qryCount;
                }
                lstmforms.add(mf);
            }
then it started working perfectly..

All Answers

Ravikant Saini 1Ravikant Saini 1
Daniel,
            I think you can do it by use of this.
Database.executeBatch(new UpdateMonitoringForms(),20);

CJWilderCJWilder
for (Monitoring_Form__c mf:scope) {
	IF(mf.ObjectName__c!= 'DO NOT PROCESS'){
		String qryString = 'SELECT count() FROM ' + mf.ObjectName__c +' where Form_ID__c = '+mf.Form_Name__c+'';
		integer qryCount = Database.countQuery(qryString);
		mf.Salesforce_Record_Count__c = qryCount;
	}

        lstmforms.add(mf)
}
This may work for the dynamic count
Daniel B ProbertDaniel B Probert
hi cj thanks for the response that does look exactly what i'm after a small tweak and adding a debug I can see that the qry string is correct - my issue is that I when i run the batch i get the following error:

First error: Only variable references are allowed in dynamic SOQL/SOSL.

any ideas i've been playing around with the code but just can't get past this error now..
Daniel B ProbertDaniel B Probert
ok i figured out what the issue was i had to change it to perform a like instead of =:

for (Monitoring_Form__c mf:scope) {
                IF(mf.Object__c!= 'DO NOT PROCESS'){
                    String qryString = 'SELECT count() FROM ' + mf.Object__c +' where Form_ID__c LIKE \''+mf.Form_Name__c+'\'';
                    system.debug(qryString);
                    integer qryCount = Database.countQuery(qryString);
                    mf.Salesforce_Record_Count__c = qryCount;
                }
                lstmforms.add(mf);
            }
then it started working perfectly..
This was selected as the best answer
CJWilderCJWilder
Glad I cloud help. Out of curiosity did you do a system.debug on form_name__c. Was wondering if the 15 vs 18 digit id was at play here.
Daniel B ProbertDaniel B Probert
form_name is a text field so that didn't come into it...