+ Start a Discussion
mgodseymgodsey 

Help with Querying all Fields on an Object

I want to be able to query for all creatable fields on an object without having to hardcode each field into the SOQL query. I have a method that builds the SOQL query and it works great except for when the WHEN statement includes id in: a collection of records.

 

Here is the method:

/**
    *Returns a dynamic SOQL statement for the whole object, includes only creatable fields since we will be inserting a cloned result of this query
    */
    public static string getCreatableFieldsSOQL(String objectName, String whereClause){
        String selects = '';
        
        if(whereClause == null || whereClause == ''){
            return null;
        }
        
        //get a map of field names and field tokens
        Map<String, Schema.SObjectField> fMap = Schema.getGlobalDescribe().get(objectName.toLowerCase()).getDescribe().Fields.getMap();
        List<String> selectFields = new List<String>();
        
        if(fMap!=null){
            for(Schema.SObjectField ft : fMap.values()){ //loop through all field tokens (ft)
                Schema.DescribeFieldResult fd = ft.getDescribe(); //describe each field (fd)
                if (fd.isCreateable()){ //field is creatable
                    selectFields.add(fd.getName());
                }
            }
        }

 

Here is where I invoke the method:

String oppSOQL = CSUtils.getCreatableFieldsSOQL('Opportunity', 'id in' + clonedFromOppIDs);
        system.debug('[MF] oppSOQL: ' + oppSOQL);
        
        for(Opportunity opp : (List<Opportunity>)Database.query(oppSOQL)){
            ClonedOppIDtoClonedOpp.put(opp.id, opp);
        }

 "clonedFromOppIDs" is a set of Opportunity IDs. However, when I try to execute this code I get the error message: System.QueryException: unexpected token: '{' . This is the debug log (I removed most of the fields to make it easier to read):

 

16:56:07.493 (493363000)|USER_DEBUG|[28]|DEBUG|[MF] oppSOQL: SELECT ApprovedTerms__c,Rate_Type__c,WhatChanged__c FROM Opportunity WHERE id in{006Q000000BmT4XIAV}
16:56:07.493 (493388000)|SYSTEM_METHOD_EXIT|[28]|System.debug(ANY)
16:56:07.493 (493412000)|SYSTEM_METHOD_ENTRY|[30]|Database.query(String)
16:56:07.494 (494079000)|EXCEPTION_THROWN|[30]|System.QueryException: unexpected token: '{'

 

I've tried making the WHERE clause 'id in: ' + clonedFromOppIDs but I get the same error message. Does anyone know if there is anyway I can get around this? Or have other suggestions for how to systematically query all fields without typing each one it? Any help would be much appreciated, thank you!!

Best Answer chosen by Admin (Salesforce Developers) 
pinoytechiepinoytechie

getCreatableFieldsSOQL is incomplete. i didn't see the line where it builds the query.

 

anyway, try

String oppSOQL = CSUtils.getCreatableFieldsSOQL('Opportunity', 'id in :clonedFromOppIDs');

All Answers

pinoytechiepinoytechie

getCreatableFieldsSOQL is incomplete. i didn't see the line where it builds the query.

 

anyway, try

String oppSOQL = CSUtils.getCreatableFieldsSOQL('Opportunity', 'id in :clonedFromOppIDs');
This was selected as the best answer
mgodseymgodsey

That worked, thank you! 

 

Also you're right, I definitely left off part of the method. Here it is in case it helps anyone:

 

/**
    *Returns a dynamic SOQL statement for the whole object, includes only creatable fields since we will be inserting a cloned result of this query
    */
    public static string getCreatableFieldsSOQL(String objectName, String whereClause){
        String selects = '';
        
        if(whereClause == null || whereClause == ''){
            return null;
        }
        
        //get a map of field names and field tokens
        Map<String, Schema.SObjectField> fMap = Schema.getGlobalDescribe().get(objectName.toLowerCase()).getDescribe().Fields.getMap();
        List<String> selectFields = new List<String>();
        
        if(fMap!=null){
            for(Schema.SObjectField ft : fMap.values()){ //loop through all field tokens (ft)
                Schema.DescribeFieldResult fd = ft.getDescribe(); //describe each field (fd)
                if (fd.isCreateable()){ //field is creatable
                    selectFields.add(fd.getName());
                }
            }
        }
        
        if(!selectFields.isEmpty()){
            for (string s: selectFields){
                selects += s + ',';
            }
            if(selects.endsWith(',')){
                selects = selects.substring(0,selects.lastIndexOf(','));
            }            
        }
        
        return 'SELECT ' + selects+ ' FROM ' + objectName + ' WHERE ' + whereClause;
    }

 

Esti LeiserEsti Leiser
This code is very helpful! Do you have a sample test class for it?