Appropriately Creating Relationships between Salesforce Objects in Tableau
Our company uses Salesforce with an added module called FieldFX that is integrated with Salesforce.
I have been using PowerBI for my visualization purposes, and I have been spoiled by the fact that Calculated/Formula fields in Salesforce are easily imported into PowerBI. This, however, is not the case with Tableau forcing me to re-create the calculated fields.
I ran into a subtle problem however, and I am not sure how to approach it:
We have our main Account object with a field called: Account ID - this houses all of our accounts including clients, (our) districts, division, and regions.
Within our custom -FieldFX- module another Salesforce Object was created and it is call the Job Object.
This Job Object has multiple fields, but the link back to the Account Object is contained within two fields (Clients and Location) which essentially are "Lookups" - that looks up in the Account Object and retrieves the Salesforce API (unique number) and stores them in either Clients (if they are our customers) or Location (if it is a district of ours) <- This is done by salesforce itself.
In PowerBI, these fields come straight out of the connection and they work fantastic showing both Location and Clients.
The problems lie within Tableau itself...
I can directly import the Account Object and the Job Object, however both Location and Client fields do exist in the Job object, but they contain the (unique) salesforce API numbers only. The problem is that both Location and Client API numbers exists within Account ID!! I can't use a field twice to create a relationship; meaning:
I can't create a relationship:
[Account] [Job] Account ID <-> Location Account ID <-> Client
When I join the tables...
I love the power and processing of Tableau, but it does NOT play nice with salesforce.
Can anyone who has run into similar problems please help me withthis?
EDIT: Another major problem that exists is: even if I am able to create a relationship between two tables by choosing either Client or Location there is no field that calls a lookup from the parent table so I see
Office 0011a0000041v6MAAQ <- from the Job Object 0011a0000041v6MAAR <- from the Job Object
Office Bossier City, LA <- from the Job Object Williston, ND <- from the Job Object