DescribeSObjectResult.Fields contains fields that do not appear to exist?

Question

I am using the force.com SOAP API (Partner WSDL) to query one of our Salesforce orgs and retrieve a list of all objects and all fields of each object.

This seemed pretty simple as I could quite easily retrieve a DescribeGlobalResult object which contained a collection of 'sObjects', I could then call DescribeSObject for each object to retrieve its object representation (an sObject object). Each sObject object contained a collection of Fields, perfect! :D... NO!

Oddly some of the fields contained in the field collections do not appear to exist in the targeted SalesForce org. I guess maybe these fields have been deleted or something(?). Problem I am having is distinguishing which fields actually exist and which ones do not. The Field object does not have a 'Queryable' or 'isDeleted' property. I tried selecting only fields where there byte size was 0 - but then realised what byte size was referring to :(

If it helps to know why I am trying to do this... I am attempting to build SOQL select all queries, since SOQL does not support * I have to find each field name for each object before building the query. I have done a fair bit of googling and trawling through SF's 'documentation' ... but I have found very little useful info and lot's of pompous PR telling me how great the platform is (descends into frustrated rant).

NOTE - Can we please focus on the question of... How to determine if a field exists/has been deleted/can be queried? If this is not possible or I should not be recieving these ghost fields then please say. I am aware of the flaws in the spec and the alternate approaches... I'll save those for a programmers.stackexchange.com/ question :)

CODE -

    // Please hold off on flaming for the slow, clumsy, non-defensive code - this is just an example
    public void ShowSEMYCode()
    {
        SFAPI.SFConnection sfConn = new Salesforce.API.SFConnection();
        // The above would log me in and convert any exceptions into hateful messages

        // Get global description object which contains a collection of sObjects
        SForce.DescribeGlobalResult globalResult = sfConn.describeGlobal();

        // For each sObject in the collection...
        foreach (SForce.DescribeGlobalSObjectResult globalSObj in globalResult.sobjects)
        {
            // retrieve a DescribeSObjectResult object using SObject name
            SForce.DescribeSObjectResult sObj = sfConn.describeSObject(globalSObj.name);
            List<string> fieldNames = new List<string>();

            // For each field ijn sObject.Fields collection....
            foreach (SForce.Field field in sObj.fields)
            {
                // Add its name to my list
                fieldNames.Add(field.name); // I checked - the name proerty seems to be the only property guaranteed to display the API name rather than some friendly name/label.
            }
            // Here i would simply call a method to convert my collection of column names into a SOQL string 
            // (eg: SELECT ColName1, colName2, colNam3__C FROM sObjectName) simples
        }
    }

DescribeSObjectResult class

public DescribeSObjectResult describeSObject(string sObjectType) 
{
    object[] results = this.Invoke("describeSObject", new object[] {sObjectType});
    return ((DescribeSObjectResult)(results[0]));
}

Thanks in advance.

Jamie

Examples

Object:- Account

  - Id
    - IsDeleted
    - MasterRecordId
    - Name
    - Type
    - RecordTypeId
    - ParentId
    - BillingStreet
    - BillingCity
    - BillingState
    - BillingPostalCode
    - BillingCountry
    - BillingLatitude
    - BillingLongitude
    - ShippingStreet
    - ShippingCity
    - ShippingState
    - ShippingPostalCode
    - ShippingCountry
    - ShippingLatitude  <- Phantom Field!
    - ShippingLongitude
    - Phone
    - Fax
    - Website
  ...
  ...

Object:- Idea

  - Id
    - IsDeleted
    - Title
    - CurrencyIsoCode
    - RecordTypeId
    - CreatedDate
    - CreatedById
    - LastModifiedDate
    - LastModifiedById
    - SystemModstamp
    - LastViewedDate  <- Ghost Field
    - LastReferencedDate
    - CommunityId

Object:- ApexPage

  - Id
    - NamespacePrefix
    - Name
    - ApiVersion
    - MasterLabel
    - Description
    - ControllerType
    - ControllerKey
    - IsAvailableInTouch
    - IsConfirmationTokenRequired <- Duff 
    - Markup
    - CreatedDate
    - CreatedById

Answer 1

From a general stand point, salesforce.com advises against querying more fields than you need due to performance issues, not to mention that there is an upper limit on the maximum size of a query.

That aside, sometimes you do need all fields, because you're exporting, migrating, deduplicating, or many other reasons. Usually, though, it would be better to split the functionality into two calls (for performance reasons): (1) select id from sourceobject where ..., then (2) retrieve(sourceobject, queryresultids, fields).

Regardless, using one API call or two, you still need to know which fields are which. This is where isAccessible comes into play. When you describe an object, each field that the user can currently access will have isAccessible set to true. When false, the user can't query against that field.

ShippingLatitude, by the way, isn't a phantom field; it is a sub-field of the UI element "shipping address", which has seven fields (street, city, state, postal code, country, latitude, and longitude). The UI is prone to showing some fields as a single field set, in particular address fields. You can't see it in the UI/setup screen, but that doesn't mean it doesn't exist. Assuming the user has "read" access to "Shipping Address", they will have access to the seven fields mentioned previously.

If your query is failing, check the field level security for the address field in the UI. Most likely, you'll find that the "field" is hidden, disabling all of those fields. Even system administrators can't violate field level visibility, so be sure that it is set appropriately. Administrators can describe all fields, however, which is why it is misleading to assume that just because a describe shows a field as available means the administrator has permission to query it.