WHERE condition in FeedTrackedChanges subquery

Question

I'm trying to get information about when a specific field is changes in the Contact table using FeedTrackedChanges.
I cannot use the list format that the table returns for FieldName, OldValue and NewValue since my development environment doesn't allow for a lot of flexibility.
This is as far as I could get:
SELECT
    Id,
    CreatedDate,
    (SELECT
        FieldName,
        OldValue,
        NewValue
    FROM
        FeedTrackedChanges)
FROM
    ContactFeed

What I've tried so far include:
SELECT
    Id,
    CreatedDate,
    (SELECT
        FieldName,
        OldValue,
        NewValue
    FROM
        FeedTrackedChanges
    WHERE
        FieldName = 'Email')
FROM
    ContactFeed

java.io.IOException: [InvalidFieldFault [ApiQueryFault [ApiFault  exceptionCode='INVALID_FIELD'
 exceptionMessage='
        FieldName = 'Email')
        ^
ERROR at Row:11:Column:9
field 'FieldName' can not be filtered in a query call'
 extendedErrorDetails='{[0]}'
]
 row='11'
 column='9'
]
]

And...
SELECT
    Id,
    CreatedDate,
    (SELECT
        FieldName,
        OldValue,
        NewValue
    FROM
        FeedTrackedChanges)
FROM
    ContactFeed
WHERE
    FeedTrackedChanges.FieldName = 'Email'

java.io.IOException: [InvalidFieldFault [ApiQueryFault [ApiFault  exceptionCode='INVALID_FIELD'
 exceptionMessage='
    FeedTrackedChanges.FieldName = 'Email'
    ^
ERROR at Row:13:Column:5
Didn't understand relationship 'FeedTrackedChanges' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.'
 extendedErrorDetails='{[0]}'
]
 row='13'
 column='5'
]
]

Any help or insight is greatly appreciated!
Thank you!

Derek F · Accepted Answer

The first error message you got pretty much tells you what you're looking for here.

FieldName on FeedTrackedChange cannot be used as a filter in a query (i.e. cannot be used in the WHERE clause of the query). This is corroborated by the SOAP API documentation on FeedTrackedChange

FieldName

Type
string

Properties
Group, Sort

If you could use it as a filter, the properties would include "filter"

Your second example query is incorrect because you cannot traverse downwards in the object hierarchy (i.e. from a given parent record to its (potentially) many child records). <child relationship name>.<child field name> is simply not valid syntax.

Your options here are:

To do the filtering yourself, which is to say you loop over your query result set, then have a nested loop to iterate over the List<FeedTrackedChange> inside of each ContactFeed record
Base your query around FeedTrackedChange instead of ContactFeed. Instead of a subquery to traverse down the relationship hierarchy, you'd use dot-notation to traverse up the hierarchy (e.g. SELECT FeedItemId, FeedItem.CreatedDate FROM ContactFeed WHERE FeedItemId IN :<collection of contactFeed ids>)

At time of writing, I don't think you've provided enough details to inform us about why working with child records is an issue. My best guess is that you're working in Java and using some connector/library to bring in query results from SFDC. If, for some reason, you truly are unable to iterate over the embedded collection of child records, then you need to change the data you're retrieving so that you can filter the resulting FeedTrackedChange records in code rather than as a part of the query.

WHERE condition in FeedTrackedChanges subquery

One Answer

Add your own answers!

Ask a Question