get count of all the fields containing data in a specific object

Question

public static  Map<String, decimal> getFieldsCount(String objectName){
                Map<string,Schema.SObjectField> get_F_name=Schema.getGlobalDescribe().get(objectName).getDescribe().fields.getMap();
    List<String> fieldlist=new List<String>();
    for(String s:get_F_name.keySet())
    {
        fieldlist.add(''+get_F_name.get(s));
    }
    Map<String, decimal> fieldMap = new Map<String, decimal>();
    String query1='SELECT Count(Id) FROM '+objectName;
    AggregateResult aggr=Database.query(query1);
    decimal totalRecordCount=(decimal)aggr.get('expr0');
    for(String s:fieldlist)
    {
           String query='SELECT Count(Id) FROM '+objectName +' where ' +s +' != null';
           AggregateResult aggrResults=Database.query(query);
           decimal summ=(decimal)aggrResults.get('expr0');//totalRecordCount
           fieldMap.put(s,summ*100);
           System.debug('fieldmao '+s +' val '+summ*100);
    }
return fieldMap;
}

the error message I am getting:- System.QueryException: Address fields can only be filtered using Distance expressions

Answer 1

Address field (for example: BillingAddress, ShippingAddress etc. in Account object) is a compound field that cannot be used for filtering in SOQL WHERE clause. For such fields, you would want to use its individual component fields (for example: BillingAddress field is composed of BillingStreet, BillingCity, BillingState, BillingPostalCode, BillingCountry, BillingLatitude & BillingLongitude). Considerations & Limitations w.r.t to compound fields is documented here.

If you are trying to perform data clean-up in your org, consider using one of the many apps in app-exchange or the one suggested by @AdrianLarson in his comment. Otherwise, if you need this for a specific use case, filter out the compound field in your code using something like this:

for(String s : fieldlist)
{
    // Get the field type
    String fieldType = get_F_name.get(s).getDescribe().getType().name();

    if(fieldType != 'ADDRESS' // Check for Address field
       || fieldType != 'TEXTAREA' // Check for Description field
       || fieldType != 'PHONE' // Check for Phone field
    ){
        // Write your code here
    }
}

In this code snippet, get_F_name.get(s).getDescribe().getType().name() returns one of the enum values mentioned here (in upper case). And this snippet is just an example, you might have other field types that not filterable (or non-indexed fields or standard fields that cannot be filtered upon) and you will have to tweak your code logic to accommodate all of that. There could also be fields that are not compound fields yet not filterable, so you will have to write logic to handle them appropriately based on your business requirements. For example, description field is of type text area and having value in it might not be mandatory requirement for your business. Therefore, you will first have to identify the fields that you want to consider and build your code accordingly.

Also, you don't need additional maps and lists to get the field type, you may write simpler code as given above.