0

I need to run the below query for 200 fields changing the field each time and then copy the resulting most recent date.Is there any way to do this quicker than doing it 1 by 1?

SELECT name, CreatedDate FROM Account WHERE name!= null ORDER BY createdDate DESC NULLS FIRST

I tried to do this using an apex class:

public  class Allfields {

public void getallFieldValues(){
    Account account = new Account();
    // get all account fields from database schema
    Schema.SObjectType objectType = account.getSObjectType();
    set<String> accountFields = objectType.getDescribe().fields.getMap().keySet();
    system.debug(accountfields);
    string soql = '';
    for(String fld1:accountFields){
        soql = 'select '+fld1+',createddate from account ORDER BY CreatedDate DESC NULLS FIRST';
        getvalues(soql);
    }    
}
public void getvalues(String query){
    list<Account> newList=new list<Account>();
    newList = database.query(query);
    System.debug(newList);

}}

There are 500 different fields in the Account object and I am looking for a way to find the last time each of those fields were populated in a record so that I can go ahead and delete the fields that are never used.

But I am getting an error "duplicate field selected: CreatedDate". What am I doing wrong in my code here and is this how it's usually done? Thank you you for your time

  • What are you trying to solve for? This is going to blow up.. – EricSSH Apr 20 '18 at 1:38
  • You do realize CreatedDate cannot be null, right? – Adrian Larson Apr 20 '18 at 1:47
  • For each field, I need to find the most recently created value and the CreatedDate. I can use LIMIT 1 to reduce the number of returned results. – Sooraj Apr 20 '18 at 1:48
  • The CreatedDate coincides when the Account Record was created.. Again what are you trying to solve for – EricSSH Apr 20 '18 at 1:51
  • Sorry I was not clear in my question. In the client org, there are 500 fields for Account and I would like to see the last date a field has been used and value populated in it so that I can go ahead and remove the unnecessary fields. – Sooraj Apr 20 '18 at 12:52
3

The code in the OP will make a SOQL query for every single field on the object, which is going to hit limits very quickly and probably isn't what you want anyway. I'm guessing you actually want the code below. I've removed the CreatedDate from the list of retrieved fields since it will be in the list anyway, which is what is causing your error. Keep in mind that retrieving all the fields on an object can inflate its size in memory and cause you to exceed the heap limit, so be careful using unbounded code like this.

public void getallFieldValues(){
    // get all account fields from database schema
    Schema.SObjectType objectType = Account.getSObjectType(); //Note that you can actually call getSObjectType on the type name without instantiating a variable
    List<String> accountFields = new List<string>(objectType.getDescribe().fields.getMap().keySet());
    string soql = 'select '+ String.join(accountFields, ',') +' from account ORDER BY CreatedDate DESC NULLS FIRST';
    List<Account> result = Database.query(soql);
}
|improve this answer|||||
2

The reason you are appending "createddate" either you write an if block to exclude created date or remove your hardcoding. Below will work for you even if new field added or old field deleted.

soql = 'select '+fld1+' from account ORDER BY CreatedDate DESC NULLS FIRST'; 
|improve this answer|||||

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.