How to link multiple tables in Salesforce to retrieve bulk data with Smartconnect?
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.
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 firstname.lastname@example.org as this is likely an instance specific issue.
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.
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.