Skip to content


How to link multiple tables in Salesforce to retrieve bulk data with Smartconnect?

Beat asked 5 years ago
Hi All,
I’ve been recently mandated from our Sales Dept to help them integrate data from SalesForce CRM with our Dynamics GP and vice-versa..
For now I’m just trying to wrap my head around that thing (SF CRM) and by the sake of god, cannot seem to be able to find a few to make a simple join between a few tables, as I’d do in SQL server.. that concept doesn’t simply exist in SF..
How can I retrieve data from various fields, considering that it is located in the following tables : Account – Asset – Product – Account Owner..
It has to be noted that those tables might be customized as far as I could grasp from the SF users.. Of course, there is also a filter that needs to be applied on the Asset status and an ‘active’ Asset field (??).
Another query should retrieve data from Account – Account Note, using some filters too..
Soon as I try to link more than 2 tables, I get an error message in SC when trying to validate the query : Object reference not set to an instance of an object.
Trying to preview the data in the query builder, returns an error like ‘INVALIDE TYPE: Didn’t understand relationship ‘Product2__r’ in the FROM part of the query call…
but the thing is that this field name doesn’t show up anywhere in the query builder/editor.. Where do I find that relationship names ?
Thanks in advance for any help.
Ethan Sorenson Staff replied 5 years ago

I would check your relationship to the Account Owner table. This is a custom object in SalesForce, and it is possible there is something wrong with the relationship. If you are still unable to figure it out I would recommend contacting as this is likely an instance specific issue.

Beat replied 5 years ago

Thanks Ethan,
I had a discussion yesterday with Chris and he showed me how to get around the limiation of 2 tables by using multi-source connection to SalesForce and then link the resulting sources together. That is awesome and I wasn’t aware about this capability of SC.. He told me also that SC 2017 had just been release a few weeks back.

@GP_Beat replied 5 years ago

One thing I noticed when dealing with Bulk data load from Salesforce is that user could anytime change/add fields in their tables in SF, thus ending up in SC with new fields that would not be mapped to the endpoint.. Is there a way to get notified by the map (if scheduled) that new fields have been added to a source (SF table) ? Otherwise how would we ever know there are new data fields coming along.. ? somehow this is one of the weakness of such system, that any poweruser can add/remove/change custom objects in the tables.. kind of messy & uncontrolled environment.

If you would like to submit an answer or comment, please sign in to the eOne portal.