Querying BigObject in SOQL Query tool yields incorrect exception; query in Developer Console works fine
Execute this query in IC2 SOQL Query pane:
SELECT FieldHistoryType FROM FieldHistoryArchive WHERE FieldHistoryType = 'Contact'
RESULT:
org.apache.cxf.binding.soap.SoapFault: BIG_OBJECT_UNSUPPORTED_OPERATION: Aggregate functions not supported
Execute exact same query in Developer Console and results are returned
FieldHistoryArchive is a BigObject. Doc on how to query
Dev console at V50; IC2 connection at V50; latest IC2
Comments (21)
-
repo owner -
repo owner Eric, I'm going to ask a dumb question so please bear with me. I've created an object with Track Field History enabled. I've created instances of that object. While I have the
*__History
object, I don't haveFieldHistoryArchive
in the org. To save me from having to dig around, is there something specific I need to do in the org to enable that? Thanks! -
reporter Scott -
FieldHistoryArchive
comes with the Field Audit Trail feature of Salesforce Shield. And, just to make testing even more fun, it appears that the records inFieldHistoryArchive
get populated asynchronously (and I don't know what the delay is except that it is more than minutes)FieldHistoryArchive
is a Big Object so the behavior I observed might happen on any Big Object, not just the OOTBFieldHistoryArchive
. In my org, we’re not using any custom Big Objects -
repo owner Hah! Yeah, it occurred to me that this was a Shield feature a few minutes after I sent that question. If memory serves, though, the content of the
_History
object is replicated into theFieldHistoryArchive
Big Object, as you said, async. So one way or the other you must configure a standard or custom object for field history tracking, right? Then you also need to have Shield enabled in the org to save off the long-lasting history securely. Or am I misremembering?Let me see if I can set things up to try to reproduce this...
-
reporter So one way or the other you must configure a standard or custom object for field history tracking, right?
YES
Then you also need to have Shield enabled in the org to save off the long-lasting history securely.
YES
-
repo owner Thanks. It's funny how things can get evicted from your mental cache. The last company where I worked before going 100% full-time on IC2 was in the health care space and I helped set things up for Shield platform encryption including field history tracking. It just took a minute to pull back together those braincells. I think I almost have it set up properly again and hopefully will either reproduce what you're seeing or minimally have more insight into what might be happening.
-
repo owner Eric, are you by chance doing this in a scratch org? That's what I'm trying and it's failing when trying to set the history retention policy. If you're in a scratch org, what org shape option(s) are you using to allow that? Otherwise I'm going to need to see if I can get Salesforce support to enable it in one of my orgs before I can truly try to reproduce this.
-
reporter I’m using a Sandbox, not scratch org. Doesn’t a new Developer Edition come with Shield feature license? Seems to me there is a trailhead on Shield that requires one to enable / configure in a playground org (though the Trailhead is on encryption) https://trailhead.salesforce.com/content/learn/modules/spe_admins/spe_admins_set_up?trail_id=shield
I could tell stories about Shield encryption and all the hassles it creates once you start encrypting fields
-
repo owner I'm trying to remember if we had to have Shield enabled by support or if we could enable it ourselves in DE orgs. I have a few so I'll try that next.
And yes, we had an interesting time getting Shield encryption going, especially with deterministic encryption since we needed to be able to filter/order by some of the data. I certainly appreciate the complexity of the problem, but man, it's far from a transparent solution.
-
reporter when one encrypts Account.Name or Contact.Name (as we had to), you suddenly discover that you can’t install a lot of appexchange packages because they have coded testmethods using filter or order by on Account and Contact objects! Doug Ayers' excellent package is uninstallable for us and we had to wait a year for Apttus to upgrade their Contract Management package. I also had to manually rewrite some testmethods in the fflib unlocked package. And the list goes on …
-
repo owner Yeah, without going into significant detail, I've run into similar. Our packages were installed in a customer org with some other packages that expected to be able to filter/order by some standard object fields that needed to be encrypted according to HIPAA. Made from some interesting discussions...
I'm going to keep trying to reproduce this today. I'll let you know how it goes.
-
repo owner Eric, I verified that I'd set up this scratch org as indicated in that Trailhead module. The issue I'm hitting is with the propagation of field history from the org's
*_History
into theFieldHistoryArchive
BigObject. The latter doesn't exist. Based on everything I can find, it seems that I need to grant my user the "Retain Field History" permission--which I don't see anywhere--and I need to set up the object with a history retention policy, and when I attempt to deploy that it fails with "Cannot create a Retention Policy in this organization". Do you know if you've done both of those in your org? Or am I overcomplicating this and there's some other way thatFieldHistoryArchive
ends up in the org? -
reporter boy – I didn’t think this was going to be so involved … I’m sorry it is causing you grief.
- A retention policy shouldn't be necessary as we just took the defaults - again, this is in a sandbox/prod org world; no scratch. We merely licensed the whole suite of Shield products - event monitoring, field audit trail, platform encryption and everything was set up automatically
Retain Field History
shows up as a General User Permission and is checked true for ourSystem Administrator
profile (uneditable)
NOTE Once Field Audit Trail is enabled,
HistoryRetentionPolicy
is automatically set on the supported objects. By default, data is archived after 18 months in a production organization, after one month in a sandbox organization, and all archived data is stored for 10 years. The default retention policy is not included when retrieving the object’s definition through the Metadata API. Only custom retention policies are retrieved along with the object definition.so, from the above, it would appear it takes 30 days for history to archive.
3. If you create a Dev Edition org (not scratch) - do you see
FieldHistoryArchive
? (i.e. perhaps the issue is scratch?) -
repo owner Okay. That permission definitely doesn't exist in this scratch org. I'll create a new DE org tomorrow and try to reproduce this there. Hopefully that will go better. I'll keep you posted.
-
repo owner Okay, I created a DE org and have configured/populated it with the same stuff I had in the scratch org. Unfortunately I'm having the exact same issues where
FieldHistoryArchive
does not exist in this org, and when I try to set the history retention policy (which is part of the Salesforce documentation for how to get this all working), the deployment fails with the same "Cannot create a Retention Policy in this organization". Do you know of any other step you had to take to enableFieldHistoryArchive
in the org? Obviously that's going to be critical to reproducing, understanding, and (hopefully) fixing this. -
reporter here’s some good news - I can reproduce the problem using any Big Object - you don't need to fuss with
FieldHistoryArchive
1 - Create a Big Object
MyBigObject
in Setup; add a single field calledMyKey
and then a single index calledMyIndex
that usesMyKey
. Add a permission set that gives read access to this object and assign to yourself.2 - Run this query in Dev Console -
select id, MyKey__c from MyBigObject__b
- it will return 0 records3 - Run same query in IC2 SOQL tool -
select id, MyKey__c from MyBigObject__b
RESULT =org.apache.cxf.binding.soap.SoapFault: BIG_OBJECT_UNSUPPORTED_OPERATION: Aggregate functions not supported
as for your questions…
Retention policies are not required to be setup; there are defaults – see https://help.salesforce.com/articleView?id=field_history_workflow_examples.htm&type=5This example demonstrates how to set a field history data retention policy using Metadata API. Edit the metadata only if you want to override the default policy values (18 months of production storage and 10 years of archive storage).
There was no special action we took to get this enabled - we licensed the feature. I looked at some of my Trailhead orgs and other dev editions I had lying around and they don’t have
FieldHistoryArchive
either. I’m going to surmise you’ll need to contact SFDC Support (oh fun!) and have them enable it in your Dev Edition org. What’s interesting in our PROD org is that you don't even see Shield as a feature license listed in `Company Information` - perhaps suggesting that SDFC Support is the only route as there is no way I can directly confirm in our PROD org that we licensed the feature (except by looking at our renewal subscription in the SFDC Store.For information about enabling Field Audit Trail, contact your Salesforce representative.
-
repo owner That worked, Eric, and it should be a very simple fix. I should have thought about this earlier, but it's the pre-query check for row count that's causing the issue, not the actual query. I'll just exempt queries against Big Objects from that check. Thanks for helping to make this reproducible!
-
repo owner Fix committed for inclusion in the next release. Thanks again for helping with the diagnostics!
-
reporter Not sure how you identify a BigObject; obviously anything ending in `__b` but `FieldHistoryArchive` is an OOTB big object and there may be others I’m (blissfully) unaware of
ah, yes, I remember - real time event monitoring history are big objects - there are many of these, all OOTB
https://help.salesforce.com/articleView?id=real_time_event_monitoring_storage.htm&type=5
-
repo owner Yeah, that might be tricky. I looked at the metadata and there's nothing distinct except for perhaps the ID prefix. I'll see if I can definitively identify which ones are BigObjects. Worst case (aka, likely) scenario I'll see whether the row count check failed specifically because it's not aggregatable and skip that check.
-
repo owner - changed status to resolved
Delivered in 2.1.5.4.
- Log in to comment
Hmmmm...I'm not sure why that would happen unless the Dev Console is using a different API to run this query. I would assume they're both just using
PartnerApi.query()
. Let me investigate a bit...