[SalesForce] Accessing Multiple Fields From a SOQL Query Using FORMAT()

The Setup

I've got a horrible feeling I'm being rather dumb and can't see the wood for the trees here, but how do you get more than one field returned when using the SOQL FORMAT() function?

The documentation suggests that it can be used in conjunction with other fields:

The FORMAT function supports aliasing. In addition, aliasing is required when the query includes the same field multiple times. For example:

SELECT Id, LastModifiedDate, FORMAT(LastModifiedDate) formattedDate FROM Account

Yet they neglect to mention what the Type of data the query returns. Because it's using an alias you'd expect something akin to an AggregateResult but the compiler indicates that the type is a list of the object in question.

Experimenting

I've tried running the following code using execute anonymous to see what's going on:

// Case 1: the `Amount__c` field as a regular field plus an aliased version
List<Transaction__c> t = (List<Transaction__c>)[select Id, Amount__c, Format(Amount__c) amt from Transaction__c];
System.debug(t);
    
// Case 2: the `Amount__c` field using `FORMAT()` with no alias
t = (List<Transaction__c>)[select Id, Format(Amount__c) from Transaction__c];
System.debug(t);
    
// Case 3: the `Amount__c` field not using `FORMAT()` for the sake of my sanity 
t = (List<Transaction__c>)[select Id, Amount__c from Transaction__c];
System.debug(t);

The three corresponding output lines are:

DEBUG|(Transaction__c:{amt=£42.00}, Transaction__c:{amt=£80.00}, Transaction__c:{amt=£33.00})

DEBUG|(Transaction__c:{Id=a0724000006iAz4AAE, Amount__c=42.00}, Transaction__c:{Id=a0724000006iAyzAAE, Amount__c=80.00}, Transaction__c:{Id=a072400000NEoOhAAL, Amount__c=33.00})

DEBUG|(Transaction__c:{Id=a0724000006iAz4AAE, Amount__c=42.00}, Transaction__c:{Id=a0724000006iAyzAAE, Amount__c=80.00}, Transaction__c:{Id=a072400000NEoOhAAL, Amount__c=33.00})

So in case 1 I get the formatted value but nothing else, in case 2 the formatting is nowhere to be seen, and in 3 I get what I'd expect (still sane).

Where it gets really interesting is the variable assignment lines that are included in the logs. These seem to show vectors where the fields are all available, so for case 1, I see this:

VARIABLE_ASSIGNMENT [1]|t|{"s":1,"v":[{"s":2,"v":{"Id":"a0724000006iAz4AAE","Amount__c":42.00,"amt":"£42.00"}},{"s":3,"v":{"Id":"a0724000006iAyzAAE","Amount__c":80.00,"amt":"£80.00"}},{"s":4,"v":{"Id":"a072400000NEoOhAAL","Amount__c":33.00,"amt":"£33.00"}}]}|0x2f4c8335

I've tried assigning to List<Object> and List<SObject> with no success, and took a wild stab with Map<String, Object> with no luck. I find it hard to believe that case 1 is functioning as designed, so what am I missing? Am I missing something obvious or does this appear to be a platform bug?

A Hacky Workaround – Not an Answer

Through some crazy gymnastics I can get a Map<String, Object> representing the full results which is enough for my particular use case right now, but it doesn't really answer the question:

List<Object> o = (List<Object>)JSON.deserializeUntyped(JSON.serialize([select Id, Amount__c, Format(Amount__c) amt from Transaction__c]));
for(Object obj : o)
{
  Map<String, Object> sobj = (Map<String, Object>)obj;
  System.debug(sobj);
}

16:27:21:009 USER_DEBUG [5]|DEBUG|{Amount__c=42.00, Id=a0724000006iAz4AAE, amt=£42.00, attributes={type=Transaction__c, url=/services/data/v37.0/sobjects/Transaction__c/a0724000006iAz4AAE}}

Best Answer

It looks to me that this FORMAT() documentation fails to mention that the formatted value can only be obtained using a get of the alias. This produces the expected result for the formatted values and confirms that the type returned by FORMAT is String:

Contact c = [
        select Name, FORMAT(Birthdate) bb, FORMAT(CreatedDate) cc
        from Contact
        where Birthdate !=null
        limit 1
        ];
System.debug('>>> ' + c.Name + ' ' + c.get('bb') + ' ' + c.get('cc'));
System.debug('>>> ' + (c.get('bb') instanceof String));

I get a "duplicate field selected" error if I also include the raw Birthdate field in the query.

PS

Be aware though that there are other gotchas - see LaceySnr's comment about using get('Id') failing if using the the SObject type. Bizarrely though using the SObjectField type works around the problem:

SObject c = [
        select Id, Name, FORMAT(Birthdate) bb, FORMAT(CreatedDate) cc
        from Contact
        where Birthdate !=null
        limit 1
        ];
System.debug('>>> ' + c.get(Contact.Id) + ' ' + c.get(Contact.Name) + c.get('bb'));

(Maybe the underlying implementation is a decorator that fails to forward the get(String) but does forward the get(SObjectField)?)

Related Topic