11

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}}

7

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)?)

  • 2
    Hmmm, so that works. For my use case though I actually need to use the get() syntax for all of my fields as they're determined dynamically at run time. If you cast that Contact to an SObject and try .get('Id') it'll say that Id is an invalid field! – Matt Lacey Sep 6 '16 at 7:55
  • @LaceySnr Yeah looks fragile. See the PS - a pain to convert your string field names into their SObjectField equivalent but doable. – Keith C Sep 6 '16 at 8:07
  • So are you saying you have to use .get(SObjectField) to get non-aliased fields when you use FORMAT? – Adrian Larson Sep 6 '16 at 12:02
  • @AdrianLarson Based on my one test case - yes. – Keith C Sep 6 '16 at 12:26
  • 1
    Hmmmm, I might have a look at this approach, but the added difficulty is that the object itself is also dynamic. Something tells me it'd be easier to format the currency field myself :) – Matt Lacey Sep 6 '16 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.