Hi all,
I posted a similar followup to a years-old question asked by someone else, but I need some clarity from the community.
Our use case: When we implemented Salesforce back in 2012/2013 we had several custom objects related to the Contact. These are read-only, for reporting/viewing purposes only. A scheduled integration/automation "pushes" recent changes (audited on the database server level) from our Student Information System to these custom objects (they send upsert OR delete calls via SOAP API, based on a calculated "external ID" key field).
As can be expected, for various reasons we have had data become "out of sync" or "stale" in relation to our SIS. I have been making a strong push to move to External Objects. This would provide real-time viewing of the related data from our SIS. I coded an oData 2.0 data source from scratch to expose the tables/views and the related external objects. I was able to consume the oData service in Salesforce "External Data Sources." I modified the resulting external objects as needed (field length, type, lookup relationships, etc.). I added the related lists to Contact page layouts. All was looking great.
Then the issue of reporting came up. Some associates report on this data, and filter on it. ONe of thse custom objects is called "Service Indicator."
In our Environment, EnrollmentRx is our "main" App Exchange package and their primary object is "Application". This object has a lookup relationship to Contact - a master/detail relationship to be exact. So one contact can have many applications. Contact is required.
Auto-created Report type: Applications with Contacts.
So with the custom object, which also had a master/detail relationship with Contact, the team was able to create a new report type Applications with Contacts with Service Indicators. A must have B, B must have C. The team also created reports with filters like "Application Status = Enrolled" and "Service indicator Code = XXX". This would produce a report of only applications with that status, only service indicators with that code, tied together by the Contact relationship.
Now they tried creating a new report type with the external object (call it Ext Service Indicator) as the C record instead. Again, the same way A must have B and B must have C. Things looked promising at first - the Application Status filters worked as normal, as did any other filters on Contact or Application.
However, filters on Ext Service Indicator are not applied before the "join" is done behind the scenes. So if there are ANY Ext Service Indicator records related to the Contact, a row will be returned in the report, with null/placeholder values for any of the Ext Service Indicator fields in the report.
This is clearly different than how the filters work with "regular" custom objects. I tried modifying the Contact lookup on the external object to be indirect lookup, or a "regular" lookup (we do store the SFDC ContactID in our SIS when we first pull over the applicant/application data from Salesforce). It made no difference.
I was thinking there must be a way to make this work. We can't do master/detail lookups/relationships on external objects - unless there is something I missed and if so please let me know if I am wrong. Here are some other alternatives I tried:
Ext Service Indicator as primary object ("A" record) in report type: I can't use Contact or Application as the B record as ther is no parent/child relationship. So I included Contact fields via the Contact lookup, and then Applciation records via the "Active Application" lookup on the Contact (this is not optimal but I wanted to see what would happen). This returned an error because the row count for the various subsets was too high.
Joined report - Report type Ext Service Indicator the only object. Tried joining the Applications with Contacts report type, and also just the Contact report type. Apparently there is no common field, though both have a lookup to Contact.
I am at a loss and short of creating separate external objects that match specific subsets of pre-filtered values, which would be nightmarish to maintain, I cannot think of any workable solution. I had advocated for external objects for a long time and the college was willing to give it a shot. Now I find myself needing to explain yet one more "quirk" preventing adoption. We are charged for storage space for copying data into custom objects, so external objects would allow us to delate that data and free up some storage space. This was another "selling" point on my part. We do not want to store duplicate/copied data in our CRM unless absolutely necessary.
I am honestly at a point where my only recommendation to the team is to report directly from our SIS or some other external reporting tool if possible, unless anyone here has been able to find a workaround to filtering on external objects when they are not the "A" record in the report definition. Or if you have some up with some way to use joined reports in a way that enforces filters on external objects and correctly links the two and knocks out non-matching rows (by definition, in my example, all Applications with have a Contact, and all Service Indicators will have a Contact). I am a bit concerned when I read that even if I had a joined report it would still be similar to an "outer join" so I had to start with Ext Service Indicator as the primary object and put me right back in the same quandry.
I look forward to hearing from any of you who have found a workable solution, using the delivered Report Type / Reports functionality in Salesforce. Purchasing third-party analytics/reporting tools from the App Exchange is not an option for us, just FYI.
Thanks so much for any insights you may have, and pointers as to what I am doing incorrtectly if applicable. I think the issue is there is not a "master-detail" relationship as I think that would only make sense with real objects (as there are rules that concern deleting parents will delete children, etc. and this really would not apply to externally-hosted data).
Peace,
Jim
#Appexchage Apps #Reporting #Reports & Dashboards
