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

5
  • What are you trying to solve for? This is going to blow up..
    – EricSSH
    Apr 20, 2018 at 1:38
  • You do realize CreatedDate cannot be null, right?
    – Adrian Larson
    Apr 20, 2018 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. Apr 20, 2018 at 1:48
  • The CreatedDate coincides when the Account Record was created.. Again what are you trying to solve for
    – EricSSH
    Apr 20, 2018 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. Apr 20, 2018 at 12:52

2 Answers 2

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);
}
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'; 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .