Forum Discussion

ivanoharris's avatar
ivanoharris
Regular Visitor
9 years ago
Solved

salesforce object connector - Lookup relationship support ?

Does anyone know if lookup relationships are supported in the Salesforce Object Connector.  For example, I'd like to query the object Account and pull in Name from a lookup object called Market.  Typ...
  • ivanoharris's avatar
    ivanoharris
    9 years ago

    OK, after playing around with this some more I found a solution and it's actually quite simple and functional.  I just couldn't find any documentation on the subject.

     

    For reference here is how you can query on lookup relationship fields using the Salesforce Object connector:

     

    1 - When picking Salesforce Object connector, on the screen where you select the object you just check the option at the bottom of the window entitled "Include Relationship Columns"

    2 - Click on "Choose Columns" and scroll to the bottom where all of the lookup fields are located.  They will end in __r, such as Market__r.  Select the lookup relationship you'd like to use

    3 - From the preview window, click on the hierarchy looking button on the column header for the lookup relationship.  This will pop out a window where you can select the relationship fields in question, such as Market__r.Name

     

    Here's a screenshot for reference and the Dax it generates for reference:

     

     

    Dax from Advanced Editor:

     

    let
    Source = Salesforce.Data("https://login.salesforce.com/", [CreateNavigationProperties = true]),
    Account1 = Source{[Name="Account"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Account1,{"Name", "RecordTypeId", "Market__r"}),
    #"Expanded Market__r1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Market__r", {"Name"}, {"Market__r.Name"}),
    #"Expanded Market__r" = Table.ExpandRecordColumn(#"Expanded Market__r1", "Market__r", {"Id", "Name", "Parent_Market__c", "Geocode__c", "Geocode_Address__c", "Parent_Market__r"}, {"Market__r.Id", "Market__r.Name", "Market__r.Parent_Market__c", "Market__r.Geocode__c", "Market__r.Geocode_Address__c", "Market__r.Parent_Market__r"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Market__r", each ([RecordTypeId] = "012130000018FFPAA2"))
    in
    #"Filtered Rows"